slow response adding a column to a table
User on IDS 10.0FC5 (Solaris) found ALTER TABLE very slow on a table with 10M+ rows, 150+ columns and ~7KB rows. Respondents explained that Informix can do a fast "in-place alter" (metadata only, pages converted as they're read/written), but only in certain cases; otherwise it does a "slow alter" that physically rewrites every row. Key point raised: altering/increasing the length of existing VARCHAR columns (and UDT/LVARCHAR types) forces a slow alter, which can't be avoided via ONCONFIG, and with ~7KB rows spanning four pages the rewrite is inherently costly. Advice was to check the performance manual's in-place alter rules, add only new columns in supported ways, and reconsider the 150-column design. No specific fix for the user's case was confirmed in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello We are facing very slow response while adding or modifying any column in table. This table contains more the 10 million records and more the 150 column. We are using IDS 10.0 FC5 at sun Solaris platform. Thanks
2008/10/17 OMER KHAN <oskhan@i2cinc.com>: > Hello > > We are facing very slow response while adding or modifying any column in > table. This table contains more the 10 million records and more the 150 > column. > > We are using IDS 10.0 FC5 at sun Solaris platform. > > Thanks > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Omar I'm not surprised !! Depends on the current layout of the table, what columns you are adding or modifying, any defaults. This could either be trivial or require a complete rewrite of the table. More info, what is your definition of slow and why do you think it should be quicker?? Keith
Adding a column can be done through what we call "in-place" alter. This kind of alter table is virtually instantaneous, because the engine doesn't change the physical layout of the table. Only the table's definition. After this, when you select the old pages it converts them to new format. When you write a new page it will use the new format and when you update an old page it will convert it and write it back in the new format. There are cases when this can not be done. Please check the performance manual for "in-place alter" to check the cases where IDS will do an in-place alter or a "slow alter". In case of doubt, send us the old table schema and the ALTER TABLE statement. Regards. On Fri, Oct 17, 2008 at 5:23 AM, OMER KHAN <oskhan@i2cinc.com> wrote: > Hello > > We are facing very slow response while adding or modifying any column in > table. This table contains more the 10 million records and more the 150 > column. > > We are using IDS 10.0 FC5 at sun Solaris platform. > > Thanks > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...
You are running into the "slow alter". The database is physically rewriting all of the pages of your table. This can happen if you have opaque data types or UDTs in your table. I would step back and ask why you have 150 columns in a table. Knowing nothing about your application, I cannot imagine an entity which has 150 attributes. You may want to check your data model and determine if you are adding columns to the right location. Perhaps you should be creating new tables instead of expanding an existing definition. cheers j. Sane ego te vocavi. Forsitan capedictum tuum desit. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of OMER KHAN Sent: Friday, October 17, 2008 12:24 AM To: ids@iiug.org Subject: slow response adding a column to a table [13728] Hello We are facing very slow response while adding or modifying any column in table. This table contains more the 10 million records and more the 150 column. We are using IDS 10.0 FC5 at sun Solaris platform. Thanks **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
Hello Guys, Thanks for all the responses. First, i would like to inform that we are using the default data types for all the columns which are presently avaiable in this table. And now we are trying to add 3 new columns with the default data types again (i.e. decimal, varchar, char etc.) and additionally we are also trying to modify some old columns by just increasing their lenghts. So i there any setting in the onconfig through which i could restrict database from rewriting all the pages of that table (if it is doing so). Also tell me that by usuing in-place alter, does it degrade the performance of insert/delete/upgrade or not? I will be waiting for your response. Thanks. Regards, Omer Saeed Khan
VARCHAR or LVARCHAR? LVARCHARS are implemented as a built-in UDT, they are not a native type and so are not supported for in-place ALTERs! Art On Fri, Oct 17, 2008 at 11:26 AM, OMER KHAN <oskhan@i2cinc.com> wrote: > Hello Guys, > > Thanks for all the responses. > > First, i would like to inform that we are using the default data types for > all > the columns which are presently avaiable in this table. And now we are > trying > to add 3 new columns with the default data types again (i.e. decimal, > varchar, > char etc.) and additionally we are also trying to modify some old columns > by > just increasing their lenghts. > > So i there any setting in the onconfig through which i could restrict > database > from rewriting all the pages of that table (if it is doing so). > > Also tell me that by usuing in-place alter, does it degrade the performance > of > insert/delete/upgrade or not? > > I will be waiting for your response. Thanks. > > Regards, > Omer Saeed Khan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
No we are not using Lvarchar, we are only using the native types varchar. Additionally, please let me know that without using in-place alters, is it possible to do addition of column. My table rowsize is around 7k, and i am still unable to understand that why it takes so long to add a new column. How to resolve that situation. Thanks. Omer
OMER KHAN said: > My table rowsize is around 7k Flogging is too good for some people. -- Bye now, Obnoxio http://obotheclown.blogspot.com/
Please send along your table DDL as well as the column you are trying to add. j. Sane ego te vocavi. Forsitan capedictum tuum desit. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of OMER KHAN Sent: Friday, October 17, 2008 1:45 PM To: ids@iiug.org Subject: Re: RE: slow response adding a column to a table [13736] No we are not using Lvarchar, we are only using the native types varchar. Additionally, please let me know that without using in-place alters, is it possible to do addition of column. My table rowsize is around 7k, and i am still unable to understand that why it takes so long to add a new column. How to resolve that situation. Thanks. Omer **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
OMER KHAN said: > My table rowsize is around 7k Flogging is too good for some people. -- Bye now, Obnoxio http://obotheclown.blogspot.com/
Hi Omer, if you're increasing the length of existing varchars, this will definitely incur "slow alter" on the table, no way to avoid it... Regards Davorin
Yes, you can add columns even when the engine decides that it cannot use in-place alter. It just has to rewrite every row of the table and maybe move rows to a forwarding page if they no longer fit on their home page or maybe split a row that is now longer than a page onto multiple pages. In your case, since your rows are 4 normal pages long, every row updated requires four pages to be read and written to (unless the rows exist in 8K or 16K dbspaces of course). Art On Fri, Oct 17, 2008 at 1:45 PM, OMER KHAN <oskhan@i2cinc.com> wrote: > No we are not using Lvarchar, we are only using the native types varchar. > > Additionally, please let me know that without using in-place alters, is it > possible to do addition of column. > > My table rowsize is around 7k, and i am still unable to understand that why > it > takes so long to add a new column. How to resolve that situation. > > Thanks. > Omer > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.