This article has allowed me to bring down the execution time for an SP from 3 secs to 0.5 secs. Great Job. I suprised that this information is not present itn books online. Especially since SQL Server recommends the use of Temporary tables over Cursors. However I found that every DML on a temporary table causes the SP to recompile. This can be avoided by embedding all Temp Table DML's within sp_executeSQL. This brought the execution time of my SP from 2.5 to 0.5 secs.

I have one more observation

This is with respect to

SET ANSI_WARNINGS ON

Here is the part of the profiler trace when a SP contains this statement

Event Duration

SP:CacheHit 0execute sp_checkavail

SP:CacheMiss 0sp_checkavail

SP:CacheMiss 0sp_checkavail

SP:CacheMiss 0sp_checkavail

SP:CacheRemove 0sp_checkavail

SP:CacheInsert 0sp_checkavail

SP:Starting 0sp_checkavail

SP:StmtStarting 0-- sp_checkavail SET NOCOUNT ON

SP:StmtCompleted0-- sp_checkavail SET NOCOUNT ON

SP:StmtStarting 0-- sp_checkavail SET ANSI_WARNINGS OFF

SP:StmtCompleted0-- sp_checkavail SET ANSI_WARNINGS OFF

SP:StmtStarting 0-- sp_checkavail IF @bedrooms = 'H' or @bedrooms = 'S'

SP:Recompile 0sp_checkavail

SP:CacheMiss 0sp_checkavail

SP:CacheMiss 0sp_checkavail

SP:CacheInsert 0sp_checkavail

SP:StmtStarting 0-- sp_checkavail IF @bedrooms = 'H' or @bedrooms = 'S'

SP:StmtCompleted 391-- sp_checkavail IF @bedrooms = 'H' or @bedrooms = 'S'

When I comment out the SET ANSI_WARNINGS OFF

the trace looks like

Event Duration

SP:CacheHit 0execute sp_checkavail

SP:CacheMiss 0sp_checkavail

SP:ExecContextHit 0sp_checkavail

SP:Starting 0sp_checkavail

SP:StmtStarting 0-- sp_checkavail SET NOCOUNT ON

SP:StmtCompleted 0-- sp_checkavail SET NOCOUNT ON

SP:StmtStarting 0-- sp_checkavail SET ANSI_WARNINGS OFF

SP:StmtCompleted 0-- sp_checkavail SET ANSI_WARNINGS OFF

SP:StmtStarting 0-- sp_checkavail IF @bedrooms = 'H' or @bedrooms = 'S'

SP:StmtCompleted 0-- sp_checkavail IF @bedrooms = 'H' or @bedrooms = 'S'

Any Comments