Ad hoc queries vs stored procedures vs Dynamic SQL. Can anyone say pros and cons?
Stored procedures are simply a group of statements that perform some functions on the database. These functions can be insertion, updating, selecting or deleting rows on one or more database tables. Ad hoc queries on the other hand serves the same purpose as stored procedures with one big difference.
Advantages of Stored Procedures This can save a lot of time writing, especially if you are doing ad-hoc queries. This also gives you a slight performance edge on the first execution of a stored procedure over the first execution of an ad-hoc query. A stored procedure is ultimately a function call on a database.
Stored Procedures outperform dynamic SQL in almost all aspects. They are faster, secure, and easy to maintain, and require less network traffic. As a rule of thumb, stored procedures should be used in scenarios where you don't have to modify your queries and your queries are not very complex.
Ad hoc queries are single questions or requests for a database written in SQL or another query language by the user on-demand--typically when the user needs information outside of regular reporting or predefined queries.
Stored Procedures
Dynamic SQL (i.e. uses exec command within a stored procedure)
Ad Hoc SQL (i.e. created in your business code)
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.
Stored procedures PROs:
Stored procedures CONs:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With