Preparing SQL Statements (by yag)

I was involved in an email thread this week about optimization and a discussion of the ability to prepare SQL statements (as ADO, ADO.NET and Fox all let you do) before running them came up. There is an on going misconception about prepare.  There are a couple of issues around preparing statements:

  • SQLServer 6.5 did not do that great of a job of caching queries.  If you have an application that used a standard small number of complex queries you would get a big advantage by preparing those queries and maintaining a reference to the command object.

  • SQLServer 2000 added a very nicely built procedure cache.  This procedure cache now does cost based analysis of the query. So if the query is expensive to run it will try to keep it around longer.  It also balances the memory usage of the server with the regular buffer management.  

  • Since the database is now managing the procedure cache.  An application that is handling its own prepared statements will now be pinning procedures in the cache.  This causes the cache manager to keep statements even when the database may be more optimized by throwing that statement out and using that memory for something else.  The application does not have the global view of resource usage that the database has.

  • There may still be some statements that are worth caching manually but it is not common.

Anyway, I found this interesting and thought you might as well.

Comments (4)

  1. John Koziol says:

    Yeah, there were major changes in SQL 2000 as to how queries were cached. Prior to that version, there were very rigid rules on re-use of queries – as of 2000 it wasn’t so rigid insofar as a minor change would not force a re-compile of the query.

  2. You have been Taken Out! Thanks for the post.

  3. Gustavo Proto says:

    I do not understand what you mean by "… if the query is expensive to run it will try to keep it arround longer …". Does "run" mean parsing, validating and creating an access plan for the statement? If so, should you say that if, say 50, clients are executing over and over the same simple (or complex) set of SQL statements on the database they may perform better if they do not prepare the statements and just execute them multiple times?


  4. yag says:

    Gustavo – I’m saying that since SQL2K and beyond do a good job of caching for you, you don’t have to do it yourself thru the prepare/execute mechanism.