Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Optimizing Stored Procedure
Message
 
 
À
25/10/2004 15:40:56
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Autre
Divers
Thread ID:
00953428
Message ID:
00955131
Vues:
21
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--
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform