Editorials

LINQ What is its Place?

by Ben Taylor

LINQ – What is its Place?
The concept of making LINQ work natively in SQL Server has certainly resulted in a great response from our readers. I have so many responses it would take up three or four whole newsletters.

People tend to fall in one or more camps when it comes to the idea of enabling LINQ to work natively in SQL Server, just at TSQL queries and .Net CLR queries do.

  1. No way. If people want to work with a database, then learn TSQL where all the capabilities are already there. You can tune TSQL in ways that you can’t tune LINQ.
  2. Interesting idea. It would enable people to utilize skills they use elsewhere while operating with the same performance as TSQL. It could even be embedded in Stored Procedures, Triggers, etc.
  3. What’s the point? We should be using ORMs coupled with native TSQL for complicated things. Since the ORMs already place things in objects, LINQ can simply work with the objects.
  4. Not a good architecture…perhaps making the line too fuzzy between layers.

Jeremy:
Readers are asking why the inverted syntax and putting it down to paranoid-MS-taking-over-the-world theories, but as far as I am aware it is done that way simply to allow Intellisense to work; it’s hard to guess what might be in the select list if you don’t know the object you are selecting FROM…

Interesting discussion though…

Dave:
ORMs are heralded as a way to overcome the ‘impedence mismatch’ between .NET web related code (C#) and SQL database code. There’s nothing wrong with this, but most proponents of ORMs have a serious tunnel vision problem.

  1. The ‘impedence mismatch’ is really a skills deficit. Most C# coders have only basic SQL skills and don’t want to learn. They are being coddled. This does not help them or their employers.
  2. Most ORM environments now include a ‘code first’ pattern in which the database is generated from the model. This is marketed as a good thing. It is if you have a slacker mentality or are working with training wheels databases. But it’s a bad idea for more complex environments unless you are just prototyping.
  3. Throw in some joins and a complex where clause and few C# hotshots will get the correct result set from their LINQ code. Just because your code compiles does not mean it’s errorless.
  4. There is a huge irony in that MVC web development proponents claim that good web code can only be done ‘close to the metal’ as it were, requiring the developer to know the ins and outs to HTTP protocols, while these same people want to remain as far from and as ignorant of how a database works as possible. These people see the abstraction layered over HTTP protocols by web forms as an abomination, while blithly accepting the premise that doing the same thing on the datbase side is a good thing. Hmm.

From where I sit, it’s equally ok, if not better, to code close to the metal on the database side – especially when more complex, performance sensitive queries are being constructed / run. This does not mean that ORMs are bad. Once set up properly (there’s the rub), they enable development of many basic CRUD furnctions without having to use SQL. But this absolves no one of the task of designing a database properly, or from understanding what SQL is actually being submitted when LINQ queries are run. If you’re a web coder that writes database code, you can’t call yourself competent unless you do.

Finally, I must pass on the fact the LINQ is not proprietary. We are currently building a site using LINQ to NHibernate, a popular open source ORM.

Troy:
I’ve spent the last 3 months ripping out LINQ to SQL from an OLTP system and replacing it with ADO and stored procedures. The concept is great for small to medium operations but performance is horrible on large volume transaction systems. We even did many optimization techniques from Microsoft dev team without much luck. Plus, LINQ to SQL is dead for future versions of .net. EF 4 is the new way to go and does perform better than LINQ to SQL but still not as good as ADO/procs. For ORM, my preference would be EF4 with POCO design and you can still utilize stored procedures.

LINQ to SQL problems.

  1. Bloats the query cache in SQL Server due to constant change in parameters. This is mostly on strings which sends parameter data length based on string size.
  2. Always utilizes nvarchar data type. If you have a mismatch with your table this will cause index scan.
  3. Too much overhead in applicaiton layer to handle heavy volumes.
  4. Doesn’t do well for complex query translation.


I like LINQ in certain situations and looks good for StreamInsight. It is just not good for high volume OLTP systems. We were running 26K transactions per second on over 2TB database. I think authors that promote LINQ to SQL should think more about performance than its convience.

My 2 cents

Raymond:
I agree with a lot of the comments made:

  • You are persisting another layer
  • LINQ is encapsulated from a DBA / DB Developer. As a DB Developer and back end developer (vb.net), I would be horrified to see LINQ implemented in any application full stop. How can I performance tune a query? What if my schema changes – I’ll have to deploy DLL’s. You are tightly coupling your layers to the DB
  • Its tools for the trade / horses for courses. LINQ seems to be aimed at application / web developers. If I attempted web design, would you be happy if I used Frontpage or maybe out of date or poor strategies as I didn’t fully understand web development? SQL Development and design is an art like any other aspect of development / architecture and is the best tool for DB interaction

Whilst I appreciate LINQ allows connection to a multitude of sources without change of syntax, that can be circumnavigated mostly by using ADO in the data layer.

And finally, doesn’t the “old fashioned way” get developers thinking about solutions properly rather than coding willy nilly without thought for architecture or performance?

I’ve never seen the point in LINQ, but your article shows why – DB design and development is an afterthought and the time and effort should be placed on the presentation layers. I agree that it can speed up development and helps junior developer who can be daunted by an n-tier approach or advanced SQL, but we are not painting by numbers here.

Jeremy:
First let me say I enjoy reading you columns, I prefer to listen and not reply, but on the topic “LINQ Is It the New SQL” I cannot keep my opinion to myself. Three years ago my company made the decision to rewrite one of our major applications. With this new rewrite we decided to “get with the latest and greatest technologies”. We leveraged LINQ to Entities everywhere. As a dba I am a strong believer of tried and true over latest and greatest. I was less than thrilled with the change, but hopeful.

Weeks into development I was shocked to see how chatty our application was and how ugly the generated queries were. I remember wondering why the entity queries created unique parameters ex.(@p__linq__33, then @p__linq__34 ) on each query preventing query plan reuse, and working with MSFT on a bug fix which ended as a workaround(compiled queries). We also began shaping the model to meet needs of the entity requirements and linq queries. I think you can guess where this is going. I still have a 44 join query that printed out to 15 pages. When discussing concerns when our senior developers they noted that developers write poor stored procedures as well. Certainly they do and can, but LINQ is excellent at helping people who do not understand the underlying architecture to create horrific queries, most were happy to have the code working. We learned ways to tune the linq queries, but found it more efficient to go back to using TSQL procedures to drive our important business logic, and wishing we had the time to remove it all.

So is LINQ the new SQL? Yes, if you are building a website to sell your Mom’s knitted sweaters and performance and scalability will not likely be a concern.

No, if you are building a complex OLTP application with various business rules.

If you have something further to share on this topic, feel free to send me a note. However, I’m probably going to post the majority of any new comments on the SSWUG Facebook page. Send your comments to btaylor@sswug.org.

Cheers,

Ben

$$SWYNK$$

Featured Article(s)
Troubleshooting SQL Server 2008 Management Studio
In this article, Alexander Chigrik explains some problems that you can have with SQL Server 2008 Management Studio.

Featured White Paper(s)
Query Tuning Strategies for Microsoft SQL Server
Written by Quest Software

When you’re looking for a reliable tool to diagnose … (read more)

Tags:
Author: Ben TaylorBen is a Relational Database veteran starting in the early eighties on DEC Vax database systems such as Bloom and Harvest. He has specialized in SQL engines for more than two decades, with a primary focus on Microsoft SQL Server. Ben has worked with small and large databases for both OLAP and OLTP working on systems for many industries such as Medical (Medical Claims and Eligibility Warehouse, HMO Provider Management, Enrollment), Financial (credit card processing, mortgage, stock portfolio management), Manufacturing (Assembly Line and Product Assemblies), Sales, Auto Auctions and more.