none
Is LINQ to SQL horribly inefficient? RRS feed

  • Question

  • Hi,
    My team recently used LINQ to SQL for a project consisting of high traffic WPF services that interface w/ a MSSQL database. The project architect has since grown to hate LINQ and shudder at it's name because he believes that repeatedly compiling the SQL statements rather than having precompiled procedures is extremely costly. I, on the other hand, love that I didn't have to build any data classes and haven't seen any problems with the system we built. How costly is compiling the SQL queries? He mentioned something about the compilation involving mapping out joins ahead of time? 
    Friday, January 15, 2010 5:39 PM

Answers

  • As always, it is hard to make general yet meaningful performance claims, because the relative cost of doing certain operations depends on your application. Obviously there is going to be some overhead using LINQ instead of raw SQL, but is it enough to be noticeable in your app? Without measuring your app to see if LINQ is causing bottlenecks, about the most general advice I can give you is to read Rico Mariani's series of posts on LINQ performance (be wary of the numbers - some posts reference pre-.NET 3.5 RTM code bases) starting here: http://blogs.msdn.com/ricom/archive/2007/06/22/dlinq-linq-to-sql-performance-part-1.aspx

    As for your specific situation, if you have queries that you repeat often, then you might be able to benefit from precompiling your queries. Rico talks about this in one of his posts, but you should also have a look at the MSDN documentation for storing and reusing queries using the CompiledQuery class.

    Hope that helps
    David

    Blog - http://blogs.rev-net.com/ddewinter/ Twitter - @ddewinter
    • Marked as answer by _xr280xr_ Tuesday, January 19, 2010 4:24 PM
    Friday, January 15, 2010 6:31 PM
    Answerer

All replies

  • As always, it is hard to make general yet meaningful performance claims, because the relative cost of doing certain operations depends on your application. Obviously there is going to be some overhead using LINQ instead of raw SQL, but is it enough to be noticeable in your app? Without measuring your app to see if LINQ is causing bottlenecks, about the most general advice I can give you is to read Rico Mariani's series of posts on LINQ performance (be wary of the numbers - some posts reference pre-.NET 3.5 RTM code bases) starting here: http://blogs.msdn.com/ricom/archive/2007/06/22/dlinq-linq-to-sql-performance-part-1.aspx

    As for your specific situation, if you have queries that you repeat often, then you might be able to benefit from precompiling your queries. Rico talks about this in one of his posts, but you should also have a look at the MSDN documentation for storing and reusing queries using the CompiledQuery class.

    Hope that helps
    David

    Blog - http://blogs.rev-net.com/ddewinter/ Twitter - @ddewinter
    • Marked as answer by _xr280xr_ Tuesday, January 19, 2010 4:24 PM
    Friday, January 15, 2010 6:31 PM
    Answerer
  • If he is worried about SQL Server repeatedly having to compile the TSQL then he shouldn't worry - the SQL is parameterised and will re-use the query plan (more so on .NET 4.0 where parameter sizes are fixed).

    If he is worried about the LINQ query compilation on the client it is possible to use CompiledQueries to mitigate this factor.

    [)amien
    Saturday, January 16, 2010 7:00 AM
    Moderator
  • Thanks to both of you. I didn't know you could programmatically create precompiled queries. So according to Rico's blog, it sounds like there is a significant amount of overhead when using LINQ queries; about 42% of the time in his example was compiling and "Jitting costs" (not sure exactly what fell under this). But it sounds like LINQ could definately be used for a medium scale project if used correctly. I think I will look more into how SQL Server compiles and executes SQL to get a better idea of the additional cost. If you have a good link explaining the process I would appreciate it. Thanks
    Tuesday, January 19, 2010 4:23 PM
  • Here's another good blog post that seems to support what I have experienced:

    http://peterkellner.net/2009/05/06/linq-to-sql-slow-performance-compilequery-critical

    Friday, August 27, 2010 2:07 PM