Toffer’s long winded advice about using Linq To SQL

Hello again everyone.  Today I’m going to resque you from one of my saved up posts (which I have a lot of) and give you some fresh thoughts that are coming to you live and in person…well…they were…not now since you’re a little behind in reading what comes out of my head…but whatever.  😛

The topic of today’s little brain dump is Linq to SQL.  I know, I can hear you now…Gosh Toffer, what is that?  It sounds AMAZING!  Well, it is…a little too amazing at times, which is what leads to my post.  So…Linq to SQL is a Visual Studio Data item template.  Kinda like the template “Class” with a whole bunch of extra bells and whistles.  You can get to it from the Add New Item dialog, which looks similar across most versions of Visual Studio…I think Linq To SQL has been around since at least 2010 so Visual Studio 2010 should have it available all the way up to current versions.

LinqToSQLItem

The nice thing Linq To SQL offers is it automagically maps out objects that map to tables defined in a given database (I think other datasources can be used as well but I’ve only used it for databases…and more specifically Microsoft SQL databases, but I know other databases can be used just the same).  What’s more…not only does it create these objects, but it also goes a step further and creates sub-objects which represent foreign key relationships in a database.

Wooooaaaah Toffer…you’re slinging around some crazy talk now.  Let me define a few things for those of you that may not know SQL as well.  Let’s say we wanted to create a database for a parking garage…and even though no parking garage in the world would do this…lets say this garage is going to track every type of vehicle that comes through the garage.  So we’ll make it simple and have 2 tables in this database.  I’m not going to go too in depth about SQL, but if you the EXCITED reader have questions, feel free to ask away and I’ll do my best to help.

Vehicle:
CREATE TABLE [dbo].[Vehicle]
(
    [Id]        INT NOT NULL PRIMARY KEY,
    [Doors]     INT NOT NULL,
    [Wheels]    INT NOT NULL,
    [Size]      NVARCHAR(50) NOT NULL,
    [Make]      NVARCHAR(50) NOT NULL,
    [Model]     NVARCHAR(50) NOT NULL
)

GarageStall:
CREATE TABLE [dbo].[GarageStall]
(
    [Id]            INT NOT NULL PRIMARY KEY,
    [VehicleId]     INT NOT NULL,
[StallNumber]   INT NOT NULL,

    [TimeIn]        DATETIME2 NOT NULL,
    [TimeOut]       DATETIME2 NULL,

    CONSTRAINT [FK_GarageStall_Vehicle]
FOREIGN KEY ([VehicleId])
REFERENCES [Vehicle]([Id])

)

Ok…above we have the T-SQL code to create 2 tables.  One for storing generic vehicle info, the other for storing where a car is parked and when it checked in and checks out.  You may notice the extra “Constraint” bit in the GarageStall table.  This is how a foreign key relationship is created.  It means that for every value in the VehicleId column there MUST be a record in the Vehicle table with the same Id that represents what VehicleId is refering too.

Why did I just go through and explain all that?  Weeellllll…because what Linq To SQL will do with that is create a C# class object called “GarageStall” with the following properties:

  • Id – int
  • VehicleId – int
  • Vehicle – C# class object
  • StallNumber – int
  • TimeIn – DateTime
  • TimeOut – DateTime

If you notice, Linq To SQL creates an extra property that there is no column defined for.  While scanning through the database definintion it see’s the foreign key relationship and creates an actual object with an actual reference to the vehicle object that’s created for the Vehicle table.  It’s makes it sooooo easy to use.  For example if within some C# code you wanted to get the Make and Model of a vehicle you have a record for in the Garage table, you’d just do something like the following…here I’m just grabbing the first record in the GarageStalls table…and whatever that first record is, I’m then trying to print the make and model of the vehicle that it references.

var stall = DataContext.GarageStalls.FirstOrDefault();
Console.WriteLine(stall.Vehicle.Make);
Console.WriteLine(stall.Vehicle.Model);

Just looking at the above the usage example should show that it’s fast and super quick to use…which is part of the problem and why I went to such length to explain what’s going on.  Even though it’s super easy to use…behind the scenes…this is what’s happening in SQL (and I’m not giving you what the exact T-SQL is, just giving a general idea of what’s happening).

First, the below query will happen…

select top 1 * 
from GarageStall as g
    join Vehicle as v on g.VehicleId = v.Id

But as soon as you make use of the Vehicle object, the below query is immediatly executed, in this case it’s actually executed twice…once when getting the Make and again for the Model

select * 
from Vehicle
where Id = <VehicleId parameter from GarageStall query done above>

At first glance this doesn’t look so bad.  Sure…for one record, it’s still fast and performant.  Now…imagine we’re doing something like the following for 1 million records…

foreach(var stall in DataContext.GarageStalls)
{
    Console.WriteLine(stall.Vehicle.Make);
    Console.WriteLine(stall.Vehicle.Model);
}

This would produce 3 million queries to the database and generated a massive amount of network traffic and server processing time…and it takes a good long while for all that data to be assembled into the C# classes.  So even though it’s super easy to use…it ends up being super slow…and when you’re using this as your data source for some sort of web app or WPF app…if you let Linq To SQL do its thing, your UI thread will lock up and your app will appear to be frozen.  Bleh.

Here’s what I do to speed things up.

List<Vehicle> vehicles = DataContext.Vehicles.ToList();

foreach(var stall in DataContext.GarageStalls)
{
Vehicle vehicle = vehicles
.Where(v => v.Id == stall.VehicleId)
.FirstOrDefault();

    Console.WriteLine(vehicle.Make);
    Console.WriteLine(vehicle.Model);
}

Now there is a single query done to the Vehicle tables to fill up the “vehicles” variable…the ToList forces the query to happen right away and stores the results all in local memory.  Then as we spin through the GarageStall records, instead of having to do another query when using the “Vehicle” property…we only use the “VehicleId” property and look it up from the “vehicles” list that’s sitting in memory.  Now we’re down to 1,000,001 queries being done to the database.  There’s other ways to speed this up…but this one right here increases the speed of using Linq To SQL classes by a huge factor.

So ultimately what I’m trying to say is if you’re experiencing perf issues using your Linq To SQL…spend some time looking at how the data is being queried for and see if there’s any way you can do some local caching of data that’s quried for often.  Good luck!  🙂

Advertisements
Toffer’s long winded advice about using Linq To SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s