Stored procedures - do they get compiled?

[JulianUK]

Hi
Can someone please tell me what is done upon creation of a SQL Server 2k stored procedure in terms of any sort of compilation?

I have a stored proc that builds its own SQL based on the params its given and i'm concerned that any optimisation that normally takes place at creation time cannot possibly take place as the SQL is contained in strings (and ultimately EXEC'd). Or is there no such optimisation?

Many thanks in advance,
Julian

 

[tlbroadbent]

The dynamically executed statements must be compiled at run time. Usually, SQL server will create an optimized query plan. However, you can't tune the query as you can static code. Make sure table indexes are not fragemented. Reindex or Defrag as needed. Also make sure statistics are up to date. You should know which columns are candidates for searching and be able to build indexes on those columns. If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[JulianUK]

Thankyou tlbroadbent, for your useful input, and congrats on your Tipmaster of the week too...