SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


NOT IN query very expensive, 100% CPU


NOT IN query very expensive, 100% CPU

Author
Message
scogeb
scogeb
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 420
Hello all. Hopefully someone will be able to help me out with this query.

The below query takes 42 minutes, returns 1 row and sits at 100% CPU the entire time.
SELECT table1.column1,'99',left(table1.column2,2),max(table1.column3),
'Yes', 'Test'
FROM table1
WHERE ltrim(rtrim(table1.column1))+ltrim(rtrim(left(table1.column2,2)))
NOT IN (SELECT DISTINCT ltrim(rtrim(table2.column1))+ltrim(rtrim(left(table2.column2,2)))
FROM table2)
AND table1.column4 = 'Yes'
GROUP BY table1.column1,left(table1.column2,2)

When I break it into the 2 componants like below, they both finish in < 2 seconds.

Part 1 - 1 seconds, 15,000 rows
SELECT ltrim(rtrim(table1.column1))+ltrim(rtrim(left(table1.column2,2))) as test, table1.column1,'99',left(table1.column2,2),max(table1.column3), 'Yes', 'Test'
FROM table1
WHERE table1.column4 = 'Yes'
GROUP BY table1.column1,left(table1.column2,2)
1 second 15,000 rows

Part 2 - 2 seconds, 19,000 rows
SELECT DISTINCT ltrim(rtrim(table2.column1))+ltrim(rtrim(left(table2.column2,2)))
FROM table2
2 seconds 19,000 rows

What is wrong with the first query that causes it to take so long? It's really just a combination of the bottom 2 queries.

Thanks!
lrosales
lrosales
SSC-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 Visits: 428
Try using not exists(= boolean) instead of not in(= iteration) :-)

"We never plan to Fail, We just fail to plan"Smile
Luis Cazares
Luis Cazares
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17376 Visits: 19122
Another option:

SELECT table1.column1,
'99',
left(table1.column2,2),
max(table1.column3),
'Yes',
'Test'
FROM table1
LEFT
JOIN table2 ON ltrim(rtrim(table1.column1))+ltrim(rtrim(left(table1.column2,2)))
= ltrim(rtrim(table2.column1))+ltrim(rtrim(left(table2.column2,2)))
WHERE table2.column1 IS NULL
AND table1.column4 = 'Yes'
GROUP BY table1.column1,left(table1.column2,2)




Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
carlosaamaral
carlosaamaral
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 1049
SELECT table1.column1,
'99',
left(table1.column2, 2),
max(table1.column3),
'Yes',
'Test'
FROM table1
WHERE NOT EXISTS ( SELECT 1
FROM TABLE2 WHERE ltrim(rtrim(table2.column1)) + ltrim(rtrim(left(table2.column2, 2)))=
ltrim(rtrim(table1.column1)) + ltrim(rtrim(left(table1.column2, 2)))
)

AND table1.column4 = 'Yes'

GROUP BY table1.column1, Left(table1.column2, 2)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27212 Visits: 17557
carlosaamaral (9/11/2012)
SELECT table1.column1,
'99',
left(table1.column2, 2),
max(table1.column3),
'Yes',
'Test'
FROM table1
WHERE NOT EXISTS ( SELECT 1
FROM TABLE2 WHERE ltrim(rtrim(table2.column1)) + ltrim(rtrim(left(table2.column2, 2)))=
ltrim(rtrim(table1.column1)) + ltrim(rtrim(left(table1.column2, 2)))
)

AND table1.column4 = 'Yes'

GROUP BY table1.column1, Left(table1.column2, 2)


The reason the original (and yours) are slow is because they are not SARGable. The where clause have functions in them which renders the indexes unusable and causes a table/index scan. The left join solution that Luis presented should prove to perform far better.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91537 Visits: 45285
Is the ltrim and rtrim necessary? SQL ignores trailing spaces when comparing string columns, if you have leading spaces it's probably better to clean up the data if possible.

If you can get rid of the functions (they're often used where not necessary), then use not exists or not in (the left join is slightly slower usually). Also consider that there's a behavioural difference between not in and not exists when nulls are involved (on non-nullable columns they perform identically)

p.s. You don't need a DISTINCT in an IN or NOT IN subquery.

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


Sergiy
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10914 Visits: 11970
carlosaamaral (9/11/2012)
SELECT table1.column1,
'99',
left(table1.column2, 2),
max(table1.column3),
'Yes',
'Test'
FROM table1
WHERE NOT EXISTS ( SELECT 1
FROM TABLE2 WHERE ltrim(rtrim(table2.column1)) + ltrim(rtrim(left(table2.column2, 2)))=
ltrim(rtrim(table1.column1)) + ltrim(rtrim(left(table1.column2, 2)))
)

AND table1.column4 = 'Yes'

GROUP BY table1.column1, Left(table1.column2, 2)


I believe the only reason to use concatenation was IN construction.
NOT EXISTS does not need that:

SELECT table1.column1,
'99',
left(table1.column2, 2),
max(table1.column3),
'Yes',
'Test'
FROM table1
WHERE NOT EXISTS ( SELECT *
FROM TABLE2
WHERE table2.column1 = table1.column1
and left(table2.column2, 2) = left(table1.column2, 2)
)
AND table1.column4 = 'Yes'
GROUP BY table1.column1, Left(table1.column2, 2)



I removed trims as most likely they are not required.
RTRIM is not needed for sure, and LTRIM looks like just a sign of a bad habit.

Please check your data to make sure my assumption is correct.
scogeb
scogeb
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 420
Thanks everyone. I learned a lot here. I went with the NOT EXISTS and it finishes in 2 seconds now. Very nice!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90704 Visits: 41150
scogeb (9/12/2012)
Thanks everyone. I learned a lot here. I went with the NOT EXISTS and it finishes in 2 seconds now. Very nice!


Have you tried the NOT IN without the DISTINCT?

Also, what's with all the LTRIM/RTRIM stuff? Data should be properly stored without leading spaces and trailing spaces hardly ever matter except when determining the actual number of bytes a strinng may contain.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search