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.

<table cellpadding=”5” cellspacing=”1” border=”0” width=”630” font-size:10px;color:#000;”>

  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</p>

Changed data is no issues because of re-compile</td> Compiled once at first run, and stored in Procedure Cache</p>

Changed data might be a performance bottleneck. Can be
solved with recompile</td> Compiled at runtime, and execution plan is not stored

(unless
using the more efficient sp_executesql)</p>

Changed data is no issue because of re-compile</td> </tr> 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</p>

Query and resultset are send across
network/internet</td> Query executes server side</p>

Execute statement
and resultset are send
across network/internet</td> Query executes server side</p>

Statement
and resultset are send across
network/internet</td> </tr> 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.</p>

Dependency window in SSMS shows SP content</td> No standard way to search through </tr> 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 </table> ## Why Hive is not supporting Stored Procedure? Hive and Hbase are not support stored procedure. However, Hive plans to support Sp ([HIVE-3087](https://issues.apache.org/jira/browse/HIVE-3087)) in the future. HBase has no plan about supporting Sp since it only focuses on being a Storage and more like NoSQL. [Hive UDF](https://cwiki.apache.org/Hive/languagemanual-udf.html) could implement some function of stored procedure, though it's not enough. # RDBMS vs.Hadoop Here is a comparison of the overall differences between the RDBMS and MapReduce-based systems such as Hadoop:

	RDBMS	MapReduce
Data size	Gigabytes	Petabytes
Access	Interactive and batch	Batch
Structure	Fixed schema	Unstructured schema
Language	SQL	Procedural (Java, C++, Ruby, etc)
Integrity	High	Low
Scaling	Nonlinear	Linear
Updates	Read and write	Write once, read many times
Latency	Low	High

# Beyond Hadoop: Fast Ad-Hoc Queries on Big Data