THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andrew Kelly

LINQ to the rescue

I heard someone state that LINQ (see http://www.sqlmag.com/Article/ArticleID/48759/sql_server_48759.html for details on LINQ) was going to rescue developers from having to know TSQL. Well if that's true then who will rescue us from LINQ?  LINQ like so many other technologies that have come about makes certain things easier by abstracting the underlying objects or code and providing a "simpler" interface. That sounds great but as we have learned over and over again this comes at a price. The price here will ultimately be performance in the database. LINQ will allow you to write an English like statement that will be translated into TSQL and sent to the database as essentially an adhoc sql statement. So again instead of steering developers towards writing efficient and reusable stored procedures we give them the exact opposite. We will end up with code that has little chance of being optimized properly and few chances of getting plan reuse. As a consultant specializing in scalability and performance I can tell you that the number one culprit out there in this regard is exactly these types of applications. Ones in which the developers are coerced or even forced into using adhoc code due to time constraints or technologies such as LINQ. Now don't get me wrong I am not blaming these issues on developers, I used to be one:). They just happen to be the ones creating the code for the database these days. Most DBA's if they had their way would prefer to use stored procedures first. Only time will tell but I expect my job as a performance consultant to heat up in the future once everyone starts using LINQ. You see most large apps started out small and performance isn't as much of an issue then. But they tend to grow very large these days and adhoc sql and poorly optimized queries just don't cut it in the long run. I hope to be proven wrong but history leads me to believe otherwise. Any way happy coding...
Published Thursday, September 06, 2007 12:22 PM by Andrew Kelly

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jamie Thomson said:

YES. Someone had the balls to say it!! :)

I thought exactly the same thing when I first heard about LINQ but when no-one else said it I thought I was the only one! I also don't do much perf tuning work so I decided not to speak out loud cos I didn't have much of a leg to stand on.

cheers Andrew.

-Jamie

September 6, 2007 11:57 AM
 

Denis Gobo said:

>>I hope to be proven wrong but history leads me to believe otherwise

Just look at twitter,they used RoR and got something up and running very fast

Then they became popular and had to rewrite all the code that made it so easy to create the app to begin with (active record etc etc)

If you start with procs (and have someone who knows how to write them) you will not fall as quickly into this septic hole

How do you check the execution plan for a proc? easy CTRL + K/M(depending on version) showplan text/io/time and there it is

When you have one of these apps first you have to figure out what code is executed so you have to run profiler, on a busy system it will slow down the system even more

Once you think you know what code it is how do you optimize it when the framework spits out the SQL code? So you have to start changing that and in the end you will have a complete rewrite

September 6, 2007 12:03 PM
 

LNBruno said:

Shhhh!  Wait 2 yrs. & we'll have another neat & cool that'll save the world...

Wait!  What's that?  RDMS's are dead?  Oh!  Columnize everyone!

Puh-leese!

September 6, 2007 10:20 PM
 

Fabian said:

Well, I'm a developer so I would like to expose the developers view on this issue.

One of the points brought up seems to be the fact that instead of writing a very efficient stored procedure that can be optimized, we write a query against the objects.

LINQ offers the ability to generate the SQL statement that the query you are writing will execute. With such query you can execute it in SQL and find out what the results are, if the query seems slow or poorly writen, you can always tweak the query agains your objects or ultimately use a stored procedure instead.

But for what I've seen the queries produced by LINQ are very efficient so if you don't have to do this for 90% of the queries you write, you have saved yourself time.

As far as the reusability goes, there are plenty of patterns and practices that are well documented and show you how to reuse this particular queries you are writing with code. So if you are a good developer, you will reuse this queries. Obviously if we are making the assumption that some one can reuse a stored procedure we can also make the assumption that we can reuse the query that was written in code. Same principle.

So LINQ is not the solution to all the problems, but it sure makes a few things better.

September 13, 2007 11:17 AM
 

Andrew Kelly said:

I think you validated my point pretty well since you missed the point entirely. You see I was not talking about reuse of the code itself but the compiled query plans that the queries ultimate generate inside the database.  The reuse of the plans are a key factor in how effecient and performant the database server will be. Adhoc queries are the # 1 offender when it comes to plan reuse. Again no offense to you or any other developers but this is typically not their primary concern. They have enough to worry about getting the app code in shape. While the queries that LINQ generates may be fine for small applications they will never support high volume, large scale applications in it's current form. And as I eluded to before too many apps are developed against a very small set of test data with very few concurrent users. The problem is that these days they tend to grow quickly and when you get hundreds or thousands of concurrent users with tens or hundreds of GB's of data it becomes a very different story.  

September 13, 2007 12:56 PM
 

Fabian said:

So what you are saying is that if you send the same query to SQL, SQL would not keep the plan around and it would generate the same plan over and over?

September 14, 2007 4:53 PM
 

Andrew Kelly said:

That depends. Adhoc sql with trivial plans are not kept and are never reused.  But most queries are not repeated exactly. SELECT * FROM Table WHERE ID = 1 is not the same as SELECT * FROM Table WHERE ID = 2.  This is a simplified example but essentially these two queries while they appear to be the same are not and will generally each get a different plan and not reuse the ones from before. SQL Server does have the ability to auto parameterize some of these so they can reuse the same plan. If LINQ does the right things they can take advantage of some of this capability. But as the query gets more complex this becomes more and more difficult for this to happen without manual intervention from the DBA. This KB goes into good detail on plan reuse.   http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

September 14, 2007 5:01 PM
 

noeld said:

BINGO!

September 17, 2007 2:59 PM
 

Kevin Boles said:

I for one am GRATEFUL for LINQ!!  As a consultant I see good times ahead cleaning up more performance issues created by the latest and greatest from Microsoft!  :-)

November 23, 2007 8:32 PM
 

Kevin Kline said:

There’s been a lot of positive press for LINQ, such as the article about LINQ by Mike Otey at http://www.sqlmag.com/Article/ArticleID/48759/sql_server_48759.html

December 23, 2008 4:16 PM
 

KKline said:

Great stuff, Andy.  I riffed on this at http://sqlblog.com/blogs/kevin_kline/archive/2008/12/23/linq-enabling-or-entangling.aspx.  

I'd use a Pingback, if I knew how to make one.  <grin>

-Kev

December 23, 2008 4:19 PM
 

Aaron Fischer said:

LINQ is sending a parameterized queries. To the best of my understanding(SQL server documentation) SQL Server treats this the same as a stored proc in regards to a cached query plan.  

December 23, 2008 5:53 PM
 

Andrew Kelly said:

Aaron,

I haven't looked at LinQ in a while but back when I first wrote this there was not a lot of that going on. I know they intended to fix some of that so maybe they have. But I would doubt they did a bang up job if history is any indication of how Microsofts own developers write SQL code.

December 23, 2008 7:53 PM
 

aspiringgeek said:

Andy, I missed your post the first time around & found it thanks to Kevin Kline's post:

 http://sqlblog.com/blogs/kevin_kline/archive/2008/12/23/linq-enabling-or-entangling.aspx.

My position on any new technology finds me repeating the words parroted by the great Ronaldus Magnus: Trust but Verify.

So far my verification mirrors yours.  Like that other great new technology, CLR, there's a lot of abuse out there by developers.

Unlike the ability to relatively quickly modify classic T-SQL, the app re-writes demanded by bad LINQ simply ain't gonna happen as quickly.

Great post!

December 24, 2008 10:23 AM
 

Jonathan Kehayias said:

Aaron,

While your comment;

"LINQ is sending a parameterized queries. To the best of my understanding(SQL server documentation) SQL Server treats this the same as a stored proc in regards to a cached query plan."

is true, just being parameterized doesn't make a plan reusable.  One problem with LINQ is that the parameter definition changes for strings based on the length of the string being passed, regardless of the actual table columns definition.  So if you wanted find where FirstName = 'Jonathan' you get a different parameterization and plan than where FirstName = 'Aaron'.  My name would be passed as a nvarchar(8) and your name would be passed as a nvarchar(5).

It gets worse when you use arrays or collections because the query passed is based on the number of items in the collection and you get a query like

WHERE FirstName in (@1, @2)

with @1 declared as nvarchar(8) and @2 declare as nvarchar(5) having values Jonathan and Aaron passed respectively.  The next time use Andrew and Kevin, and you have a different query and a different plan completely.  Cache bloat is a big problem with LINQ to SQL for this very reason.

To compound the issue LINQ's nonuse of the table column definitions can result in full table scans where a column in the table is defined as a varchar(20) since the parameters are passed as nvarchar's to the database engine which forces an implicit conversion on the column side of the filter criteria and this will have the same effect as doing a CAST() on the column which will cause a table scan.  I spent hours looking at trace data trying to figure this particular problem out for a customer of mine before I caught the implicit conversion and that their new service was using LINQ to SQL instead of their original stored procedures.  They took two steps backwards and ended up changing the LINQ code out for a tableadapter because the queries were 10 times slower.

Where would I use LINQ to SQL?  Probably in a small app that wouldn't be generating lots of queries, but not in a enterprise application.  I had considered it for the XEvents Manager I wrote, but decided to use SqlDataReaders instead again for performance.

January 8, 2009 2:00 PM
 

Alan said:

I don't think that LINQ to SQL is recommended widely for enterprise sized applications. For smaller applications it is a tool for rapidly creating fully functional and easily maintainable code. To knock this technology because it does not suit your particular requirements is missing the point. To use LINQ to SQL on a large database and expect no performance issues is also missing the point. Don't use a screwdriver to bang in a nail. (Unless, of course, you've lost your hammer;-)

June 25, 2009 9:31 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement