Slow or no results on table insert executing linked server stored procedure

Question

Slow or no results on table insert executing linked server stored procedure

Sam Letcher

SSC Veteran

Points: 295
More actions
June 24, 2008 at 1:14 pm

#122275

I have some code that executes a stored procedure on a linked server and inserts the results into a temporary table so it can be further manipulated, like so:
create table #shifttime(deptname varchar(10), machid varchar(10), shift int, tottime float)
insert into #shifttime(deptname, machid, shift, wttottime)
exec [remote-server].[database].dbo.procGetTotalTimeByPlantAndShift @datefrom = '6/3/08', @dateto = '6/3/08'
This runs in about three seconds from my server (SQL 2000 SP4) calling the procedure from a linked 2000 server.
The linked server is being upgraded and we are testing it on SQL 2005 SP2.
Executing the procedure by itself returns data in less than a second, but adding the line to insert the results into the table causes the query to become unresponsive. I have watched it run in the query analyzer for over 30 minutes before killing the connection. It doesn't matter that the table is a temp table, I have tried it with a normal table with the same results.
Any ideas?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 1

Clear proc cache and clear the free buffers using DBCC commands of a similar name. Then, recompile the stored procedure code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sam Letcher SSC Veteran Points: 295 More actions · Answer 2

Thanks, but no luck trying DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS then running the EXEC statement with WITH RECOMPILE.

Same results. The EXEC statement runs fine alone, but I end up having to kill the query if I try to insert the results into my table.

Prakash Heda SSCertifiable Points: 5539 More actions · Answer 3

I used it it a little different fasion: if you are not able to fix it then you can use this also...

procedure stores data in a temp table and then, query data from linked server temp table to another table

exec [remote-server].[database].dbo.procGetTotalTimeByPlantAndShift @datefrom = '6/3/08', @dateto = '6/3/08'

insert into #shifttime(deptname, machid, shift, wttottime)

select * from [remote-server].[tempdb].dbo.##tableresult

Try and let me know if any issue...

Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA

Sam Letcher SSC Veteran Points: 295 More actions · Answer 4

Prakash Heda (6/24/2008)
I used it it a little different fasion: if you are not able to fix it then you can use this also...
procedure stores data in a temp table and then, query data from linked server temp table to another table
exec [remote-server].[database].dbo.procGetTotalTimeByPlantAndShift @datefrom = '6/3/08', @dateto = '6/3/08'
insert into #shifttime(deptname, machid, shift, wttottime)
select * from [remote-server].[tempdb].dbo.##tableresult
Try and let me know if any issue...

Thanks Prakash,

This solution works and performs well.

Having that extra table out there just as a pass through for the data bothers me though. It shouldn't be necessary.

Sergiy SSC Guru Points: 110208 More actions · Answer 5

By starting INSERT you begin a transaction.

By getting data for INSERT from remote server you make that transaction distributed.

DTC must inspect all objects used within the transaction, apply necessary locks, resolve possible conflicts, etc. Depending on amount of data and complexity of procedure it could be quite expensive exersise.

When you use ##table approach that ##table is the only remote object involved in the transaction.

Of course, it's way easier and way cheaper in terms of resorces required.

I believe ##table would not be required if remote database and procedure design would be optimal.

_____________
Code for TallyGenerator