which kind of cursor usage is more appropriate?

[volcano]

Hello, do u think which one of the following performs better in a stored procedure?

1. There are many cursors which do a small part of job
2. There is only one cursor which does the largest part of
job

I know cursors can deteriorate the performance of an application. But in my case I have to use it. So welcome for your any advice!

 

[Tim1]

Hiya,

The majority of time taken using a cursor is in the DECLARE and OPEN clause, because that is where the cursor will build the query plan and then create a temporary table with the results set in it.

As a general rule of thumb, the longer the select statement takes to return results, the longer the cursor will take to open (that is basically what you do when you open a cursor). The fetch time is minimal, so long as there are not huge gaps that it must jump).

Basically, if your single select is very complicated, or has a large where clause, use the smaller ones.

Tim

 

[volcano]

Thanks Tim for you advice!