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.

About these ads

4 Responses to Comparison: Ad-hoc vs Stored Procedure vs Dynamic SQL

  1. You don’t explain what an ad-hoc query is, compared against dynamic sql.
    From what I read here, ad-hoc is what is inside your codebase. You can write it yourself as a string, or an O/RM creates it. But I don’t really get what you mean with Dynamic SQL. Do you mean string concatenated sql?!?!

    • DevJef says:

      Not completely. An ad-hoc query is a T-SQL query that is executed, in orther to retrieve data. This can be done from within a tool (application, Stored Procedure, etc.) or from SQL Server Management Studio (SSMS). So almost every query is considered an ad-hoc query.

      A dynamic SQL statement on the other hand, is a statement that is build from concatenated string, possibly with a few variables included in it to execute specific pieces of T-SQL. This statement is executed against the engine by using “EXEC ([String with statement])”, or using “EXEC SP_EXECUTESQL([String with statement])”.

      Hopefully this will clear things up for you? ;)

      • No, not really… Because then the third column looks really weird to me. For example O/RM is impossible? O/RM are used most of the time to create those queries.

        On the other hand, this is really, really looked at from the perspective of a database developer. Because comments like “No standard way to search through” is totally wrong from a developers perspective. I can create all kinds of diagrams from this code, look up references, etc. Also testing it is much easier because tooling provides additional information that simple profiler cannot provide. For example the mini profiler you can ‘plugin’ to your mvc project, supplies all T-SQL executed against the database. Whereas with SQL Profiler you get a view from _all_ queries against your database, also the queries that aren’t yours.
        :-)

      • DevJef says:

        Object Relational Mapping is impossible in Dynamic SQL statement, because you don’t query the objects. In a normal ad-hoc query you talk to the objects (tables, views, etc.), but with dynamic SQL you skip this phase. You skip the parsing of you statement, and execute it directly to the engine. So you can’t really map your objects, which is possible with SP’s or ad-hoc queries. Your dynamic statement actually is nothing more and nothing less then a bunch of text, which is converted to a statement by the engine.

        And regarding to the searching: I agree. If you use your source control to search through your code, you’re totally right! But how many scripts are actually source controlled, not counting your applications? In most cases there are thousands of queries that aren’t source controlled. And those scripts are used to maintenance a database or are used to create a business process. So in many cases it’s hard to track these scripts with functionality in it. And yes, you can use profiler or other tools to track this. But how often do companies track these types of statements 24/7? ;)

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

Follow

Get every new post delivered to your Inbox.

Join 50 other followers

%d bloggers like this: