August 11, 2006 at 12:11 pm
Hi,
I try to tune a slow query:
SELECT 1 FROM tab1
WHERE col1 = ?
AND col2 = ?
AND col3 = ?
AND (SELECT COUNT(col1) FROM tab2 a
WHERE a.col4 <> '10'
AND a.col5 IN ('AB', 'BC', 'CD')
AND a.col1 <> tab1.col1
AND a.col5 = (SELECT b.col5 FROM tab2 b WHERE b.col1 = tab1.col1)
AND EXISTS (SELECT 1 FROM tab1 c
WHERE c.col1 = a.col1
AND c.col2 = 1)
) = 0
tab2 is parent of tab1 (1 to many)
The problem is the first part of the query:
SELECT 1 FROM tab1
WHERE col1 = ?
AND col2 = ?
AND col3 = ?
is fixed and can't be changed, in the rest of the query, I have to use tab2 two times and tab1 another time, how can I make this query runs faster?
August 11, 2006 at 1:24 pm
First thing you need to check here is appropriate
clustered and non clusetered index.
You need to consider the size of your table and
then if feasible create a non clustered covering index
that include col1,col2 and col3 in first query.
For Second and third Select also check and make sure
you have index on column in where clause.
This should be your first step and should definitely
help boost performance.
Hope this helps,
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
August 11, 2006 at 1:39 pm
I find multiple nested subqueries tends to kill performance very quickly. Oftentimes you can rewrite them with clever joins. Though Sameer is correct, covering your queries with indexes is a good place to start. Also look at your query execution plan and see which part is causing the most trouble and think about what you can do to rewrite that.
August 11, 2006 at 2:20 pm
Aaron I agree with your thoughts.
use of table variable or temptable for subqueries is good option. I thought I will come to that level in next post.
Thanks,
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply