SQL Joins on varchar fields timing out

Question

Hi, 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.

Answer 1

Simply add an index.

Answer 2

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.

Answer 3

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)