principleMajor
Efficency of stored procedures vs raw queries
Viewed 0 times
storedproceduresefficencyrawqueries
Problem
I have read much on both sides of this debate: is there a signficant performance gain to be had by using only stored procedures over raw queries? I am specifically interested in SQL Server but would be interested in any and all databases.
Solution
It is less so in SQL Server 2008 and higher, but it is still there. What it comes down to is the execution plan cache and SQL Server being able to auto-parametrize queries which are sent in. When using stored procedures (that don't have dynamic SQL within them) the queries are already parametrized so SQL Server doesn't need to generate a plan for each query when it's run as the plans are already stored in the plan cache.
And don't forget about the security issues (dynamic SQL, minimum permissions, etc.) that go away when using stored procedures.
When the app is using dynamic SQL against the base tables to select, insert, update and delete the data in the tables the application needs to have rights to all those objects directly. So if someone uses SQL Injection to get onto the server they will have rights to query, change or delete all the data in those tables.
If you are using stored procedures they only have the rights to execute the stored procedures getting back just the information which the stored procedure would return. Instead of issuing a quick delete statement and blowing everything away they would need to figure out what procedures can be used to delete data then figure out how to use the procedure to do so.
Given that SQL Injection is the easiest way to break into a database, this is kind of important.
And don't forget about the security issues (dynamic SQL, minimum permissions, etc.) that go away when using stored procedures.
When the app is using dynamic SQL against the base tables to select, insert, update and delete the data in the tables the application needs to have rights to all those objects directly. So if someone uses SQL Injection to get onto the server they will have rights to query, change or delete all the data in those tables.
If you are using stored procedures they only have the rights to execute the stored procedures getting back just the information which the stored procedure would return. Instead of issuing a quick delete statement and blowing everything away they would need to figure out what procedures can be used to delete data then figure out how to use the procedure to do so.
Given that SQL Injection is the easiest way to break into a database, this is kind of important.
Context
StackExchange Database Administrators Q#4706, answer score: 33
Revisions (0)
No revisions yet.