How to avoid Subquery
-
Hi All
Thank in advance for those who reply. I have written a query
select * from TableA
where ID not in
(
select a.ID
from TableA a inner join TableB b on a.ID= b.ID
where (a.TransactionDate between dateadd(dd , -90 , getdate()) and getdate())
)
However, it takes almost 2-3 minutes for the execution of the query
How can I rewrite the query using inner join? I had number of tried but fails. Any suggestion will be helpful. Thanks. -
select * from TableA
LEFT join TableB b on a.ID= b.ID AND (a.TransactionDate between dateadd(dd , -90 , getdate()) and getdate())
WHERE B.ID IS NULL
I'm sure you have clustered index on column a.TransactionDate
_____________
Code for TallyGenerator -
Thanks for prompt reply.
-
The outer join certainly does the trick but the subquery wasn't the problem... the fact that you made it a correlated subquery may have been.
select * from TableA
where ID not in
(
select a.ID
from TableA a
inner join TableB b on a.ID= b.IDwhere (a.TransactionDate between dateadd(dd , -90 , getdate()) and getdate())
)
"WHERE NOT IN" actually runs a tiny bit faster than Left Outer's with a NULL detector. 'Course, that won't work if you have more than one column that you need to check.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff,
select * from TableA
where TransactionDate NOT between dateadd(dd , -90 , getdate()) and getdate()
will be even more faster, but I believe TableB was mentioned in original query on purpose.
Your query will return all entries from TableA not within the time period.
Original (and my) query will return all entries from TableA not within time period OR not referenced in TableB.
That's different.
_____________
Code for TallyGenerator -
Heh... yeah, I crossed out the wrong part, huh? Shouldn't post when I'm pooped. Thanks, Serqiy.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi,
I had the same problem when I switched from SQL2K to SQL2K5. I replaced the IN/Subquery part with an EXISTS statement, and it worked perfectly.
select * from TableA
where NOT EXISTS
(
select * from TableA a inner join TableB b on a.ID= b.ID
where (a.TransactionDate between dateadd(dd , -90 , getdate()) and getdate())
AND TableA.ID=a.ID
)
Robert -
My experience in SQL2000 is that joins in subqueries always produce nested loops. I therefore suspect that Sergiy's LEFT JOIN solution, or something like the following, will be more efficient.
SELECT *
FROM TableA
WHERE NOT EXISTS (
SELECT *
FROM TableA a
WHERE TableA.[ID] = a.[ID]
AND a.TransactionDate BETWEEN DATEADD(dd , -90 , GETDATE()) AND GETDATE()
AND EXISTS (
SELECT *
FROM TableB b
WHERE a.[ID] = b.[ID]
)
)
-
But, that's more correlated subqueries... why not use a simple join for the WHERE EXISTS?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I was thinking of a left semi join operator which should require less reads than a join; especially if there are a lot of FKs in TableB for each PK in TableA.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply