Ad hoc queries vs stored procedures vs Dynamic SQL

Question

Ad hoc queries vs stored procedures vs Dynamic SQL. Can anyone say pros and cons?

Best regards, Kristaps

Answer 1

Stored Procedures

• Pro: Good for short, simple queries (aka OLTP--i.e. add, update, delete, view records)
• Pro: Keeps database logic separate from business logic
• Pro: Easy to troubleshoot
• Pro: Easy to maintain
• Pro: Less bits transferred over network (i.e. only the proc name and params)
• Pro: Compiled in database
• Pro: Excellent query plan caching (good for OLTP queries--benefits from plan reuse)
• Con: Excellent query plan caching (bad for OLAP queries--benefits from unique plans)
• Con: Makes you tied to that SQL vendor

Dynamic SQL (i.e. uses exec command within a stored procedure)

• Pro: Good for short, simple queries (aka OLTP)
• Pro: Keeps database logic separate from business logic
• Pro: Less bits transferred over network (i.e. only the proc name and params)
• Pro: Allows any table, database, or column to be referenced
• Pro: Allows predicates (in WHERE clause) to be added/removed based on parameters
• Pro: Good query plan caching (mediocre-to-good for both OLTP and OLAP queries)
• Con: Only the static elements of the proc can be compiled
• Con: Makes you tied to that SQL vendor
• Con: More difficult to troubleshoot

Ad Hoc SQL (i.e. created in your business code)

• Pro: Good for long, complex quieres (aka OLAP--i.e. reporting or analysis)
• Pro: Flexible data access
• Pro: ORM usage is possible; can be compiled/tested in code (i.e. Linq-to-Sql or SqlAlchemy)
• Pro: More bits transferred over network (i.e. the whole query and params)
• Pro: Poor query plan caching (good for OLAP queries--benefits from unique plans)
• Con: Poor query plan caching (bad for OLTP queries--benefits from plan reuse)
• Con: More difficult to maintain, if you don't use an ORM
• Con: More difficult to troubleshoot, if you don't use an ORM

Note: Always parameterize your ad hoc SQL.

For OLAP ad hoc SQL: only parameterize string data. This satisfies two conditions. It prevents SQL injection attack. And it makes the queries look more unique to the database. Yes, you'll get a poor query plan cache hit ratio. But that's desirable for OLAP queries. They benefit from unique plan generation, since their datasets and most efficient plans vary greatly among given parameters.

Answer 2

Stored procedures

• Pro: Permissioning of actions without needing to grant more fundamental rights at the table level.
• Pro: Discrete and versionable
• Pro: Allows you to isolate your schema from your data access code.
• Con: Can be tedious to code CRUD procedures
• Con: Need to be kept in line with underlying schema

Ad hoc and dynamic - see Bill Paetzke's answers and comments.

Also, don't forget patterns such as bulk insert for SQL which isn't in your list but should still be considered.

Answer 3

Stored procedures PROs:

• Compiled. This means that it's faster to run and has positive impact on your database server's CPU due to bypassing optimization/compilation stage for all but first execution.
• Allow clean permissioning control over complex read and write queries.
• Provide for reusable API allowing one GOOD efficient implementation, instead of a bunch of Yahoos on a variety of platforms from a variety of apps re-implementing the samke queries and risking getting inefficient implementations
• Like any API, provide abstraction layer. You can change underlying implementation (schema) without changing any code calling the SP. That's an extremely big plus when there's 100s of apps across all platforms which use the query.

Stored procedures CONs:

• Hard to code flexible logic compared with dynamic SQL
• Having pre-compiled version can lead to less efficient execution as your data drifts and optimizer choices change. This is easy to ameliorate by re-compiling once in a while.

Answer 4

RDBMS? This answer is specific to older oracle

In older oracle version < 11, dynamic sql does not reuse existing SGA sqltext plans, it creates a new entry for every execution plan the parser needs. With a lot a dynamic sql calls the sqltext area gets flushed fast enough that query reuse goes way down and peformance follows it on down.