question

BrettW avatar image
BrettW asked

Poor performance on query with nested AND's and OR's

Hello, I have a query that is performing very poorly. It's a select that takes 45+ minutes to run. The tables are not that large. It seems to be the WHERE clause - lots of nested AND's and OR's. Can someone help with some ideas on how to rewrite this so performance is better? Here are some vital statistics: duplicate_consumer row count: 6998 oceloc1 row count: 7397 oceloc2 row count: 1537 Here is the query (I'm using SELECT * for brevity): SELECT * FROM BrettScratch.dbo.OCELoc1 loc1 , BrettScratch.dbo.OCELoc2 loc2 , brettscratch..duplicate_consumer dc WHERE ( ( loc1.consumer_uuid = dc.consumer_uuid AND loc2.consumer_uuid = dc.b_consumer_uuid ) OR ( loc2.consumer_uuid = dc.consumer_uuid AND loc1.consumer_uuid = dc.b_consumer_uuid ) ) AND loc1.levelcare_locuscare_uuid = loc2.levelcare_locuscare_uuid AND ( ( loc1.start_date <= ISNULL(loc2.end_date, '3/3/3333') AND ISNULL(loc1.end_date, '3/3/3333') >= loc2.start_date ) OR ( loc2.start_date <= ISNULL(loc1.end_date, '3/3/3333') AND ISNULL(loc2.end_date, '3/3/3333') >= loc1.start_date ) ) AND loc1.Start_Date <= loc2.Start_Date Let me know if you need more info. Thanks in advance for any assistance. Brett
t-sqlperformancewhereorand
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I beleive your query needs re-structuring. For that you need to let us know the tables structures, some sample data and the desired output. May be someone could come up with more robust solution. Optimizing someone's code without prior knowledge of logic is often very very difficult. We can only let you know general rules, standards etc to follow, whereas, it may be needing a whole re-structuring.
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
I'd start with rewriting the query to use ANSI joins. Also make sure you have indexes on columns involved in joins and WHERE clauses. If possible - avoid using ISNULL and other scalar valued functions, because they make it harder for sql server to use indexes.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

GPO avatar image
GPO answered
I know you've use select * just for brevity, but do you know which columns in your select clause are covered by particular indexes? SELECT * or SELECT [lots of columns not included in a given index] will mean (as far as I understand it anyway) that the query analyser will revert to a table scan (going through the entire table) or lots of RID lookups and/or bookmark lookups. This will slow you down.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

pipthegeek avatar image
pipthegeek answered
I have often found that using seperate queries and UNION ALL the results instead of using OR gives better performance. ISNULL round a field will probably prevent an index being used. If it is REALLY necessary then you can consider creating a calculated column that performs the ISNULL and then index that calculated column. If you could give us a script to create the tables, any existing indexes and some sample data then you mroe likely to get some more specific answers. Also if you could explain what the query is trying to return it would be helpful.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
Fortunately, have the time to have a close look at this Starting of with, it can be easily re-structured with ANSI joins something like SELECT * FROM BrettScratch.dbo.OCELoc1 loc1 INNER JOIN BrettScratch.dbo.OCELoc2 loc2 ON loc1.levelcare_locuscare_uuid = loc2.levelcare_locuscare_uuid INNER JOIN brettscratch..duplicate_consumer dc ON ( ( loc1.consumer_uuid = dc.consumer_uuid AND loc2.consumer_uuid = dc.b_consumer_uuid ) OR ( loc2.consumer_uuid = dc.consumer_uuid AND loc1.consumer_uuid = dc.b_consumer_uuid ) ) WHERE ...... Then came the filter ( ( loc1.start_date = loc2.start_date ) OR ( loc2.start_date = loc1.start_date ) ) If we restructure above statements, then they can be re-written as (I hope I do not need a coffee) ( ( loc1.start_date = START_DATE SHOULD BE DEFINED Then loc1.Start_Date
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.