CODEWITHSUNDEEP
sqljoinsql-delete
Mark Clancy
Mark Clancy

Reputation: 7889

SQL Joins on varchar fields timing out

I have a join which deletes rows that match another table but the joining fields have to be a large varchar (250 chars). I know this isn't ideal but I can't think of a better way. Here's my query:

DELETE P 
FROM dbo.FeedPhotos AS P
INNER JOIN dbo.ListingPhotos AS P1 ON P.photo = P1.feedImage
INNER JOIN dbo.Listings AS L ON P.accountID = L.accountID
WHERE P.feedID = @feedID

This query is constantly timing out even though there are less than 1000 rows in the ListingPhotos table.

Any help would be appreciated.

Upvotes: 3

Views: 260

Answers (3)

Timothy Khouri
Timothy Khouri

Reputation: 31855

Simply add an index.

CREATE INDEX idx_feedPhotos_feedid
    ON dbo.FeedPhotos (feedId)

Upvotes: 1

gbn
gbn

Reputation: 432471

I would consider:

  • rewriting to use EXISTS. This will stop processing if one row is found more reliably then relying on JOIN which may have many more intermediate rows (which is what Aaronaught said)

  • ensure all datatypes match exactly. All differences in length or type will mean no indexes will be used

  • speaking of which, do you have an index (rough guess) on feedid, photo and accountid?

Something like:

DELETE
    P 
FROM
    dbo.FeedPhotos AS P
WHERE
    P.feedID = @feedID
    AND
    EXISTS (SELECT * FROM
             dbo.ListingPhotos P1
           WHERE P.photo = P1.feedImage)
    AND
    EXISTS (SELECT * FROM
             dbo.Listings L
           WHERE P.accountID = L.accountID)

Upvotes: 2

Aaronaught
Aaronaught

Reputation: 122664

I'd probably start by removing this line, as it doesn't seem to be doing anything:

INNER JOIN dbo.Listings AS L ON P.accountID = L.accountID

There might not be a lot of rows in ListingPhotos, but if there are a lot of rows in Listings then the join won't be optimized out.

Also check your indexing, as any join is bound to be slow without the appropriate indexes. Although you should generally try to avoid joining on character fields anyway, it's usually a sign that the data is not normalized properly.

Upvotes: 4

Related Questions