MHO: LINQ to SQL and Entity Framework: Panacea or evil incarnate? Part 6

This is the last post in the series, at least for now. I'll update it (or post more on the topics discussed here) as the products involved evolve and mature. This one's about:

LINQ to SQL and EF queries will be "untunable" because, even if you discover a performance problem, you can't change the underlying API code to produce the exact SQL query that you want. There's too many layers of abstraction to change it.

T-SQL is a declarative language, allowing you the ability to rewrite queries for better performance. A whole cottege industry has grown up around this (I teach it myself), and it usually consist of changing the SQL to get the plan you want, based on your intimate knowledge of the (current) data and the (current) use cases. As one of the simplest examples, you can switch between joins, correlated subqueries, and nested subqueries to see which one gives best performance. Or use EXISTS rather than a correlated subquery or IN clause.

Because the queries (LINQ and/or ESQL) are programmatically transformed in SQL queries there is not (that I'm currently aware of) the ability to "rephrase" LINQ/ESQL queries to produce subtlely different SQL queries and thus better performance. If you can produce rephrased SQL by changing a LINQ/ESQL query (not just rewriting a LINQ/EF query to produce different results that are more optimal), I'd be interested in hearing about it. Perhaps another cottege industry awaits…

BTW, although most/many SQL queries can be rewritten (sometimes many different ways) and tested for best generated query plan/best performance, the limitation is that, in future, the query processor can get smarter, thus making your past years' work unncessary. Usually though, you've benefited from rewriting SQL for that extra 6 mos-5 years until the query processor changed anyway.

Besides query rewrites, you can also "hint" queries, in most dialects of SQL I've seen. This helps when the query processor chooses a suboptimal plan (uncommon, but not unheard of) and you have intimate knowledge or data and use cases. Or when you're trying to service different use cases with the same query; SQL queries only have one plan at a time (modulo parallelized plans) and you might have to satisfy different use case by differently hinting the same query. Because the translation to SQL is deeply imbedded in the LINQ/EF source code, if I find a performance problem that can be helped with a hint, I can't hint in the LINQ/ESQL code. This means going back to using stored procedures (they work with hints) and away from the model.

Hinting is usually not preferred over rewriting the SQL because hints "tie the query processor's hands", i.e. if the statistics change so that a different plan would work better, the query processor can't use this information because you've told it how to accomplish the query. You've changed SQL from a declarative language to an imperative language. It's best not to put query hints in code, but separate them to a separate layer. SQL Server 2005 calls this separate layer plan guides. The plan guide is a named database object that relates a hint to a query without changing the underlying code. You can add/drop plan guides or turn them on/off at will. Or re-evalute them when things (the statistics or use cases) change.

Can you use plan guides with LINQ/EF queries? Two things to keep in mind. First, a plan guide for a SQL statement requires an exact match on a batch-by-batch basis. Machine-generated SQL will likely make exact match easier, but you will have to check that the guides are being used each time LINQ/EF libraries changes. Second, plan guides work best if you have a limited number of them in your database. They're meant to be special-case… not to add another level of complexity to an already complex (and getting more so as the layers of abstraction increase) situation. So use these with care.

So, is this an issue worth worrying about? I think we'll need to wait and see. Fix a few "bad (generated) SQL or bad queries" problems before giving up entirely. Or, fix performance problems (in the generated SQL) by going to stored procedures and see how many procs you have after a year. Are the folks who are licking their chops in anticipation of LINQ/EF related perf problems justified? Well, its not me that thinks optimizing declarative languages will always have its place.

MHO.

Hope you enjoyed this series. As implementations of these models take hold, I'll be watching for items that would change my opinions. Or prove them…

One thought on “MHO: LINQ to SQL and Entity Framework: Panacea or evil incarnate? Part 6

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.