We need to stop using Stored Procedures
2 days ago
- Stored procedures from applications are unnecessary and often cause more problems than they solve, as they offer no performance benefit over parameterized queries.
- Plan caching and parameterized queries, often cited as sproc benefits, can be achieved with regular SQL, so sprocs add no special value.
- Using sprocs separates application and database code versions, leading to migration headaches, rollback difficulties, and potential out-of-sync issues.
- Application developers should own their database queries to keep them versioned with app logic, enabling easier rollbacks and updates.
- Best practices for app-DB interaction include minimizing network requests, using indexes (including covered and filtered), and being aware of ORM pitfalls like N+1 queries and Cartesian explosions.
- Dynamic queries with varying parameters can monopolize CPU; TVPs (table-valued parameters) can help reuse query plans.
- Be careful with type conversions in parameterized queries to avoid implicit conversions that slow down performance.
- Filtered indexes are useful for narrow queries and should be used with hardcoded values, not parameters.
- Overall, sprocs should be the exception, used only for specific security needs, not as a default, and developers should focus on learning SQL and profiling.