Replace Not in subquery with a self join

Question

Post reply

Replace Not in subquery with a self join

somaiya.chintan

SSC Veteran

Points: 212
More actions
August 25, 2010 at 7:37 am

#239470

Hi,
This might have been addressed already, but I am in hurry so didnt have a chance to browse through forum.I'm new to SQL server. Apologies if this is already covered and would appreciate if someone can point me to that link. Here's the problem description
I have the below query. The problem is that this query is doing a lot of logical reads which is slowing it down. Not quite sure, but I feel a self join would save lot of reads. (Correct me if I am wrong and suggest a self join query even if it might not reduce the number of reads.).
DELETE FROM RPF WITH (ROWLOCK)
WHERE U_S = 'PPP'
AND e_d = '20100811'
AND e_id NOT IN ( SELECT DISTINCT e_id
FROM RPF WITH (NOLOCK)
WHERE U_S = 'PPP'
AND e_d = '20100811'
AND r_f = 'N')
I tried the below, but apparently, there are certain e_ids which have both 'Y' and 'N' in the R_F flag. so obviously the results differ.
DELETE FROM RPF WITH (ROWLOCK)
WHERE U_S = 'PPP'
AND e_d = '20100811'
AND R_F <> 'N'
Any help is appreciated.
-Chintan

Viewing 12 posts - 1 through 11 (of 11 total)

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

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

Try using NOT EXISTS. It might reduce the Number Of Reads.

DELETE

FROM RPF RO WITH (ROWLOCK)

WHERE RO.U_S = 'PPP'

AND RO.e_d = '20100811'

AND NOT EXISTS ( SELECT * FROM RPF RI WITH (NOLOCK) WHERE RO.U_S = 'PPP' AND RO.e_d = '20100811' AND RO.r_f = 'N' AND RI.e_id = RO.e_id )

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

somaiya.chintan SSC Veteran Points: 212 More actions · Answer 2

Thanks, I'm not at work now. I'll try that first thing I'm in and will post the results.

somaiya.chintan SSC Veteran Points: 212 More actions · Answer 3

The NOT EXISTS doesn't help much. It mnages to reduce the reads but only by a very very tiny number. Any other suggestions? Appreciate the help.

LutzM SSC Guru Points: 107049 More actions · Answer 4

I don't think you'd need the DISTINCT in your NOT IN clause. It won't matter anyway if there'd be more than one row returned per id. This would eliminate a usually expensive SORT DISTINCT operation.

Side note: I also vote against the heavy usage of all those locking hints. There a just few scenarios where such a hint is helpful (not that I can think of one right now though...).

So I would try:

DELETE FROM RPF

WHERE U_S = 'PPP'

AND e_d = '20100811'

AND e_id NOT IN ( SELECT e_id

FROM RPF

WHERE U_S = 'PPP'

AND e_d = '20100811'

AND r_f = 'N')

Another question would be: Are there any supporting indexes for the select query?

Or: Can you post the execution plan for that query?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Nevyn SSChampion Points: 14210 More actions · Answer 5

This would be the join syntax (not tested as I dont have ddl) if you dont want a subquery.

WITH rpfnot (e_id) AS

(

SELECT DISTINCT e_id

FROM RPF

WHERE U_S = 'PPP'

AND e_d = '20100811'

AND r_f = 'N'

)

DELETE RPF

FROM RPF

LEFT OUTER JOIN rpfnot ON RPF.e_id = rpfnot.e_id

WHERE rpfnot.e_id IS NULL

somaiya.chintan SSC Veteran Points: 212 More actions · Answer 6

Thanks. I tried this, but there appears to be some syntax error with it. Have never seen this kind of statement before so couldnt fix it somehow.

any more suggestions?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Not a suggestion, but...

http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/

Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 8

somaiya.chintan (8/25/2010)
DELETE FROM RPF WITH (ROWLOCK)

any specific reason to use ROWLOCK ? ...i think sql server itself decides to use locking mechanism.. i am just curious:-)

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 9

somaiya.chintan (9/7/2010)
Thanks. I tried this, but there appears to be some syntax error with it. Have never seen this kind of statement before so couldnt fix it somehow.
any more suggestions?

use semicolon like ;WITH rpfnot (e_id) AS

(

SELECT DISTINCT e_id

FROM RPF

WHERE U_S = 'PPP'

AND e_d = '20100811'

AND r_f = 'N'

)

DELETE RPF

FROM RPF

LEFT OUTER JOIN rpfnot ON RPF.e_id = rpfnot.e_id

WHERE rpfnot.e_id IS NULL

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Just note that it is not a requirement that a CTE start with a ;. It's a requirement that the statement before be terminated with a ;. Since this termination of statement with; is becoming more and more necessary on various statements, it will be useful to get into the the habit of always terminating every statement with a ;

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 11

GilaMonster (9/7/2010)
Just note that it is not a requirement that a CTE start with a ;. It's a requirement that the statement before be terminated with a ;. Since this termination of statement with; is becoming more and more necessary on various statements, it will be useful to get into the the habit of always terminating every statement with a ;

thanks Gail..i already know that but i posted it as i was assuming that the sql code is a intermediate part of any script or SP.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)