Re: How can I insert multiple rows in a single SQL statement

From: Jonathan Leffler <jleffler_at_earthlink.net>
Date: Sat, 27 Jul 2002 06:53:13 GMT
Message-ID: <3D424336.2090303_at_earthlink.net>


Ryan Hennessy wrote:

> Obnoxio The Clown wrote:
>
>

>>Ryan Hennessy wrote:
>>
>>>I desperately need to reduce the number
>>>of transactions to one.
>>>
>>Why?
>>

>
> Because the operation for which I am trying to increase performance
> involves the creation of anywhere from 1000 to 8000 records in one go.
> The current implementation creates these records one-at-a-time, and the
> operation takes at least 20 minutes to complete on my Ultra 10. As the
> database interaction is the most significant factor, I need to get more
> work done per transaction to increase performance. Essentially, what
> I want to do is replace each of the 1000s of calls to
> db_add_mister_record(struct record*) with something like this:
>
> char query[SOME_LARGE_SIZE] = "INSERT INTO record VALUES ";
> char query_pos = strlen(query);
>
> /* LOOP A WHOLE PILE OF TIMES */
>
> <pre processing>
>
> /* previously we had db_add_mister_record(&temp_record) */
>
> /* instead just append to the query */
> query_pos += sprintf(query + query_pos, "(%d, %d, %d), ",
> temp_record.field_a, temp_record.field_b, temp_record.field_c);
>

You need to replace those %d's with question mark place holders, then prepare the string (outside the loop), and then repeatedly execute the statement, supplying new values for each call. As it is, you are making the server do a lot more work as it treats each row anew.

Once you've got that working, you can wring a few more ounces of performance by tweaking FET_BUF_SIZE and using an insert cursor and PUT statements (Informix extensions). But the key part is to prepare the statement once and the execute it many times.

As Art Kagel mentioned, there are utilities to do this stuff too. His ones are pretty damn good; I tend to use my own SQLCMD instead, but in an out-n-out race against his stuff, I wouldn't want to bet on SQLCMD. OTOH, when I last tested against DB-Load, SQLCMD handily outperformed it; but that testing was sufficiently long ago to be irrelevant now.

You'd also need to worry about the db_* function interface -- it isn't standard, so whoever provided it may not have provided the access you need to prepared statements with placeholders and the like. For that, you might have to drop down into raw ESQL/C. Look at the db_* function manuals rather carefully.

> /* END LOOPING */
>
> /* overwrite trailing comma */
> *strrchr(query, ',') = 0;
>
> /* execute query */
> db_execute_query(query);
>
> But, obviously, the database gives me a syntax error for the
> multiple-row insert. Can anybody think of something clever that fits in
> with the above program structure?
>
> Thanks,
> Ryan.
>
>

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix 1.00.PC2 -- http://dbi.perl.org/
Received on Sat Jul 27 2002 - 08:53:13 CEST

Original text of this message