Semi Join - performance issue
-
Friday, February 01, 2013 8:22 AMI have a strange performance issue trying to upgrade our SQL 2005 to SQL2012.
TableA in on a database running SQL2012
Remoteserver is a 2005 server
When I run the following query my current production server (TableA is on SQL2005 in production), the performance is great:
select * from TableA where CustID not in (select CustID from remoteserver.PRDA.dbo.Cust)
But when I run the same query on my SQL2012, I get a left anti semi join and the query run for a very long time. How can I get ride of the Left Anti Semi Join?
If I save the result of the query from the remote server in a temp table, and use the temp table it works well.
select CustID into #tempCust from remoteserver.PRDA.dbo.Cust
select * from TableA where CustID not in (select CustID from #tempCust).
All Replies
-
Friday, February 01, 2013 8:24 AM
pull the remote data over into a table variable (avoids log bloat and DTC), and then work with it locally. Depending on the amount of data, staging to a table variable and then dumping into a local temp table (for the stats those keep) can actually be worth it sometimes.
If you load into a table variable from remote, it takes less aggressive locks, because of how table variables work. That's my experience with it, anyway. Haven't done that trick for a few years, so don't know how well it works on more modern SQL versions. Last time I tried it, was still on SQL 2005. So test thoroughly if you decide to try that.
select * from TableA a where not exists (select CustID from remoteserver.PRDA.dbo.Cust b where b.Custid=a.Custid)
Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/
- Edited by Ahsan Kabir Friday, February 01, 2013 8:24 AM
- Marked As Answer by RAYAIN Saturday, February 02, 2013 3:59 PM
-
Friday, February 01, 2013 9:47 AM
please follow these links
http://www.benjaminnevarez.com/2011/05/optimizer-statistics-on-linked-servers/
http://msdn.microsoft.com/en-us/library/ms175129.aspx
http://sqlblog.com/blogs/linchi_shea/archive/2009/07/21/performance-impact-linked-server-security-configuration-and-how-it-can-hurt-you.aspx
Thanks and regards, Rishabh K
.png)
