x

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?

more ▼

asked Oct 28, 2009 at 07:38 AM in Default

thatismatt gravatar image

thatismatt ♦♦
210 7 8 9

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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!

more ▼

answered Oct 28, 2009 at 08:00 AM

Kev Riley gravatar image

Kev Riley ♦♦
52.8k 47 49 76

(comments are locked)
10|1200 characters needed characters left

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!

more ▼

answered Oct 28, 2009 at 09:37 AM

Håkan Winther gravatar image

Håkan Winther
15.6k 34 37 48

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 28, 2009 at 07:47 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x245
x108
x106
x52
x7

asked: Oct 28, 2009 at 07:38 AM

Seen: 2619 times

Last Updated: Oct 28, 2009 at 07:39 AM