Distributed SELECT and DELETE performance

Question

question

thatismatt asked Oct 28, '09

Distributed SELECT and DELETE performance

Why is it that the following distributed select query (returning about 2,500 rows) takes almost 2 minutes:

SELECT * FROM dbo.Calls c
LEFT JOIN LINKEDSERVER.GM.dbo.cal gc
   ON c.GMID = gc.LOPRECID COLLATE DATABASE_default
WHERE gc.LOPRECID IS NULL

But the equivalent distributed delete query:

DELETE c FROM dbo.Calls c
LEFT JOIN LINKEDSERVER.GM.dbo.cal gc
   ON c.GMID = gc.LOPRECID COLLATE DATABASE_default
WHERE gc.LOPRECID IS NULL

completes in under 10 seconds?

performance select linked-server delete distributed

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-10-28T08:00:32Z

Kev Riley answered Oct 28, '09

My first reaction would be to see what is being returned in the SELECT query. It could be 400 columns of blob data!

Dont forget a SELECT query returns data as well as messages (such as 1 row(s) affected), whereas the DELETE will just return messages. The time taken for a query to complete includes the return!

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2009-10-28T07:47:25Z

Matt Whitfield answered Oct 28, '09

It's really difficult to answer that one based on the information present, but it's pretty likely that in the top query it is returning a lot more data than it needs to. I have also seen issues whereby LOB or In-Row Overflow type columns can take a long time to return data based on certain network conditions. I've never nailed it down fully as to why, however.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2009-10-28T09:37:47Z

Håkan Winther answered Oct 28, '09

As Matt and Kev says, the select returns every column in the select statement due to select * and i recommend you to change it to Select c.*

Your where clause filters the data anyway to only display data from C that does not have any match in GC, but SQL have to get all the records from GC anyway to do the filter.

In the delete statement the linked server will only return gc.LOPRECID to delete all records from c that doesnt have a match in GC.

IF you want even more performance i recommend you to do a "OPENQUERY".

SELECT * FROM dbo.Calls c
LEFT JOIN (SELECT LOPRECID COLLATE DATABASE_default FROM OPENQUERY ('yourlinked_server_name', SELECT LOPRECID FROM GM.dbo.cal)) AS GC
ON c.GMID = gc.LOPRECID COLLATE DATABASE_default
WHERE gc.LOPRECID IS NULL

(Or something like that)

Sorry for the layout of the code!

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question