dbload and load
Question: why is dbload faster than dbaccess's SQL LOAD when loading a whole file? Answers: dbload uses larger insert-cursor buffers (FET_BUF_SIZE is set by default for dbload but not dbaccess) and commits every N rows, whereas LOAD commits only once at the end, so logging/transaction overhead is much higher; one poster shared timings showing an optimal commit interval around 25k rows. John Miller suggested external tables (CREATE EXTERNAL TABLE + INSERT...SELECT, optionally with MERGE) as faster still; a reader noted this runs as one big transaction and consumes many row locks, with the advice to choose page/table-level locking or a RAW table.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello,
when a parnter loading a whole file into the table, they find the performance
of dbload is better than SQL load, who know the internal reason? Any reply
will be appreciated.
CHUAN LU wrote:
> Hello,
>
> when a parnter loading a whole file into the table, they find the performance
> of dbload is better than SQL load, who know the internal reason? Any reply
> will be appreciated.
If you are committing dbload every couple of thousand rows, the
transaction logging is much more efficient. SQL LOAD doesn't offer the
option. You probably won't see it on small tables.
--
Cheers,
Obnoxio The Clown
http://obotheclown.blogspot.com
I will now proceed to pleasure myself with this fish.
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
I believe FET_BUF_SIZE is set by default for
dbload, but not for dbaccess. I would try setting
FET_BUF_SIZE to 32000 for both and seeing if
that changes the results.
John F. Miller III
STSM, Support Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 03/09/2010 12:16:14 AM:
> Hello,
>
> when a parnter loading a whole file into the table, they find the
performance
> of dbload is better than SQL load, who know the internal reason? Any
reply
> will be appreciated.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Also, dbload is a server tool....
Which tool are they using for "load"? If used in ISQL it can be used
remotely and that would imply data transport across servers...
Just a thought. Not probable anyway...
Regards.
On Tue, Mar 9, 2010 at 4:13 PM, John Miller iii <miller3@us.ibm.com> wrote:
> I believe FET_BUF_SIZE is set by default for
> dbload, but not for dbaccess. I would try setting
> FET_BUF_SIZE to 32000 for both and seeing if
> that changes the results.
>
> John F. Miller III
> STSM, Support Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 03/09/2010 12:16:14 AM:
>
> > Hello,
> >
> > when a parnter loading a whole file into the table, they find the
> performance
> > of dbload is better than SQL load, who know the internal reason? Any
> reply
> > will be appreciated.
> >
> >
> >
>
>
>
*******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
*******************************************************************************
> 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...
--0016e6498360ecae7d0481619f6a
Another interesting result observation I like to share.
For a regular table and default configuration, We just change the number
of rows to be committed( a parameter of dbload) You can see the total
loading time changing. It is a curve. You may find the Point there.
Rows Time
=================================
5 rows 46 minutes 31 seconds
20 rows 26 minutes 7 seconds
40 row 23 minutes 15 seconds
100 rows 24 minutes 11 seconds
300 rows 22 minutes 53 seconds
650 rows 22 minutes 44 seconds
1250 rows 22 minutes 33 seconds
2500 rows 22 minutes 46 seconds
5000 rows 21 minutes 16 seconds
10000 rows 20 minutes 15 seconds
25000 rows 19 minutes 28 seconds
50000 rows 20 minutes 16 seconds
75000 rows 20 minutes 33 seconds
100000 rows 20 minutes 18 seconds
200000 rows 22 minutes 14 seconds
400000 rows 28 minutes 34 seconds
800000 rows 29 minutes 3 seconds
1600000 rows 39 minutes 35 seconds
Want to know what Fast Informix DBAs like to do?
Frank
On Tue, Mar 9, 2010 at 7:27 AM, Obnoxio The Clown
<obnoxio@serendipita.com>wrote:
> CHUAN LU wrote:
> > Hello,
> >
> > when a parnter loading a whole file into the table, they find the
> performance
> > of dbload is better than SQL load, who know the internal reason? Any
> reply
> > will be appreciated.
>
> If you are committing dbload every couple of thousand rows, the
> transaction logging is much more efficient. SQL LOAD doesn't offer the
> option. You probably won't see it on small tables.
>
> --
> Cheers,
> Obnoxio The Clown
>
> http://obotheclown.blogspot.com
> I will now proceed to pleasure myself with this fish.
>
> --
> This message has been scanned for viruses and
> dangerous content by OpenProtect(http://www.openprotect.com), and is
> believed to be clean.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0016e6407c9ee4b01a048162838c
Frank:
I am not sure if you are aware but in version 11.50.xC6
there was a new loading mechanism that I find very easy
to use, which I believe is faster than both dbload and load.
It is called external tables and to do the load you simple
do the following.
In addition, you can combine the external table feature and the newly
add MERGE statement to insert new rows and update existing
rows (or various combinations).
CREATE EXTERNAL TABLE t1_ext SAMEAS t1
USING
(
DATAFILES('DISK:/tmp/t1.unl'),
FORMAT 'DELIMITED',
DELIMITER '|',
RECORDEND '',
Deluxe,
NUMROWS 50,
MAXERRORS 50,
REJECTFILE ''
);
INSERT INTO t1 SELECT * FROM t1_ext;
DROP TABLE t1_ext;
John F. Miller III
STSM, Support Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 03/09/2010 10:40:31 AM:
> [image removed]
>
> Re: dbload and load [19289]
>
> FRANK
>
> to:
>
> ids
>
> 03/09/2010 10:42 AM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> Another interesting result observation I like to share.
>
> For a regular table and default configuration, We just change the number
> of rows to be committed( a parameter of dbload) You can see the total
> loading time changing. It is a curve. You may find the Point there.
>
> Rows Time
> =================================
> 5 rows 46 minutes 31 seconds
> 20 rows 26 minutes 7 seconds
> 40 row 23 minutes 15 seconds
> 100 rows 24 minutes 11 seconds
> 300 rows 22 minutes 53 seconds
> 650 rows 22 minutes 44 seconds
> 1250 rows 22 minutes 33 seconds
> 2500 rows 22 minutes 46 seconds
> 5000 rows 21 minutes 16 seconds
> 10000 rows 20 minutes 15 seconds
> 25000 rows 19 minutes 28 seconds
> 50000 rows 20 minutes 16 seconds
> 75000 rows 20 minutes 33 seconds
> 100000 rows 20 minutes 18 seconds
> 200000 rows 22 minutes 14 seconds
> 400000 rows 28 minutes 34 seconds
> 800000 rows 29 minutes 3 seconds
> 1600000 rows 39 minutes 35 seconds
>
> Want to know what Fast Informix DBAs like to do?
>
> Frank
>
> On Tue, Mar 9, 2010 at 7:27 AM, Obnoxio The Clown
> <obnoxio@serendipita.com>wrote:
>
> > CHUAN LU wrote:
> > > Hello,
> > >
> > > when a parnter loading a whole file into the table, they find the
> > performance
> > > of dbload is better than SQL load, who know the internal reason? Any
> > reply
> > > will be appreciated.
> >
> > If you are committing dbload every couple of thousand rows, the
> > transaction logging is much more efficient. SQL LOAD doesn't offer the
> > option. You probably won't see it on small tables.
> >
> > --
> > Cheers,
> > Obnoxio The Clown
> >
> > http://obotheclown.blogspot.com
> > I will now proceed to pleasure myself with this fish.
> >
> > --
> > This message has been scanned for viruses and
> > dangerous content by OpenProtect(http://www.openprotect.com), and is
> > believed to be clean.
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --0016e6407c9ee4b01a048162838c
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
dbload uses larger buffer size for an insert cursor and also commits after N
rows while the dbaccess LOAD verb only commits after all rows are loaded.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
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 Tue, Mar 9, 2010 at 3:16 AM, CHUAN LU <luchuan114@sina.com> wrote:
> Hello,
>
> when a parnter loading a whole file into the table, they find the
> performance
> of dbload is better than SQL load, who know the internal reason? Any reply
> will be appreciated.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001517588b7abde71b0481644d06
Thanks a lot, John! Will try.
Frank
On Tue, Mar 9, 2010 at 2:43 PM, John Miller iii <miller3@us.ibm.com> wrote:
> Frank:
>
> I am not sure if you are aware but in version 11.50.xC6
> there was a new loading mechanism that I find very easy
> to use, which I believe is faster than both dbload and load.
> It is called external tables and to do the load you simple
> do the following.
>
> In addition, you can combine the external table feature and the newly
> add MERGE statement to insert new rows and update existing
> rows (or various combinations).
>
> CREATE EXTERNAL TABLE t1_ext SAMEAS t1
> USING
> (
> DATAFILES('DISK:/tmp/t1.unl'),
> FORMAT 'DELIMITED',
> DELIMITER '|',
> RECORDEND '',
> Deluxe,
> NUMROWS 50,
> MAXERRORS 50,
> REJECTFILE ''
> );
>
> INSERT INTO t1 SELECT * FROM t1_ext;
> DROP TABLE t1_ext;>
> John F. Miller III
> STSM, Support Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 03/09/2010 10:40:31 AM:
>
> > [image removed]
> >
> > Re: dbload and load [19289]
> >
> > FRANK
> >
> > to:
> >
> > ids
> >
> > 03/09/2010 10:42 AM
> >
> > Sent by:
> >
> > ids-bounces@iiug.org
> >
> > Please respond to ids
> >
> > Another interesting result observation I like to share.
> >
> > For a regular table and default configuration, We just change the number
> > of rows to be committed( a parameter of dbload) You can see the total
> > loading time changing. It is a curve. You may find the Point there.
> >
> > Rows Time
> > =================================
> > 5 rows 46 minutes 31 seconds
> > 20 rows 26 minutes 7 seconds
> > 40 row 23 minutes 15 seconds
> > 100 rows 24 minutes 11 seconds
> > 300 rows 22 minutes 53 seconds
> > 650 rows 22 minutes 44 seconds
> > 1250 rows 22 minutes 33 seconds
> > 2500 rows 22 minutes 46 seconds
> > 5000 rows 21 minutes 16 seconds
> > 10000 rows 20 minutes 15 seconds
> > 25000 rows 19 minutes 28 seconds
> > 50000 rows 20 minutes 16 seconds
> > 75000 rows 20 minutes 33 seconds
> > 100000 rows 20 minutes 18 seconds
> > 200000 rows 22 minutes 14 seconds
> > 400000 rows 28 minutes 34 seconds
> > 800000 rows 29 minutes 3 seconds
> > 1600000 rows 39 minutes 35 seconds
> >
> > Want to know what Fast Informix DBAs like to do?
> >
> > Frank
> >
> > On Tue, Mar 9, 2010 at 7:27 AM, Obnoxio The Clown
> > <obnoxio@serendipita.com>wrote:
> >
> > > CHUAN LU wrote:
> > > > Hello,
> > > >
> > > > when a parnter loading a whole file into the table, they find the
> > > performance
> > > > of dbload is better than SQL load, who know the internal reason? Any
> > > reply
> > > > will be appreciated.
> > >
> > > If you are committing dbload every couple of thousand rows, the
> > > transaction logging is much more efficient. SQL LOAD doesn't offer the
> > > option. You probably won't see it on small tables.
> > >
> > > --
> > > Cheers,
> > > Obnoxio The Clown
> > >
> > > http://obotheclown.blogspot.com
> > > I will now proceed to pleasure myself with this fish.
> > >
> > > --
> > > This message has been scanned for viruses and
> > > dangerous content by OpenProtect(http://www.openprotect.com), and is
> > > believed to be clean.
> > >
> > >
> > >
> > >
> >
>
>
>
*******************************************************************************
>
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --0016e6407c9ee4b01a048162838c
> >
> >
> >
>
>
>
*******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0016361e88b435eb3b04816664a1
John, your method as described below chews up a ton of row locks, I had to
alter my base table type(raw) and then issue the insert statement. Am I
missing something?
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John
Miller iii
Sent: Tuesday, March 09, 2010 1:44 PM
To: ids@iiug.org
Subject: Re: dbload and load [19290]
Frank:
I am not sure if you are aware but in version 11.50.xC6
there was a new loading mechanism that I find very easy
to use, which I believe is faster than both dbload and load.
It is called external tables and to do the load you simple
do the following.
In addition, you can combine the external table feature and the newly
add MERGE statement to insert new rows and update existing
rows (or various combinations).
CREATE EXTERNAL TABLE t1_ext SAMEAS t1
USING
(
DATAFILES('DISK:/tmp/t1.unl'),
FORMAT 'DELIMITED',
DELIMITER '|',
RECORDEND '',
Deluxe,
NUMROWS 50,
MAXERRORS 50,
REJECTFILE ''
);
INSERT INTO t1 SELECT * FROM t1_ext;
DROP TABLE t1_ext;
John F. Miller III
STSM, Support Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 03/09/2010 10:40:31 AM:
> [image removed]
>
> Re: dbload and load [19289]
>
> FRANK
>
> to:
>
> ids
>
> 03/09/2010 10:42 AM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> Another interesting result observation I like to share.
>
> For a regular table and default configuration, We just change the number
> of rows to be committed( a parameter of dbload) You can see the total
> loading time changing. It is a curve. You may find the Point there.
>
> Rows Time
> =================================
> 5 rows 46 minutes 31 seconds
> 20 rows 26 minutes 7 seconds
> 40 row 23 minutes 15 seconds
> 100 rows 24 minutes 11 seconds
> 300 rows 22 minutes 53 seconds
> 650 rows 22 minutes 44 seconds
> 1250 rows 22 minutes 33 seconds
> 2500 rows 22 minutes 46 seconds
> 5000 rows 21 minutes 16 seconds
> 10000 rows 20 minutes 15 seconds
> 25000 rows 19 minutes 28 seconds
> 50000 rows 20 minutes 16 seconds
> 75000 rows 20 minutes 33 seconds
> 100000 rows 20 minutes 18 seconds
> 200000 rows 22 minutes 14 seconds
> 400000 rows 28 minutes 34 seconds
> 800000 rows 29 minutes 3 seconds
> 1600000 rows 39 minutes 35 seconds
>
> Want to know what Fast Informix DBAs like to do?
>
> Frank
>
> On Tue, Mar 9, 2010 at 7:27 AM, Obnoxio The Clown
> <obnoxio@serendipita.com>wrote:
>
> > CHUAN LU wrote:
> > > Hello,
> > >
> > > when a parnter loading a whole file into the table, they find the
> > performance
> > > of dbload is better than SQL load, who know the internal reason? Any
> > reply
> > > will be appreciated.
> >
> > If you are committing dbload every couple of thousand rows, the
> > transaction logging is much more efficient. SQL LOAD doesn't offer the
> > option. You probably won't see it on small tables.
> >
> > --
> > Cheers,
> > Obnoxio The Clown
> >
> > http://obotheclown.blogspot.com
> > I will now proceed to pleasure myself with this fish.
> >
> > --
> > This message has been scanned for viruses and
> > dangerous content by OpenProtect(http://www.openprotect.com), and is
> > believed to be clean.
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --0016e6407c9ee4b01a048162838c
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
It would be interesting to see how fast my dbcopy utility can transfer the
data from the external table to the real one. Like dbload it uses partial
transactions and big buffers so it is lightening fast on normal tables and
uses as few locks as you configure it to (10,000 by default).
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
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 Wed, Mar 10, 2010 at 2:18 PM, Plugge, Joe R. <JRPlugge@west.com> wrote:
> John, your method as described below chews up a ton of row locks, I had to
> alter my base table type(raw) and then issue the insert statement. Am I
> missing something?
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John
> Miller iii
> Sent: Tuesday, March 09, 2010 1:44 PM
> To: ids@iiug.org
> Subject: Re: dbload and load [19290]
>
> Frank:
>
> I am not sure if you are aware but in version 11.50.xC6
> there was a new loading mechanism that I find very easy
> to use, which I believe is faster than both dbload and load.
> It is called external tables and to do the load you simple
> do the following.
>
> In addition, you can combine the external table feature and the newly
> add MERGE statement to insert new rows and update existing
> rows (or various combinations).
>
> CREATE EXTERNAL TABLE t1_ext SAMEAS t1
> USING
> (
> DATAFILES('DISK:/tmp/t1.unl'),
> FORMAT 'DELIMITED',
> DELIMITER '|',
> RECORDEND '',
> Deluxe,
> NUMROWS 50,
> MAXERRORS 50,
> REJECTFILE ''
> );
>
> INSERT INTO t1 SELECT * FROM t1_ext;
> DROP TABLE t1_ext;>
> John F. Miller III
> STSM, Support Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 03/09/2010 10:40:31 AM:
>
> > [image removed]
> >
> > Re: dbload and load [19289]
> >
> > FRANK
> >
> > to:
> >
> > ids
> >
> > 03/09/2010 10:42 AM
> >
> > Sent by:
> >
> > ids-bounces@iiug.org
> >
> > Please respond to ids
> >
> > Another interesting result observation I like to share.
> >
> > For a regular table and default configuration, We just change the number
> > of rows to be committed( a parameter of dbload) You can see the total
> > loading time changing. It is a curve. You may find the Point there.
> >
> > Rows Time
> > =================================
> > 5 rows 46 minutes 31 seconds
> > 20 rows 26 minutes 7 seconds
> > 40 row 23 minutes 15 seconds
> > 100 rows 24 minutes 11 seconds
> > 300 rows 22 minutes 53 seconds
> > 650 rows 22 minutes 44 seconds
> > 1250 rows 22 minutes 33 seconds
> > 2500 rows 22 minutes 46 seconds
> > 5000 rows 21 minutes 16 seconds
> > 10000 rows 20 minutes 15 seconds
> > 25000 rows 19 minutes 28 seconds
> > 50000 rows 20 minutes 16 seconds
> > 75000 rows 20 minutes 33 seconds
> > 100000 rows 20 minutes 18 seconds
> > 200000 rows 22 minutes 14 seconds
> > 400000 rows 28 minutes 34 seconds
> > 800000 rows 29 minutes 3 seconds
> > 1600000 rows 39 minutes 35 seconds
> >
> > Want to know what Fast Informix DBAs like to do?
> >
> > Frank
> >
> > On Tue, Mar 9, 2010 at 7:27 AM, Obnoxio The Clown
> > <obnoxio@serendipita.com>wrote:
> >
> > > CHUAN LU wrote:
> > > > Hello,
> > > >
> > > > when a parnter loading a whole file into the table, they find the
> > > performance
> > > > of dbload is better than SQL load, who know the internal reason? Any
> > > reply
> > > > will be appreciated.
> > >
> > > If you are committing dbload every couple of thousand rows, the
> > > transaction logging is much more efficient. SQL LOAD doesn't offer the
> > > option. You probably won't see it on small tables.
> > >
> > > --
> > > Cheers,
> > > Obnoxio The Clown
> > >
> > > http://obotheclown.blogspot.com
> > > I will now proceed to pleasure myself with this fish.
> > >
> > > --
> > > This message has been scanned for viruses and
> > > dangerous content by OpenProtect(http://www.openprotect.com), and is
> > > believed to be clean.
> > >
> > >
> > >
> > >
> >
>
>
>
>
*******************************************************************************
>
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --0016e6407c9ee4b01a048162838c
> >
> >
> >
>
>
>
>
*******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0015174027a627a64d048179ea6d
It is doing one large insert statement as a single
transaction. You can use row, page or table level locking, depending
on your needs and requirements.
John F. Miller III
STSM, Support Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 03/10/2010 11:18:34 AM:
> [image removed]
>
> RE: dbload and load [19302]
>
> Plugge, Joe R.
>
> to:
>
> ids
>
> 03/10/2010 11:24 AM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> John, your method as described below chews up a ton of row locks, I had
to
> alter my base table type(raw) and then issue the insert statement. Am I
> missing something?
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
John
> Miller iii
> Sent: Tuesday, March 09, 2010 1:44 PM
> To: ids@iiug.org
> Subject: Re: dbload and load [19290]
>
> Frank:
>
> I am not sure if you are aware but in version 11.50.xC6
> there was a new loading mechanism that I find very easy
> to use, which I believe is faster than both dbload and load.
> It is called external tables and to do the load you simple
> do the following.
>
> In addition, you can combine the external table feature and the newly
> add MERGE statement to insert new rows and update existing
> rows (or various combinations).
>
> CREATE EXTERNAL TABLE t1_ext SAMEAS t1
> USING
> (
> DATAFILES('DISK:/tmp/t1.unl'),
> FORMAT 'DELIMITED',
> DELIMITER '|',
> RECORDEND '',
> Deluxe,
> NUMROWS 50,
> MAXERRORS 50,
> REJECTFILE ''
> );
>
> INSERT INTO t1 SELECT * FROM t1_ext;
> DROP TABLE t1_ext;>
> John F. Miller III
> STSM, Support Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 03/09/2010 10:40:31 AM:
>
> > [image removed]
> >
> > Re: dbload and load [19289]
> >
> > FRANK
> >
> > to:
> >
> > ids
> >
> > 03/09/2010 10:42 AM
> >
> > Sent by:
> >
> > ids-bounces@iiug.org
> >
> > Please respond to ids
> >
> > Another interesting result observation I like to share.
> >
> > For a regular table and default configuration, We just change the
number
> > of rows to be committed( a parameter of dbload) You can see the total
> > loading time changing. It is a curve. You may find the Point there.
> >
> > Rows Time
> > =================================
> > 5 rows 46 minutes 31 seconds
> > 20 rows 26 minutes 7 seconds
> > 40 row 23 minutes 15 seconds
> > 100 rows 24 minutes 11 seconds
> > 300 rows 22 minutes 53 seconds
> > 650 rows 22 minutes 44 seconds
> > 1250 rows 22 minutes 33 seconds
> > 2500 rows 22 minutes 46 seconds
> > 5000 rows 21 minutes 16 seconds
> > 10000 rows 20 minutes 15 seconds
> > 25000 rows 19 minutes 28 seconds
> > 50000 rows 20 minutes 16 seconds
> > 75000 rows 20 minutes 33 seconds
> > 100000 rows 20 minutes 18 seconds
> > 200000 rows 22 minutes 14 seconds
> > 400000 rows 28 minutes 34 seconds
> > 800000 rows 29 minutes 3 seconds
> > 1600000 rows 39 minutes 35 seconds
> >
> > Want to know what Fast Informix DBAs like to do?
> >
> > Frank
> >
> > On Tue, Mar 9, 2010 at 7:27 AM, Obnoxio The Clown
> > <obnoxio@serendipita.com>wrote:
> >
> > > CHUAN LU wrote:
> > > > Hello,
> > > >
> > > > when a parnter loading a whole file into the table, they find the
> > > performance
> > > > of dbload is better than SQL load, who know the internal reason?
Any
> > > reply
> > > > will be appreciated.
> > >
> > > If you are committing dbload every couple of thousand rows, the
> > > transaction logging is much more efficient. SQL LOAD doesn't offer
the
> > > option. You probably won't see it on small tables.
> > >
> > > --
> > > Cheers,
> > > Obnoxio The Clown
> > >
> > > http://obotheclown.blogspot.com
> > > I will now proceed to pleasure myself with this fish.
> > >
> > > --
> > > This message has been scanned for viruses and
> > > dangerous content by OpenProtect(http://www.openprotect.com), and is
> > > believed to be clean.
> > >
> > >
> > >
> > >
> >
>
>
>
*******************************************************************************
>
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --0016e6407c9ee4b01a048162838c
> >
> >
> >
>
>
>
*******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>