Re: How can I insert multiple rows in a single SQL statement
From: Obnoxio The Clown <obnoxio_at_hotmail.com>
Date: Mon, 22 Jul 2002 16:58:08 +0100
Message-ID: <3D3C2B90.105_at_hotmail.com>
>
> 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?
Received on Mon Jul 22 2002 - 17:58:08 CEST
Date: Mon, 22 Jul 2002 16:58:08 +0100
Message-ID: <3D3C2B90.105_at_hotmail.com>
Have you looked into the FET_BUF_SIZE environment variable? Also, page 14-17 of the ESQL/C programmers manual? (PUT?)
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);
>
> /* 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?
Received on Mon Jul 22 2002 - 17:58:08 CEST