Tuesday, April 15, 2008

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.

No comments: