December 13, 2017 at 10:26 am
Apologies if this has been asked before, searching for it yields too many results.
I've been eliminating NOT EXISTS in some code by LEFT JOINing tableX and using a WHERE TableX.JoinColumn IS NULL
Big gains, I'm pleased with that.
My next challenge is OR in a JOIN criteria, so...
SELECT id
FROM table1
LEFT JOIN table2
ON table1.keycolumn = table2.keycolumn
AND (table1.code1 = table2.code1 OR table1.text1 = table2.text1)
Performing 2 SELECT queries and UNIONing them to avoid the OR comes to mind,
The bigger picture is that I want to LEFT JOIN to this subquery to reproduce the NOT EXISTS functionality that performs so badly.
I'm eliminating records that match the crazy OR join.
This is a medical application, I can't change the tables, merely report on them.
Any ideas most appreciated.
Thank you
December 13, 2017 at 11:58 am
r5d4 - Wednesday, December 13, 2017 10:26 AMApologies if this has been asked before, searching for it yields too many results.I've been eliminating NOT EXISTS in some code by LEFT JOINing tableX and using a WHERE TableX.JoinColumn IS NULL
Big gains, I'm pleased with that.My next challenge is OR in a JOIN criteria, so...
SELECT id
FROM table1
LEFT JOIN table2
ON table1.keycolumn = table2.keycolumn
AND (table1.code1 = table2.code1 OR table1.text1 = table2.text1)Performing 2 SELECT queries and UNIONing them to avoid the OR comes to mind,
The bigger picture is that I want to LEFT JOIN to this subquery to reproduce the NOT EXISTS functionality that performs so badly.
I'm eliminating records that match the crazy OR join.This is a medical application, I can't change the tables, merely report on them.
Any ideas most appreciated.
Thank you
Have you tried using temp tables to break things down? Joining to your index-optimised temp table might speed things up.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 13, 2017 at 3:10 pm
r5d4 - Wednesday, December 13, 2017 10:26 AMApologies if this has been asked before, searching for it yields too many results.I've been eliminating NOT EXISTS in some code by LEFT JOINing tableX and using a WHERE TableX.JoinColumn IS NULL
Big gains, I'm pleased with that.My next challenge is OR in a JOIN criteria, so...
SELECT id
FROM table1
LEFT JOIN table2
ON table1.keycolumn = table2.keycolumn
AND (table1.code1 = table2.code1 OR table1.text1 = table2.text1)Performing 2 SELECT queries and UNIONing them to avoid the OR comes to mind,
The bigger picture is that I want to LEFT JOIN to this subquery to reproduce the NOT EXISTS functionality that performs so badly.
I'm eliminating records that match the crazy OR join.This is a medical application, I can't change the tables, merely report on them.
Any ideas most appreciated.
Thank you
You could use 2 LEFT JOINS and then determine which one of the 2 values you want to keep using COALESCE.
SELECT ...
, COALESCE(t2.col2, t3.col2) AS col2
, COALESCE(t2.col3, t3.col3) AS col3
, COALESCE(t2.col4, t3.col4) AS col4
FROM table1 t
LEFT JOIN table2 t2 ON t2.col1 = t.col1
LEFT JOIN table2 t3 ON t3.text1 = t.text1
You cannot utilize a UNION ALL - it would have to be a UNION which will require a sort operation to remove duplicates.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 14, 2017 at 5:02 am
Sometimes, not always, an OR causing slow behavior can be replaced by two copies of the query combined using UNION ALL. Not seeing the details here I don't want to say for sure that will improve things, but it can.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 14, 2017 at 5:28 am
Thank you all,
That's given me some more ideas to try.
December 20, 2017 at 1:06 pm
FYI, if you have a GROUP BY in the query with the OR in the JOIN, then you have a different kind of problem, and the solution is more likely to involve "pre-generating" a temp table with perhaps some primary key values that can be used to limit records, after indexing said temp table. Of course, there's always more than one way to "skin the cat", so to speak.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 22, 2017 at 10:22 am
Grant Fritchey - Thursday, December 14, 2017 5:02 AMSometimes, not always, an OR causing slow behavior can be replaced by two copies of the query combined using UNION ALL. Not seeing the details here I don't want to say for sure that will improve things, but it can.
+ 1
They key data in our database involves a very similar type of condition. I did a comparison and ultimately landed on the solution Grant calls out. Far fewer table scans. Overall, much better query plan.
December 22, 2017 at 10:48 am
SoCal_DBD - Friday, December 22, 2017 10:22 AMGrant Fritchey - Thursday, December 14, 2017 5:02 AMSometimes, not always, an OR causing slow behavior can be replaced by two copies of the query combined using UNION ALL. Not seeing the details here I don't want to say for sure that will improve things, but it can.+ 1
They key data in our database involves a very similar type of condition. I did a comparison and ultimately landed on the solution Grant calls out. Far fewer table scans. Overall, much better query plan.
If you have an OR condition in the JOIN - and there is no possibility that the values in the OR statement overlap...then you can convert it to a UNION ALL. If the values overlap it would require a UNION which may not improve performance over the JOIN.
The alternatives are an OUTER JOIN or CROSS APPLY - and now that I think about it a CROSS APPLY might just be better.
SELECT ...
FROM table1 t1
CROSS APPLY (SELECT ...
FROM table2 t2
WHERE t2.col1 = t1.col1
OR t2.col2 = t1.col2) As t3
WHERE ...
A normal INNER JOIN with an OR would include the row one time for a match on either condition - if you UNION ALL that same row would be included in both queries and cause duplicate rows to be returned. An OUTER JOIN will match the same row and result in a single row returned - the CROSS APPLY will also match a single row returning only the one row that matches.
If the OR is in the WHERE clause - it can only be modified to a UNION ALL if the OR is performed on the same column. As soon as you have an OR statement across multiple columns you have to insure that only one of the OR conditions can be true before a UNION ALL will work.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 20, 2018 at 5:49 pm
Can you try IN, instead of OR... that can sometimes produce two index seeks against one table instead of a scan.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
January 22, 2018 at 3:33 am
Break OR Sign with UNION ALL can overcome performance and help results in required dataset
January 29, 2018 at 10:50 am
Wait, using "NOT EXISTS" is bad?
January 30, 2018 at 2:45 pm
Jeffrey Williams 3188 - Friday, December 22, 2017 10:48 AMSoCal_DBD - Friday, December 22, 2017 10:22 AMGrant Fritchey - Thursday, December 14, 2017 5:02 AMSometimes, not always, an OR causing slow behavior can be replaced by two copies of the query combined using UNION ALL. Not seeing the details here I don't want to say for sure that will improve things, but it can.+ 1
They key data in our database involves a very similar type of condition. I did a comparison and ultimately landed on the solution Grant calls out. Far fewer table scans. Overall, much better query plan.If you have an OR condition in the JOIN - and there is no possibility that the values in the OR statement overlap...then you can convert it to a UNION ALL. If the values overlap it would require a UNION which may not improve performance over the JOIN.
The alternatives are an OUTER JOIN or CROSS APPLY - and now that I think about it a CROSS APPLY might just be better.
SELECT ...
FROM table1 t1
CROSS APPLY (SELECT ...
FROM table2 t2
WHERE t2.col1 = t1.col1
OR t2.col2 = t1.col2) As t3
WHERE ...A normal INNER JOIN with an OR would include the row one time for a match on either condition - if you UNION ALL that same row would be included in both queries and cause duplicate rows to be returned. An OUTER JOIN will match the same row and result in a single row returned - the CROSS APPLY will also match a single row returning only the one row that matches.
If the OR is in the WHERE clause - it can only be modified to a UNION ALL if the OR is performed on the same column. As soon as you have an OR statement across multiple columns you have to insure that only one of the OR conditions can be true before a UNION ALL will work.
One word of caution, all of these might be alternatives, but they are NOT guaranteed to return the same amount of rows as the original query. The UNION or UNION all for example could create duplicates you didn't have or eliminate duplicates you wanted.
If the OR returned exactly the set of rows you wanted, then you want to be careful that a rewrite doesn't change the count.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply