I have a table that has 19 million rows in it. It has a QTY column and I want to make the QTY column equal 1 on every row. However whenever I issue the update statement, it takes an hour to do 20,000 records. This means it will finish about the time I retire.. Is there anything I can do to speed this up??
Updating 19 million rows
Updating 19 million rows
What version of Texis is this (texis -version)? What is the full exact SQL statement (s) you are using? What are the types and fields of all indexes on this table?
Updating 19 million rows
Here ya go:
Texis Web Script (Vortex) Copyright (c) 1996-2000 Thunderstone - EPI, Inc.
Commercial Version 3.01.971289085 of Oct 11, 2000 (alpha-dec-osf4.0)
the statement is
tsql "update books set QTY_AVAIL = '1'"
The field is a varchar and there was no index on that column. Later I added one so that I could issue:
tsql "update books set QTY_AVAIL = '1' where QTY_AVAIL !='1'"
so it could start where it left off.
did you want to see a dump from SYSINDEX and SYSCOLUMNS
Texis Web Script (Vortex) Copyright (c) 1996-2000 Thunderstone - EPI, Inc.
Commercial Version 3.01.971289085 of Oct 11, 2000 (alpha-dec-osf4.0)
the statement is
tsql "update books set QTY_AVAIL = '1'"
The field is a varchar and there was no index on that column. Later I added one so that I could issue:
tsql "update books set QTY_AVAIL = '1' where QTY_AVAIL !='1'"
so it could start where it left off.
did you want to see a dump from SYSINDEX and SYSCOLUMNS
Updating 19 million rows
What other indexes exist on the table? Since QTY_AVAIL is a varchar (why not int?), every row update causes all indexes to be updated as well, since the row might move.
Updating 19 million rows
Is there some reason QTY_AVAIL is a varchar instead of an int? Since updating a varchar field could potentially move the record around all the indexes need to be updated with that update.
John Turnbull
Thunderstone Software
Thunderstone Software
Updating 19 million rows
Yeah.. its a legacy column left over from long ago.. So what your telling me is if I rebuild the table with int as the column type my problem will go away??
M.
M.
Updating 19 million rows
It will certainly be faster, since fewer indexes need to be modified during your update.
Updating 19 million rows
Ah.. My kingdom for an alter command
Updating 19 million rows
It wouldn't matter much. Even if alter was provided it would still take about the same amount of time to restructure the table.