.NET Linq to SQL Performance Issue with Inherited Discriminators
I'm having a performance issue with a LINQ to SQL model that has a lot of inherited classes.
I isolated the issue and it seems to be some sort of issue with LINQ to SQL generated code itself.
I created a sample program using Northwind to flesh out the issue. It returns the first row of Customer in both scenarios. The database query is trivial and runs <1ms.
In the 'small' scenario, it has 4 types, with one base Customer and three derived types using LINQ inheritance.
The the "large" scenario, it has over 60 types with one base Customer and over 60 derived types using LINQ inheritance with an INT column discriminator.
The performance of the LARGE datacontext is horrible compared to the first (over 2 seconds), even though it's the same query and same data returned.
Here is the output.
Performance Comparison of LINQ to SQL
Run each 3 times in a row for cache
With 4 Inherited Tables
00:00:00.2340004 of Small Test for First Customer:A Bike Store
00:00:00 of Small Test for First Customer:A Bike Store
00:00:00 of Small Test for First Customer:A Bike Store
With >60 Inherited Tables
00:00:01.7004030 of Large TestFirst Customer:A Bike Store
00:00:00.0156000 of Large TestFirst Customer:A Bike Store
00:00:00.0156001 of Large TestFirst Customer:A Bike Store
With 4 Inherited Tables
00:00:00 of Small Test for First Customer:A Bike Store
00:00:00 of Small Test for First Customer:A Bike Store
00:00:00 of Small Test for First Customer:A Bike Store
With >60 Inherited Tables
00:00:00.0156000 of Large TestFirst Customer:A Bike Store
00:00:00.0156000 of Large TestFirst Customer:A Bike Store
00:00:00.0156001 of Large TestFirst Customer:A Bike Store
Press Any Key
Notice that the first time the LARGE dataset is run, it takes 1.7 seconds vs. .23 seconds (7x slower)
Even 2nd set of runs is slower, but acceptable performance.
Here is the full application for download http://cid-02bee16e84f5c99f.office.live.com/se开发者_JAVA百科lf.aspx/Public/TestDalLinqPerformance.zip
From our debugging, it has something to do with this generated code:
[global::System.Data.Linq.Mapping.InheritanceMappingAttribute(Code="2", Type=typeof(Customer2))]
[global::System.Data.Linq.Mapping.InheritanceMappingAttribute(Code="67", Type=typeof(Customer67))]
[global::System.Data.Linq.Mapping.InheritanceMappingAttribute(Code="65", Type=typeof(Customer65))]
[global::System.Data.Linq.Mapping.InheritanceMappingAttribute(Code="615", Type=typeof(Customer615))]
[global::System.Data.Linq.Mapping.InheritanceMappingAttribute(Code="513", Type=typeof(Customer613))]
public partial class Customer : INotifyPropertyChanging, INotifyPropertyChanged
When generated with a 40 plus table Inheritance, this is large and somehow slow from with the LINQ 2 SQL classes. We've also validated that is has something to do with the 'InheritanceDiscriminator' because if you remove it, the performance is fine.
Subsequent calls are fast and and seem to cache something in the Thread Local Storage. Is there a way to persist that across multiple threads?
I can see from your code that you indent to add a new subclass per Customer. This unfortunately will not work because of an internal L2S perf problem. Sooner or later you have to give up this approach. You can also easily find out what function is causing the problem by running you app under load (hold F5) and stop the debugger a few times. Look where it stops most often.
精彩评论