asked
Improve conditional OR (AND)
Hi... i'm trying to improve the time that takes a query with multiple inner joins, but I note that it takes longer when I put in the where clause this conditional. Is there any posilibity to improve it? AND ((a.Line = '02' AND b.Family = '01') OR (a.Line = '02' AND b.Family = '02') OR (a.Line = '03' AND b.Family = '02') OR (a.Line = '08' AND b.Family = '01') ) It takes almost 6 minutes... :( and in each table has single index for line and family. There are another indexes also. Thanks for your help!
Basically the OR clause can lead to scans. If you really need to retrieve information like that, you might try restructuring the query to use UNION ALL and recreate the query with each seperate OR element combined with the others. As to the indexes, check the execution plan. See if you're getting scans or work tables created. Just because you have an index doesn't mean it's being used appropriately.
I did some cursory tests a while ago and found that in some cases using a union all was substantially faster than using OR. But my results were not entirely consistent and I never got back to fully investigate it. I will point out that even where it does generate a real improvement in performance the UNION ALL version will be much longer and harder to understand than the OR version.
The UNION ALL is good but let's hope that index maintenance is up to date or it could get ugly.
Yeah, it will make things messier, no doubt. The key is changing a table scan, or scans, into three or four very simple seeks. It doesn't always work, depending on other parts of the query (any thing else causing scans is going to negate the work).
I would first try refactoring it to a join - because that is what makes most logical sense in the context of the problem domain, and is one of the 'four reasons for a join'. So, something like CREATE TABLE #joinValues (Line varchar(2), Family varchar(2)) CREATE UNIQUE CLUSTERED INDEX IX_joinValues_1 on #joinValues (Line, Family) INSERT INTO #joinValues (Line, Family) VALUES ('02', '01') INSERT INTO #joinValues (Line, Family) VALUES ('02', '02') INSERT INTO #joinValues (Line, Family) VALUES ('03', '02') INSERT INTO #joinValues (Line, Family) VALUES ('08', '01') SELECT * FROM table INNER JOIN #joinValues jv ON table.Line = jv.Line AND table.Family = jv.Family You may find that works a lot quicker...
