Menu
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?
267
A modern disk can do ~1000 fsyncs per second, but MySQL will group multiple writes with each fsync. An okay rule-of-thumb would be 5000-15,000 writes per second, depending on things like writes per transaction, number of indexes, hardware, size of writes, etc.
The INSERT statement in MySQL also supports the use of VALUES syntax to insert multiple rows as a bulk insert statement. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas.
MySQL INSERT multiple rows statement In this syntax: First, specify the name of table that you want to insert after the INSERT INTO keywords. Second, specify a comma-separated column list inside parentheses after the table name. Third, specify a comma-separated list of row data in the VALUES clause.
You can put 65535 placeholders in one sql.So if you have two columns in one row,you can insert 32767 rows in one sql. Show activity on this post. Show activity on this post. You can insert an infinite number of rows with one INSERT statement.
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.
76
There is no limit on the number of rows per se; The limit is on the number of bytes transferred to the server.
You could build the bulk insert up to the number of bytes specified in 'max allowed packet'. If I wanted to use the least amount of inserts I would try that.
32
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
