alainb:

It looks like you should be able to reduce the number of database calls. The method will likely differ based on the database, but you should be able to reduce the number of operations for each set, possibly to a single transaction. Examples:

MySQL

MySQL allows you to insert multiple records with a single statement, like so:

insert into FooBar (ID, col1, col2, col3)
values ((select max(ID)+1 from FooBar), 'a', 1, 'flort'),
       ((select max(ID)+2 from FooBar), 'b', 2, 'zap'),
       ((select max(ID)+3 from FooBar), 'c', 3, 'kazoo')
[download]

This would let you use a single communication for each table type.

MS SQL Server / Sybase / Oracle / etc.

insert into FooBar (ID, col1, col2, col3)
select ID, col1, col2, col3 from (
    select max(ID)+1 from FooBar) ID, 'a' col1, 1 col2, 'flort' col3
    union
    select max(ID)+1 from FooBar), 'b', 2, 'zap'
    union
    select max(ID)+1 from FooBar), 'c', 3, 'kazoo'
)
[download]

Oracle

Some database drivers, such as Oracle, let you send a block of SQL to execute, so you can build blocks and do all your inserts with one swell foop:

declare
   v_ID number;
begin
   -- First table
   select max(ID) into v_ID from FooBar;
   insert into FooBar (ID, col1, col2, col3)
   values (v_ID+1, 'a', 1, 'flort');
   insert into FooBar (ID, col1, col2, col3)
   values (v_ID+2, 'b', 2, 'zap');
   insert into FooBar (ID, col1, col2, col3)
   values (v_ID+3, 'c', 3, 'kazoo');

   -- Second table
   select max(ID) into v_ID from BarBaz;
   insert into BarBaz (ID, col1)
   values (v_ID+1, 'etcetera');
end;
[download]

Using a work table

Another option, that I frequently find useful, is to create some temporary work tables and bulk load the data into them. The bulk loader should be about the fastest way to get data into the database. Then create a stored procedure that integrates the data in the work tables into the real tables. For adding large sets of data to a database, this is pretty hard to beat.

...roboticus

When your only tool is a hammer, all problems look like your thumb.

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.