Richard Wesley wrote:

> Are there any tricks to getting good performance with FB when doing
> batch inserts? I am getting about 1e6 rows/hr creating an 8e6 row
> table which seems rather slow. The insert is being done in a loop
> with a prepared statement before any indexes are created. Would
> batching the inserts improve things?
>
> (This is with FB2 RC3, but I am hoping the answer is more general.)
>

What middleware do you use? I can insert 1e6 rows in about 2 to 6 minutes. It
depends on row size and hardware but you can get a rough idea of the speed. I
also curious if jaybird or .net driver have a similar verb.

testing configuration (table structure and hardware) is contained in the
followng email. Vladimir Tsvigun (developer of firebird odbc driver) also
remarked that he took about 1 sec to insert 10000 rows (another config).

> David Mitchell wrote:
>> I am using the MS Access driver Version 4.00.6304.00. The initial DB is
>> empty. The script is saving the file names, sizes, time stamps and 3
>> integer values for all the files on my hard drive. I make two calls to
>> ddins, one with all directory paths and one with all file names. The
>> directory path add worked fine, it has 4 columns, paths, time stamps and
>> 2 integer values.
>>
>> sqlbulkoperations will hang with the standard character format ddins or
>> the modified boxed format one. Both forms of ddins will work with the
>> smaller MAXARRAYSIZE.
>>
>
> Thanks David, I can reproduce the bug. I must only tested with simplier cases
> that it worked. To workaround ms access bug, it should restore the old value
> MAXARRAYSIZE=: 65535
>
> as a comparison for ms access,
> ch=. ddcon_jdd_ 'dsn=jdb1'
> ch ddsql~ 'create table table1(fname varchar(250),fsize long,fts timestamp,int1
> long, int2 long, int3 long)'
> ch ddsql~ 'delete from table1'
> nr=.100000
> da=. ((nr,250)$'abc');((nr,1)$i.nr);((nr,16)$'{d ''2006-07-28''}');((nr,1)$4 5
> 6);((nr,1)$7 8 9);((nr,1)$0 1 2)
> ch ddins~ 'select fname,fsize,fts,int1,int2,int3 from table1';da
>
> timing on my aged celeron 1.7 (MAXARRAYSIZE=: 65535)
> 100000 row 30 sec
> 1000000 rows 6 min
>
> what's your timing?
>

Hi Bill,
I tried your test on two systems: A Dell laptop with 1400 MHz Pentium M CPU, 1
Gig memory and Hitachi 40G drive; A home built with 2191 MHz AMD CPU, 1.5 Gig
memory and Maxtor 80G SATA drive.

Laptop:
1e5 rows: 5.5 sec
1e6 rows: 74-171 sec

Desktop:
1e5 rows: 4-5 sec
1e6 rows: 95-109 sec


--
regards,
bill