Bug #85266 Array Insert is not working as expected.
Submitted: 2 Mar 2017 6:15 Modified: 17 Apr 2017 11:39
Reporter: kriti suwalka Email Updates:
Status: No Feedback Impact on me:
Category:Connector / ODBC Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[2 Mar 2017 6:15] kriti suwalka
We are trying to use array insert feature of MySQL ODBC driver but we are observing strange behavior.  Our sample ODBC code achieving array insert seems to be correct as ODBC trace does show the correct behavour however when we check slow query logs at server,each row is seen inserted individually. Which is leading performance issues.
We are having 10K rows and having SQL_ATTR_PARAMSET_SIZE set to 1000. ODBC trace shows 10 SQLExecute statements which is fine but slow query logs at server shows single row insert(10k entries for insert statement).

Attaching ODBC trace as well as slow query logs.

How to repeat:
Create an odbc connection to MySQL. Enable array insert and try to insert large number of rows in target table using sample ODBC application. Check ODBC trace logs and slow query logs.
[10 Mar 2017 10:38] Bogdan Degtyariov
Hi Kriti,

Sorry, I do not quite get what the problem is.
Is it the slow query inserting 1000 rows at a time or 10K separate INSERT queries?
[13 Mar 2017 7:45] kriti suwalka
Hi Bogdan,

Slow query is inserting single row at a time hence 10k insert statements can be seen in slow query logs. When SQL_ATTR_PARAMSET_SIZE is set to 1000, ideally it should insert 1000 rows at a time but it is not.
[16 Mar 2017 7:32] Bogdan Degtyariov
Hi Kriti,

Thanks for your reply. Now the case is clear for me.
Your program is preparing the following statement:

INSERT INTO sampleTable VALUES (?, ?, ?);

It does not matter how many rows you intend to insert. The prepared query specifically limits it to one row at a time. The parameters of arrays can be used not only for INSERT, but for SELECT, UPDATE and DELETE queries as well. In case of UPDATE the SQL syntax does not allow updating many rows to different values that satisfy different WHERE clauses. And therefore queries are executed one by one.

The point could be argued that for the performance reasons only INSERT should be enhanced with the  multi-row INSERT like this:

INSERT INTO sampleTable VALUES (?, ?, ?), (?, ?, ?) .... (?, ?, ?);

However, this is not feasible because it is not necessary that all values are supplied as parameters, some of them could be hardcoded:

INSERT INTO sampleTable VALUES (?, NOW(), ?, 1, "abc", ?);

Doing multiple row insert for such query is tricky and might not always work because the driver cannot predict the application logic. Therefore it is left to the user to determine the format of the row.

If you wish to insert 10000 rows at a time you should prepare a query with all rows as following:

INSERT INTO sampleTable VALUES (?, ?, ?), (?, ?, ?) ...<9997 rows>... (?, ?, ?);

This issue looks like a usage problem that has several solutions. The easiest and the most efficient one I have just described to you.
[17 Mar 2017 10:56] kriti suwalka
Hi Bogdan,

We are trying to use array insert feature of mysql odbc driver. "SQL_ATTR_PARAMSET_SIZE" is the parameter which needs to be set if we want to insert multiple rows in single insert statement. We are setting this parameter to 1000 that means with one insert statement it should insert 1000 rows. If you see the ODBC trace logs, it will show you only 10 SQLExecute() statements but it will insert 10k rows in the target table(As 1 insert statement will insert 1000 rows because of setting SQL_ATTR_PARAMSET_SIZE to 1000. 10 insert statement means 10*1000 = 10k rows) which is expected behavior. But slow query logs show you 10k insert statements which is problematic. It means that driver is not honouring SQL_ATTR_PARAMSET_SIZE parameter and sending 10k insert statements to server whereas it should send only 10 insert statement. Ideally array insert should improve the performance but we are not seeing any change.
ODBC trace captures correct behavior which shows that we have correctly configured our application to use array insert.

This is MySQL article on array insert.
[17 Mar 2017 11:39] Bogdan Degtyariov

Your ODBC trace has only one SQLExecute(), which is correct, but this SQLExecute() call generates all multiple inserts, which you see in the server log.

Do you actually have a server query log inserting 1000 rows in one INSERT?
[18 Apr 2017 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".