.NET Linq到SQL性能问题与继承的判别器 [英] .NET Linq to SQL Performance Issue with Inherited Discriminators

问题描述

我遇到了LINQ to SQL模型的性能问题，该模型有很多继承的类。

I'm having a performance issue with a LINQ to SQL model that has a lot of inherited classes.

我隔离了这个问题，它似乎是某种类型LINQ to SQL生成代码本身的问题。

I isolated the issue and it seems to be some sort of issue with LINQ to SQL generated code itself.

我使用Northwind创建了一个示例程序来充实问题。它在两种方案中都返回Customer的第一行。数据库查询很简单，运行时间<1ms。

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.

在小方案中，它有4种类型，一个基本Customer和三个使用LINQ继承的派生类型。

In the 'small' scenario, it has 4 types, with one base Customer and three derived types using LINQ inheritance.

大场景，它有超过60种类型，一个基本客户，超过60种派生类型，使用LINQ继承和INT列鉴别器。

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.

与第一个（超过2秒）相比，LARGE datacontext的性能非常糟糕，即使它是相同的查询并返回相同的数据。

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.

这是输出。

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

请注意，第一次运行LARGE数据集时，它需要1.7秒而非.23秒（慢7倍）

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.

以下是完整的下载应用程序 http：// cid-02bee16e84f5c99f.office.live.com/self.aspx/Pu blic / TestDalLinqPerformance.zip

Here is the full application for download http://cid-02bee16e84f5c99f.office.live.com/self.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

当使用40 plus表继承生成时，这很大，并且从LINQ 2 SQL类开始变慢。我们还验证了它与'InheritanceDiscriminator'有关，因为如果你删除它，性能很好。

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?

推荐答案

我可以从你的代码中看到你缩进添加一个新的子类每个客户。遗憾的是，由于内部L2S性能问题，这将无法工作。迟早你 放弃这种方法。您还可以通过在加载下运行应用程序（按住F5）并停止调试器几次来轻松找出导致问题的功能。查看最常停留的位置。

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.

这篇关于.NET Linq到SQL性能问题与继承的判别器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！