Query Performance
-
Hi guys,
What will be the correct way of structuring this query in order to maximize performance. Specific reference to order of items in where clauses, and the JOIN on portions?
Three tables
A,B,C
B assosiative entity
C.z primary key for C
Select A.this, a.that, a.etc from A WITH (NOLOCK)
inner join B ON B.x = A.x and B.y = 1
inner join C ON B.z = C.z
where
C.dtMax > @dtDate
AND A.dtJournal <= @Param1
AND A.a = 1
AND A.b = 1
AND A.c = 2
AND A.d = 1
AND A.e = 1
AND A.f <=70
AND A.g = @Param2any feedabck will be greatly appreciated!
-
the best thing you can do is to use query analyser and use the "predict execution plan" (ctrl+L) or "show execution plan" (ctrl+k) option to check out what it's doing.
Write your query in a number of ways an see if there is any change in the plan.
Do you have indexes for c.dtmax , A.dtjournal and foreignkeycolumn(s) ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
What about column "g" in table A? Use the Show Estimated Exection Plan to see if the query performing a table scan or an index scan/seek.
dab
-
You really cannot determine this without doing some execution plan analysis. Also, make sure that your database statistics are updated before doing any real analysis because this will cause the sql server optimizer to come up with the best possible plan.
Quest Software has a very slick Query Tuning interface that will re-write your queries for you and produce the fastest possible alternative. If you'd like more information go to http://www.quest.com and download TOAD for SQL Server. Contact me directly for more information.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply