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?

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: Better security (users don't need direct table access)
• 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
• Con: More vulnerable to SQL injection attacks

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: 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 bits transferred over network (i.e. the whole query and params)
• Con: More difficult to maintain, if you don't use an ORM
• Con: More difficult to troubleshoot, if you don't use an ORM
• Con: More vulnerable to SQL injection attacks

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.