LINQ is faster then it looks

I was recently working on a LINQ query. I needed to get some information from a database table and I needed to know if certain information regarding the information I just retrieved from that database table was available in one of two other database tables. I wasn't interested in the information itself, just whether or not there was information. Basically I needed to return some stuff and two boolean's.

I couldn't get it to work in one single query, so as always I turned to my colleague who knows a helluva lot more about database and SQL than me and asked him for help. Since he doesn't know LINQ he asked me the SQL that LINQ creates for the query so he could use that as a starting point. So I fired up LINQPad and generated the SQL.

The SQL LINQ produced for my particular query was a lot of selects. It was selects within selects within selects within selects with a "Case-Then" with selects. So, understandably, my colleague expressed his disgust with the SQL and then proceeded to give me a very lean-and-mean query that did exactly what I wanted. I updated my existing LINQ query to use some of the SQL stuff he used in creating the query and my LINQ query gave the exact results I wanted. I was happy, but my colleague wasn't.

My colleague wanted the final SQL as produced by the LINQ query that I had now, so I went into LINQPad again, generated the query and send it to him. This time LINQ had added another level of selects and after seeing the query my colleague went on a rant about how bad LINQ was and how much performance we'd lose if we'd use it. I wasn't worried. This particular project I was working on was not a high-profile application, but rather an internal tool for supporting some testers. So as long as I got an answer from the SQL Server within 1 second I was happy. My colleague wasn't. He wanted to know exactly hoe much performance was lost on this LINQ nonsense.

My colleague took the SQL query he handcrafted into perfection over 30 minutes time and took the bloated SQL Query that LINQ produced for the query I created in less then 5 minutes and ran them both through the SQL Server Profiler. Then his heart stopped. According to the SQL Server Profiler both queries took exactly the same time, in fact both queries resulted in exactly the same execution plan!

Again this was just a reminder of something I mentioned before: Don't guess about performance. Measure! And also, the SQL Server Query Optimizer is pretty bad-ass :-)

LINQ means working fast

Now granted, I have not yet done a really big project with LINQ and I have really limited experience with LINQ so far, but let's not kill the hype here, okay? :-)

At my office we have almost finished our part in a whole connected chain of systems to provision products that my company offers to its end-users. The system we build is an abstraction on top of the technical provisioning systems and through our system they are integrated for use by the commercial systems at the top of the chain. In the beginning of last week they told me that one of the big commercial system would need more time to get done. "More time" in this case meant around a month. Knowing how good estimates by programmers are, I'm positive it will be more then a month, but I digress. Even with that delay, they still want to start the pilot project and start using parts of the provisioning chain. So they asked me to build a kind off web-front-end to our system. So that they could start handling orders manually at the very least. And while the big commercial system was delayed for (at least) a month. They asked me to do it in 2 or 3 days. And because they were desperate they told me to build it no matter what. The front-end would contain next to no logic and would only need to communicate with out system, which I know very well, so I decided to build it with some new technology I was itching to try out, so I chose to build it with ASP.NET MVC and LINQ.

I had played with ASP.NET MVC before, but they updated the preview bits and I found some welcome changes. Aside from spending some extra time in getting the URLs to look nice it wasn't really that challenging. No, the fun part was in using LINQ.

In my team we have defined a set of "best practices" or some development "rules" and one of the rules is that we always use Stored Procedures (sprocs). So when I write a Data Access Layer (DAL) for any program I always create a data-model (the database tables), then I create sprocs for all the things I need to do to the data that will be stored in the database (reads, inserts, updates, etc) and then I will write some C# code to access these sprocs and to create C# objects out of the returned data (and of course code to be able to input data to the sprocs). This time I chose to go with LINQ and I've never written my DAL in such a short time. Even if I still had to find out some basic things on how to use LINQ.

I think that I wrote my DAL with LINQ in less then 20% of the time it would have taken me if I had to use our 'conventional' way. It's so simple. Now, be aware that I did not do any complicated stuff here. Just you basic selects, inserts and left outer joins. Nothing more. But I think that's exactly where the strength of LINQ lies. All the simple stuff is trivial to do in LINQ, but takes more effort to do as sprocs and SqlDataReaders. Why waste your valuable development time on that?

I encourage every developer out there to try LINQ. Give it a good whirl and don't judge the LINQ book by its cover. Yes, it generates dynamic SQL, but I've some tests (some very unscientific tests) and found that LINQ is faster then using SqlDataReader with sprocs on simple queries. I just took a large table (500,000+ items) with some 12 columns and made an sproc and a LINQ query (that resulted in the exact same SQL as the sproc) and did some loop testing. When doing the same query as fast as possible in a loop, LINQ will be faster then the SqlDataReader/sproc method until around 300 queries. After that LINQ seems to be getting slower. Personally I think this has to do with the fact that for the sproc SQL Server only needs to see a single word (the name of the sproc) to recognize what you want and re-use it's caching, whereas with the LINQ query it will need to compare the whole query to notice it already exists in the cache. And maybe network traffic also plays a role. Network traffic is less with an sproc. So with these test results in mind, sprocs would only be faster if you have a really high-traffic site (but even then you should measure before you start to optimise). And if you want to be Agile. You'll release early and often. So you can start cramming out features while using LINQ for your DAL so you won't waste precious time on sprocs and such, and then replace the LINQ DAL with an sproc DAL when you start to notice the performance hit. Or you combine them, which is also entirely possible.