locked
Performance difference between SQL procedure and EF RRS feed

  • Question

  • Hi,

    My question is on general aspect on performance between EF and stored procedure.  I have implemented a procedure where I have 2 loops, nested.  The outer loop runs up to million times and the inner loop always runs for 260 times.  I couldn't yet figured out how to remove the queries out of loops.  So it is a procedural way of implementation and not much saving by running with database engine.

    Now, the outer loop can run up to million times and the only difference between runs is the Random number that the query uses.  And as I've mentioned I can not remove it from the loop.  So my question is if I run the outer loop using LINQ on the client side, I suppose the linq query will be run in memory, will it improve the performance?  This is important as it takes about 3 hours to run it around million cycles.

    Thanks

    Thursday, June 12, 2014 10:08 PM

Answers

  • Assuming that there are database queries in the innermost loop, it is unlikely that a procedural looping implementation will perform better in client code than in TSQL.

    >And as I've mentioned I can not remove it from the loop. 

    You should get some help with that and transform the process to set-based TSQL instead.  If you can post your tables and code in the TSQL forum http://social.msdn.microsoft.com/Forums/en-US/home?forum=transactsql, someone will probably help you.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Friday, June 13, 2014 4:16 PM
  • Entity Framework (ORM) or any ORM is not suited for long running jobs, because of the overhead of using an ORM such as object materialization. You are belter off just using straight up T-SQL in-line or through a sproc using ADO.NET and SQL Command objects. 

    One thing you can do is use Resharper, let Reshaper look at the foreach loop code and refactor it if possible. It will probably use a better constructed Linq statement if your data are objects in a collection that is queryable, which might make the execution faster. Who knows? 

    You can install the full version of Resharper and use it for 30 days on a trial basis.

    • Proposed as answer by Magnus (MM8)MVP Tuesday, June 17, 2014 9:04 AM
    • Marked as answer by Fred Bao Monday, June 23, 2014 8:34 AM
    Thursday, June 12, 2014 10:34 PM

All replies

  • Entity Framework (ORM) or any ORM is not suited for long running jobs, because of the overhead of using an ORM such as object materialization. You are belter off just using straight up T-SQL in-line or through a sproc using ADO.NET and SQL Command objects. 

    One thing you can do is use Resharper, let Reshaper look at the foreach loop code and refactor it if possible. It will probably use a better constructed Linq statement if your data are objects in a collection that is queryable, which might make the execution faster. Who knows? 

    You can install the full version of Resharper and use it for 30 days on a trial basis.

    • Proposed as answer by Magnus (MM8)MVP Tuesday, June 17, 2014 9:04 AM
    • Marked as answer by Fred Bao Monday, June 23, 2014 8:34 AM
    Thursday, June 12, 2014 10:34 PM
  • Thank you.  Well, the idea came to me after my manager had a meeting with our outsourcing company, who develop some applications in .net for us.  And they mentioned that this particular query will be much faster performed in .net using Entity Framework.

    On slightly different point, I've just installed SQL Server 2014 Express on my local machine.  I have to say that the performance increase is quite noticeable, about 5 times faster. 

    Thanks

    Friday, June 13, 2014 11:29 AM
  • Assuming that there are database queries in the innermost loop, it is unlikely that a procedural looping implementation will perform better in client code than in TSQL.

    >And as I've mentioned I can not remove it from the loop. 

    You should get some help with that and transform the process to set-based TSQL instead.  If you can post your tables and code in the TSQL forum http://social.msdn.microsoft.com/Forums/en-US/home?forum=transactsql, someone will probably help you.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Friday, June 13, 2014 4:16 PM
  • Thanks,

    Eventually I've managed to remove the nested looping in my SQL procedure which boosted the performance quite dramatically. 

    Thanks for your suggestions

    Tuesday, June 17, 2014 8:28 AM