Stored Procs vs Dynamic SQL

In DeKlarit's booth at TechEd I had to talk more than I wanted about the famous Stored Procs vs Dynamic SQL debate.

People still thinks Stored Procedures are faster, even if there is much evidence that shows otherwise. Fortunately, when they go and ask the LinQ for SQL/Entities team they get the same answer than they get from me. They are not.

To add more evidence, a couple of weeks ago I was in Redmond in a meeting with the ADO.NET team, and a Tech Lead from that team wrote the code below and said 'this is the fastest way to execute this SQL sentence with the .NET framework today'.

SqlCommand cmd = sqlConnection.CreateCommand();
cmd.CommandText = @"
       SELECT Sales PersonId, FirstName, HireDate
       FROM SalesPerson as sp
       INNER JOIN Employee AS e ON sp.SalesPersonID = e.EmployeeID
       INNER JOIN Contact AS c ON e.EmployeeID = c.ContactID
       WHERE e.HireDate < @date";
cmd.Parameters.AddWithValue("@date", date");

DbDataReader r = cmd.ExecuteReader();

(if it looks similar to the query shown here is just a coincidence ;) )

Why is this _faster_ than a stored procedure? Because the SQL Server engine could select a better execution plan depending on the value of the @date parameter.

Even in that room there was people that were surprised.

 


 

   

14 Comments

  • It was nice running into you @ TechED, and the demo of Deklarit was simply fantastic.

    I wish the development world gets off the stored procedure crack - couldn't agree with you more.

  • You know the dynamic sql vs stored proc thing is pretty tired.

    In the end, usage of stored procs is more about maintaining a consistent level of data access across applications accessing your data. Stored procs simplify the management of these scenarios, otherwise you're placing granular permissions on every column of every table and that's going to be a major headache to manage.

    For smaller databases, I'm sure that's ok, but in an environment of scale, that's not going to cut it.

    I guess what I'm trying to say is that the speed of this or that is a lesser concern over the generic management of how applications can and will interact with that data, something stored procedures can and do supply.

  • I wish people would stop confusing Stored Procs and Dynamic SQL. Stored Procedures can contain Dyamic SQL. SQL that is crafted and executed outside a Stored Procedure is actually called Dirty SQL.

    For quick and dirty apps, Dirty SQL can be the way to go.

  • I agree with the comments posted by Jon. If you have a lot of stored procs I don't want to have some that perform several miliseconds faster than others in code and the others at the DBMS level. This is a nightmare for maintenance.

    Another thing is that in most companies we use Data Access Layer generators which simplifies life.

    Also in the given example it is not necessarely true that the dynamic sql will outperform the same stored procedure because it all depends on the distribution of the data and the selectivity. That dynamic sql might running faster this week but will it still run faster than the equivalent stored proc next week?

    Still i fight the management nightmare the biggest issue for not mixing dynamic sql with stored procs.

  • Dave_T, thank you for confusing everyone and their moms.

  • If the SQL is generated by a code generator or SQL generation runtime then there is no maintenance problems. Actually, you don't need to maintain the stored procs or any SQL code at all.

    That's the context of most of the Dynamic SQL vs Stored Procs debate, and that's why LinQ over databases will probably make stored procs obsolete for single line SQL statements in the mid term.

    Stored procs still have a place for data access intensive batch procedures, and for complex sql statements that you need to hand tune, but you'll resort to them just as an optimization technique.

  • Gabriel,

    With the stored proc the plan is always the same. With dynamic SQL the plan can change depending on the parameter values. So, if SQL does the job well, it will generate a better execution plan with dynamic SQL.

    So, it's actually the opposite of what you say. With stored procs the plan can be good today and bad tomorrow because it does not take into account how the data is distributed because it does not know the parameter values. With dynamic SQL it can be good each day.

  • I am by far no SQL Server expert but I know for sure that the execution plan for stored procedures are not forever.

    First of all the execution plan is persisted in volatile memory and it is quite possible that it will be deleted from memory causing the SQL Server to recompile the stored proc.

    Also when certain conditions are met the stored procedure will be recompiled as well. Two of these conditions I was referring to:

    1. Everytime the new distribution statistics is generated
    2. And when a lot of data modifications is done on a table referenced by a stored procedure

    Dynamic SQL comes with a lof of overhead (parsing, optimalisations on server-side) therefore I do not believe that dynamic SQL will always outperform the stored proc.

    Also not to forget that stored procs are not only used for performance, security considerations can play a role too and also if the database is a multi-application database or not.

    By the way if you don't want an execution plan to be persisted for your stored procedure in memory you can always use the WITH RECOMPILE statement of the CREATE PROCEDURE DDL statement.

  • Oh yeah I forgot to mention that the quote "this is the fastest way to execute this SQL sentence with the .NET framework today" with the emphasis put on "with the .NET Framework" should not be neglected as the guy who is being quoted does not say that dynamic SQL outperforms stored procs he is only stating that calling the dynamic SQL over calling a stored procedure is faster in the .NET Framework.

  • Is not that dynamic SQL will cause that the plan will be calculated in each execution. The plan will be cached. But the first time it calculates it, it will use the parameter value to figure it out.

  • I use dynamic SQL inside stored procedures to build stored procedures that also use Dynamic SQL - multi generation stuff using Information_schema views to pull in the names of components. It has the advantage that if a database structure is changed the entire suite of built sps can be rebuilt and they automatically conform to the new data astructure. So structure changes are a breeze. Change the structure - run one sp that takes about 2 seconds to run and the rest all works like magic. The main generator creates triggers, sps, tables, whatever is needed.
    I can usually build a generator faster than what it takes to manaully contruct all the sps required for a typical database. And the generator is re-usable across databases. Once the tables are created with foreign keys the generator sp is cut and pasted into place and run and voila 2 seconds later all of our standard sps used to interface into the tables are there.

  • We all take this simple explanation "Because the SQL Server engine could select a better execution plan depending on the value of the parameter" as granted.

    Now, why can't SQL Server determine a stored procedure's execution plan only once it has its parameters? This would make stored procs faster since you don't have to send the full query but just a name to SQL Server.

    To me, this sounds like an SQL Server's bug. There should be an option on stored procs to say whether their execution plan should be computed when executed or beforehand.

  • Strange enough I had dozens and dozens of cases in SQL Server 7 to 2005 where I upgraded dynamic SQL to SPs and had the "promised" increase in performance. Never drastic, but measurable. Actually, when hitting heavy the performance was indeed also more consistent.
    When later I had to use EXECUTE inside SPs the performance drop was also measurable. I am talking about real enterprise applications and mass data (100-500 GB databases).

    I am no guru about statistics usage, but how could the value of the parameter make SQL server select a different index usage strategy? Or different join types? I could see reasons (drastic change in table size) when an old SP execution plan might be invalid, still this sounds very artificial to me.

    Then again, I could by lying about the performance.

    Stored Procedures are a requirement for my work for several issues, dynamic SQL is a NO GO pratice for years. It's not only about the performance thing (as I often have to use EXECUTE inside SPs) but rather about versioning, team development, debugging, profiling, centralized logging (DB-level), error handling, modulization and reusage... Heck, I even take them for this alone and would accept a 10% performance hit (which never occured to me indeed).

    I sometimes *feel* that people doing lectures, writing books, and also releasing enterprise platforms/frameworks have only little experience how to work in 20 people project that run 3-5 years. I've working in several project with Microsoft Consulting Services, they also required a 100% SP/Function usage.

  • Arnaud:
    "There should be an option on stored procs to say whether their execution plan should be computed when executed or beforehand."

    Well, there is still WITH RECOMPILE, I use it if I have several different outgoing result sets for my SPs.

Comments have been disabled for this content.