locked
Performance question about Linq queries vs stored procs called by Linq RRS feed

  • Question

  • User625999351 posted

    Theoretically, is it realistic to expect a major performance improvement if a complex Linq query is replaced by a Linq call to a stored procedure? 

    Is such an arrangement inherently superior performance-wise to conventional Linq queries off a DBMS?

    I ask because I have an app with some fairly complex Linq queries and there have been some complaints about performance.  I was considering a switch.

    Monday, December 12, 2016 7:56 PM

Answers

  • User-821857111 posted

    Theoretically, is it realistic to expect a major performance improvement if a complex Linq query is replaced by a Linq call to a stored procedure? 
    Quite often, this is the case. That's why support for stored procedures is included within the framework. I usually run more complex Linq-generated queries through a profiler for this reason.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 12, 2016 9:03 PM

All replies

  • User753101303 posted

    Hi,

    You can expect this but it is still possible that  your Linq query could be better written (loading related data that are not actually used, using too many includes, missing db side indexes etc...) or even that the problem is unrelated (for example it happens to see someone that tries to render a huge HTML page wiht thousands of rows).

    You could likely use query.ToString() to have a look at the underlying SQL query and see how it performs in SSMS. You could also have a look at SQL Server profiler or management views to get some data about the query causing the issue.

    Else this is just a guess, you can guess right most of the time but it can be also time consuming to do a change and realize later that the problem was caused by something else (also it can help to do the rioght thing from the start the next time you are in the same situation).

    Monday, December 12, 2016 8:39 PM
  • User-821857111 posted

    Theoretically, is it realistic to expect a major performance improvement if a complex Linq query is replaced by a Linq call to a stored procedure? 
    Quite often, this is the case. That's why support for stored procedures is included within the framework. I usually run more complex Linq-generated queries through a profiler for this reason.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 12, 2016 9:03 PM