Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Optimizing Stored Procedure
Message
 
 
To
25/10/2004 15:40:56
General information
Forum:
Microsoft SQL Server
Category:
Other
Miscellaneous
Thread ID:
00953428
Message ID:
00955131
Views:
20
David,

>How do I measure query efficiency?

That's what execution plans and tests are for.

>Often, there are multiple ways of writing a single query. For example,

>SELECT Table1.Field1
>FROM   Table1
>       INNER JOIN Table2
>       ON Table1.PK1 = Table2.FK2
>WHERE  Table2.Field2 = value1

>
>could be written,
>
>SELECT Table1.Field1
>FROM   Table1
>       INNER JOIN
>          (SELECT  Table2.Field2, Table2.FK1
>           FROM    Table2
>           WHERE   Table2.Field2 = value1)
>       ON Table1.PK1 = Table2.FK1

>
>or,
>
>SELECT Table1.Field1
>FROM   Table1
>WHERE  Table1.PK1 IN
>          (SELECT  Table2.FK1
>           FROM    Table2
>           WHERE   Table2.Field2 = value1)

>
>Are any of these more efficient than the others?


In my experience, the first one and following one are most efficient but, again, analyze execution plans an run tests.
SELECT Table1.Field1 FROM   Table1
WHERE  EXISTS (SELECT *  FROM Table2
           WHERE Table1.PK1 = Table2.FK2
              AND Table2.Field2 = value1)
>The type of query that I am having the most trouble with is when I want to select the rows in a table that don't have their primary keys listed as a foreign key in a table with which it has a 0..* relationship. I have been coding it as,
>
>SELECT Table1.Field1
>FROM   Table1
>       LEFT OUTER JOIN Table2
>       ON Table1.PK1 = Table2.PK2
>WHERE  Table2.FK1 IS NULL

>
>Is there a more efficient way to write this query? Microsoft SQL doesn't support MINUS, which would be my first inclination.


I would first try EXISTS query
SELECT Table1.Field1 FROM  Table1
  WHERE NOT EXISTS (SELECT * FROM    Table2
           WHERE   Table1.PK1 = Table2.FK2)
--sb--
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform