Updating 19 million rows

[MiniMe]

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??

[Kai]

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?

[MiniMe]

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

[Kai]

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.

[John]

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.

[MiniMe]

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.

[Kai]

It will certainly be faster, since fewer indexes need to be modified during your update.

[MiniMe]

Ah.. My kingdom for an alter command

[bart]

It wouldn't matter much. Even if alter was provided it would still take about the same amount of time to restructure the table.