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