query optimization\

Question

Post reply

query optimization\

MISO

SSC Rookie

Points: 25
More actions
November 6, 2003 at 6:03 am

#101747

I'm sorry that I posted a mistake in my query. Here is the query. Can Any one say how to optimize it.

-------------------------------------------

delete from table1 where ((NEWS_LETTER_ID in

("FLOWERS","BOUQUETS") or NEWS_LETTER_ID like

'newsletter%')) and (REGISTERED_ID=(select ID from IDTABLE where EMAIL_ADDRESS='key1'))

Any suggestion is greatly appreciated

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

guarddata SSCertifiable Points: 6583 More actions · Answer 1

OR in a where clause is too often a signal for a table scan. Where possible, and this looks like a good time, break the commands into separate, precise commands

DELETE FROM table1 WHERE NEWS_LETTER_ID = 'FLOWERS'

DELETE FROM table1 WHERE NEWS_LETTER_ID = 'BOUQUETS'

DELETE FROM table1 WHERE REGISTERED_ID = ( SELECT ID FROM IDTABLE WHERE EMAIL_ADDRESS = 'Key1' ) AND NEWS_LETTER_ID LIKE 'newsletter%'

Guarddata-

Vladan SSC-Insane Points: 22072 More actions · Answer 2

Hm... I understood the original query differently - according to parentheses the second part of condition /i.e. (REGISTERED_ID=(select ID from IDTABLE where EMAIL_ADDRESS='key1'))/ should always be applied.

That makes it two deletes, each with two conditions (with AND operator between them), which should work better than the proposed one. You should also check indexes and query execution plan - a lot depends on number of records.

guarddata SSCertifiable Points: 6583 More actions · Answer 3

Oops - you are correct Vladan, I got the parenthesis mixed up

Still - I think there are three conditions. As you say - it does depend on the number of rows in the table, but a table of any size (indexed by NEWS_LETTER_ID and REGISTERED_ID) would be most efficient with:

DELETE FROM table1 WHERE REGISTERED_ID = ( SELECT ID FROM IDTABLE WHERE EMAIL_ADDRESS = 'Key1' ) AND NEWS_LETTER_ID = 'FLOWERS'

DELETE FROM table1 WHERE REGISTERED_ID = ( SELECT ID FROM IDTABLE WHERE EMAIL_ADDRESS = 'Key1' ) AND NEWS_LETTER_ID = 'BOUQUETS'

DELETE FROM table1 WHERE REGISTERED_ID = ( SELECT ID FROM IDTABLE WHERE EMAIL_ADDRESS = 'Key1' ) AND NEWS_LETTER_ID LIKE 'newsletter%'

Guarddata

lambje SSCrazy Points: 2055 More actions · Answer 4

If there is only one row coming back from the IDTABLE you could do something like this:

DECLARE @ID INT (?)

SELECT @ID = ID FROM IDTABLE WHERE EMAIL_ADDRESS = 'key1'

DELETE FROM table1 WHERE REGISTERED_ID = @ID AND NEWS_LETTER_ID = 'FLOWERS'

DELETE FROM table1 WHERE REGISTERED_ID = @ID AND NEWS_LETTER_ID = 'BOUQUETS'

DELETE FROM table1 WHERE REGISTERED_ID = @ID AND NEWS_LETTER_ID LIKE 'newsletter%'

If there's more than one row then I would change the IN to an EXISTS. The first hit on a record in IDTABLE will end that subquery and should be more optimal.

Jeff