i have stored procedure with 45 cursor, so the stored procedure taking long time to finish. is it possible to get running time of each cursor in the SP? , if yes please help me out.

thanksSelva

45 cursors?!

I'm not even going to ask what your sproc does. It could be a very complicated process you're doing over there or it could be Jeff Moden's worst nightmare lol. Are these cursors modifying data row by row or are they being used to drive a complicated ETL system or something?

On a serious note, use a logging table where you insert a row for each beginning and end of a cursor. You can then review the result of the log table for times of each cursor.

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda. David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.Howard Zinn

Instead of using CURSOR you can try WHILE loop like below example. If possible dont use CURSORs in the procedure.

A well written forward-only readonly cursor will perform similarly to a WHILE loop. Changing this stored procedure to using WHILE loops instead of cursors will cost a great deal of time and run the risk of introducing errors - and may not improve performance at all.A stored procedure using 35 cursors suggests naive coding - but it may not be the case; maintenance routines commonly (and correctly) use cursors. Without seeing the code, you're guessing.

i have stored procedure with 45 cursor, so the stored procedure taking long time to finish. is it possible to get running time of each cursor in the SP? , if yes please help me out.

thanksSelva

It would help us a great deal if you could post the code - or part of it, if it's lengthy. If you choose to post only a part of it, make sure you include at least a couple of the cursor loops so folks can get some idea of what it's supposed to do.

If it's not realistic to convert the sp to set-based coding, then there may still be some scope for improving the performance of the sp by changing the definition and use of the cursors.

,@LineType as varchar (20) --Added For ASSM on 22-apr-2010 ,@DiscountOnLabourCharges as Decimal--Added For ASSM on 22-apr-2010 ,@DiscountOnMaterialCharges as Decimal--Added For ASSM on 22-apr-2010 ,@DiscountOnStoneCharges as Decimal--Added For ASSM on 22-apr-2010 ,@LabourCharges as Decimal--Added For ASSM on 22-apr-2010 ,@MaterialCharges as Decimal--Added For ASSM on 22-apr-2010 ,@SalesTax as Decimal--Added For ASSM on 22-apr-2010 ,@ServiceTax as Decimal--Added For ASSM on 22-apr-2010 ,@StoneCharges as Decimal--Added For ASSM on 22-apr-2010

Your given example doesn't contain anything qualifying use of cursors (or WHILE LOOPs, which may even be slower than properly implemented cursors).

Having 45 cursors means you are trying to do some complex (or at least multi-step processing). In order to help we will need much more details and explanation of requirements. Please follow the link at the bottom of my signature which will lead you to the tips how to get bets help in shortest time on this forum.

i have stored procedure with 45 cursor, so the stored procedure taking long time to finish. is it possible to get running time of each cursor in the SP? , if yes please help me out.

thanksSelva

If you are unwilling to optimise all of this code as set-based queries, which most folks would recommend, then there is some scope for improving the performance of the cursors. I recommend you read this excellent article by Hugo Kornelis.

With 45 cursors in one sproc he will never know which ones are taking the longest unless he implements some logging.

This is why I suggested he inserts a record into a log table at the beginning and end of each cursor. He can then work out from his log table the cursors which are taking the longest time?

At least then he can concentrate on tackling the worst cursors rather than recoding them all to set based.

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda. David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.Howard Zinn