Any way to improve Linq-To-SQL performance?

Question

Please offer advice if you can...

I've based my project on Linq-To-SQL, and the (almost) finished application has very poor performance. I've fired up the SQL Profiler many times to optimize the queries with LoadOptions to reduce the number of round-trips to the database server, but at the end of the day, my conundurum is weaved into the following points:

1. Microsoft's official advice is that the DataContext should not have a long life-span - that is - they advise you to create a DataContext, materialize objects, make changes, call SubmitChanges(), then dispose. My application follows this pattern religiously.

2. The DataContext tends to be very clingy to it's entities, and detaching and re-attaching (unchanged) objects from one context to another has proven to be problematic when it comes to change tracking and deferred-relationship loading of the re-attached objects. As such, keeping a loaded object cache has proven to be problematic.

3. When the LoadOptions are set on a DataContext to load several layers of relationships, the performance suffers because the resulting data is the product of the related rows, instead of the sum of them. The particular schema that I'm pulling data from literally causes a 144 fold increase in the volume of data returned when LoadOptions are set, so naturally this ends up being a performance killer, rather than a helper.

4. Without LoadOptions being set (or even when setting it at a reduced level), the DataContext does hundred of round-trips to the database.

I've also noticed that the DataContext ignores that an object is already loaded the first time that an object is visited via a relationship... e.g:

Dim allCustomers as Customers() = Context.Customers.ToArray()
Dim allOrders as Orders() = Context.Orders.ToArray()

For Each o as Order in allOrders
    Console.WriteLine(o.Customer.Name)      ' <- Triggers round-trips to database!!!
Next

In summary, the problem I have is that the performance I'm getting is unacceptable for my customer, and:

1. Holding a single, application-wide DataContext is ill-advised.
2. Caching and re-attaching entities is (seemingly) problematic.
3. With deep, multi-level relationships, DataContext.LoadOptions hurts performance
4. Pre-loading an entire table's data doesn't help - DataContext still refreshes the loaded object when a foreign key relationship is visited.

I really feel like I've missed something fundamental here, perhaps in the overall design pattern I've adopted, and would appreciate any tidbits of advice you may have for getting a loaded Object-Graph out of a DataContext without all the round-trips or bloated network traffic.

Your advice?

Answer 1

Please have a look at the site, http://www.sidarok.com/web/blog/content/2008/05/02/10-tips-to-improve-your-linq-to-sql-application-performance.html Hope this helps.

Answer 2

i think you can find a database profiler to check what SQL statement your LInqtoSQL program send to database, and from which you might found something to optimize