Very slow sp_cursorprepexec

Question

Very slow sp_cursorprepexec

Viewing 9 posts - 1 through 10 (of 10 total)

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

SQLian SSC-Addicted Points: 422 More actions · Answer 1

Assuming the query is the same in both instances I would probably be looking at the connection from the app to SQL Server. You have given no clue as to what tech the app is using though.

Mike01 SSChampion Points: 11420 More actions · Answer 2

can you grab the actual query that the app is sending (the sp_cursorprepexec statement)? Run that in ssms to see how it performs. At least then it's apples to apples. Then you can examine the execution plan to see what it is doing. Maybe something simple as adding with recompile might work

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

andrew 67979 Ten Centuries Points: 1268 More actions · Answer 3

Yes the query is the same in both instances
It's just a cut and paste from the profiler and obviously replacing the params with the actual values
In SSMS it runs in less than one second ... as opposed to over 13 minutes as reported in both cases by the Profiler

Jo Pattyn SSC-Dedicated Points: 32571 More actions · Answer 4

"obviously replacing the params with the actual values"
do you mean

SELECT *
FROM A
where col1=@param1

with
SELECT * FROM A where col1=1
In the latter case you don't have parameter sniffing and you have the actual value the optimizer kan optimize for.
(otherwise it picks the one of the first execution)

Did you execute a parameterized query?

andrew 67979 Ten Centuries Points: 1268 More actions · Answer 5

andrew 67979

Ten Centuries

Points: 1268

August 9, 2018 at 8:19 am

#2001026

Yes that is what I did

What is parameter sniffing ?

Jo Pattyn SSC-Dedicated Points: 32571 More actions · Answer 6

Some recent blogs about parametersniffing
Parametersniffing 1
Parametersniffing 2
Parametersniffing 3

andrew 67979 Ten Centuries Points: 1268 More actions · Answer 7

Thanks for the links

Just so I don't misunderstand this does it definitely apply to sp_cursorprepexec ?

The reason I ask is are these calls not 'one-off' queries i.e. the sp is not stored on the sever and callled the SQL is sent each time with this command ?

I certainly see the same slow performance each time

Thanks

sgmunson SSC Guru Points: 110651 More actions · Answer 8

Just an fyi ... If memory serves me correctly, sp_cursorprepexec is there to get ready to serve up the result set as a cursor that your application can "hold on to", as long as it keeps the connection open... If that's the case, then running the query, unless you also include the sp_cursorprepexec; inside of SSMS, is a very different animal. It just isn't going to do the same thing. If my recall on this is correct, then you might have some kind of capacity constraint that you're not aware of, and was doing a pretty good job of hiding itself until now...

Because I wasn't sure, I looked it up, and here's where you can find info on it:

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-cursorprepexec-transact-sql?view=sql-server-2017

That's Microsoft's documentation. Just a quick web search on sp_cursorprepexec provided a lot more links to look at...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 9

It's not just parameter sniffing to be concerned with. You need to check the datatypes of the parameters in the sp_cursorprepexec with those in the table. For example, if Entity Framework or some other god-forsaken orm is passing parameters as NVARCHAR() and your table column is VARCHAR(), you'll have an implicit conversion where the entire column in the tables must be converted before the comparison can be made. I'm going through that again (for the 253,786th time) with 3rd party software.

It prevents index seeks and can cause some seriously incorrect plans.

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