IDS Forum

Re: dbload and load

Posted By: FRANK
Date: Tuesday, 9 March 2010, at 6:17 p.m.

In Response To: Re: dbload and load (John Miller iii)

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

Messages In This Thread

• dbload and load
  CHUAN LU -- Tuesday, 9 March 2010, at 3:16 a.m.
  • Re: dbload and load
    Obnoxio The Clown -- Tuesday, 9 March 2010, at 7:27 a.m.
    • Re: dbload and load
      FRANK -- Tuesday, 9 March 2010, at 1:40 p.m.
      • Re: dbload and load
        John Miller iii -- Tuesday, 9 March 2010, at 2:43 p.m.
        • Re: dbload and load
          FRANK -- Tuesday, 9 March 2010, at 6:17 p.m.
        • RE: dbload and load
          Plugge, Joe R. -- Wednesday, 10 March 2010, at 2:18 p.m.
          • Re: dbload and load
            Art Kagel -- Wednesday, 10 March 2010, at 5:35 p.m.
          • RE: dbload and load
            John Miller iii -- Monday, 15 March 2010, at 7:06 p.m.
  • Re: dbload and load
    John Miller iii -- Tuesday, 9 March 2010, at 11:13 a.m.
    • Re: dbload and load
      Fernando Nunes -- Tuesday, 9 March 2010, at 12:44 p.m.
  • Re: dbload and load
    Art Kagel -- Tuesday, 9 March 2010, at 3:48 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.