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