Cursor keep runing out my memory, please help.

Cursor keep runing out my memory, please help.

Post by Tomm » Fri, 27 Apr 2001 17:17:01



Hi All,

Say If i want to update a table T1(c1,c2,c3,c4)  with 1,300,000 records.

I use this command:

update T1 set c1='a', c2='b',c3='c',c4 ='d'

Now if I use cursor do the same job like below:

declare test cursor  for select c1,c2,c3,c4 from T1
open test
fetch next from test

begin
update T1 set c1='a',     c2='b',     c3='c',     c4='d'
where current of test
end
close test
deallocate test
----------------------------------------
Holy slow. The hard driver keep runing and soon my server tell me that it
has been run out memory. It is only spend about 2 mins to do the update with
the first command, but the second script take about 40 mins and give me the
message tell me momory has been used out.

Any expert can tell what happened? Is there something wrong in my second
script? Some how I need the second script to process records one by one.
Please point me to the right way to use the Cursor.

Any help will be appreciated!

Tim

 
 
 

Cursor keep runing out my memory, please help.

Post by oj » Sat, 28 Apr 2001 02:42:53


Tommy,

Cursor will always be S L O W that's why we will try to avoid it if we can.

If you are going to update that many rows, I would suggest running the
update without cursor.  BTW, why do you need to update row one by one? Did
you know that you can use "filter/criteria" in your update statement...

begin tran
Update T1
set c1='a',
    c2='b',
    c3='c',
    c4='d'
where c0 = 'xxxxx'

begin
print 'successful'
commit tran
end
else
begin
print 'not good...'
rollback tran
end

hth.

-oj


> Hi All,

> Say If i want to update a table T1(c1,c2,c3,c4)  with 1,300,000 records.

> I use this command:

> update T1 set c1='a', c2='b',c3='c',c4 ='d'

> Now if I use cursor do the same job like below:

> declare test cursor  for select c1,c2,c3,c4 from T1
> open test
> fetch next from test

> begin
> update T1 set c1='a',     c2='b',     c3='c',     c4='d'
> where current of test
> end
> close test
> deallocate test
> ----------------------------------------
> Holy slow. The hard driver keep runing and soon my server tell me that it
> has been run out memory. It is only spend about 2 mins to do the update
with
> the first command, but the second script take about 40 mins and give me
the
> message tell me momory has been used out.

> Any expert can tell what happened? Is there something wrong in my second
> script? Some how I need the second script to process records one by one.
> Please point me to the right way to use the Cursor.

> Any help will be appreciated!

> Tim


 
 
 

Cursor keep runing out my memory, please help.

Post by oj » Sat, 28 Apr 2001 03:46:46


Steve,

There is always a way to do things.  However, without knowing your
DDL/sprocs it's hard to imagine.

Perhaps, you could post some snippets.

Remember, you can update a column with calculated value. For example..

update t1
set col1=(select count(*) + 100 from t2)
where col2='xxxx'

hth.

-oj


> Tks oj,

> The reason why I want to process rows one bye one is I can not use one
> Update statement with filter do the job. It is too complex to use just one
> update statement. Say for each row I need to call another SP to process
the
> data and use the returned result to update this record.

> It there anyway can make it faster?

> Thanks for the advice !

> Steve



> > Tommy,

> > Cursor will always be S L O W that's why we will try to avoid it if we
> can.

> > If you are going to update that many rows, I would suggest running the
> > update without cursor.  BTW, why do you need to update row one by one?
Did
> > you know that you can use "filter/criteria" in your update statement...

> > begin tran
> > Update T1
> > set c1='a',
> >     c2='b',
> >     c3='c',
> >     c4='d'
> > where c0 = 'xxxxx'

> > begin
> > print 'successful'
> > commit tran
> > end
> > else
> > begin
> > print 'not good...'
> > rollback tran
> > end

> > hth.

> > -oj



> > > Hi All,

> > > Say If i want to update a table T1(c1,c2,c3,c4)  with 1,300,000
records.

> > > I use this command:

> > > update T1 set c1='a', c2='b',c3='c',c4 ='d'

> > > Now if I use cursor do the same job like below:

> > > declare test cursor  for select c1,c2,c3,c4 from T1
> > > open test
> > > fetch next from test

> > > begin
> > > update T1 set c1='a',     c2='b',     c3='c',     c4='d'
> > > where current of test
> > > end
> > > close test
> > > deallocate test
> > > ----------------------------------------
> > > Holy slow. The hard driver keep runing and soon my server tell me that
> it
> > > has been run out memory. It is only spend about 2 mins to do the
update
> > with
> > > the first command, but the second script take about 40 mins and give
me
> > the
> > > message tell me momory has been used out.

> > > Any expert can tell what happened? Is there something wrong in my
second
> > > script? Some how I need the second script to process records one by
one.
> > > Please point me to the right way to use the Cursor.

> > > Any help will be appreciated!

> > > Tim

 
 
 

Cursor keep runing out my memory, please help.

Post by Stev » Sat, 28 Apr 2001 03:23:18


Tks oj,

The reason why I want to process rows one bye one is I can not use one
Update statement with filter do the job. It is too complex to use just one
update statement. Say for each row I need to call another SP to process the
data and use the returned result to update this record.

It there anyway can make it faster?

Thanks for the advice !

Steve


> Tommy,

> Cursor will always be S L O W that's why we will try to avoid it if we
can.

> If you are going to update that many rows, I would suggest running the
> update without cursor.  BTW, why do you need to update row one by one? Did
> you know that you can use "filter/criteria" in your update statement...

> begin tran
> Update T1
> set c1='a',
>     c2='b',
>     c3='c',
>     c4='d'
> where c0 = 'xxxxx'

> begin
> print 'successful'
> commit tran
> end
> else
> begin
> print 'not good...'
> rollback tran
> end

> hth.

> -oj



> > Hi All,

> > Say If i want to update a table T1(c1,c2,c3,c4)  with 1,300,000 records.

> > I use this command:

> > update T1 set c1='a', c2='b',c3='c',c4 ='d'

> > Now if I use cursor do the same job like below:

> > declare test cursor  for select c1,c2,c3,c4 from T1
> > open test
> > fetch next from test

> > begin
> > update T1 set c1='a',     c2='b',     c3='c',     c4='d'
> > where current of test
> > end
> > close test
> > deallocate test
> > ----------------------------------------
> > Holy slow. The hard driver keep runing and soon my server tell me that
it
> > has been run out memory. It is only spend about 2 mins to do the update
> with
> > the first command, but the second script take about 40 mins and give me
> the
> > message tell me momory has been used out.

> > Any expert can tell what happened? Is there something wrong in my second
> > script? Some how I need the second script to process records one by one.
> > Please point me to the right way to use the Cursor.

> > Any help will be appreciated!

> > Tim

 
 
 

Cursor keep runing out my memory, please help.

Post by Joe Celk » Sat, 28 Apr 2001 14:12:39


Quote:>> The reason why I want to process rows one bye one is I can not use one UPDATE statement with filter do the job. It is too complex to use just one UPDATE statement. <<

I seriously doubt this.  I had a consulting job at McGraw-Hill Educational Testing Services years ago where they updated a table based on grading stadndardized tests.  The guy that had written the code did a terrible job of it.  I changed it and removed hundreds of lines of code and got it to run about 2,700 times faster without his procedural code.  

By using CASE expressions, subqueries expressions and good logic, you can do the same thing and get it to work.  Since you did not publish any DDL or tell us what you are doing, nobody can help you except with generalities.  

Quote:>> ... Say for each row I need to call another SP to process the data and use the returned result to update this record. <<

There is a major problem.  You are thinking about procedures that operate and return scalar values -- like this:

WHILE ...
BEGIN ...
   UPDATE Foobar
      SET xxx = myproc(a, b, c),
   WHERE CURRENT OF Curse_of_Foobar;
END;

Instead think about code like this:

  UPDATE Foobar
     SET xxx =
             CASE WHEN a > b
                  THEN 75
                  WHEN ...
                  ELSE xxx END,
         yyy =COALESCE (
             (SELECT MAX (f)
                FROM Bloop
               WHERE Bloop.xx = Foobar.xxx), 0),
         ...
 WHERE ... ;

Take all the sequential procedureal logtic and turn it into declarative statements and set oriented logic.  In some cases, you might have to do one UPDATE after the other on the same table, but try to avoid that, too.

--CELKO--

SQL guru at Trilogy
===========================
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

1. query time outs! please help

iam trying to run this following query for within EM
select count(*) from tablename

it gives me the following error.
microsoft[odbc sql server driver] timeout expired

i have set the time out options to 0 in EM server
properties, but still no luck. The same query runs fine in
sql query anlayzer. but fails with the same error if tried
within ado application. this table contains 50,00,000 rows.

please if any one has a soln kindly help

2. DATE is not a valid function

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

4. Sorting Records in a Table

5. Help datareport keeps showing same cursor ?

6. Sql Server GUI

7. HELP! OnLine keeps grabbing virtual memory

8. Database Reviews

9. Please help - Merge replication keeps failing

10. Please help - Keep getting authentication failure when trying to restore database

11. FIXED: Please help - Keep getting authentication failure when trying to restore database

12. ADO Question - Please help - How To Keep one connection open

13. Paradox File Associations - keeps grabbing .SQL extention - help please