More Efficient Query
-
Can this query be executed more efficiently?
The nested loop part of the plan executes 153 times:
SELECT * FROM db..table1 t1
INNER JOIN db..table1Detail t2
ON t1.formNbr = t2.formNbr
AND t1.AdmitDate = t2.AdmitDate
WHERE t1.FormNbr IN
(
'00170942',
'01528970',
'00173700',
'01529205',
'00173982',
'01529231',
'00141747')
-
If possible, you could put the values for the IN clause into a table. Then just use that table in the FROM clause.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
The Where clause executes after the Inner join (so all records in both tables are inner joined first) then the Where clause reduces the records selected. Move the IN () statement from the WHERE clause to the ON clause to reduce the records joined.
eg
SELECT * FROM db..table1 t1
INNER JOIN db..table1Detail t2
ON t1.formNbr = t2.formNbr
AND t1.AdmitDate = t2.AdmitDate
AND t1.FormNbr IN
(
'00170942',
'01528970',
'00173700',
'01529205',
'00173982',
'01529231',
'00141747')
-
Just a note of caution: Using the ON clause to reduce the records while using an outer join does not always produce obvious results. If the record is not selected or one of the values tested in the on clause is null, the result might be produce a lot more records than desired. I find using a derived table with the where clause less confusing.
Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉 -
Another thing to consider is the indexing and number of rows involved. We have many situations where running a UNION or a CURSOR, with each query specific to a precise INDEX SELECT, improves performance.
Check the execution plan...if this is an index scan, break out the query.
Guarddata-
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply