How does a cursor affect the optimizer (and the execution plan) ?

Question

How does a cursor affect the optimizer (and the execution plan) ?

Gamleur84

Default port

Points: 1475
More actions
March 6, 2019 at 1:37 am

#416746

Hi,
This is Part 2 of Slow in the Application, Fast in SSMS. How to force the execution plan generated by SMSS.
In summary, the same query is performing slow in the application and fast in SSMS. I can see that the execution plans are different and that would explain why it is slow in the application. The queries are parametrized, the parameters and the attributes are the same. The only difference I can find is the query triggered by the application is using a cursor.
How does a cursor affect the optimizer ? The difference between both execution plan is the first index seek. The bad execution plan starts with an index seek on 3 common value. It reads 1/3 of the table, which represents 90 000 rows. Then the data goes to the next the next operator which is a nested loop. The good execution starts an index seek (different index) on a uncommon value and reads 1 rows. It makes the whole difference in term of logical reads.
I am not trying fix the execution plan at this point, but I am keen to understand why a cursor can make a difference.
Does it make sense ?
Thank you

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

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

So, without the plans in hand to compare them, everything I'm writing is speculation.

First up, we're attempting to compare the execution of two radically different queries, one with a cursor and one without. Can this seriously impact the performance of the query? Yes. Depending on what's going on, absolutely the query performance can be affected.

Now, I think I'm getting from your question that, in fact, you're seeing two different execution plans for the SELECT statement of the cursor, one when the cursor is run, and one when you just run the query. This could come from two different, but related, causes. First, you're not using the same parameter values in the two queries. Due to parameter sniffing, it's possible that your data distribution results in plan for one value and one plan for another value. Or, you're testing the cursor by running a procedure with parameters (and sniffing) while comparing it to a batch run through SSMS using a local variable (no sniffing except in the case of recompiles). If that speculation is correct, then the difference in plans is down to the sniffed value of a parameter versus the average value used when you have a local variable (again, unless the statement recompiles, then the local variable can be sniffed). For giggles you could put the RECOMPILE hint in the local batch to see if it changes the plan to match what you're seeing from the procedure (with parameter sniffing).

Or, I'm way off base and don't understand your situation because I can't see the queries or the plans.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 2

Gamleur84 - Wednesday, March 6, 2019 1:37 AM
Hi,
This is Part 2 of Slow in the Application, Fast in SSMS. How to force the execution plan generated by SMSS.
In summary, the same query is performing slow in the application and fast in SSMS. I can see that the execution plans are different and that would explain why it is slow in the application. The queries are parametrized, the parameters and the attributes are the same. The only difference I can find is the query triggered by the application is using a cursor.
How does a cursor affect the optimizer ? The difference between both execution plan is the first index seek. The bad execution plan starts with an index seek on 3 common value. It reads 1/3 of the table, which represents 90 000 rows. Then the data goes to the next the next operator which is a nested loop. The good execution starts an index seek (different index) on a uncommon value and reads 1 rows. It makes the whole difference in term of logical reads.
I am not trying fix the execution plan at this point, but I am keen to understand why a cursor can make a difference.
Does it make sense ?
Thank you

A part of the problem is that the application is probably using different connection settings than SSMS. For example, SSMS defaults to using "SET ARITHABORT" to ON whereas the application is probably defaulting to it being OFF. The same is particularly true of the ANSI NULL settings (as has sparked a great amount of controversy in the development world because MS wants to make all connections have them ON with no option to revert to OFF).

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

Jeff Moden - Wednesday, March 6, 2019 9:42 AM
A part of the problem is that the application is probably using different connection settings than SSMS. For example, SSMS defaults to using "SET ARITHABORT" to ON whereas the application is probably defaulting to it being OFF. The same is particularly true of the ANSI NULL settings (as has sparked a great amount of controversy in the development world because MS wants to make all connections have them ON with no option to revert to OFF).

Additionally - if I recall the other thread properly - the difference appears to be that the query coming from that application is execute using sp_cursor_prepare (in other words - as a cursor) whereas from SSMS it is not executed that way.

So - the question I have is how is that code called from within the application? What is the actually application code that is setting up this query and calling it? Is it python, java, c# - something else? My guess is that no only is there ANSI setting issues - but also application code issues.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Gamleur84 Default port Points: 1475 More actions · Answer 4

Thank for for all response. Finally I was able to get the same performance in SMSS and the application. I different was the values passed in parameters. I initially captured the query in SQL Profiler and for some reason the value shown were incorrect. Maybe because I was not reading the trace correctly due to the fact this is is executed in a cursor and that seems to be a bit more difficult to track. Anyway, I figured out the correct values and got the same performance issue once I put the correct value in the query in SMSS.