MySQL transaction with thousands of Inserts - how many "round trips" does it take?

Question

I've got C# code that accesses MySQL through ODBC.

It creates a transaction, does a few thousand insert commands, and then commits. Now my question is how many "round trips", so to speak, happen against the DB server? I mean, does it simply transmit every insert command to the DB server, or does it cache/buffer them and send them in batches? And is this configurable in any way?

Thanks.

Answer 1

Its hard to say without seeing your code, but Im assuming you are executing the statements 1 at a time. So, you will get 1 round trip per insert statement. In MSSql you can execute multiple inserts in a single statement eg.

cmd.ExecuteNonQuery "insert table values (1) insert table values (2)"

So you can create a big string and execute it (I think it will have a limit), I assume this will work for MySql

Also in MSsql you have a batch inserter (Lookup SqlBulkCopy), in MySql perhaps try loading the data from a temp file

Answer 2

It does one round trip per query you submit (regardless of whether it's in a transaction or not).

It is possible, in MySQL, to use "extended insert" syntax which allows you to insert several (or indeed, many) rows in a single statement. This is generally considered a Good Thing.

Answer 3

When using MySQL 4.x a few years ago, we ran into a hard limit on query size that was not configurable.

This probably won't help you much as, 1) I don't remember what the hard limit was, 2) you're probably not using MySQL 4.x, and 3) we weren't using transactions.

Good luck!

Answer 4

A round trip to the DB server is not the same as a round trip to the database on disk.

Before you decide that the round trips are a bottleneck, do some actual measurements.

There are ways to insert multiple rows with a single insert, depending on your DBMS. Before you invest the coding effort, figure out whether it's likely to do you any good.

Answer 5

Mysql has an extended SQL style that can be used, where mass inserts are put in several at a time.

INSERT INTO table (id, event) VALUES (1, 94263), (2, 75015), (3, 75015);

I will usually collect a few hundred insert-parts into a string before running the SQL query itself. This will reduce the overhead of parsing and communication by batching them yourself.

Answer 6

So what's the limit on the number of rows that can/should be inserted in one extended insert command?

Any ideas on how to go about figuring the right number? (naturally I assume the performance benefit decreases at some point as a function of the number of rows)

Answer 7

depends on where you invoke sql statement, I tried it once with Mysql JDBC driver and get an error saying the limit is 1MB but it is configurable . I didn't bother to try to configure it and just split the sql statements into smaller pieces