September 23, 2004 at 6:06 pm
I have been given an SQL Select statment that runs with poor performance. It has CASEs in the Select, INs in the Where, and Joins. Where do I start when trying to (1) Define what part of that query is hurting it most, (2) improve performance? Making a flat table for query access is not an option. The database load 3 million records a week.
Thanks for any feedback you can provide.
September 24, 2004 at 8:22 am
Paste the query in Query Analyzer and then hit Ctl+L, this shows the estimated execution plan for the query. You can then look at each part of the query and easily determine which is the most espensive and begin working from there.
Generally you will want indexes on any columns in your WHERE clause and on the columns in the ON clause in the JOINs.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
September 24, 2004 at 5:38 pm
Hi,
This sounds like a 'report' style query which proceses a reasonable number of rows. If the joins are used primarily to 'look up' descriptions then a significant improvement can be acheived by using derived tables.
e.g.
select MyLookup1.Description, MyLookup2.Description, etc...
from (Select code1, code2, code3 from MyBaseTable1
join MyBaseTable2 ON etc...
where MyBaseTable.SomeCol = @AParameter
) as WorkTable1
JOIN MyLookup1 on WorkTable1.code1 = MyLookup1.code1
JOIN MyLookup2 on WorkTable1.code2 = MyLookup2.code2
etc.
I hope this makes some sense. It's a bit difficult to give an example without more details. The principle here though is to do the 'main' query first (as a derived query) and do the lookups on the much smaller set of rows in teh derived query.
If you need more help, I'll need more details of the query.
P.S. the ctrl + L is a very good place to start. look for the 'high' percentages.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply