Linked Server delete and insert speed issue

[hoggle]

We have a linked server and everything works fine, except whenever I need to run a delete or insert command the process is terribly slow. We have a T1 connection between the two sites and it only deletes around 100 records, which seems like it shouldn't take 15 minutes to run, but it does.

I issue the DELETE command like this:
(LOFTHOUSESQL2 being the linked server)
DELETE LOFTHOUSESQL2.QueryTrx.dbo.SO_90_UDF_Sales_Order
FROM LOFTHOUSESQL2.QueryTrx.dbo.SO_90_UDF_Sales_Order t1, #temp t2
WHERE t1.SalesOrderNumber = t2.SalesOrderNumber

I've read that this can also be done using the OPENQUERY command, which helps the speed, but I cannot find any documentation on using the DELETE or INSERT command while using OPENQUERY.

So basically I guess what I'm asking is, does the OPENQUERY command improve the speed (if so anyone have some sample delete and insert syntax) or is there another way to improve the speed?

thanks for any insight.

 

[hoggle]

I didn't realize I didn't say that both servers are SQL Server 2000.

 

[hoggle]

I think I found a solution...
microsoft has a KBA on this exact situation.

http://support.microsoft.com/defaul...port/kb/articles/Q309/1/82.ASP&NoWebContent=1

basically you have to run DBCC FREEPROCCACHE
before any deletes, note this only works for SQL7 and SQL2000 though.

 

[bertovich]

hoggle,

You are encountering serveral problems with cross server joins. Your temp table resides on the calling server.

Create the temp table on the destination server instead. That way the joined tables will all be on the same server and the query optimizer can create more efficient query plans.

Also, it is good practice to put your SQL commands in stored procedures if you haven't already done so.

One more thing, check the properties of your linked servers. Make sure the Collation Compatible checkbox is checked (by default it is unchecked), that is of course assuming the collation is the same on the two SQL Servers. Otherwise, one of the servers has to perform a collation conversion everytime data is compared between the two servers.

bill