locked
Stored Procedures vs. Dynamic SQL RRS feed

  • Question

  • why SP's are technically faster than dynamic SQL?
    Sunday, September 27, 2009 9:55 PM

Answers

  • A brief answer: Stored Procedures store (and reuse) their execution plans, dynamic SQL does not.  An execution plan tells the database engine what to do, if SQL Server does not have to generate this execution is quicker.

    • Marked as answer by Hooray Hu Monday, September 28, 2009 1:49 AM
    Sunday, September 27, 2009 11:15 PM
  • Search "stored procedure sql statement faster" on http://www.msdn.com and the first result contains the answer to the question:

    They allow faster execution.
    If the operation requires a large amount of Transact-SQL code or is performed repetitively, stored procedures can be faster than batches of Transact-SQL code. They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use resulting in much faster execution times.

    Thanks,
    Hooray
    • Marked as answer by Hooray Hu Monday, September 28, 2009 1:49 AM
    Monday, September 28, 2009 1:49 AM

All replies

  • A brief answer: Stored Procedures store (and reuse) their execution plans, dynamic SQL does not.  An execution plan tells the database engine what to do, if SQL Server does not have to generate this execution is quicker.

    • Marked as answer by Hooray Hu Monday, September 28, 2009 1:49 AM
    Sunday, September 27, 2009 11:15 PM
  • Thanks RobOz82, But I want to know more about it. So please help me...
    Monday, September 28, 2009 1:46 AM
  • Search "stored procedure sql statement faster" on http://www.msdn.com and the first result contains the answer to the question:

    They allow faster execution.
    If the operation requires a large amount of Transact-SQL code or is performed repetitively, stored procedures can be faster than batches of Transact-SQL code. They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use resulting in much faster execution times.

    Thanks,
    Hooray
    • Marked as answer by Hooray Hu Monday, September 28, 2009 1:49 AM
    Monday, September 28, 2009 1:49 AM