April 2, 2014 at 9:43 am
I've run into issues in the past dealing with extremely poorly written queries with LINQ. I've been under the impression for awhile that LINQ is fine for very simple queries but when writing anything complex, it should be done using a stored proc. Some developers disagree with that thought and think LINQ is fine for everything. Anyone have any thoughts?
April 2, 2014 at 10:35 am
JoshDBGuy (4/2/2014)
I've run into issues in the past dealing with extremely poorly written queries with LINQ. I've been under the impression for awhile that LINQ is fine for very simple queries but when writing anything complex, it should be done using a stored proc. Some developers disagree with that thought and think LINQ is fine for everything. Anyone have any thoughts?
LINQ, like any other automated code writing ORM will produce absolutely horrid queries. It also will generate queries to populate every single column from the table even if they are not needed. There is no lazy load of properties or that sort of thing. It also requires a code recompile when the table changes.
I have no issue with LINQ and use it for some things, but I would steer clear of automated sql unless performance is not an issue for the end users and I have never seen that happen.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 2, 2014 at 2:17 pm
Sean Lange (4/2/2014)
JoshDBGuy (4/2/2014)
I've run into issues in the past dealing with extremely poorly written queries with LINQ.
Ditto 😎
Using stored procedures is a good practice, a) how it retrieves the results is under your control ( you know the database), b) it can be changed as far as the results are consistent, c) tends to perform better, d) more manageable in terms of security.
April 3, 2014 at 11:02 am
JoshDBGuy (4/2/2014)
I've run into issues in the past dealing with extremely poorly written queries with LINQ. I've been under the impression for awhile that LINQ is fine for very simple queries but when writing anything complex, it should be done using a stored proc. Some developers disagree with that thought and think LINQ is fine for everything. Anyone have any thoughts?
True. Keep the usage of LINQ to minimum. Developers like it as it make their lives easy.
But when it comes to performance try to use SPs as much as possible.
--
SQLBuddy
April 7, 2014 at 2:02 pm
From my experience, it's how you write the LINQ query that makes the biggest difference. Whether you are using lambda expressions or writing from selects, etc... I have been able to write some pretty sophisticated logic using LINQ and had the query turn out exactly how I would expect if I were to write it in T-SQL. Also the blanket statement that stored procedures are better isn't necessarily the case. You have the potential pitfalls of parameter sniffing and dealing with the potential for multiple execution plans depending on what execution settings are used. From what I've seen dynamic sql executed with sp_executesql can perform better in many cases than just utilizing a stored procedure. Especially if the stored procedure needed many parameters causing SQL to create a bad execution plan, where as dynamic queries executed with sp_executesql can generate the the query specific plan. Remember there is hardly ever a single solution for everything. Do your own research and make sure whatever you choose is right for your situation.
April 7, 2014 at 2:22 pm
john.deprato (4/7/2014)
From my experience, it's how you write the LINQ query that makes the biggest difference. Whether you are using lambda expressions or writing from selects, etc... I have been able to write some pretty sophisticated logic using LINQ and had the query turn out exactly how I would expect if I were to write it in T-SQL. Also the blanket statement that stored procedures are better isn't necessarily the case. You have the potential pitfalls of parameter sniffing and dealing with the potential for multiple execution plans depending on what execution settings are used. From what I've seen dynamic sql executed with sp_executesql can perform better in many cases than just utilizing a stored procedure. Especially if the stored procedure needed many parameters causing SQL to create a bad execution plan, where as dynamic queries executed with sp_executesql can generate the the query specific plan. Remember there is hardly ever a single solution for everything. Do your own research and make sure whatever you choose is right for your situation.
As long as you are parameterizing you dynamic sql it can perform quite well. I don't think anyone here suggested you should always use stored procs or anything like that. Certainly not from me. As with everything in sql server "it depends". There are valid cases for just about everything that language has to offer.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 7, 2014 at 4:38 pm
john.deprato (4/7/2014)
From my experience, it's how you write the LINQ query that makes the biggest difference. Whether you are using lambda expressions or writing from selects, etc... I have been able to write some pretty sophisticated logic using LINQ and had the query turn out exactly how I would expect if I were to write it in T-SQL. Also the blanket statement that stored procedures are better isn't necessarily the case. You have the potential pitfalls of parameter sniffing and dealing with the potential for multiple execution plans depending on what execution settings are used. From what I've seen dynamic sql executed with sp_executesql can perform better in many cases than just utilizing a stored procedure. Especially if the stored procedure needed many parameters causing SQL to create a bad execution plan, where as dynamic queries executed with sp_executesql can generate the the query specific plan. Remember there is hardly ever a single solution for everything. Do your own research and make sure whatever you choose is right for your situation.
You have those same pitfalls with anything capable of reusing an execution plan. Keep in mind that parameter sniffing is normally a good thing.
Shifting gears a bit, how did you check your embedded code for potential scalability problems? Do you always use parameters to avoid SQL Injection? If the code slows down, who's going to troubleshoot and repair it? When it needs a change, who's going to do all of that again?
I'm certainly not one to say that you have to use stored procedures for everything. Are you saying to not use stored procedures for anything?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 7, 2014 at 6:57 pm
Not saying that at all. We use stored procedures when it makes sense. I was just making sure that no one was implying they were the only way to solve whatever problem you are working on. Parameter sniffing may have it's advantages, I've just seen it cause problems that you just need to be aware of. I'm just trying to make sure all tools are available from the tool box and it appeared that some were just making broad statements that really should be addressed on a case by case basis. 😀
April 7, 2014 at 8:09 pm
john.deprato (4/7/2014)
Not saying that at all. We use stored procedures when it makes sense. I was just making sure that no one was implying they were the only way to solve whatever problem you are working on. Parameter sniffing may have it's advantages, I've just seen it cause problems that you just need to be aware of. I'm just trying to make sure all tools are available from the tool box and it appeared that some were just making broad statements that really should be addressed on a case by case basis. 😀
In that case, I agree. "It Depends". 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
April 16, 2014 at 6:54 am
I've been working with Linq-generated SQL for a while and have definitely had similar experiences. When the queries are straight-forward selects on a group of tables with simple joins, then you'll generally get adequate performance.
If you're working with more complex business objects where you're looking at many tables and/or views, then the nesting and joining of objects can become inefficiently large and complex.
A big weakness of many ORMs is their propensity to SELECT * from any entities they query, which can be a huge performance hit for SQL Server as it will always be retrieving far more data than it needs.
I would definitely recommend creating views for simple sets of joins that will be relatively stable, and stored procedures for more complex business logic. This is your only way to gain control of the SQL you are working with to ensure performance is adequate.
One last note on Linq---if a query is not parameterized, then it will be more difficult to track performance as each query that is executed will likely have a different execution plan (unless they are identically written).
April 16, 2014 at 8:19 am
Ed Pollack (4/16/2014)
I've been working with Linq-generated SQL for a while and have definitely had similar experiences. When the queries are straight-forward selects on a group of tables with simple joins, then you'll generally get adequate performance.If you're working with more complex business objects where you're looking at many tables and/or views, then the nesting and joining of objects can become inefficiently large and complex.
A big weakness of many ORMs is their propensity to SELECT * from any entities they query, which can be a huge performance hit for SQL Server as it will always be retrieving far more data than it needs.
I would definitely recommend creating views for simple sets of joins that will be relatively stable, and stored procedures for more complex business logic. This is your only way to gain control of the SQL you are working with to ensure performance is adequate.
One last note on Linq---if a query is not parameterized, then it will be more difficult to track performance as each query that is executed will likely have a different execution plan (unless they are identically written).
With Telerik they do give you the option to profile the objects in memory using their profiling tool which can be helpful.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply