December 26, 2006 at 12:26 am
----SELECT open_date,id FROM AHDPROD.call_req WHERE
( ( priority = 0 OR priority = 1 OR priority = 2 ) AND
( assignee IS NOT NULL OR group_id IS NOT NULL ) AND
active_flag = 1 AND type = 'I' ) AND ( ( NOT
( category IN ( SELECT persid FROM AHDPROD.prob_ctg WHERE sym
LIKE '%LOA.%' OR sym LIKE '%HIPAA%' OR sym LIKE '%Policy and Compliance%' OR sym LIKE 'Medical%'
OR sym LIKE 'Benefits%' OR sym LIKE '%Corporate Programs%' ) ) )
AND ( NOT ( zassigned_to_group IN ( SELECT id FROM AHDPROD.ctct
WHERE id = 728814 OR id = 2206777 OR id = 13643808 OR id = 19670457 OR id = 20377430 ) ) ) OR
( assignee = 515985 ) ) ORDER BY open_date DESC ----
Hi
Can any one pls help me to fine tune this query, as of now it is taking more than 15min to exe.
Regards
Sumon
December 26, 2006 at 4:07 am
can you post the structure of AHDPROD.call_req and the indexes that are on it
MVDBA
December 26, 2006 at 5:18 am
You could try to replace the not in to not exists . With in it has to fetch all records before comparing, with exists it can cease fetching if one is found.
December 26, 2006 at 8:23 am
Try this. It might be faster.
SELECT open_date,id
FROM AHDPROD.call_req
WHERE
( priority in( 0,1,2)
AND active_flag = 1 AND type = 'I'
AND ( assignee IS NOT NULL OR group_id IS NOT NULL )
AND NOT exists
( SELECT * FROM AHDPROD.prob_ctg
WHERE AHDPROD.call_req.category = AHDPROD.prob_ctg.persid
and
(
sym LIKE '%LOA.%'
OR sym LIKE '%HIPAA%'
OR sym LIKE '%Policy and Compliance%'
OR sym LIKE 'Medical%'
OR sym LIKE 'Benefits%'
OR sym LIKE '%Corporate Programs%'
)
)
AND NOT exists ( SELECT * FROM AHDPROD.ctct
WHERE AHDPROD.call_req.zassigned_to_group = AHDPROD.ctct.id
and id in (728814,2206777,13643808,19670457,20377430)
)
)
OR ( assignee = 515985 )
ORDER BY open_date DESC
Tom
December 26, 2006 at 10:46 am
Also try to match the datatypes for index usage. If active_flag is a bit-field use active_flag = convert(bit,1)
December 26, 2006 at 2:39 pm
Probably you cannot significantly improve performance of this query.
The whole database requires refinement.
Clause like this:
LIKE '%LOA.%' OR sym LIKE '%HIPAA%' OR ...
shows that you have data mixed with other data in the same storage place.
SQL Server needs to scan through whole table looking for the string hidden somewhere in the dirt.
Multiple "OR" will make this table scan multiple.
Clause like this:
NOT ( zassigned_to_group IN ( SELECT id FROM AHDPROD.ctct
WHERE id = 728814 OR id = 2206777 OR id = 13643808 OR id = 19670457 OR id = 20377430 ) ) )
causes hash join of 2 copies of the tables involved. Both tables are being copied into tempdb (excluding the lines specified in WHERE) and joined over there with no index used.
Multiple "OR" statement against absolutely different entities tells me you don't really know what tou are looking for. Try to build the query in your native language and see if you can succeed. If you think you succeeded read that statement to your fellows and see if they can understand.
And final. Query containing "OR" in WHERE clause typically is much slower than same query with UNION.
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply