26 performance tuning questions and solutions, ad hoc sql vs stored procedures, Determine when to use stored procedures vs. SQL in the code, differences between raw sql and stored procedure, dynamic sql vs stored procedures performance, Efficency of stored procedures vs raw queries, embedded sql vs stored procedures, How to tune SQL queries, Interview questions for SQL Server Performance Tuning, Looking for SQL Optimization Interview Questions, performance sql server, Performance tips for faster SQL queries, Performance Tuning, Performance Tuning for SQL Server, Query Optimization, Query Performance Tuning, Raw SQL, Raw SQL Vs Stored Procedures, SQL Complex Queries, SQL Optimization Interview Questions, sql performance, sql performance and tuning, sql performance explained pdf, sql performance tips, SQL Performance Tuning, sql performance tuning and optimization, sql performance tuning interview questions, sql performance tuning tips, SQL Query Optimizer, SQL Query Tuning or Query Optimization, SQL raw queries versus stored proceedures, SQL SERVER Interview questions, SQL server optimization interview questions and answers, sql server performance query, sql server performance slow, SQL Server Performance Tuning, SQL Server Performance Tuning Tips, SQL SERVER Tips, SQL Tuning Overview, Stored Procedures–vs- Embedded Queries, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases, When is it better to write “ad hoc sql” vs stored procedures, When is it better to write “ad hoc sql” vs stored procedures [duplicate]
Ad-hoc / Raw SQL Vs Stored Procedures?
Let’s go through some of the differences given below-
Compile – Well Ad-hoc SQL will be compiled every time we pass the query to the optimizer. But in case of stored procedures execution plan will be cached. One more point here is to we may need to recompile stored procedure from time to time. In any we have added indexes or statistics are updated then SQL server will recompile the stored procedure.
Please note that we have two types of recompilation – Statement level recompilation and procedure level recompilation.
Tuning and Future Usage in Data Retrieval Cases – Mostly I used to write stored procedures because they are easier for us to test and fine tune. If you want to change the stored procedure in future it is easy. You can just change it and deploy on the server required and test the application.
You don’t need to open the application, change the query and deploy it and after that you can test it. You can save lot of time in this case.
DBAs also like SPs as they can easily optimize stored procedures.
Future Usage in column addition cases – Now if you have an insert query and you wanted to add a new column to the insert statement then in stored procedure cases you have to change the SP as well as a change in required in the application tier to pass an extra value. So in these scenarios Ad-hoc will be better as you just have to change at one place.
Complex Operations / Performance – If you are performing some complex operations stored procedures are better as it will be very difficult to write complex operation at application tier as you have to use lot of concatenation for variables and other things.
In case of performance also SP are better. Add complex JOINS, couple hundreds of thousands records, and you will see SP advantage. Stored procedure by virtue of being compiled resolves all bindings at compile time (i.e. it won’t compile if you misspelled a table name) with in-line query, you find this only after compiling application and running test.
Security / SQL Injections – Stored procedures can be used to prevent SQL Injection attack, though they do not do it automatically. For example case of implicit conversion between data types: a stored procedure would allow for examining the parameters before executing them; think of it as of another level of protection.
Also stored procedure’s logic resides on the server, so its source code cannot be as easily accessed as by de-compiling desktop application. Stored procedure inside SQL Server is a securable, so it adds granularity to security schema.
So, which one to use – SPs or ad-hoc SQL? The answer is “it depends.” Personally I have used SPs most of the time as it has more performance and maintainability benefits