July 15, 2015 at 12:09 pm
Hi,
I have a SQL statement with a Left join.
SELECT * FROM tblNew a INNER JOIN tblOld b
ON a.empId=b.empId and a.deptid<>b.deptId
This query is executing in less than 5 seconds.
But when I add the additional logic( OR b.deptId is null) this query is taking more than 3 minutes.
SELECT * FROM tblNew a INNER JOIN tblOld b
ON a.empId=b.empId and a.deptId<>b.deptId
OR b.deptId is null
Since no two nulls are equal I am thinking a.deptid<>b.deptId covers the logic b.deptid is null as well( if b.deptId is null). b.deptId will not be equal to a.deptid and that record will be returned as as result of the first condition(a.deptId<>b.deptId).Is the OR clause redundant?
Thanks.
July 15, 2015 at 12:15 pm
Comparison to NULL always yields UNKNOWN not True or False so you probably do want the OR b.deptid IS NULL logic to make sure your query is correct.
You say that you have a LEFT JOIN but the queries you included are INNER JOIN's. Can you explain the requirements of the query? You may want a LEFT JOIN without the b.deptid IS NULL.
To determine the reason why one query is faster than another you need to compare the Execution Plans to see how the query optimizer is accessing the data differently.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 15, 2015 at 1:03 pm
I am sorry following is the right query.
SELECT * FROM tblNew a LEFT JOIN tblOld b
ON a.empId=b.empId WHERE a.deptId<>b.deptId
OR b.deptId is null
July 15, 2015 at 1:12 pm
sql_novice_2007 (7/15/2015)
I am sorry following is the right query.SELECT * FROM tblNew a LEFT JOIN tblOld b
ON a.empId=b.empId WHERE a.deptId<>b.deptId
OR b.deptId is null
This query is logically an INNER JOIN because you put criteria a.deptId<>b.deptId in the WHERE clause.
So essentially you want to return all the employees where the deptid in tblOld is NULL or Doesn't match the deptid in tblNew or the empid doesn't exist in tblOLD(LEFT JOIN). I'd probably do that with a UNION like this:
SELECT
*
FROM
tblNew AS A
LEFT JOIN tblOld AS B
ON A.empid = B.empid AND
A.deptid <> B.deptid
UNION
SELECT
*
FROM
tblNew AS A
LEFT JOIN tblOld AS B
ON A.empid = B.empid AND
B.deptid IS NULL;
Many times this pattern will perform better than the OR condition because the optimizer can choose different indexes to satisfy the 2 queries in the UNION. The UNION will eliminate duplicates. If you want to see duplicates use UNION ALL instead of UNION.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 15, 2015 at 1:13 pm
sql_novice_2007 (7/15/2015)
I am sorry following is the right query.SELECT * FROM tblNew a LEFT JOIN tblOld b
ON a.empId=b.empId WHERE a.deptId<>b.deptId
OR b.deptId is null
We have no way to know what your objective is for this query, nor whether or not it achieves that objective. Lacking that knowledge, anything we say would probably not even be as good as a SWAG (aka scientific wild-a$$ guess). What we do see is the New table being LEFT joined to the Old table, based on the same Employee ID value, but you're WHERE clause says that either the Department ID values have to differ, or there is no matching record in the Old table. As to performance, that will probably depend on how those tables are indexed, but it would be useless to try and determine the right indexes without knowing the query's objective, and the reason behiind the change in logic, as given your update of the query, we now don't really know for sure what the old query was. We need to know the reason behind the change in logic.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply