.debug

On my current project that I happen to mistakenly used as a guinea pig for L2E, I wanted to apply an optimization on a select query: I wanted to allow “a dirty read” on a table where L2E sits on.

Upon searching, I stumbled upon on Scott Hanselman’s post on the project.  As I go through the comments, one commenter gave me an equivalent of a painful smack on the head; this is what he said:

I very rarely comment on blogs, but this time I have an itch that I just gotta scratch….

the question/concern I have is in regard to tight coupling and the lack of “Seperation of concerns” that this seems to introduce.

When setting the transaction isolation level in the application binaries arent we creating a very tightly coupled scenario in which the code has too much intimate knowledge of the DB?

How is a production DBA going to troubleshoot database performance,blocking, and deadlock issues? All we’ll see in the profiler is a bunch of inline SQL Statements with no indication of where they live or where they are coming from.

If the isolation level has to be changed, it would require a C# code change, a full QA cycle and a binary redeploy.

Seems to me that placing this in sprocs shields the app from having to know too much about the database.

Futhermore, this makes me think of some comments Bob Beauchemin had on his blog regarding performance tuning SQL generated by LINQ. He’s right…..if we cant control the SQL that is generated by the API, we will likely have huge performance issues that cannot easily be fixed without a huge redesign at the application level.

Currently, as all SQL code lives in sprocs, when I see a performance issue, I can turn the profiler on an very quickly determine the sproc that is the troublemaker and then I can quickly and easily modify the sql as needed to optimize it. Knowing all the way there are many ways to write the same query while yielding the same results but very different performance. Sometimes I just need to change a join to use a correlated sub-query instead. Somtimes I need to tweak the method used for filtering my WHERE Clause…..

while making changes to the sproc I can instantly see the performance gains by viewing the query plan and monitoring sub-tree cost, etc. If LINQ is generating SQL for me, this becomes impossible.

Am I worried about nothing here or are these issues really going to exist with the wide adoption of LINQ?

If you really look at it, L2E has lost its appeal.  Mr. pdxJaxon, I nominate thee as 2010 Programmer of the Year.

I re-wrote the part of the code that uses L2E to an sproc.  It’s now at least 50% faster.  Time to gloat.