Perf problem with subquery in a WHERE/IN clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Perf problem with subquery in a WHERE/IN clause

Author

Topic

mwoertz
Starting Member

1 Post

Posted - 2002-01-22 : 01:55:26

Performance problem with the following query. It runs in 5-7 seconds on SQL 7.0 but 15+ minutes in SQL 2000. I have narrowed the offending code to be "AND ssn IN (SELECT ssan from #FamilySSN)". If I hard code values instead of using the select statement it works fine. The #FamilySSN table is created above in the proc. The ssan values in #FamilySSN are there, I have tested for that. I have tried other variations of a subquery within a WHERE/IN clause and they don't work either. What am I missing?

SELECT Commissary, SSN, PurchaseDate, UnitPrice, Quantity, Total, [UPC Description],
Cashier, Register, Tender
INTO #Temp1
FROM NewCommissaryView
Where PurchaseDate > dateadd(month, -24, getdate())
AND ssn IN (SELECT ssan from #FamilySSN)
AND Tender = 'X'
ORDER BY PurchaseDate, Commissary

Thanks,
Mike

fisherman_jake
Slave to the Almighty Yak

159 Posts

Posted - 2002-01-22 : 02:08:37

have you tried joining onto #FamilySSN
ie:


 ...
FROM NewCommissaryView N
Inner Join
    #FamilySSN F
On N.ssn = F.ssn
 ...

==================================================
World War III is imminent, you know what that means... No Bag limits!!!
Master Fisherman

Nazim
A custom title

1408 Posts

Posted - 2002-01-22 : 02:21:12

Apart from wht jake has suggested you can create appropriate indexes(eg: one on ssn) that should speed up your query.

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-22 : 04:31:19

create unique index ix on #FamilySSN (ssan)

SELECT Commissary, SSN, PurchaseDate, UnitPrice, Quantity, Total, [UPC Description],
Cashier, Register, Tender
INTO #Temp1
FROM NewCommissaryView, #FamilySSN
Where NewCommissaryView.PurchaseDate > dateadd(month, -24, getdate())
AND NewCommissaryView.ssn =#FamilySSN.ssan
AND NewCommissaryView.Tender = 'X'
ORDER BY NewCommissaryView.PurchaseDate, NewCommissaryView.Commissary

Although an in clause is efficient for selects it tends to be very bad for any updates.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources