Update Large Table Help !!

Update Large Table Help !!

Post by Divyesh Khatr » Fri, 21 Sep 2001 00:55:15



I have a table with 3.5million records and i have one IDNO field with
primary key.  I want to you update a field with substring of another..

e.g.

Update table
set field2 = substring(field1,1,2)

I ran the following script

DECLARE MyUpdate CURSOR
READ_ONLY
FOR SELECT table1.idno FROM table1 WITH (NOLOCK)


OPEN MyUpdate



BEGIN

 BEGIN
  UPDATE TABLE1
SET FIELD2 = SUBSRTING(FIELD1,1,2)

 END

END

CLOSE MyUpdate
DEALLOCATE MyUpdate
GO

I had it run over night and it then I finally had to cancel it in the
morning becuase it was still running.. I checked and it only updated 320,000
records in 15 hours... Less than 10%..

Any ideas how i can super speed this up is appreciated.

Divyesh

 
 
 

Update Large Table Help !!

Post by Gary Vida » Thu, 20 Sep 2001 13:03:39


Perhaps you should just try an update statement
Say :

Update table1 SET FIELD2 = SUBSTRING(FIELD1,1,2)

Cursors are generally very resource intensive and is not advisable on such a
large rowset

HTH,

Gary Vidal
Systems Analyst

Update table1 set

> I have a table with 3.5million records and i have one IDNO field with
> primary key.  I want to you update a field with substring of another..

> e.g.

> Update table
> set field2 = substring(field1,1,2)

> I ran the following script

> DECLARE MyUpdate CURSOR
> READ_ONLY
> FOR SELECT table1.idno FROM table1 WITH (NOLOCK)


> OPEN MyUpdate



> BEGIN

>  BEGIN
>   UPDATE TABLE1
> SET FIELD2 = SUBSRTING(FIELD1,1,2)

>  END

> END

> CLOSE MyUpdate
> DEALLOCATE MyUpdate
> GO

> I had it run over night and it then I finally had to cancel it in the
> morning becuase it was still running.. I checked and it only updated
320,000
> records in 15 hours... Less than 10%..

> Any ideas how i can super speed this up is appreciated.

> Divyesh


 
 
 

1. Large Cursor updating Large Table

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

2. what is the code for system date

3. Large table update/vacuum PLEASE HELP!

4. Jump to selected record

5. A large update multiple tables!! Help

6. More invoice ?'s

7. UPDATE on large table NOT updating

8. OLE Problems

9. Need help updating UPDATE-ing large number of records

10. Updates Failing on large table

11. Begin Transaction for Update on a large table

12. Not able to update large table using dynaset recordset

13. Alter/update large tables - VERRRY annoying behaviour!