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

Stored procedures, faster or not? (SQL Server)

I'm not really that knowledgeable about databases. I can do a select or two and I understand joins, but anything more complex that then that and I won't like it. I don't really like SQL. The language seems very illogical to me for some reason.

We do have a lot of discussion at work about databases. A colleague of mine really loves delving into database problems and he even made a list of SQL standard practices. One of these standard practices says that we should always use stored procedures instead of dynamical SQL statements.

I always want to know the 'why'. Why are we supposed to use only stored procedures? And the reasons invariably are:

1. Stored procedures are not susceptible to SQL injection.
2. Stored procedures are safer because you can secure them on the database level.
3. Stored procedures enable us to update database logic or fix a bug without having to release a new version of the software.
4. Stored procedures are faster then dynamic SQL.

Almost two years ago and ran into a weblog post that debunked all of these points: Stored procedures are bad, m'kay?.

Given this article, there is still a lot of discussion at the office and people generally still say stored procedures are faster and safer. So yeah, stored procedures are faster then SQL created by adding SQL statements and their parameters together with string concatenations, but according to the article they're not faster then properly parameterized SQL queries. And as for security at the stored procedure level, we don't do that in our office, so that shouldn't be a concern. Also, when changing a stored procedure, we still need to create a new release of the software, even though only a SQL script changed. That's just the way we work. So the 'software release' argument is also nonsense.

I will agree that stored procedures are faster in that you only send the name of the query and the parameters, while with dynamic SQL you'd need to send the entire query with the parameters. So it's faster in that you save a few bytes sent over the wire. And for some really rare cases those few bytes can make a difference, but not in the applications we make.

In the end it's a discussion about what 'feels' better I think. One of the many religious wars fought in the field of software development.