0

I've used prepared statements (MySQL & PHP) for many years, but only with an eye to being safe against SQL injection. I've never re-used a prepared statement, I've always built them up every single time I want something back from the database.

I currently have a query that runs inside a loop. Each iteration of the loop I build the SQL string, prepare the query, and execute it with the relevant value for the bound parameter.

Someone mentioned that if I instead prepared the query outside of the loop, and inside the loop only bound the new parameter value and executed it, I'd see a performance increase. And not just the nanoseconds that you'd expect from not having to build a string up each loop.

But I ran a test, and I didn't seem to.

Should I have?

I'm talking about MySQL/MariaDB. He uses SQL Server.

1
  • 1
    On SQL Server it definitely doesn't save any server time (because all parameterized queries go through sp_executesql and then get retrieved from the query plan cache), it may save time on the client due to not needing to build up the parameter objects again. The extra time is going to be nanoseconds. Commented Feb 12, 2023 at 18:47

1 Answer 1

1

Reusing a prepared statement saves your the SQL parsing and compilation overhead. For simple statements it is quite small, so it wouldn't be surprising to not see much improvement on a lightly loaded system with simple statements being executed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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