mysql making pauses at inserts

Question

I am using cf 8 on a windows server with big processors and
ram.

When inserting many rows via CF query statements this takes a
lot of time.

I analyzed this performance lag with mySQL Administrator and
I found out that mySQL makes pauses of 10 to 15 seconds between the
insertions, that means: Cold Fusion wants to insert some thousands
records.
mySQL processes e.g. 130 records, then makes a pause of 14
seconds, then processes the nect 140 records, then again makes a
pause of 12 seconds, and so on.

Does anybody know how to configure cf or mysql to get rid of
this strange behaviour?
How can I force mySQL to process all insertion statements
without making those pauses?

Any hint or advice will be highly appreciated.

Thanks a lot in advance

ralf guttmann

Dan_Bracuk · Answer

You might be able to put your loop inside your query instead of your query inside your loop. the syntax is

insert into yourtable
(field1, field2, etc)
<cfloop>
select distinct #value1#, #value2#, etc
from some_small_table
<cfif loop not over>
union
closing tags

Test it though. It's not always faster.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded