Linked Server problem

Hi,<br /><br />I'm having a problem with a linked server. I'm trying to replace an existing SQL job which is of the form<br /><br />'insert into &lt;local db&gt; select from &lt;remote db&gt;'<br /><br />with the reverse, called from the other server ie.<br /><br />'insert into &lt;remote db&gt; select from &lt;local db&gt;' <br /><br />In other words, I'm converting a pull of the data into a push. It works fine [<img src='/community/emoticons/emotion-1.gif' alt='' />], but takes 9 times longer to complete [<img src='/community/emoticons/emotion-6.gif' alt='' />].<br /><br />Looking at the execution plan, the time seems to be taken up on a table spool, although I can't work out which server the spool is being done. Any help would be much appreciated<br /><br />Cheers<br /><br />Martin<br />

The existing SQL runs on SVR2 and pulls the data from SVR1;

insert into TABLE_A
(COL_A, COL_B, COL_C, COL_D)
(selectCOL_A, COL_B, COL_C, COL_D
fromSVR1.SourceDB.dbo.TABLE_A)

The new SQL runs on SVR1 and pushes the data to SVR2;

INSERT INTO SVR2.ShadowDB.dbo.TABLE_A
(COL_A, COL_B, COL_C, COL_D)
SELECT COL_A, COL_B, COL_C, COL_D FROM TABLE_A

The old SQL copies 461,000 rows in about a minute, and the new SQL does the same job in 9 minutes.

I've tried using OPENQUERY, but that has very little effect.

Cheers

Martin

I accept this, but why is the 'push' so much slower than the 'pull' ?

(The actual stored procedure is more complicated, which is why we're using linked servers rather than DTS or BCP. I can cope with a copy time of a minute, but not nine.)

The source DB is SQL7 and the destination is SQL2000, but the service packs are all up to date.

There aren't any indexes on either table, but that shouldn't matter anyway. In both cases the data is being moved from SVR1 to SVR2, so how come it takes so much longer to push the data than pull it [?]

I've already checked out the tips page, which is very good but doesn't really help me. Using OPENQUERY doesn't really work because most of the remote work is done in the insert, and I've set the collation compatible flags.

I've also just tried the same thing going from/to SQL2000 and I get the same problem. Is there any way of controlling the use of the spool table ?

Do a profiler trace an look for any sp_cursorfetch in the slow scenario. More info here:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1944

/Argyle

I tried a profiler trace and couldn't find any reference to sp_cursorfetch. When I look at the destination (remote) DB, it looks like it's doing the following;


exec sp_reset_connection
set implicit_transactions on

exec sp_cursoropen @P1 output, N'select * from "SourceDB"."dbo"."TABLE_A"', @P2 output, @P3 output, @P4 output

exec sp_cursor 180150000, 4, 0, N'ShadowDB.dbo.TABLE_A', @COL_A = 'VAL_A', @COL_B = 'VAL_B', @COL_C = 'VAL_C', @COL_D = 'VAL_D', @COL_E = NULL, @COL_F = NULL, @COL_G = NULL

INSERT [ShadowDB].[dbo].[TABLE_A]([COL_A],[COL_B],[COL_C],[COL_D],[COL_E],[COL_F],[COL_G]) VALUES(@Param000004, @Param000005, @Param000006, @Param000007, @Param000008, @Param000009, @Param000010)

with the sp_cursor and insert repeated a fair few times, and it certainly seems to be that which is eating up the time. I don't know if this helps anyone ?

Yea I meant sp_cursoropen and not sp_cursorfetch. Use pull of possible or try the technique with OPENQUERY mentioned in the link above. Not sure if it works in a push scenario though.

/Argyle

OK. I tried openquery without success

Thanks everyone for your help. It looks like I'm scuppered though....