Why execute stored procedures is faster than SQL query from a script?

by

SQL Server basically goes through these steps to execute any query (stored procedure call or ad-hoc SQL statement):

1) syntactically check the query
2) if it’s okay – it checks the plan cache to see if it already has an execution plan for that query
3) if there is an execution plan – that plan is (re-)used and the query executed
4) if there is no plan yet, an execution plan is determined
5) that plan is stored into the plan cache for later reuse
6) the query is executed

The point is: ad-hoc SQL and stored procedures are treatly no differently.

If an ad-hoc SQL query is properly using parameters – as it should anyway, to prevent SQL injection attacks – its performance characteristics are no different and most definitely no worse than executing a stored procedure.

Stored procedure have other benefits (no need to grant users direct table access, for instance), but in terms of performance, using properly parametrized ad-hoc SQL queries is just as efficient as using stored procedures.

Update: using stored procedures over non-parametrized queries is better for two main reasons:

  • since each non-parametrized query is a new, different query to SQL Server, it has to go through all the steps of determining the execution plan, for each query (thus wasting time – and also wasting plan cache space, since storing the execution plan into plan cache doesn’t really help in the end, since that particular query will probably not be executed again)

  • non-parametrized queries are at risk of SQL injection attack and should be avoided at all costs

More Related Contents:

  1. Insert results of a stored procedure into a temporary table
  2. Function vs. Stored Procedure in SQL Server
  3. SQL Server Insert if not exists
  4. SQL Server – How to lock a table until a stored procedure finishes
  5. Stored procedure or function expects parameter which is not supplied
  6. What is a stored procedure?
  7. Search text in stored procedure in SQL Server
  8. SQL Call Stored Procedure for each Row without using a cursor
  9. SQL Server SELECT into existing table
  10. How do I execute a stored procedure once for each row returned by query?
  11. How to check if a stored procedure exists before creating it
  12. Execute Stored Procedure from a Function
  13. SQL: Select dynamic column name based on variable
  14. How to return the output of stored procedure into a variable in sql server
  15. SQL Server silently truncates varchar’s in stored procedures
  16. How do I pass a list as a parameter in a stored procedure?
  17. Stored procedure that exports data into csv files only exports to one file
  18. Custom Date/Time formatting in SQL Server
  19. Could not find stored procedure ‘dbo.aspnet_CheckSchemaVersion’
  20. Insert Update stored proc on SQL Server
  21. SQL Server: should I use information_schema tables over sys tables?
  22. Inserting rows into a table with one IDENTITY column only [duplicate]
  23. UNION the results of multiple stored procedures
  24. How to pass a temp table as a parameter into a separate stored procedure
  25. Eliminate and reduce overlapping date ranges
  26. Functions vs Stored Procedures
  27. How to execute a stored procedure inside a select query
  28. Dynamic order direction
  29. How do I execute a MS SQL Server stored procedure in java/jsp, returning table data?
  30. Call stored procedure with table-valued parameter from java

Leave a Comment