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
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.
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.
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.
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