SQL Server Performance Forum – Threads Archive

SQL Server Performance Tuning Tips Contradiction?

I read the following inhttp://www.sql-server-performance.com/transact_sql.asp –tip begin ———————-
Sometimes you might want to merge two or more sets of data resulting from two or more queries using UNION. For example: SELECT column_name1, column_name2
FROM table_name1
WHERE column_name1 = some_value
UNION
SELECT column_name1, column_name2
FROM table_name1
WHERE column_name2 = some_value This same query can be rewritten, like the following example, and when doing so, performance will be boosted: SELECT DISTINCT column_name1, column_name2
FROM table_name1
WHERE column_name1 = some_value OR column_name2 = some_value And if you can assume that neither criteria will return duplicate rows, you can even further boost the performance of this query by removing the DISTINCT clause.
–tip end———————- First of all I want to add that I think the SQL Server Performance Tuning Tips at http://www.sql-server-performance.com/transact_sql.asp is AWESOME. But I have found that many of my slow queries have ‘OR’s in them and if I do the opposite of the above and split it into UNIONs (or preferably UNION ALLs) I get dramatic performance improvement. This is such a contradiction to the tip above, can anybody explain? See my sql below. I realize the tip says "Sometimes", but it also says "performance will be boosted". Changing you this query from ORIGINAL to NEW change it from having 8,000,000 logical reads to 33,000 (I just changed the IN and OR to UNION before the first UNION statement) ORIGINAL
Select * From JobNOS Where UniqueID IN (Select JobNOSID From Ticket Where ProcessID = ‘311ACB9F-4A32-47FD-97C2-BE77A80E347A’)
OR DisplayID IN (Select Import_JobNOS From Ticket Where ProcessID = ‘311ACB9F-4A32-47FD-97C2-BE77A80E347A’)
UNION Select JobNOS.* From JobNOS
INNER JOIN Ticket ON JobNOS.CustomerJobName = Ticket.CustomerJobNumber
WHERE Ticket.ProcessID = ‘311ACB9F-4A32-47FD-97C2-BE77A80E347A’
UNION Select JobNOS.* From JobNOS
INNER JOIN Ticket ON JobNOS.CustomerJobNumber = Ticket.CustomerJobNumber
WHERE Ticket.ProcessID = ‘311ACB9F-4A32-47FD-97C2-BE77A80E347A’
NEW
Select DISTINCT JobNOS.* From JobNOS
Inner Join Ticket On Ticket.JobNOSID = JobNOS.UniqueID
Where ProcessID = ‘311ACB9F-4A32-47FD-97C2-BE77A80E347A’
UNION
Select DISTINCT JobNOS.* From JobNOS
Inner Join Ticket On Ticket.Import_JobNOS = JobNOS.DisplayID
Where ProcessID = ‘311ACB9F-4A32-47FD-97C2-BE77A80E347A’
UNION
Select JobNOS.* From JobNOS INNER JOIN Ticket ON JobNOS.CustomerJobNumber = Ticket.CustomerJobNumber WHERE Ticket.ProcessID = ‘311ACB9F-4A32-47FD-97C2-BE77A80E347A’

What are you doing? You store the same value in different columns? Are you overloading the columns? In the old query the last 2 unions look identical…and your access path is going against the same table 4 times…. You should try and do it in 1 trip….. Brett :cool:

Go with what works for you. That is why it is important to test all the tips on this website to see if they meet your specific need. I would be interesting to see the execution plans of both options to better understand what is happening. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

We aren’t doing overloading, we have records with valid records:
Ticket.JobNOSID = JobNOS.UniqueID
and we have records which have just been imported:
Ticket.Import_JobNOS = JobNOS.DisplayID In the old query the last 2 unions are not identical
ON JobNOS.CustomerJobName = Ticket.CustomerJobNumber
ON JobNOS.CustomerJobNumber = Ticket.CustomerJobNumber Thanks for the comments, though Rodney

What about executions plan, like Brad said?
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Yeah, I agree that performance tips, as great as they are, should always be tested, because at the end of the day, if you are trying to assess the efficiency/speed of a particular query, without knowing what data you are querying against, the query speed is pretty much non-deterministic. Ok, not exactly non-deterministic, but the speed of a query is a function of both the query and the underlying data – this is why there are often contradictions – what works well for one data set may not work well for another. (and probably wont !-)

Also, UNIONS result in a sorted dataset – if you have a small resulting dataset that you are UNIONing then the union may not be that expensive, but if it’s large then I would imagine the sorting part would take up more and more of the query – you can check this in the Execution plan. In my tests I find the OR is much quicker if you specify the column names (I tried 3). If I do a select * on a fairly large table (many columns) then the difference comes out very minimal but ORs still ahead slightly. Dave.

Here is my execution plans. –ORIGINAL Select * From JobNOS Where UniqueID IN (Select JobNOSID From Ticket Where ProcessBatchID = ‘A626C3F1-9C40-4FDC-BB42-89B73D777E5D’) OR DisplayID IN (Select Import_JobNOS From Ticket Where ProcessBatchID = ‘A626C3F1-9C40-4FDC-BB42-89B73D777E5D’) UNION Select JobNOS.* From JobNOS INNER JOIN Ticket ON JobNOS.CustomerJobName = Ticket.CustomerJobNumber WHERE Ticket.ProcessBatchID = ‘A626C3F1-9C40-4FDC-BB42-89B73D777E5D’ UNION Select JobNOS.* From JobNOS INNER JOIN Ticket ON JobNOS.CustomerJobNumber = Ticket.CustomerJobNumber WHERE Ticket.ProcessBatchID = ‘A626C3F1-9C40-4FDC-BB42-89B73D777E5D’ Table ‘Worktable’. Scan count 30866, logical reads 30901, physical reads 0, read-ahead reads 0. Table ‘Worktable’. Scan count 30866, logical reads 30901, physical reads 0, read-ahead reads 0. Table ‘Ticket’. Scan count 61732, logical reads 6296235, physical reads 53, read-ahead reads 0. Table ‘JobNOS’. Scan count 3, logical reads 5769, physical reads 317, read-ahead reads 1927. |–Merge Join(Union) |–Merge Join(Union) | |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1076] OR [Expr1077])) | | |–Nested Loops(Left Semi Join, WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1076])OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[JobNOS].[DisplayID]), DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1077] = [PROBE VALUE])) | | |–Nested Loops(Left Semi Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[JobNOS].[UniqueID]), DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1076] = [PROBE VALUE])) | | | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OysterPE].[dbo].[JobNOS].[PK_JobNOS]), ORDERED FORWARD) | | | |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[JobNOS].[UniqueID]=[Ticket].[JobNOSID])) | | | |–Bookmark Lookup(BOOKMARK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1002]), OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OysterPE].[dbo].[Ticket])) | | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OysterPE].[dbo].[Ticket].[IX_Ticket_ProcessBatchID]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Ticket].[ProcessBatchID]=A626C3F1-9C40-4FDC-BB42-89B73D777E5D) ORDERED FORWARD) | | |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[JobNOS].[DisplayID]=[Ticket].[Import_JobNOS])) | | |–Bookmark Lookup(BOOKMARK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1004]), OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OysterPE].[dbo].[Ticket])) | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OysterPE].[dbo].[Ticket].[IX_Ticket_ProcessBatchID]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Ticket].[ProcessBatchID]=A626C3F1-9C40-4FDC-BB42-89B73D777E5D) ORDERED FORWARD) | |–Sort(DISTINCT ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[JobNOS].[UniqueID] ASC, [JobNOS].[DisplayID] ASC, [JobNOS].[CustomerJobNumber] ASC, [JobNOS].[CustomerJobName] ASC, [JobNOS].[TypeID] ASC, [JobNOS].[JobSiteID] ASC, [JobNOS].[StatusID] ASC, [JobNOS].[ReasonClosedID] | |–Nested Loops(Inner Join, WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Ticket].[CustomerJobNumber]=[JobNOS].[CustomerJobName])) | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OysterPE].[dbo].[JobNOS].[PK_JobNOS]), ORDERED FORWARD) | |–Table Spool | |–Bookmark Lookup(BOOKMARK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1008]), OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OysterPE].[dbo].[Ticket])) | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OysterPE].[dbo].[Ticket].[IX_Ticket_ProcessBatchID]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Ticket].[ProcessBatchID]=A626C3F1-9C40-4FDC-BB42-89B73D777E5D) ORDERED FORWARD) |–Sort(DISTINCT ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[JobNOS].[UniqueID] ASC, [JobNOS].[DisplayID] ASC, [JobNOS].[CustomerJobNumber] ASC, [JobNOS].[CustomerJobName] ASC, [JobNOS].[TypeID] ASC, [JobNOS].[JobSiteID] ASC, [JobNOS].[StatusID] ASC, [JobNOS].[ReasonClosedID] ASC, |–Nested Loops(Inner Join, WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Ticket].[CustomerJobNumber]=[JobNOS].[CustomerJobNumber])) |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OysterPE].[dbo].[JobNOS].[PK_JobNOS]), ORDERED FORWARD) |–Table Spool |–Bookmark Lookup(BOOKMARK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1043]), OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OysterPE].[dbo].[Ticket])) |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OysterPE].[dbo].[Ticket].[IX_Ticket_ProcessBatchID]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Ticket].[ProcessBatchID]=A626C3F1-9C40-4FDC-BB42-89B73D777E5D) ORDERED FORWARD) NEW Select DISTINCT JobNOS.* From JobNOS Inner Join Ticket On Ticket.JobNOSID = JobNOS.UniqueID Where ProcessBatchID = ‘A626C3F1-9C40-4FDC-BB42-89B73D777E5D' UNION Select DISTINCT JobNOS.* From JobNOS Inner Join Ticket On Ticket.Import_JobNOS = JobNOS.DisplayID Where ProcessBatchID = ‘A626C3F1-9C40-4FDC-BB42-89B73D777E5D' UNION Select JobNOS.* From JobNOS INNER JOIN Ticket ON JobNOS.CustomerJobNumber = Ticket.CustomerJobNumber WHERE Ticket.ProcessBatchID = ‘A626C3F1-9C40-4FDC-BB42-89B73D777E5D’ Table ‘Worktable’. Scan count 30866, logical reads 30901, physical reads 0, read-ahead reads 0. Table ‘Ticket’. Scan count 3, logical reads 306, physical reads 63, read-ahead reads 0. Table ‘JobNOS’. Scan count 67, logical reads 2151, physical reads 14, read-ahead reads 1923. |–Sort(DISTINCT ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Union1043] ASC, [Union1044] ASC, [Union1045] ASC, [Union1046] ASC, [Union1047] ASC, [Union1048] ASC, [Union1049] ASC, [Union1050] ASC, [Union1051] ASC, [Union1052] ASC, [Union1053] ASC, [Union1054] ASC, [Union1055] ASC, [Union |–Concatenation |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Ticket].[JobNOSID])) | |–Bookmark Lookup(BOOKMARK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1002]), OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OysterPE].[dbo].[Ticket])) | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OysterPE].[dbo].[Ticket].[IX_Ticket_ProcessBatchID]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Ticket].[ProcessBatchID]=A626C3F1-9C40-4FDC-BB42-89B73D777E5D) ORDERED FORWARD) | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OysterPE].[dbo].[JobNOS].[PK_JobNOS]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[JobNOS].[UniqueID]=[Ticket].[JobNOSID]) ORDERED FORWARD) |–Bookmark Lookup(BOOKMARK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1004]), OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OysterPE].[dbo].[JobNOS])) | |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Ticket].[Import_JobNOS])) | |–Bookmark Lookup(BOOKMARK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1006]), OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OysterPE].[dbo].[Ticket])) | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OysterPE].[dbo].[Ticket].[IX_Ticket_ProcessBatchID]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Ticket].[ProcessBatchID]=A626C3F1-9C40-4FDC-BB42-89B73D777E5D) ORDERED FORWARD) | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OysterPE].[dbo].[JobNOS].[Unique_JobNOS_DisplayID]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[JobNOS].[DisplayID]=[Ticket].[Import_JobNOS]) ORDERED FORWARD) |–Nested Loops(Inner Join, WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Ticket].[CustomerJobNumber]=[JobNOS].[CustomerJobNumber])) |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OysterPE].[dbo].[JobNOS].[PK_JobNOS]), ORDERED FORWARD) |–Table Spool |–Bookmark Lookup(BOOKMARK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1041]), OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OysterPE].[dbo].[Ticket])) |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[OysterPE].[dbo].[Ticket].[IX_Ticket_ProcessBatchID]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Ticket].[ProcessBatchID]=A626C3F1-9C40-4FDC-BB42-89B73D777E5D) ORDERED FORWARD)

Performance tuning tips are often contradictory, since theres no clear cut answer and every option usually involves a trade off of some sort. So whats good for one scenario may not be the best option for another.

]]>