Opening cursor in stored procedure

[waggers]

I have created a cursor in a sp that is causing me some problems.

If I run the select statement(only) in Query Analyser I get a nil result in approx 1 sec - this is fine!

But if I create the cursor in QA or in the sp, everything is still fine until I issue the 'open' command. Opening the cursor takes about 4 minutes. The select statement is reasonably simple (1 inner join and two condition where clause)

Why does opening a nil result cursor take so long? And what can I do to improve this.

I am using SQL Server 7, WinNT 4.0 sp6

TIA

Tom W

[sig][/sig]

 

[waggers]

Problem solved. I created a clustered index and the whole sp now runs in approx 3 seconds.

waggers [sig][/sig]

 

[christine1]

How did you do it? I have a few stored procedures that (were created by someone else)that have many nested cursors. It literally takes hours to run the sp. Any advice on how to improve my sp's.

I am on 6.5 and preparing to move to 7.0. Should I just transfer the sproc's over and fix them there? (I don't think nested cursors will fly with 7.0)

Thanks,

Chris [sig][/sig]

 

[waggers]

Chris,

All of the stored procedures that I have created using 'nested cursors' have all performed horribly. By nested cursors I am talking about cursors inside cursors using loops etc.

In short, to get acceptable performance I got rid of all of them and re-engineered all my sql statements to allow for the use of single cursors within a stored procedure. There is probably a better way but I dont know what it is.

The current problem was resolved by revisiting my indexing - cut the time for a single cursor from 4 minutes to 3 seconds. A bit extreme but there you go!

You might want to have a look at what indexes are being used on the sql statements and create or revise any indexes that are not providing the correct behaviour.

BTW Changing one index to a clustered index cut the processing time from 4 secs to 3 secs on a small number of records - 25% improvement that should be significant as the records grow.

Currently this system is being prototyped, so the records numbers are quite low - no stress testing yet!

Cursors do seem to have performance problems when they are being opened...I dont know why but equivalent Oracle cursors seem to be much quicker. Possibly something I'm doing wrong, but I dont know where.

Let me know how you get on.

Tom W [sig][/sig]

 

[jnicho02]

Tom/Chris,
In the past I have found that programmers overuse cursors. Programmers often come from a sequential progamming background and the idea of cursors fits this.....get a record set, get next record, update the value, continue the loop....SQL is based around sets, and cursors mean that you are applying statements on a set of one thing at a time. This doesn't give it a chance to get up to speed, hence poor performance.

If you have lots of cursors I would start by seeing if it can be written in 'proper' SQL first. [sig]<p> <br><a href=mailto: > </a><br><a href=

http://www.geocities.com/jnicho02/>my

home</a><br> [/sig]

 

[christine1]

Thanks for the advive. I agree with you both, first, that cursors will hender SQL performance and secondly, programmers often come from a sequential progamming background, hence the creation of the cursed cursor .

I have a lot of work in front of me as I have over 100 sproc's that need to be run at any given time.

Final question though, should I bring my stored procedures over to 7.0 and work on them. Or fix them in 6.5 first?

Thanks again,

Chris
[sig][/sig]