Large Cursor updating Large Table

Large Cursor updating Large Table

Post by BeverlyPerkin » Sat, 04 Mar 2000 04:00:00



I'm running a stored procedure building a large cursor (1 mill rows) to
update a large table (64 mill rows).  It stops sometimes with an error
"Could not generate asynchronous keyset.  The cursor has been
deallocated."

Has anyone had this problem?

Thanks for the help

 
 
 

Large Cursor updating Large Table

Post by BPMargoli » Sun, 05 Mar 2000 04:00:00


Beverly,

To be honest this is a guess ... but who knows, it might be the right one :-)

If you reference section "Keyset-driven Cursors" in the SQL Server 7.0 Books
Online, it states "The keyset for a keyset-driven cursor is built in tempdb when
the cursor is opened." So it might just be that you need a larger tempdb.


Quote:> I'm running a stored procedure building a large cursor (1 mill rows) to
> update a large table (64 mill rows).  It stops sometimes with an error
> "Could not generate asynchronous keyset.  The cursor has been
> deallocated."

> Has anyone had this problem?

> Thanks for the help


 
 
 

Large Cursor updating Large Table

Post by Beverly Perkin » Tue, 07 Mar 2000 04:00:00


Thanks for the info.

Since I posted this question, I found that this error is a known SQL7 problem and there may be a fix coming.  I found the error on the support website for Microsoft.com.

Our DBA's solution was to re-write the SP without a cursor.  This is not always possible.  What is your feeling about the use of cursors in stored procedures?

Thanks again.

* Sent from Devdex.com http://www.devdex.com The Web Developers Index *
The world's largest index site for Microsoft web technologies.

 
 
 

Large Cursor updating Large Table

Post by BPMargoli » Tue, 07 Mar 2000 04:00:00


Beverly,

I believe it is always possible to code an SP without a cursor. Either SQL (the
language) or T-SQL (which supports looping) can be used to avoid cursors.

When possible, straight SQL outperforms cursors usually by an order of
magnitude. The performance difference can vary when one is forced to use T-SQL
and looping.

BTW, thanks to Ron Talmadge for the discussion we had on this point last week.


Quote:> Thanks for the info.

> Since I posted this question, I found that this error is a known SQL7 problem

and there may be a fix coming.  I found the error on the support website for
Microsoft.com.
Quote:

> Our DBA's solution was to re-write the SP without a cursor.  This is not

always possible.  What is your feeling about the use of cursors in stored
procedures?
Quote:

> Thanks again.

> * Sent from Devdex.com http://www.devdex.com The Web Developers Index *
> The world's largest index site for Microsoft web technologies.

 
 
 

Large Cursor updating Large Table

Post by Beverly Perkin » Sat, 11 Mar 2000 04:00:00


Could you point me in the direction of some resources regarding looping in a stored procedure without cursors?  This is really the only reason I'm using a cursor in the first place.  I am attempting to update a 'batch' of rows at a time, then issue a commit.  If I knew how to grab a group of rows without a cursor, this would accomplish the same thing.

* Sent from Devdex.com http://www.devdex.com The Web Developers Index *
The world's largest index site for Microsoft web technologies.

 
 
 

Large Cursor updating Large Table

Post by William Talad » Tue, 14 Mar 2000 04:00:00


Here are two templates I copy and use when I want impact to be low during
working hours:

/* update every row in a table one row at a time by primary key */
set nocount on



begin
  UPDATE bky SET orig_seller_info = 0 where orig_seller_info is null and


end
go

/* update every row in a table one row at a time */
set nocount on
set rowcount 1  -- or 10 or 100 or n
go



begin
  UPDATE max_hold_bky SET year = xyear, docket_number = xdocket_number where
year is null

end
go
set rowcount 0
go

Could you point me in the direction of some resources regarding looping in a
stored procedure without cursors?  This is really the only reason I'm using
a cursor in the first place.  I am attempting to update a 'batch' of rows at
a time, then issue a commit.  If I knew how to grab a group of rows without
a cursor, this would accomplish the same thing.

* Sent from Devdex.com http://www.devdex.com The Web Developers Index *
The world's largest index site for Microsoft web technologies.

 
 
 

1. querying and cursors on large databases returning large resultsets

Hi,

I'm busy looking into changing a query console that performs queries on a
live system.  Obviously, there are issues involved when doing this, and I'd
just like some input as to what other people out there do.

The database I will be testing with is quite big (I haven't restored it yet,
but I've been told it's in the region of 25 gb).  Anyway, the current
consoles have become an issue for a while.  A user enters search criteria
for the transactions he wants, at which point the console then does the
query.  The query can be quite intensive on the live system, causing serious
degradation in it's performance.  I was wondering what opinions people out
there with this kind of experience do.

Some of the questions I have are:
*  How do search engines retrieve results so quickly, and know more or less
how many results they have?  An example is doing a search for "dynamic
cursor" on the google search engine.  It can tell you that it took 0.13
seconds to find a 99400 results.  How does it know this so quickly?  And how
does it know how many results are available (even if it is an estimate),
unless the entire query was performed?. Creating any kind of cursor
(client/server) for that would take a lot of memory.
* The current consoles I think populate a grid with all the results, so if
there are a few hundred thousand results, it results in having to move all
those results across the network.  Solutions would to be to use a server
cursor instead.  There is the option of keyset and dynamic.  A keyset
initially takes a long time to get all the results, and a lot of memory is
used on the server, and space in tempdb.  A dynamic cursor seems more
responsive initially and use less memory, because it doesn't determine the
whole resultset.  However, documentation seems to indicate that subsequent
fetches are resource intensive.  How much of an issue is this?  Would you
use suggest using a dynamic cursor?
* Obviously another solution is to never select more than a certain number
of records (doing a select top n).  Is this what anyone else out there does?
This is not always what is wanted, since someone might want to see more than
n results (although this value could be configurable).
* Yet another option would be to implement paging yourself (although the
commands would be specific in each case - I means this is not a generic
solution for applications).  How does this affect the query optimizer, since
the sql statement changes all the time?
* When having a query console, do you ensure that all search criteria is on
indexed columns?  or do you allow columns to be queried that are not indexed
(which results in a table scan).

thanks,
malcolm

2. SQXML Slow problem

3. UPDATE on large table NOT updating

4. Problem with Table.APPEND and .INSERT

5. How do i speed up inserting a large amount of data into a very large table

6. VB Programmer job opportunity in Victoria, BC

7. Descending Order Index

8. Large deletes/inserts against large tables....

9. Cursor fetch on large table

10. Cursor failure because table row too large

11. Cursors operations on large table

12. Slow Updating in large table