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

In message <3D3C25ED.87025A6E_at_alcatel.com>, Ryan Hennessy <ryan.hennessy_at_alcatel.com> writes
>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);
>
>/* 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.
>

Have you considered using an insert cursor? That always seemed to give a good performance improvement over a singleton insert... The ESQL/C programmers manual gives a description of it, and some hints and tips about increasing the put buffer size to improve performance further.

Remember to flush the buffered rows at the end, though!

-- 
Andrew Lennard                                     andy_at_kontron.demon.co.uk