mostly c# and wpf

Month: August 2016

After my last post, I got a lot of good advice on Reddit, mostly on better ways of handling my SQL data. The main point people were bringing up was using ORMs. Now, I’ve heard of the major ORMs, mostly the Entity framework I guess, but I’ve never met or spoken to anyone who’s particularly excited about. I’ve heard a lot of “Good idea, bad execution”. Now I’m not sure if that’s accurate or fair, but that’s what I’ve heard.

Most of the advice I was receiving was to use something called Dapper. After a little research I realized that Dapper was written (or at least co-written) by the same Marc Gravell that wrote FastMember (the project that sped up my reflection code). Quite a coincidence I’d say, I guess this guy knows what he’s doing in C#.

Anyway, I updated my benchmark code to compare what I was doing to using Dapper. Since Dapper actually handles the execution of the command as well as returning it to a type, I had to change my tests a little bit, so it looks like everything’s slower compared to last time, but in this test I’m timing the SQL query and converting to class.

If you take a look on the Github page for this project you’ll see a few other examples of how this works, but this is the simplest case.

I tested this against reading the command into a SqlDataReader and parsing (something a lot of people were recommending I do instead of converting to a DataTable).

Here’s the results –

Now, I don’t know exactly how accurate this is, my benchmarking is pretty sloppy overall (They have more benchmarking on the project site, so feel free to dig a little deeper), but Dapper was faster, or at least essentially the same speed. Add to that the fact that it’s way easier to use than writing your own custom SqlDataReader parsing method for each stored procedure in your project and Dapper gets the win by a mile.

All I know if that I’m going to be giving a real shot on Monday when I get back to work and probably recommending it to the rest of the guys in my department.

My new job has gotten me involved in a lot more SQL than I’d ever had to in the past. Now this can be interesting, but there’s a whole ton of boilerplate code that goes along with writing SQL, most of which involves converting DataTables to List.

My general opinion on DataTables is that you should immediately convert them into some equivalent C# object representing the properties you’re returning from SQL. I know this isn’t how everyone feels, especially if you’re then taking this data and converting it into some other model-type data, but for the purposes of this post, we’re going to be doing it, stick with me!

I’ve got three different ways of converting the DataTable to List and I’ve benchmarked their relative speeds for your reading pleasure. Here’s the initial setup so you can replicate it.

I’ve also got the test data here, so feel free to grab it. For this test, I had 5000 rows of data (the test file is 1000 records). For the record, this is all MSSQL, and probably won’t work with SQLite without a little tweaking.

To go along with this, I’ve also got a C# class with properties for each of the Columns in the SQL table, here’s that too

OK, so this is pretty good and fast. The downside is when you have actual nullable fields that can actually contain null data. In this test, all the data has been faked out, so we don’t have to deal with that, but when you do, this can slow it down considerably.

When you go from a non-null field like

Int6 = Convert.ToInt32(row["Int6"])

and make it nullable, suddenly you have to start writing your code like this –

or (as emn13 on reddit pointed out to me, a simpler conversion would be)

Int6 = row["Int6"] as int?

Obviously the more you have of that, the worse it gets. This leads us to

2 – LINQ

One of the guys at my work showed me this way. I’m pretty comfortable with LINQ, but I didn’t know you could get an enumerable for a DataTable, and I knew nothing about the Field structure. Here’s what it looks like

So, I’m going to give you a sneak peak at the end results and let you know that this is the fastest version. If you’re looking to turn a DataTable into a List manually, this is your guy. Super fast, and handles nullable fields with ease.

3 – Reflection

Now, I know everyone gets so hung up on speed with reflection, and that, after all, is the entire reason I’m doing this, but if you’re looking for a nice generic way of converting DataTables, I think you’ll like this.

The trick to speeding this up a lot mostly relies on someone else’s smarts. The snippet below uses a project called FastMember by Marc Gravell. Here he is describing how it came to be. Now, I didn’t spend much time looking into it, I just gave it a try and it worked great, so please feel free to read more on the Github site and his blog if you’re not feeling comfortable. Luckily for us, there’s a Nuget package available for FastMember, so just include that, and use the code below, you’ll be fine.

This runs pretty great on its own, but by using Parallel in the ForEach we’re able to at least half our time. If you’re running a 4 core or even an 8 core machine, it can be even better, but YMMV.

So, obviously this method is a little slower, but what you lose in speed, you make up for in flexibility. You’ll never have to add or remove code when you change your stored procedure. You’ll never have to write boiler plate code like in Methods 1 and 2 ever again. Just keep in mind that this works best for small result sets.

Results

OK, here’s the final results.

It’s pretty much as we’d expect for methods 1 and 2. If you have more nullable fields, 1 will get slower and slower, almost to the speed of method 3, so if you’re looking to do it manually, go with 2. The one thing that amazes me is how close to LINQ speeds we can get with Reflection and FastMember, so give it a try.