0

I am using MS SQL Server 2014.

For simplicity, suppose I have a linked server 'LinkSrvr' with database 'LinkDB', and table 'LinkTbl', as well as a local server 'LocalSrvr' with database 'LocalDB' and table 'LocalTbl'.

Assume both tables have identical structures (Id int identity(1,1), Val varchar(max) NULL), and IdentityInsert is ON for LocalTbl. 

SELECT TOP(n) Id, Val FROM [LinkSrvr].[LinkDB].[dbo].[LinkTbl]

(where 'n' is any positive integer) returns records almost immediately.

In addition, 

INSERT INTO [LocalDB].[dbo].[LocalTbl] (Id, Val) 
SELECT TOP(1) Id, Val 
FROM [LinkSrvr].[LinkDB].[dbo].[LinkTbl]

functions just as quickly.

However, 

INSERT INTO [LocalDB].[dbo].[LocalTbl] (Id, Val) 
SELECT TOP(2) Id, Val 
FROM [LinkSrvr].[LinkDB].[dbo].[LinkTbl]

(and any TOP(n) greater) simply sits and says "executing query" for a seemingly indefinite amount of time, and never inserts any records.

I have only just started working with Linked Servers, so I am not sure if that may be playing a part in this.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
Eric
  • 53
  • 9
  • Try adding (nolock) at the end of query to see if that works – Harsh Nov 23 '15 at 02:49
  • 1
    So you're saying `TOP 2` and `TOP 3` give different performance? How do the query plans compare? (CTRL-L). Just analyse the `select` part for now - exclude the insert part while trying to troubleshoot. Note that without an `ORDER BY`, you can't guarantee which 10 records you'll get. – Nick.Mc Nov 23 '15 at 03:17
  • @Nick.McDermaid Top(1) returns results immediately, any other value just seems to run the query indefinitely without returning anything. – Eric Nov 23 '15 at 14:01
  • @Harsh Thanks for the suggestion, I will give it a try when I have a chance. I should also mention, nothing *should* have been modifying [LinkTbl] for at least three months now, but I could be wrong! – Eric Nov 23 '15 at 14:10
  • As a side note, I shudder every time I see a TOP without an ORDER BY. You cannot guarantee an expected order or consistent results without explicitly ordering your result set. – Joe Stefanelli Nov 23 '15 at 16:47
  • @Eric Is `[LinkTbl]` a table or view? – Lukasz Szozda Nov 23 '15 at 16:52
  • @JoeStefanelli Normally I would agree, but in this particular case we were simply trying to grab a sample of items from [linkTbl], so _which_ records are returned is not as important as how many are returned. Still, a very valid point that a lot of people seem to overlook. – Eric Nov 24 '15 at 20:31
  • @lad2025 It is a table. – Eric Nov 24 '15 at 20:32
  • @Eric Please provide it's full structure tables you insert to and select from – Lukasz Szozda Nov 24 '15 at 20:33
  • @lad2025 Unfortunately, I cannot provide the actual data structures due to the proprietary nature of the data, but I will see if I can at least provide a more representative structure with column names different than the actual ones. – Eric Nov 24 '15 at 20:36
  • @Eric skip names, just overall structure, types, constraint and indexes. Check especially for XML/ntext/image columns – Lukasz Szozda Nov 24 '15 at 20:37
  • @Eric Even better please post image of execution plans both TOP 1 and TOP n, of course cover sensitive data – Lukasz Szozda Nov 24 '15 at 20:39
  • Turns out the issue was related to two varchar(MAX) columns in the source table. Many of the records have a very large restore script stored in those columns, which was slowing everything down. I eventually ended up just using a while loop to iterate through each record and complete the tasks we needed. – Eric Aug 16 '16 at 11:23

0 Answers0