Why declare cursor and open cursor both cost same amount of time 
Author Message
 Why declare cursor and open cursor both cost same amount of time
I have to use cursor to return a window of data, the query is complex, and I
am trying to improve the performance. I found the declare cursor statement
takes a second,  I took that, I think it need time to parse or compile it,
but the open cursor statement takes almost same amount of time, that I can
not understand, anyone know how to improve here? by the way, the query for
the cursor include a udf.

Thanks.



Tue, 01 Mar 2005 04:27:16 GMT
 Why declare cursor and open cursor both cost same amount of time

David,

A DECLARE CURSOR, I believe, just creates the query plan; it does not
actually retrieve any data. The OPEN actually retrieves the data, and,
depending upon the type of cursor used, might write some or all of the
retrieved data to the tempdb database.

How much data is being retrieved in the OPEN?

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> I have to use cursor to return a window of data, the query is complex, and
I
> am trying to improve the performance. I found the declare cursor statement
> takes a second,  I took that, I think it need time to parse or compile it,
> but the open cursor statement takes almost same amount of time, that I can
> not understand, anyone know how to improve here? by the way, the query for
> the cursor include a udf.

> Thanks.



Tue, 01 Mar 2005 04:53:11 GMT
 Why declare cursor and open cursor both cost same amount of time
I found that only happens when the query of the cursor build around a udf,
if the query is selecting data from a view, the declare doesn't take long
time. my udf return around 4000 records.


Quote:
> David,

> A DECLARE CURSOR, I believe, just creates the query plan; it does not
> actually retrieve any data. The OPEN actually retrieves the data, and,
> depending upon the type of cursor used, might write some or all of the
> retrieved data to the tempdb database.

> How much data is being retrieved in the OPEN?

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > I have to use cursor to return a window of data, the query is complex,
and
> I
> > am trying to improve the performance. I found the declare cursor
statement
> > takes a second,  I took that, I think it need time to parse or compile
it,
> > but the open cursor statement takes almost same amount of time, that I
can
> > not understand, anyone know how to improve here? by the way, the query
for
> > the cursor include a udf.

> > Thanks.



Tue, 01 Mar 2005 07:46:30 GMT
 Why declare cursor and open cursor both cost same amount of time
David,

Ahhh ... additional, and possibly critical information   :-)

There are three types of UDF's:

scalar, in-line and table.

In-line UDF's are very efficient (as efficient as views). Scalar UDF's are
very inefficient performance wise ... they operate at the speed of cursors,
and cursors typically operate at least an order of magnitude slower than a
set oriented equivalent. Table UDF's have some overhead, but can perform
satisfactorily under most conditions.

What type of UDF are you using? If it's a scalar UDF ... then that's your
problem. Scalar UDFs have the advantage of encapsulating code for ease of
maintenance, but typically cause a very significant performance hit.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> I found that only happens when the query of the cursor build around a udf,
> if the query is selecting data from a view, the declare doesn't take long
> time. my udf return around 4000 records.



> > David,

> > A DECLARE CURSOR, I believe, just creates the query plan; it does not
> > actually retrieve any data. The OPEN actually retrieves the data, and,
> > depending upon the type of cursor used, might write some or all of the
> > retrieved data to the tempdb database.

> > How much data is being retrieved in the OPEN?

> > -------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> > can be cut and pasted into Query Analyzer is appreciated.



> > > I have to use cursor to return a window of data, the query is complex,
> and
> > I
> > > am trying to improve the performance. I found the declare cursor
> statement
> > > takes a second,  I took that, I think it need time to parse or compile
> it,
> > > but the open cursor statement takes almost same amount of time, that I
> can
> > > not understand, anyone know how to improve here? by the way, the query
> for
> > > the cursor include a udf.

> > > Thanks.



Tue, 01 Mar 2005 10:04:10 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Cursor in Trigger - with cursor not open when running second time in the same SQL session

2. Could not complete cursor operation, because the table schema changed after cursor was declared

3. Could not complete cursor operation, because the table schema changed after cursor was declared

4. declare curW cursor for EXEC spMySproc - Trying to assign sp resultset to cursor variable

5. Help ! Opened cursors exceed max opened cursors

6. Open cursors and more open cursors

7. Need Help Please , Invalid Cursor Position Error and Cursor Not Open Error

8. Detect if Cursor Opened and Detect if Cursor Allocated

9. 40088 No open cursor or cursor closed.

10. help-no open cursor or cursor closed

11. No open cursor or cursor closed !!!

12. Multiple cursors - second cursor hangs when opened


 
Powered by phpBB® Forum Software