Using external table to load data
The poster asked why loading ~27M rows via an external table took 5 hours into a STANDARD table, while the same load into a RAW table ran in 5-6 minutes. Respondents explained nothing was broken: RAW tables use unlogged light appends, whereas standard tables log every row inserted; indexes/PK/referential constraints add further overhead. Suggested workaround: load into a RAW table, alter it to STANDARD, then attach it as a fragment - but note that switching a table from raw to standard invalidates it on HDR/RSS secondaries, requiring a rebuild. The poster still found a DBLOAD run on a standalone instance (41 minutes) much faster than on his RSS+ER instance, and no further explanation for that difference is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, I have been using external table to load huge data (15 hundred million rows, each month) in a table which has a row size of 252. It takes 5-6 minutes to load the data. This table is in an instance where it is defined as a RAW table. When I load 2 days of data in the table where it is defined as a regular table, it took 5 hours to load 27332877 rows using the same external table. IDS version is 11.50.FC8 and OS is uname -a SunOS x4600-02 5.10 Generic_147441-07 i86pc i386 i86pc Can somebody suggest what might be going wrong in the latter case? Thanks in advance. Nitin
Nothing's wrong. Raw tables are not logged, regular tables are. The logging is what is what is taking your extra time. --EEM -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of NITIN MATHUR Sent: Monday, April 08, 2013 11:34 AM To: ids@iiug.org Subject: Using external table to load data [30005] Hi, I have been using external table to load huge data (15 hundred million rows, each month) in a table which has a row size of 252. It takes 5-6 minutes to load the data. This table is in an instance where it is defined as a RAW table. When I load 2 days of data in the table where it is defined as a regular table, it took 5 hours to load 27332877 rows using the same external table. IDS version is 11.50.FC8 and OS is uname -a SunOS x4600-02 5.10 Generic_147441-07 i86pc i386 i86pc Can somebody suggest what might be going wrong in the latter case? Thanks in advance. Nitin ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
There is no logging going on in the raw table. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of NITIN MATHUR Sent: Monday, April 08, 2013 12:34 PM To: ids@iiug.org Subject: Using external table to load data [30005] Hi, I have been using external table to load huge data (15 hundred million rows, each month) in a table which has a row size of 252. It takes 5-6 minutes to load the data. This table is in an instance where it is defined as a RAW table. When I load 2 days of data in the table where it is defined as a regular table, it took 5 hours to load 27332877 rows using the same external table. IDS version is 11.50.FC8 and OS is uname -a SunOS x4600-02 5.10 Generic_147441-07 i86pc i386 i86pc Can somebody suggest what might be going wrong in the latter case? Thanks in advance. Nitin ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Nothing is going wrong. When the table is a raw table, then the added rows are added as a light= append without logging. When the table is a standard table, then the rows are added one at a ti= me and are logged. But be careful. If you have HDR or RSS, then when you transition the t= able from raw to standard, you will have invalidated that table on the secon= dary servers, and will need to rebuild those secondary servers. From: "NITIN MATHUR" <nitin_maths@rediffmail.com> To: ids@iiug.org, Date: 04/08/2013 11:41 AM Subject: Using external table to load data [30005] Sent by: ids-bounces@iiug.org Hi, I have been using external table to load huge data (15 hundred million rows, each month) in a table which has a row size of 252. It takes 5-6 minute= s to load the data. This table is in an instance where it is defined as a RA= W table. When I load 2 days of data in the table where it is defined as a regula= r table, it took 5 hours to load 27332877 rows using the same external ta= ble. IDS version is 11.50.FC8 and OS is uname -a SunOS x4600-02 5.10 Generic_147441-07 i86pc i386 i86pc Can somebody suggest what might be going wrong in the latter case? Thanks in advance. Nitin ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =
The latter table is in STANDARD mode, not RAW. Does it have indexes? Primary key, unique, or Referential integrity constraints on it? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Apr 8, 2013 at 12:34 PM, NITIN MATHUR <nitin_maths@rediffmail.com>wrote: > Hi, > > I have been using external table to load huge data (15 hundred million > rows, > each month) in a table which has a row size of 252. It takes 5-6 minutes to > load the data. This table is in an instance where it is defined as a RAW > table. > > When I load 2 days of data in the table where it is defined as a regular > table, it took 5 hours to load 27332877 rows using the same external table. > > IDS version is 11.50.FC8 and OS is > uname -a > SunOS x4600-02 5.10 Generic_147441-07 i86pc i386 i86pc > > Can somebody suggest what might be going wrong in the latter case? > > Thanks in advance. > > Nitin > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e01228d68de13fc04d9dcb558
I have the same IDS version as yours . I loaded 26 milllions rows into a standard table with indexes, constraints ... for less then one hour. You might have a slow disk, or huge row size ...., or together HDR,RSS.......? Thanks, Frank On Mon, Apr 8, 2013 at 1:24 PM, Art Kagel <art.kagel@gmail.com> wrote: > The latter table is in STANDARD mode, not RAW. Does it have indexes? > Primary key, unique, or Referential integrity constraints on it? > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated nor > those of the entities themselves. > > On Mon, Apr 8, 2013 at 12:34 PM, NITIN MATHUR > <nitin_maths@rediffmail.com>wrote: > > > Hi, > > > > I have been using external table to load huge data (15 hundred million > > rows, > > each month) in a table which has a row size of 252. It takes 5-6 minutes > to > > load the data. This table is in an instance where it is defined as a RAW > > table. > > > > When I load 2 days of data in the table where it is defined as a regular > > table, it took 5 hours to load 27332877 rows using the same external > table. > > > > IDS version is 11.50.FC8 and OS is > > uname -a > > SunOS x4600-02 5.10 Generic_147441-07 i86pc i386 i86pc > > > > Can somebody suggest what might be going wrong in the latter case? > > > > Thanks in advance. > > > > Nitin > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --089e01228d68de13fc04d9dcb558 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7b6787d203982a04d9dcf2c1
Everybody already mentioned what's wrong. Logging. Although there may be other differences (disk, configurations etc.) this may be the major factor. What I think remains to be said is that depending on your requirements you may do the following: - Create a raw table and load the data (should be equally fast) - Alter the table to standard (should be instantaneous) - Attach this new table as a fragment (should be instantaneous if done properly) For this to work: - You can't be using HDR/RSS - Your data must be "partitionable" - You should not have global indexes - You should create a constraint in the table where you load that will match the fragmentation strategy (to avoid slow full scans to validate the data, as we haven't implemented anything like "no validate" - maybe something I'm forgetting? Regards On Mon, Apr 8, 2013 at 5:34 PM, NITIN MATHUR <nitin_maths@rediffmail.com>wrote: > Hi, > > I have been using external table to load huge data (15 hundred million > rows, > each month) in a table which has a row size of 252. It takes 5-6 minutes to > load the data. This table is in an instance where it is defined as a RAW > table. > > When I load 2 days of data in the table where it is defined as a regular > table, it took 5 hours to load 27332877 rows using the same external table. > > IDS version is 11.50.FC8 and OS is > uname -a > SunOS x4600-02 5.10 Generic_147441-07 i86pc i386 i86pc > > Can somebody suggest what might be going wrong in the latter case? > > Thanks in advance. > > Nitin > > > > ******************************************************************************* > 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... --047d7b414c2e4b665a04d9dd1e89
Thanks all for confirming Standard table load is slower when using external table as well. However, the same number of records, when I load using DBLOAD on a different instance but same schema table(STANDARD), I finish in 41 minutes, although this instance has letter memory allocated but this is a Stand-alone instance while the one with delay has RSS+ER attached. In both scenarios, I drop all indexes, PK before I start loading. Database selected. Isolation level set. 27332877 row(s) unloaded. Database closed. Sat Apr 6 05:13:50 EDT 2013 Processing ... Please Wait ... DBLOAD Load Utility INFORMIX-SQL Version 11.70.FC5W1 Table tqh_temp had 27332877 row(s) loaded into it. Loading Completed. Sat Apr 6 05:55:16 EDT 2013