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

by

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.

More Related Contents:

  1. Refactor a PL/pgSQL function to return the output of various SELECT queries
  2. Can I pass column name as input parameter in SQL stored Procedure
  3. SQL Server – How to lock a table until a stored procedure finishes
  4. Stored procedure or function expects parameter which is not supplied
  5. nvarchar(max) still being truncated
  6. How to use table variable in a dynamic sql statement?
  7. Search text in stored procedure in SQL Server
  8. PostgreSQL parameterized Order By / Limit in table function
  9. How do I execute a stored procedure once for each row returned by query?
  10. Return multiple fields as a record in PostgreSQL with PL/pgSQL
  11. Are Stored Procedures more efficient, in general, than inline statements on modern RDBMS’s? [duplicate]
  12. Call a stored procedure with another in Oracle
  13. Which is better: Ad hoc queries or stored procedures? [closed]
  14. How to return the output of stored procedure into a variable in sql server
  15. Execute Immediate within a stored procedure keeps giving insufficient priviliges error
  16. Procedure or function !!! has too many arguments specified
  17. SQL update fields of one table from fields of another one
  18. Is it possible to have a default parameter for a mysql stored procedure?
  19. Array in IN() clause oracle PLSQL
  20. COPY with dynamic file name
  21. Inserting rows into a table with one IDENTITY column only [duplicate]
  22. UNION the results of multiple stored procedures
  23. How to pass a temp table as a parameter into a separate stored procedure
  24. How to get rid of “Error 1329: No data – zero rows fetched, selected, or processed”
  25. Error trying to call stored procedure with prepared statement
  26. What is the difference between a stored procedure and a view?
  27. Functions vs Stored Procedures
  28. How to execute a stored procedure inside a select query
  29. Call stored procedure with table-valued parameter from java
  30. PostgreSQL – dynamic value as table name [duplicate]

Leave a Comment