Thunderstone Support Forums

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

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?

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

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.

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.

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.

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

Ah.. My kingdom for an alter command

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