Slow performance in a cursor when using a sub select as part of the where clause

Question

Post reply

Slow performance in a cursor when using a sub select as part of the where clause

railgod

SSC Enthusiast

Points: 154
More actions
September 23, 2010 at 2:48 am

#229618

Hi all
I have a stored procedure that uses a cursor based on a set of clientid's. It's currently defined as
WHERE something something
AND
clients.clientid in
(
19636,
24705,
30147,
31967,
32687,
39575,
52049,
52394,
53607,
55695,
61808,
64393,
64479,
64541,
64719,
65663,
66000,
70976,
71991,
72068,
73327,
74084,
74309,
75453,
76099,
77034,
91595,
91706,
92993,
93010,
93866,
98629,
102414,
104216,
104891,
105090,
105483,
105514,
105612,
105752,
106776,
107338,
107490,
107871,
107985,
108181,
108189,
108213,
108254,
108511,
108664,
108757,
108965,
109196,
109228,
109418,
109558,
109738,
109876,
110094,
110638
)
I have since changed the where clause to read
WHERE something something
AND clients.clientid in (select clientid from table where ...)
The subselect I am using returns an identical set of clientid's, however, the performance of the stored procedure is dramatically altered by this simple change and the execution time has gone from just over 7 minutes to an as yet undetermined time (I've waited an hour and cancelled the execution).
Is there any particular reason for this behaviour?
TYIA.

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

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

post table and index definition along with exec plan.See only the query cant give good/appropriate hint/suggestion

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Two suggestions

Run this part on its own: (select clientid from table where ...)

Post the whole sproc.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

raistlinx Ten Centuries Points: 1345 More actions · Answer 3

"Slow performance in a cursor"... I stopped reading here.

Seriously though, this is a little different than what you asked for, but in addition to looking at your sub select you will get much better performance if you rewrite so you don't use cursors.

If I were to guess at your subquery problem, I would say you are missing some index(s) and querying a huge table. But as others have suggested please post your query and execution plan.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

raistlinx (9/24/2010)
"Slow performance in a cursor"... I stopped reading here.
Seriously though, this is a little different than what you asked for, but in addition to looking at your sub select you will get much better performance if you rewrite so you don't use cursors.
If I were to guess at your subquery problem, I would say you are missing some index(s) and querying a huge table. But as others have suggested please post your query and execution plan.

I agree. There's a good chance that the 7 minute cursor could take 7 seconds with a set based redaction.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

railgod SSC Enthusiast Points: 154 More actions · Answer 5

Thanks for all the suggestions guys. I've managed to reduce the overhead of doing a subselect by using a temp table and doing a join so the urgency is lost. I do intend to re-write a lot of the sprocs we have here as many of them use cursors, but time pressure is a huge factor at the moment (as always) so it will have to wait.

Thanks again chaps.