Ad hoc queries vs stored procedures vs Dynamic SQL [closed]

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

Solution 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.

Solution 2:

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.