Why is it that the following distributed select query (returning about 2,500 rows) takes almost 2 minutes:
But the equivalent distributed delete query:
completes in under 10 seconds?
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
answered Oct 28 '09 at 08:00 AM
Kev Riley ♦♦
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".
(Or something like that)
Sorry for the layout of the code!
answered Oct 28 '09 at 09:37 AM
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.
answered Oct 28 '09 at 07:47 AM
Matt Whitfield ♦♦