Are stored procedures more performant than on the fly sql?

Hello.

I've read the other day (don't remember where :( ) that there is no performance advantages on executing stored procedures than executing sql strings made "on the fly". It said that both operations are precompiled on first execution, and then cached to posterior executions... Is that correct?

Thanks in advance,
Matias

Yes they are as compared to the normal TSQL statements provided the stored procedures a recompiled whenever they are modified for update of cache plan.

You need to refer to the books online and these links http://www.sql-server-performance.com/tn_stored_procedures.asp and http://www.awprofessional.com/articles/article.asp?p=25288 to know more about SPs.

|||

Satya,

your answer is a bit confusing....

are you saying that Stored Procedures ARE faster then on the fly SQL ? or are you saying they are NOT ?

my understanding is that they ARE especially when the SQL is more complex.

This is not the only reason to use sprocs however, other reasons are improved security, improved maintainability and modularity, etc.

cheers,

|||

Yes, I thought that too. Does sql 2005 precompiles and caches text t-sql statements, making them as performant as stored procedures on subsequent calls? I'm not discussing all the advantages of using stored procedures vs on the fly sql, I just want to know if there is any performance penalty using on the fly sql.

Thanks,
Matias

|||

as per this article, "ALL" Sql Statements are cached\precompiled.

http://msdn2.microsoft.com/en-us/library/ms181055.aspx

so that would suggest that there may NOT be a huge performance issue in using sprocs.

The argument to use sprocs for Security and maintainability is more viable.

It would be fairly easy to test this use SQL Profiler......

cheers