3

I'm trying to use prepared statements with python to improve query performance, but my prepared statements are slower (by a factor of ~7.5 no matter how many iterations I set in my benchmark script).

Here is a small benchmark script I wrote:

In essence what I do for regular queries:

cursor = connection.cursor()
cursor.execute("INSERT into test (id,name) VALUES (1,'Value 1')")
... multiple inserts ...
connection.commit()

And for prepared statements:

cursor = connection.cursor(prepared=True)
sql = "INSERT into test (id,name) VALUES (%s,%s)"
cursor.execute(sql, (1, 'Value 1'))
... multiple inserts ...
connection.commit()
  • The script runs 2 loops with same number of iterations
  • 1st loop runs with the default .cursor() while 2nd with .cursor(prepared=True) as per above

And here are the results:

enter image description here

As you can see MySQLCursorPrepared=9746ms while MySQLCursor=1299ms.

Why are my prepared statements slower than regular queries? Is it an issue with the python mysql.connector driver or do I need to tweak something in my MySQL config to fix this?

1
  • the code has to be oarsed before sending and the missing content added to sthe string, so that takes its time,. Live with it. – nbk Jun 12 '20 at 9:48
1

Much faster is to build a multi-row insert:

INSERT INTO t (a,b)
    VALUES
    (1,2),
    (11,23),
    (111,234), ...

On the MySQL side, this runs about 10 times as fast (for 100-1000 rows) than single-row inserts.

1
  • Thanks, it doesn't answer the question but yes it helps. The problem I have is that I'm running my queries in a streaming environment so I can't easily batch (I would have to deal with queues and timeouts to decide when/if to batch) and batching raises other problems (eg batch size etc...). Hence why I was hoping for now to have prepared statements to continue individual inserts but faster – Panda Coder Jun 18 '20 at 12:24
0

I was bitten by the same problem today and your question was the only one that showed up in a rather exhaustive (and exhausing) Google search.

Anyway, here's what I found.

If you execute two or more different query strings as prepared statements on the same cursor, it will be very slow.

In your benchmark script, you are executing a DELETE query and then executing five identical INSERT queries on the same cursor. This is what's causing the slowness. Apparently mysql.connector doesn't allow you to prepare more than one query string on the same cursor, so when you switch from DELETE to INSERT it shuffles around a lot of things internally. The same thing happens when you loop over and execute a DELETE query again.

To fix the problem, you have to create multiple cursors and execute each unique query string on its own cursor.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.