Comparison: Ad-hoc vs Stored Procedure vs Dynamic SQL

Sometimes when you are creating a SQL query, you wonder if it’s more sensible to use a standard T-SQL, or some other possibility. In this post I will try to compare the three different possibilities, so that you can make your own decision. If you read the comparison below, keep in mind that there is no right or wrong in this. Each situation requires a different point of view, and may offer it’s own difficulties.

  Ad-Hoc Query Stored Procedure Dynamic SQL
Use it for Long, complex queries
(OLAP; for example Reporting or Analysis)
Short, simple queries
(OLTP; for example Insert, Update, Delete, Select)
Short, simple queries
(OLTP; for example Insert, Update, Delete, Select)
Performance Compiled at runtime, Execution Plan stored in Cache

Changed data is no issues because of re-compile

Compiled once at first run, and stored in Procedure Cache

Changed data might be a performance bottleneck. Can be
solved with recompile

Compiled at runtime, and execution plan is not stored

(unless
using the more efficient sp_executesql)

Changed data is no issue because of re-compile

Security Permissions (read/write) on all objects (database(s)/table(s)) Execute permissions on SP are enough Permissions (read/write) on all objects (database(s)/table(s))
Flexibility If changed, your application needs to be recompiled If changed, only need to change the SP in the database If changed, your application needs to be recompiled
Number of Statements Only 1 statement possible Multiple statements possible Multiple statements possible
Memory Usage Uses more memory then an SP Uses less memory then an ad-hoc query Uses more memory then an SP
Network traffic Query executes server side

Query and resultset are send across
network/internet

Query executes server side

Execute statement
and resultset are send
across network/internet

Query executes server side

Statement
and resultset are send across
network/internet

Separation Database logic and business logic are mostly combined in the query Seperate database logic from business logic Seperate database logic from business logic
Troubleshoot Relatively easy to troubleshoot Relatively easy to troubleshoot Difficult to troubleshoot
Maintaining Difficult because of several locations in applications and database Easy because of single location Difficult because of several locations in
applications and database
Access Difficult to access multiple objects in different databases,
or in dynamic databases
Difficult to access multiple objects in different databases,
or in dynamic
database
Allows any object (database, table, columns, etc) to be referenced
WHERE clause Fairly static WHERE clause Fairly static WHERE clause Dynamic WHERE clause (add/remove), based on parameters
Versioning Only possible via Source Controlling your application Possible via Source Controlling your database, and by commenting your SP Only possible via Source Controlling your application
CRUD Can be created by getting all your  queries together, and looking for
specific
keywords

(Update, Delete, Select, Etc)
Difficult to catch in a CRUD

(Create, Read, Update, Delete) diagram
Difficult to catch in a CRUD (Create, Read, Update, Delete) diagram
Structure Update Can be changed
simultaneously with structure changes
Needs to be altered when the underlying structure is changed Can be changed simultaneously with structure changes
Searching No standard way to search through Possible to use sys.procedures to search through SP contents.

Dependency window in SSMS shows SP content

No standard way to search through
Testing Can be compiled/tested in code Impossible to automatically compile without 3rd party tools Difficult to test in code
Mapping ORM (Object-relational mapping) is possible ORM (Object-relational mapping) is impossible ORM (Object-relational mapping) is impossible
Compiling Compiles the whole statement Compiles the whole statement Only static elements can be compiled

For the design of this comparison chart, I need to thank my buddy and colleague Pascal (Blog | @pdejonge). For the record: I’m not a designer, and my “design” was what you guys might call Fugly.

These comparison chart covers the main reasons for me to use or not use a specific option. These are my personal beliefs. If you have any suggestions to add, please don’t hesitate to contact me.

Last update on object?

How many times do you wonder about when an object was last updated? You can find out by using the default sysobjects in SQL Server:
 

USE Adventureworks

DECLARE @DBID INT = DB_ID('Adventureworks')

SELECT
	o.name AS TableName,
	ddius.*
FROM sys.dm_db_index_usage_stats ddius
JOIN sys.objects o
	ON o.object_id = ddius.object_id
WHERE database_id = @DBID
ORDER BY ddius.last_user_update DESC

 
This will give you the last_user_update, which is based on indexes. The Insert, Delete or Update are stored in the sys properties of the database. So this only works if there is an index of some sort is used on the object!

Follow

Get every new post delivered to your Inbox.

Join 50 other followers