0

My problem is simple:

I run some quite complex query using remote server and while this query runs as a charm ( <0s )

select t1.* from [linkedserver].[db].[dbo].[table1] t1 
inner join 
[linkedserver].[db].[dbo].[table2] t2
on t1.t2ID = t2.ID
WHERE t2.OtherID = 323207;

Fast query

this one takes ages ( 34s ):

delete t1 from [linkedserver].[db].[dbo].[table1] t1 
inner join 
[linkedserver].[db].[dbo].[table2] t2
on t1.t2ID = t2.ID
WHERE t2.OtherID = 323207;

Slow query

In both cases nothing is selected/deleted.

There are all necessary indexes on both tables (t1 contains approx 6,000,000 entries while t2 has 500,000). Query has been digested for the sake of example. It is not my real query but I was able trace the problem down to this point.

fontan
  • 86
  • 10
  • have you considered running it as two separate queries? – user2366842 Feb 17 '15 at 15:39
  • 1
    What if you push the whole query over? `EXEC ('delete t1 from [db].[dbo].[table1] t1 inner join [db].[dbo].[table2] t2 on t1.t2ID = t2.ID WHERE t2.OtherID = 323207;') AT [linkedserver]` – Martin Smith Feb 17 '15 at 15:42
  • 1
    An ancient [MS KB article](http://support.microsoft.com/kb/309182) might help explain this behavior. Note that the optimizations are only for simple queries, which is where the "non-SQL Server" but comes from. Since this isn't a simple query, my guess is that there's no optimization used. Its for old versions of SQL Server, but the query plan seems to suggest that this is still what happens. – Bacon Bits Feb 17 '15 at 15:47
  • it seems that EXEC (' ... will work for us - and KB article seems to be the case for mssql 2008 as well. TY for brainstorming – fontan Feb 17 '15 at 16:07

0 Answers0