Stored procedures in DBs are NOT faster

July 16, 2007

Everyone tells me stored procedures are faster because the DB caches the execution plan. This always struck me as preposterous: a DB should obviously cache all SQL queries. Finally, someone at Microsoft SQL states, “The cached execution plan [was] used to give stored procedures a performance advantage over queries. However, for the last couple of versions of SQL Server, execution plans are cached for all T-SQL batches, regardless of whether or not they are in a stored procedure. Therefore, performance based on this feature is no longer a selling point for stored procedures. Any T-SQL batch with static syntax that is submitted frequently enough to prevent its execution plan from aging out of memory will receive identical performance benefits.” That was 2004, and she implies it was around for a few versions before that date. In most cases, I don’t think stored procedure perfs is something that should concern most IT sheep.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: