Slow linq enumeration

Question

I've been working on a silverlight application which generates various graphs. It requires a bit of number crunching as well as getting a decent amount of data from the database.

For my db communications I created a web service which uses Linq2SQL. To overcome the issue of my web service blowing up, I chunk up the data which results in sometimes having 3 web service calls to get it all.

On to the issue. When getting larger amounts of data, the enumeration process "ToList()" is taking a good few seconds, which adds up to a longer than desired load time. The typical duration for my query is about 1500 according to the SQL Profiler. The ToList() call can take up to 4 seconds for 1000 records, which seems extreme.

My main question is, is there anything that can be done to speed up this process? Or perhaps is there a better way to get large amounts of data from the db to a silverlight app?

Additional Info:

My linq reference is hard coded into a partial class of my data context. This is due to an awkward stored procedure whose return type wouldn't play nice with the dbml file.

I also created a basic object which I set as the return type for my object, consisting mostly of quick property definitions such as :

 public Guid Id {get; set;} 

The follow up question is, when defining a linq stored procedure call and return types, are there any attributes which help speed up the process or am I fine just linking it to a very basic object?

Answer 1

First you need to figure out where the bottleneck is; the usual culprits:

• latency; you mention 3 calls (rather than 300), so I don't think this is the issue
• bandwidth; how fat are the objects you want to send
• core db query performance; how fast is your SELECT
• lazy loading issues; are you actually doing n+1 db operations?

Investigate the last two with a SQL trace, or some simple logging (LINQ-to-SQL has a DataContext.Log property that can be very useful here); a SQL trace may be more accurate.

To investigate if the number of trips from the Silverlight client to the web-server is the issue, try a network trace; Fiddler or WireShark should do the job.

If bandwidth is the issue (i.e. sheer bulk of data over the wire), then consider:

• compression
• a different serializer

For example, protobuf-net can make huge differences to the size on the wire; either might require slightly different code, though - the simplest approach being to pass a byte[] over the service (and using MTOM if possible).

Answer 2

I would guess that you've nested a linq query so every time you move the iterator forward, the inner query is getting re-run. Maybe you want to pre-calculate the inner query if that is your situation.

Answer 3

The answer is to stop using ToList() altogether. Don't load all your data into your web service, then serve it back to the client as one huge blob. Take a stream approach; so instead of (pseudocode)

for each record in query;
  add record to list

for each record in list;
  create a line of response
  write line to response.

Can you not stream the response like this?

for each record in query;
  create a line of response
  write line to response.

the foreach in your web service should step through the result of the query one record at a time, and memory usage will be absolutely minimal.

Answer 4

To avoid having to use the ToList(), have your web service proxy generate code that uses the List collection. Do this by finding the service reference in your Visual Studio project. Right Click > Configure Service Reference. Then under the collection type, select a collection that supports linq methods. I'd use System.Collection.Generic.List. Then update the service reference to regenerate the References.cs file.