none
Problem Entity Framework doesn't use sp_executesql to execute in .Net 4.0? RRS feed

  • Question

  • I using Entity Framework in .net 4.0.  I try to using SQL Profiler to monitor sql statement from Entity Framework executed that I saw all sql statement that it doesn't use sp_executesql to execute.   But LinqToSql will use sp_executesql to execute all sql statement.  For my knowledge, sp_executesql will use cached plan to get more performance.  My question is why Entity Framework doesn't use sp_executesql to execute? 


    Eagle
    Tuesday, June 29, 2010 3:48 PM

Answers

  • Hi Eagle,

     

    For such a LINQ to Entities query, a similar LINQ to SQL query won’t use sp_executesql either.  

     

    The use of sp_executesql is actually the only way to execute a parameterized dynamic SQL statement in SQL Server.   There are two primary “execute” primitives in TDS, “batch” and “RPC”. A batch request just contains a T-SQL string, and can be used whenever there is no need for parameters.  An RPC request is a request that takes a procedure name (no arbitrary SQL) and a set of parameters. This can be used for example to execute a stored-procedure directly. None of these cover executing a parameterized SQL statement. In order to do that SQL Server has sp_executesql, which is a system stored-procedure that takes a SQL string with parameter markers and a variable number of arguments for the parameters. So the client issues an RPC request to sp_executesql whenever it has to execute a parameterized statement.

     

    Such a query won’t use sp_executesql:

    ==============================================================

                    var cust = from c in ctx.Customers
                               where c.CustomerID == "ALFKI"
                               select c;

    ==============================================================

     

    However, such a query does use sp_executesql to pass a parameter:

    ==============================================================

                    string CustID = "ALFKI";
                    var cust = from c in ctx.Customers
                               where c.CustomerID == CustID
                               select c;

    ==============================================================

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, July 1, 2010 5:38 AM
    Moderator
  • Hi Eagle,

     

    Thanks for your following up!

     

    As I said, sp_executesql is used to execute a parameterized dynamic SQL statement.  For non-parameterized queries, we don’t need to use sp_executesql.   That’s orthogonal to whether the query is submitted directly in a batch or wrapped in sp_executesql.  In either case we should generally use parameters to promote plan reuse, and auto-parameterization or forced parameterization might replace a non-parameterized query with a parameterized one, http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/11/4-0-query-parameterization.aspx.

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, July 2, 2010 8:06 AM
    Moderator

All replies

  • No any Entity Framework professional or MS professional here?
    Eagle
    Wednesday, June 30, 2010 1:34 AM
  • Hello Eagle,

     

    Welcome to EF forum!

     

    Could you please provide us with more detailed information about the queries you are testing in both EF and LINQ to SQL?   Based on my test, EF4 will also use sp_executesql to execute certain SQL statements as LINQ to SQL does.   Also, LINQ to SQL does not use sp_executesql for every SQL statements.  

     

    Besides, EF has its own query plan caching in Entity SQL, http://msdn.microsoft.com/en-us/library/bb738562.aspx.  

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, June 30, 2010 2:59 AM
    Moderator
  • Hi Lingzhi,

     

    What information you need?


    Eagle
    Wednesday, June 30, 2010 8:57 AM
  • Hi Eagle,

     

    Like what kind of queries you are testing, please.   Some sample codes would be great. 

     

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, June 30, 2010 9:09 AM
    Moderator
  • WMSEntities db = new WMSEntities();

     var x = from zone in db.Zone
                 select zone;

    var test = x.ToArray();

    Eagle
    Wednesday, June 30, 2010 9:17 AM
  • Hi Eagle,

     

    For such a LINQ to Entities query, a similar LINQ to SQL query won’t use sp_executesql either.  

     

    The use of sp_executesql is actually the only way to execute a parameterized dynamic SQL statement in SQL Server.   There are two primary “execute” primitives in TDS, “batch” and “RPC”. A batch request just contains a T-SQL string, and can be used whenever there is no need for parameters.  An RPC request is a request that takes a procedure name (no arbitrary SQL) and a set of parameters. This can be used for example to execute a stored-procedure directly. None of these cover executing a parameterized SQL statement. In order to do that SQL Server has sp_executesql, which is a system stored-procedure that takes a SQL string with parameter markers and a variable number of arguments for the parameters. So the client issues an RPC request to sp_executesql whenever it has to execute a parameterized statement.

     

    Such a query won’t use sp_executesql:

    ==============================================================

                    var cust = from c in ctx.Customers
                               where c.CustomerID == "ALFKI"
                               select c;

    ==============================================================

     

    However, such a query does use sp_executesql to pass a parameter:

    ==============================================================

                    string CustID = "ALFKI";
                    var cust = from c in ctx.Customers
                               where c.CustomerID == CustID
                               select c;

    ==============================================================

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, July 1, 2010 5:38 AM
    Moderator
  • Why doesn't use sp_executesql to execute all sql statement?  Why is selective? What benefit in this behavior?
    Eagle
    Friday, July 2, 2010 2:36 AM
  • Hi Eagle,

     

    Thanks for your following up!

     

    As I said, sp_executesql is used to execute a parameterized dynamic SQL statement.  For non-parameterized queries, we don’t need to use sp_executesql.   That’s orthogonal to whether the query is submitted directly in a batch or wrapped in sp_executesql.  In either case we should generally use parameters to promote plan reuse, and auto-parameterization or forced parameterization might replace a non-parameterized query with a parameterized one, http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/11/4-0-query-parameterization.aspx.

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, July 2, 2010 8:06 AM
    Moderator
  • Hi Eagle,

     

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, July 9, 2010 7:30 AM
    Moderator