Do prepared statements give a performance advantage when running multiple times with similar parameters?

Question

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.

Answer 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.