LINQ vs datasets - performance hit?

Question

I am refactoring an existing VB.NET application to use Linq. I've been able to successfully get it to work, but it takes ages (over a minute) on the client machine!

They have lots of rows in the database table, but the old version of the programme on the same machine (which uses Datasets) takes 5 seconds.

My Linq queries are pretty standard, like so:

Dim query = From t As TRANSACTION In db.TRANSACTIONs _
where t.transactionID = transactionID _
select t

They only ever return one or zero rows. Any thoughts?

Answer 1

I am surprised by the huge time differential (5 seconds to 60+ seconds). I guess it would depend on how complex the TRANSACTION entity is. LINQ to SQL will process each row from your result set and turn it into an object, then add some state-tracking information to the DataContext. A DataSet simply stores the data raw, and processes it into strongly typed data as you read it from the DataTable. I wouldn't expect L2S to incur a 12-fold cost increase, but I would expect some increase.

Answer 2

The code you've pasted doesn't actually access the database at all -- what you do next with 'query' will determine how much data ends up getting transferred to the client. Is it possible something you're doing later on is causing the LINQ version to download more data than the Dataset version?

I have done the same transition on a project and seen only equivalent or better performance from LINQ but there have been instances where the LINQ version was doing a lot more roundtrips to the server, e.g. doing a Count() followed by a fetch of the data as two separate server queries. I usually solved this by doing a .ToList() to get the data locally before working on it. You have to use SQL Profiler sometimes to find out what is going on behind the scenes.