Re: Speed up alter-table execution

I would imagine that PDQ would help, but I don't know for sure. Probably depends on whether or not the table is fragmented. Your thought on a new table will probably be your best bet. The lvarchar modification is what is causing the ALTER to take so long because it isn't an "in place alter" so you are in reality doubling you table size anyway and chewing up tons of log space to make this ALTER happen. You would be better off to create a new "RAW" table and INSERT INTO SELECT * FROM blah; This will avoid the logging and maximize your speed requirement. "Terrence Mu...." <terrence@wagerworks.com> wrote: All, Is there a way to speed up the execution time of an 'alter table' statement? We have a 7 million record table that requires the following changes: * modify lvarchar(2048) column to lvarchar(32000) * add 4 integer or numeric columns to the end of the table. I've done this already on a development db (1 million records) and it took over 2.5 hrs. This would take 7 times as long in production. We can't be down that long. Other than creating a 'new' table with the 'new schema' and loading the old data, is there a way to drastically improve the execution of this alter-table? This is a Sun 280R, 2cpu, 6GB memory dbserver, running Solaris 8 and IDS 9.4 Terrence Mullins www.WagerWorks.com --------------------------------- Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard

Terrence, You said you can't be down that long... if you have down time at all to do this, you could turn off logging. don't know how much that helps, but it can't hurt. are you making both changes in the same alter statement? If so, adding the 4 new columns at the end of the table may not fall into the "in place alter" logic as the entire table is touched to change the lvarchar. An 'in place alter' would not take long and would not affect the table until a row is updated or a new one is inserted. if you combine the lvarchar change with the new fields... all rows are touched to change lvarchar, so the 4 new fields, i would assume, are added at that time. perhaps just modifying lvarchar alone would save some time? (test). after lvarchar is changed, then alter to add the 4 new columns at the end. Adding the columns at the end would then be 'in place alter'/table versioning, and be quick. what if you unloaded the primary key and the lvarchar column into another table. then drop lvarchar. then add the new length lvarchar at the end with the 4 new columns (in-place alter). then load lvarchar from the saved data table (touches all rows, kinda nullifies the in-place alter)? would informix have to process the entire table to drop the column or would it just change the DDL and pretend the column is not there? (like an 'in place delete' of sorts, i guess). of course to reclaim the space, you'd probably have to reorg. i'm pretty sure the pros out here can shoot holes in my theory, but hey... might be worth a test. do the modified/new fields affect indices? if so, mayhaps dropping them and re-creating after the alter command would save time? have you checked into HPL ? I don't know that much about it, but maybe it can chew through your data faster and allow the schema change or speed the movement to a new table/new schema? Norma Jean -----Original Message----- From: redden96@yahoo.com [mailto:redden96@yahoo.com] Sent: Tuesday, November 18, 2003 3:10 PM To: ids@iiug.org; forum.subscriber@iiug.org Subject: Re: Speed up alter-table execution [2189] I would imagine that PDQ would help, but I don't know for sure. Probably depends on whether or not the table is fragmented. Your thought on a new table will probably be your best bet. The lvarchar modification is what is causing the ALTER to take so long because it isn't an "in place alter" so you are in reality doubling you table size anyway and chewing up tons of log space to make this ALTER happen. You would be better off to create a new "RAW" table and INSERT INTO SELECT * FROM blah; This will avoid the logging and maximize your speed requirement. "Terrence Mu...." <terrence@wagerworks.com> wrote: All, Is there a way to speed up the execution time of an 'alter table' statement? We have a 7 million record table that requires the following changes: * modify lvarchar(2048) column to lvarchar(32000) * add 4 integer or numeric columns to the end of the table. I've done this already on a development db (1 million records) and it took over 2.5 hrs. This would take 7 times as long in production. We can't be down that long. Other than creating a 'new' table with the 'new schema' and loading the old data, is there a way to drastically improve the execution of this alter-table? This is a Sun 280R, 2cpu, 6GB memory dbserver, running Solaris 8 and IDS 9.4 Terrence Mullins www.WagerWorks.com --------------------------------- Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard ----------------------------------------- ============================================================ The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any reproduction, dissemination or distribution of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. Tellabs ============================================================

If there is an option: Why don't you try to shutdown transaction loggin on your database and do the alter? Chucho! PD: It can cause delays to your users... but it's a way. DL Redden wrote: > I would imagine that PDQ would help, but I don't know for sure. Probably depends on whether or not the table is fragmented. > > Your thought on a new table will probably be your best bet. The lvarchar modification is what is causing the ALTER to take so long because it isn't an "in place alter" so you are in reality doubling you table size anyway and chewing up tons of log space to make this ALTER happen. You would be better off to create a new "RAW" table and INSERT INTO SELECT * FROM blah; This will avoid the logging and maximize your speed requirement. > > "Terrence Mu...." <terrence@wagerworks.com> wrote: > All, > > Is there a way to speed up the execution time of an 'alter table' statement? > > We have a 7 million record table that requires the following changes: > > * modify lvarchar(2048) column to lvarchar(32000) > * add 4 integer or numeric columns to the end of the table. > > > > I've done this already on a development db (1 million records) and it took over > 2.5 hrs. This would take 7 times as long in production. We can't be down that > long. Other than creating a 'new' table with the 'new schema' and loading the old > data, is there a way to drastically improve the execution of this alter-table? > > This is a Sun 280R, 2cpu, 6GB memory dbserver, running Solaris 8 and IDS 9.4 > > > > > > > > Terrence Mullins > www.WagerWorks.com > > > > --------------------------------- > Do you Yahoo!? > Protect your identity with Yahoo! Mail AddressGuard > > > -- Atte, Jesús Antonio Santos Giraldo jeansagi@myrealbox.com jeansagi@netscape.net

Here are several options you can test on your test box first and apply accordingly: 1. Turn off logging to the databaes or make the table a raw table. (of course if the table has constraints you will have to drop them to make it a raw table) 2. Set PDQ 100 before running the alter sql. 3. Allocate 2 extra cpuvps to the system dynamically. (If u have 3 actual cpus then allocate 2 more dynamically) 4. Make sure your system is not allocating virtual segments when you are running your alter sql. onstat -g seg. 5. Allocate a lot of temp space using the DBTEMP environment parameter in your sql. 6. Use onstat -u command to check if the engine is waiting for buffers while it runs your alter sql, if so then use the onmode -a command to allocate memory segments to speed up the process but make sure you have enough available memory to do so. ( it will error out if it does not have the memory u are trying to allocate, so you would be fine, do this on test so u will get a good handle of it) 7. Come back here and post your success or unsuccess. Hope this would help a little. Thanks. Jean Sagi <jeansagi@myrealbox.com> wrote: If there is an option: Why don't you try to shutdown transaction loggin on your database and do the alter? Chucho! PD: It can cause delays to your users... but it's a way. DL Redden wrote: > I would imagine that PDQ would help, but I don't know for sure. Probably depends on whether or not the table is fragmented. > > Your thought on a new table will probably be your best bet. The lvarchar modification is what is causing the ALTER to take so long because it isn't an "in place alter" so you are in reality doubling you table size anyway and chewing up tons of log space to make this ALTER happen. You would be better off to create a new "RAW" table and INSERT INTO SELECT * FROM blah; This will avoid the logging and maximize your speed requirement. > > "Terrence Mu...." wrote: > All, > > Is there a way to speed up the execution time of an 'alter table' statement? > > We have a 7 million record table that requires the following changes: > > * modify lvarchar(2048) column to lvarchar(32000) > * add 4 integer or numeric columns to the end of the table. > > > > I've done this already on a development db (1 million records) and it took over > 2.5 hrs. This would take 7 times as long in production. We can't be down that > long. Other than creating a 'new' table with the 'new schema' and loading the old > data, is there a way to drastically improve the execution of this alter-table? > > This is a Sun 280R, 2cpu, 6GB memory dbserver, running Solaris 8 and IDS 9.4 > > > > > > > > Terrence Mullins > www.WagerWorks.com > > > > --------------------------------- > Do you Yahoo!? > Protect your identity with Yahoo! Mail AddressGuard > > > -- Atte, Jesús Antonio Santos Giraldo jeansagi@myrealbox.com jeansagi@netscape.net --------------------------------- Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard