Tuesday, September 2, 2008

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 :-)