What are prepared statements in the context of relational databases? How do they help and when should I consider using them?
+3
A:
A prepared statement is a query that is pre-parsed by the database. You can run the query over and over with different parameter values without the database having to parse and plan the query from scratch each time.
You use it when you need to run the same query many times, with only some data varying from time to time.
Guffa
2010-06-15 07:25:29
And there is API (ADO.NET or other) to tell the database that I want it to cache the plan so that I can use it the next time? From the API perspective is there something different I need to do while executing a command?
alwayslearning
2010-06-15 08:02:14
Generally, the database will automatically cache the plan and re-use it for the same query (within a reasonable time frame). However, this is somewhat dependent on the way the query is written in the first place. If the query is written with properly parameterized variables, the same plan will be used when the variables change. If the variables are concatenated into the SQL statement, the database will have to re-parse each time.
Allan
2010-06-15 17:08:16