0

I'm working on improving the data import performance of a web application, and one thing that has drastically improved performance, was porting database related logic from the server side code into stored procedures and functions in the database.

Also, when dealing with large amounts of similar queries, queuing them in batches to later be executed in a single SQL transaction, provided a noticeable decrease in the bottleneck of the import process.

Unfortunately I've come to a problem along the way, for some reason I cannot call a stored procedure more than once in a single SQL command.

Take this SQL where I make two consecutive calls to LegacyUpdateProduct to showcase my problem.

SQL

The stored procedure accepts a few parameters with data, does not return a result set, and out gives the @ID variable with the row ID, which in this case we have no need for anyway.

CALL LegacyUpdateProduct(92,'00010','ADAISD 345',18.81,'91020200','FSB92','GS8219','FSB92',TRUE,2,3,0,0,@ID);CALL LegacyUpdateProduct(277,'0002335600','TPASD 987',6.04,'0 002 335 ','UX79','','',TRUE,2,3,0,0,@ID);

PHP

In PHP anyone would expect to simply run the query with the two calls to LegacyUpdateProduct and that everything would be alright.

$sql = "CALL LegacyUpdateProduct(92,'00010','ADAISD 345',18.81,'91020200','FSB92','GS8219','FSB92',TRUE,2,3,0,0,@ID);CALL LegacyUpdateProduct(277,'0002335600','TPASD 987',6.04,'0 002 335 ','UX79','','',TRUE,2,3,0,0,@ID);"
# With Code Igniter
$this->db->query($sql);
# Or with raw PHP
mysqli_query($this->db->conn_id, $sql);

Sadly that isn't the case. Running this query with Code Igniter or with plain PHP throws a SQL error.

Note: I went through the data with a fine-tooth comb, and made use of Code Igniter DB escape mechanism to produce safer queries.

Error Number: 1064 You have an error in your SQL syntax

Most interesting is that when I copy and paste the allegedly erroneous SQL in PHPMyAdmin or MySQL Workbench, it runs smoothly without errors or warnings.

Actually, I was able to run up to fifty consecutive calls to LegacyUpdateProduct in a single query without a problem in PHPMyAdmin and MySQL Workbench.

I've looked through Google and SO for questions that might be related or helpful in someway, but haven't found much to go on, except that this might be a Code Igniter bug of some sorts, but even with plain PHP I found the same problem.

In an answer to another SO question that is somewhat related, it mentions this might be a Code Igniter bug, however it doesn't explain why with plain PHP mysqli_query() this query fails.

In the comments of another question, for a different error, hints that there might be a concurrency problem, were sequential calls to the procedure cannot be made without waiting for completion of the previous. I do not have enough understanding of MySQL concurrency to make a claim but, shouldn't each command in a query be executed after the previous.

Help?

Community
  • 1
  • 1
Fábio Antunes
  • 16,984
  • 18
  • 75
  • 96
  • 1
    If you want to run 2 statements at once, you have to use [multi_query](http://php.net/manual/de/mysqli.quickstart.multiple-statement.php). Or just split your query in two querys. `query` doesn't know how to use the code after the first `;`, that is why you get a syntax error. – Solarflare Aug 28 '16 at 09:10
  • Interesting, being used to run multiple queries in the same sql statement, never considered `mysqli` might have implemented that method of operation to safe guard against malicious intent. I've explored that avenue, and I'm able to run all stored procedures in the same statement on the first call to `multi_query`, however the following calls to `multi_query` are falling somehow. But more importantly I'm not noticing a performance increase, I will read the docs and get to know this method better, more importantly finding what MySQL connection flag enables execution of queries after `;`. – Fábio Antunes Aug 28 '16 at 16:24
  • 1
    To save you the time, the only thing that enables execution after the `;` is using `_multi`. And you won't get much performance increase out of it (as long as you keep your connection open). Always prefer single querys! For a lot of reasons! E.g. safety, parameter binding, it's easier ( `_multi` requires you to read all results before proceeding, that is why your following querys are failing), ... For some technical insights (e.g. the connection settings), have a look at [C API Support for Multiple Statement Execution](https://dev.mysql.com/doc/refman/5.7/en/c-api-multiple-queries.html) – Solarflare Aug 28 '16 at 16:59
  • I've toyed around with client flags a little but with no success. The source data comes from a reliable source (no user input), regardless, I still make use of Code Igniter for data escape and parameter binding to build each query, which brings some level of security to the whole process. So, best practices aside, with `mysqli` do you know a way to run multiple queries using query instead of `multi_query`? – Fábio Antunes Aug 29 '16 at 13:50
  • There is no such way. There is not a single reason to do so either. Just run your queries through separate query() calls, like you have been told already – Your Common Sense Aug 29 '16 at 13:58

1 Answers1

0

In PHP anyone would expect to simply run the query with the two calls

There is no such thing like a "query with two calls". What you effectively have is two SQL queries. While mysqli::query can run only one SQL query per call.

when dealing with large amounts of similar queries, queuing them in batches to later be executed in a single SQL command, provided a noticeable decrease in the bottleneck of the import process.

Most likely its famous innodb's paranoid mode. Either switch it off or wrap your updates in a transaction. And you will see NO noticeable decrease for the separate query() calls.

Community
  • 1
  • 1
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • Apologies, I mistakenly used SQL command instead of SQL transaction in the question, the second quote in your answer. Each batch of queries is being processed in a single transaction, I would start the transaction, call each query, and commit the transaction. – Fábio Antunes Aug 29 '16 at 14:20
  • Wrapping my calls a in stored procedure was a good suggestion. At first running all 58k calls to the stored procedure took 211 minutes, averaging 0.22 seconds per call. When I tried running them in batches of 1k per transaction, the total running time was little over 56 minutes, averaging 0.06 seconds. – Fábio Antunes Aug 30 '16 at 00:36
  • Your hint was again quite useful. My first approach with your suggestion wasn't good enough, unhappy with the improvement, went back to drawing board and redesigned that import section. Long story short, its now down to roughly 270 seconds. – Fábio Antunes Aug 30 '16 at 07:47