SQL Server prevent Table Spool

[Ranthalion]

Hi,

I have a query that inserts records from one table into another table on a linked server. The problem is that it takes close to 30 seconds to perform the query. If I just insert the records into a table on the same server instead, the query completes almost instantly. I am fairly certain that I am not dealing with a network speed problem.

I looked at the execution plan and found that the insert query that inserts the remote server includes a Table Spool/Eager Spool that the other query does not contain. This step has Cost: 83%, so that's where my time is going.

How can I prevent the Table Spool/Eager Spool? I've looked for query hints, but can't seem to find what I'm looking for. Also, I'm using SQL Server 2000.

Here is an example of my query:

Insert Into RemoteServer.Database.Owner.Table
    (field1,field2,field3,field4,field5)
Select field1,field2,field3,field4,field5
From Table

Thanks,
Ranthalion

[andreasblixt]

When testing the query locally, did you use an INSERT INTO ... SELECT query or just a standard INSERT query? If I'm not mistaken, table spool means it's creating a temporary table to hold the records from the SELECT query. I read somewhere that using the NOLOCK hint could help in these cases:

INSERT INTO RemoteServer.Database.Owner.Table
    (field1, field2, field3, field4, field5)
SELECT field1, field2, field3, field4, field5
FROM Table WITH (NOLOCK)

[Ranthalion]

When I tested, I used the exact same query, just changed the destination table. I also tried WITH (NOLOCK), but that didn't help any. I'm starting to think that there isn't a way to get around it. Maybe linked servers are always just going to be slow...
I think the Spool is going to be necessary since SQL Server may have to deal with network latency/loss and have to resend packets, so it creates the data set in memory, rather than read from the physical table again...

Thanks for the suggestions.