Make subquery into inner join
-
Hi All,
how can I make the following subquery
SELECT af.WebsiteName , af.AffiliateID , 1
FROM tblAffiliate af Inner join
tblAffiliateInvoice ai on af.AffiliateID = ai.SourceID
where af.AffiliateID in
( Select SourceID
from tblAffiliateInvoice
group by SourceID
Having count(SourceID) = 1
 
and datediff(mm , '20070201' , ai.insertDate) = 0
into Inner Join.
Will the performance of Inner Join faster than subquery?
Thanks.
-
I am not completely certain the INNER JOIN will run faster then the subquery (it all depends) but here is an example of how to use a subquery and join it notin the where clause. (hoping you have pubs db installed somewhere) Typically using a inner join instead of a subquery does run faster but as I said above it all depends)
USE PUBS
GO
SELECT *
FROM dbo.Stores S
INNER JOIN (SELECT stor_id
FROM Sales
Group BY Stor_id
HAVING Count(Ord_num) > 1) SQ
ON SQ.Stor_ID = S.Stor_id
WHERE S.Zip LIKE '9%'
You could also drop the data in to a temp table. Which will in most cases speed up the entire query.
USE PUBS
GO
CREATE TABLE #Temp
(Stor_ID INT)
INSERT #Temp
(STOR_ID)
SELECT stor_id
FROM Sales
Group BY Stor_id
HAVING Count(Ord_num) > 1
SELECT *
FROM dbo.Stores S
INNER JOIN #Temp T
ON T.Stor_ID = S.Stor_id
WHERE S.Zip LIKE '9%'
Open Query Analyzer and run an execution plan on the query both all three ways. This will help determine which way runs the fastest and what parts take up the most time. Adding an index (especially clustered) may help also.
Hope that answers your question.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply