Linked server stored proc never returns

Last Post 24 Feb 2006 11:50 AM by Lee. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Misha_SQL
New Member
New Member

--
22 Jul 2005 01:47 PM
I am really puzzled by this one. I have two servers: Server1 and Server2. Server2 is defined as a linked server on Server1. Furthermore, on Server2 I have a database (MyDB) with a table I am interested in (MyTable). The table is fairly large, but manageable -- 3 mln. records/150 MB. Now watch this:

1. When I run this query on Server2 (locally) it ruturns in about 2 min:

SELECT * INTO #temp FROM MyDB.dbo.MyTable

2. When I run this query on Server1 (using linked server), it returns in 3 min:

SELECT * INTO #temp FROM Server2.MyDB.dbo.MyTable

3. Same result if I do INSERT/SELECT instead of SELECT INTO:

INSERT #temp SELECT * FROM Server2.MyDB.dbo.MyTable

Here comes the kicker. If I wrap SELECT statement inside the stored procedure on Server2 like this:

CREATE PROC MyProc AS SELECT * FROM MyDB.dbo.MyTable

and then run this statement:

4. INSERT #temp EXEC Server2.MyDB.dbo.MyProc

It will be running for hours and never comes back!

Any ideas?

Thank you!
NR75
New Member
New Member

--
25 Jul 2005 12:15 AM
Did you try running Profiler to see what is going on and whether any data is being returned from linked server?
Lee
New Member
New Member

--
24 Feb 2006 11:50 AM
A SELECT INTO will be a lot faster anyway because of it's nature as we all know, but, yes I agree I would try and look to see what has been inserted and updated in your temp table. Make it a ## table and check it out via nolock and through another connection. You'll probably find that all the rows are being thrown back and forth from one server to another because of the way a link server handles inserts and updates - very inefficiently. Try to fetch your rows, then perform DML on them, rather than through the link server statement.
SQLUSA
New Member
New Member

--
02 Mar 2006 03:41 PM
It may get lost in the communications layers.

The hour long run is disturbing. I assume you expect fast run.

First check all the logs.

Second experiment.

See how long Query Analyzer manual experimentation takes.

Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com/orderdoubleheader/
The Best SQL Server 2005 Training in the World
You are not authorized to post a reply.

Acceptable Use Policy