[Info-Ingres] Generate 1 million rows of unique data - Fri..

Hi everyone,

I was recently trading techno nerdy stories with my children and their
friends who have, it seems, surpassed my own skills. A challenge was issued
to see which programmer could create a million unique rows in the database
the most quickly. (They were all talk no action of course ) The tools were
PHP, C++, vb, OpenROAD, mysql, ingres, mssql, Compiled code vs interpreted,
FOR loops vs WHILE loops etc.

This was my offering, standard ingres SQL and trusty terminal monitor.


select date('now');\g

create table digits (v varchar(1));\g
create table millions (v1 varchar(6)) ;\g
commit;\g

insert into digits values ('0');
insert into digits values ('1');
insert into digits values ('2');
insert into digits values ('3');
insert into digits values ('4');
insert into digits values ('5');
insert into digits values ('6');
insert into digits values ('7');
insert into digits values ('8');
insert into digits values ('9');
commit;\g

insert into millions
select a.v + b.v + c.v + d.v + e.v + f.v
from digits a, digits b, digits c, digits d, digits e, digits f;\g
commit;\g

select date('now');\g



Timings here for your interest.
The physical table generated was around 11Mb.
And I found it interesting that the 1GB transaction log filled to 37%
I guess I was expecting of the order of 6Mb
Linux almost won but ran out of transaction log.


SunFire V880 Sparc V9 8x1.2Ghz processors 32GB RAM
Storedge 3510 with Raid 0+1, dual transaction logs of 1GB
DB cache of about 60MB RAM

rows seconds
1000 0
10000 3
100000 22
1000000 230


2. SunFire V880 Sparc V9 6x750Mhz processors 12GB RAM
Storedge 3310 with RAID 0+1, dual transaction logs of 1GB
DB Cache around 30MB ram

rows seconds
1000 1
10000 4
100000 37
1000000 344


3. Linux Single 2.2 Ghz Xeon dualcore processor 2GB RAM
Internal disks RAID 0, Single transaction log of 500Mb
DB Cache around 60 MB RAM

rows seconds
1000 0
10000 1
100000 6
1000000 txn log full aborted

I will increase the log size later this weekend and try again.

Hm, this is much faster on my Linux desktop (9.1.1 a64.lnx), 62
seconds for 100000 rows.
However, I would slightly change your idea to eliminate the logging
system, i.e.
----
select date('now');\p\g

create table digits (v varchar(1));\p\g
commit;\p\g

insert into digits values ('0');
insert into digits values ('1');
insert into digits values ('2');
insert into digits values ('3');
insert into digits values ('4');
insert into digits values ('5');
insert into digits values ('6');
insert into digits values ('7');
insert into digits values ('8');
insert into digits values ('9');
commit;\p\g

create table millions as
select a.v + b.v + c.v + d.v + e.v + f.v as v1
from digits a, digits b, digits c, digits d, digits e, digits f
with nojournaling;\p\g
commit;\p\g

select date('now');\p\g
----

The create table ... as select ... with nojournaling makes sure that
the insert doesn't go through the logging system, got the 1000000 rows
in 2 seconds.

Regards
Kristoff



On Oct 24, 7:11 am, "Paul White" wrote:
> Hi everyone,
>
> I was recently trading techno nerdy stories with my children and their
> friends who have, it seems, surpassed my own skills. A challenge was issued
> to see which programmer could create a million unique rows in the database
> the most quickly. (They were all talk no action of course ) The tools were
> PHP, C++, vb, OpenROAD, mysql, ingres, mssql, Compiled code vs interpreted,
> FOR loops vs WHILE loops etc.
>
> This was my offering, standard ingres SQL and trusty terminal monitor.
>
> select date('now');\g
>
> create table digits (v varchar(1));\g
> create table millions (v1 varchar(6)) ;\g
> commit;\g
>
> insert into digits values ('0');
> insert into digits values ('1');
> insert into digits values ('2');
> insert into digits values ('3');
> insert into digits values ('4');
> insert into digits values ('5');
> insert into digits values ('6');
> insert into digits values ('7');
> insert into digits values ('8');
> insert into digits values ('9');
> commit;\g
>
> insert into millions
> select a.v + b.v + c.v + d.v + e.v + f.v
> from digits a, digits b, digits c, digits d, digits e, digits f;\g
> commit;\g
>
> select date('now');\g
>
> Timings here for your interest.
> The physical table generated was around 11Mb.
> And I found it interesting that the 1GB transaction log filled to 37%
> I guess I was expecting of the order of 6Mb
> Linux almost won but ran out of transaction log.
>
> SunFire V880 Sparc V9 8x1.2Ghz processors 32GB RAM
> Storedge 3510 with Raid 0+1, dual transaction logs of 1GB
> DB cache of about 60MB RAM
>
> rows seconds
> 1000 0
> 10000 3
> 100000 22
> 1000000 230
>
> 2. SunFire V880 Sparc V9 6x750Mhz processors 12GB RAM
> Storedge 3310 with RAID 0+1, dual transaction logs of 1GB
> DB Cache around 30MB ram
>
> rows seconds
> 1000 1
> 10000 4
> 100000 37
> 1000000 344
>
> 3. Linux Single 2.2 Ghz Xeon dualcore processor 2GB RAM
> Internal disks RAID 0, Single transaction log of 500Mb
> DB Cache around 60 MB RAM
>
> rows seconds
> 1000 0
> 10000 1
> 100000 6
> 1000000 txn log full aborted
>
> I will increase the log size later this weekend and try again.