0

I am migrating a long-lived web application (based on .net webforms, with some classic ASP) from SQL Server 2000 to SQL Server 2014.

Should I refactor my queries to use prepared statements? In most cases, the web application will prepare, then execute, each query. The next user of the same query will prepare, then execute it, again.

Is there any performance benefit, or scale-up benefit, of using prepared SQL queries in place of direct execution? Will it help the table server's query planner avoid repeating expensive operations?

Is there any secure way to store the prepared query objects in the web server (IIS) so the next client can reuse them?

I know there's a big advantage on Oracle of doing this; it can reuse execution plans for prepared statements, and the advantage of that far outweighs the cost of re-preparing identical statements. Is this also true on SQL Server 2014?

(I'm aware of the use of prepared statements to overcome sql injection vulnerabilities; I'm not asking about that.)

The question here is specifically about SQL Server, the Microsoft product.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Please refer here for SQL Caching Mechanisms. https://msdn.microsoft.com/en-us/library/Cc293623.aspx – Teoman shipahi Aug 05 '15 at 18:47
  • Why not write stored procedures? – Zohar Peled Aug 05 '15 at 19:05
  • SQL server will cache execution plans generated for ad hoc queries just like it does for other queries. I would suggest that moving your data to a data layer with stored procedures. – Sean Lange Aug 05 '15 at 19:05
  • It seems like you are experienced with databases so i am not gonna tell you advantages of store procedures, direct answer of your question is yes sql server caches execution plan for AD HOC queries. http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/ – Neeraj Prasad Sharma Aug 11 '15 at 12:51
  • possible duplicate of [performance of parameterized queries for different db's](http://stackoverflow.com/questions/1318023/performance-of-parameterized-queries-for-different-dbs) – Paul Sweatte Aug 25 '15 at 16:19

1 Answers1

1

This was said in comments by many but I am posting it as an answer since it is an answer to the question:

SQL Server caches plans. It won't recompile your queries if it is has them in cache. No advantage to prepare statements, this is already done by the caching.

Hogan
  • 69,564
  • 10
  • 76
  • 117