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.