基准Linq2SQL，Subsonic2，Subsonic3-还有其他想法可以使它们更快? [英] Benchmark Linq2SQL, Subsonic2, Subsonic3 - Any other ideas to make them faster?

问题描述

我与Subsonic 2的合作已经超过3年了……

在出现Linq，然后出现Subsonic 3之后，我开始考虑转移到连接到sql的新Linq期货.

After Linq appears and then Subsonic 3, I start thinking about moving to the new Linq futures that are connected to sql.

我必须说，我开始移动Subsonic 2并将其与SubSonic 3移植，很快我发现速度如此之慢以至于我不敢相信-并开始了所有测试.

I must say that I start move and port my subsonic 2 with SubSonic 3, and very soon I discover that the speed was so slow thats I didn't believe it - and starts all that tests.

然后我测试Linq2Sql并看到一个延迟-将其与Subsonic 2进行比较.

Then I test Linq2Sql and see also a delay - compare it with Subsonic 2.

我的问题是，特别是对于linq2sql和即将推出的dotnet版本4，我还可以采取其他措施来加快速度吗?在linq2sql设置或类上还有其他什么，而不是在我用于我的聊天室的这段代码上

My question here is, especial for the linq2sql, and the up-coming dotnet version 4, what else can I do to speed it up ? What else on linq2sql settings, or classes, not on this code that I have used for my messures

我将进行测试的项目以及结果的屏幕截图放在这里.

I place here the project that I make the tests, also the screen shots of the results.

我仅对我的问题使用Google chrome，因为这对我来说很难显示我对较复杂的程序所做的许多其他测量.这是最简单的，我只是测量数据读取.我如何证明这一点.我做了一个简单的Thread.Sleep(10秒)，看看是否在Google Chrome Measure上看到了10秒钟，是的，我看到了.

I use only for my question Google chrome, because its difficult for me to show here a lot of other measures that I have done with more complex programs. This is the most simple one, I just measure the Data Read. How can I prove that. I make a simple Thread.Sleep(10 seconds) and see if I see that 10 seconds on Google Chrome Measure, and yes I see it.

_{(来源: planethost.gr )}

_{(source: planethost.gr)}

在此Sleep thead上进行更多测试，以了解Chrome实际提供的功能.

here are more test with this Sleep thead to see whats actually Chrome gives.

延迟10秒
延迟100毫秒
零延迟

只有很短的15ms会造成混乱，与我不关心的其余测试相比，它是如此之小.

There is only a small 15ms thats get on messure, is so small compare it with the rest of my tests that I do not care about.

我只测量通过每种方法读取的数据-不计算数据或数据库延迟，也不读取任何磁盘或类似数据.稍后在带有结果的图像上，我显示出度量上不存在磁盘活动
查看此图片以查看我实际测量的内容以及是否正确

I measure just the data read via each method - did not count the data or database delay, or any disk read or anything like that. Later on the image with the result I show that no disk activity exist on the measures
See this image to see what really I measure and if this is correct

这很简单，是真实的，在我的真实问题附近，我发现在具有真实数据的真实程序中亚音速3的延迟.

Its simple, it's real, and it's near my real problem that I found the delay of subsonic 3 in real program with real data.

从此图片开始 我对每种方法有4-5个调用，一个接一个.

Start by see this image I have 4-5 calls on every method, the one after the other.

结果是. 对于100次循环，大约需要5行，一个不存在.

The results are. For a loop of 100 times, ask for 5 Rows, one not exist, approximatively..

简单的网关: 81ms
SubSonic 2: 210ms
linq2sql: 1.70秒
使用CompiledQuery.Compile的linq2sql: 239ms
亚音速3: 15.00秒(哇-极慢)

Simple adonet:81ms
SubSonic 2 :210ms
linq2sql :1.70sec
linq2sql using CompiledQuery.Compile :239ms
Subsonic 3 :15.00sec (wow - extreme slow)

项目 http://www.planethost.gr/DalSpeedTests.rar

任何人都可以确认该基准测试，或进行任何优化以帮助我吗?

有人在这里发布此链接 http://ormbattle.net/(然后将其删除-不要知道为什么)在此页面中，您可以找到一个非常有用的高级测试，除了我在这里拥有的亚音速2和亚音速3之外！

Some one publish here this link http://ormbattle.net/ (and then remove it - don not know why) In this page you can find a really useful advanced tests for all, except subsonic 2 and subsonic 3 that I have here !

我真正要问的是，现在是否有人可以技巧如何优化DAL，而不是通过更改测试代码，而是通过更改每个dal的代码和设置. 例如...

What I really ask here is if some one can now any trick how to optimize the DALs, not by changing the test code, but by changing the code and the settings on each dal. For example...

我开始搜索如何优化Linq2sql并发现 这篇文章，也许还有更多内容.

I start search how to optimize Linq2sql and found this article, and maybe more exist.

最后，我将在该页面上运行技巧，并使用所有技巧优化代码.速度从1.70.接近了1.50秒.虽然有很大的进步，但仍然很慢.

Finally I make the tricks from that page to run, and optimize the code using them all. The speed was near 1.50sec from 1.70.... big improvement, but still slow.

然后我发现了不同的方式-相同的想法文章，哇！速度被炸毁. 将此技巧与CompiledQuery.Compile结合使用，现在从1.5秒开始的时间为239ms. 这是预编译的代码...

Then I found a different way - same idea article, and wow ! the speed is blow up. Using this trick with CompiledQuery.Compile, the time from 1.5sec is now 239ms. Here is the code for the precompiled...

Func<DataClassesDataContext, int, IQueryable<Product>> compiledQuery =
    CompiledQuery.Compile((DataClassesDataContext meta, int IdToFind) =>
                          (from myData in meta.Products
                           where myData.ProductID.Equals(IdToFind)
                           select myData));

StringBuilder Test = new StringBuilder();
int[] MiaSeira = { 5, 6, 10, 100, 7 };

using (DataClassesDataContext context = new DataClassesDataContext())
{
    context.ObjectTrackingEnabled = false;

    for (int i = 0; i < 100; i++)
    {
        foreach (int EnaID in MiaSeira)
        {
            var oFindThat2P = compiledQuery(context, EnaID);

            foreach (Product One in oFindThat2P)
            {
                Test.Append("<br />");
                Test.Append(One.ProductName);
            }
        }
    }
}

优化SubSonic 3和问题

我进行了许多性能分析，然后一个接一个地开始更改，速度虽然更好，但仍然太慢.我将它们发布在亚音速组中，但是他们忽略了这个问题，他们说一切都很快...

Optimizing SubSonic 3 and problems

I make many performance profiling, and start change the one after the other and the speed is better but still too slow. I post them on subsonic group but they ignore the problem, they say that everything is fast...

在亚音速源代码中捕获了我的配置文件和延迟点

最后，我发现subsonic3对数据库结构而不是数据本身进行了更多调用.需要重新考虑请求数据的漏洞，并在可能的情况下遵循subsonic2的想法.

I have end up that subsonic3 make more call on the structure of the database rather than on data itself. Needs to reconsider the hole way of asking for data, and follow the subsonic2 idea if this is possible.

尝试像在linq2Sql中一样对subsonic 3进行预编译，但是暂时失败...

Try to make precompile to subsonic 3 like I did in linq2Sql but fail for the moment...

当我发现亚音速3极慢时，我开始对亚音速2进行检查-在相信它很快之前我从未做过检查. (是)

After I discover that subsonic 3 is extreme slow, I start my checks on subsonic 2 - that I have never done before believing that is fast. (and it is)

因此，它提出了一些可以更快的观点.例如，有很多这样的循环，实际上由于字符串处理而很慢，并且在内部进行比较循环.我必须对您说，此代码被称为百万次！在几分钟的时间里！从程序询问的数据.

So its come up with some points that can be faster. For example there are many loops like this ones that actually is slow because of string manipulation and compares inside the loop. I must say to you that this code called million of times ! on a period of few minutes ! of data asking from the program.

在少量的表和小的字段上，对于某些人来说，这可能不是一个大想法，

On small amount of tables and small fields maybe this is not a big think for some people,

但是在大​​量表上，延迟甚至更大.因此，我自己决定并优化亚音速2，方法是将字符串比较替换为数字比较！简单的.我几乎在探查器说的很慢的每个点上都这样做.我还更改了所有可能更快一点的小问题，并禁用了一些不常用的想法.

but on large amount of tables, the delay is even more. So I decide and optimize the subsonic 2 by my self, by replacing the string compares, with number compares! Simple. I do that almost on every point that profiler say that is slow. I change also all small points that can be even a little faster, and disable some not so used thinks.

结果是，在NorthWind数据库上，速度提高了5％，在我的250个表数据库上，速度提高了近20％.在Northwind上，在10秒的处理过程中减少了500ms的时间，而在500ms的处理时间中，数据库上的速度提高了100ms.我没有要向您展示的截图，因为我用不同的代码，不同的时间制作了它们，并在纸上进行了追踪.

The results, 5% faster on NorthWind database, near 20% faster on my database with 250 tables. That is count with 500ms less in 10 seconds process on northwind, 100ms faster on my database on 500ms process time. I do not have captures to show you for that because I have made them with different code, different time, and track them down on paper.

无论如何，这是我的故事，也是我所有的问题，您还知道什么才能使它们变得更快...

为此，我使用了由我优化的Subsonic 2.2，由我优化的Subsonic 3.0.0.3和Dot.Net 3.5

For this measures I have use Subsonic 2.2 optimized by me, Subsonic 3.0.0.3 a little optimized by me, and Dot.Net 3.5

推荐答案

Aristos，这是我遇到的问题.您向我们的小组发布了一个问题，我们进行了长达23天的电子邮件交流.您坚持 SubSubonic 3出现问题，并且您问我为什么我没有修复此缓慢，损坏的工具".

Aristos, here's the thing I'm having an issue with. You posted a question to our groups and we had a nice, 23-long email exchange. You insisted that SubSonic 3 has problems and you asked me why I haven't fixed this "slow, broken tool".

我试图向您解释，有233个表，SubSonic必须在加载时读取EACH ONE的架构.这不是SubSonic 3的典型用法，但我们采用这种方式，因此在后续加载时实际上比SubSonic 2更快.

I tried to explain to you that, with 233 tables, SubSonic has to read the schema of EACH ONE on load up. This isn't typical usage for SubSonic 3, but we do it this way so it's actually FASTER than SubSonic 2 on subsequent loadups.

您正在对此感到厌恶.我在回答你. RBarry在上面的评论中说我被贬低"了.我很沮丧您似乎以为我还没有对这个东西进行基准测试，而且我已经很多次了.我无法证明ORM遇到您所建议的问题，而我没有.

You're ingoring this. I'm answering you. RBarry left a comment above that I was beind "denigrating". I'm quite frustrated. You seem to think that I haven't benchmarked this thing and I have, MANY MANY times. I can't turn out an ORM with problems like the one you suggested, and I didn't.

您必须了解，如果SubSonic运行查询花了10秒钟，那么它将不会被使用.

You have to understand that if it took SubSonic 10 seconds to run a query, then it wouldn't be used.

所以-您的答案:233个表(被编写为类)需要在第一次运行时加载到提供程序内存中.

So - your answer: 233 tables, scripted out as classes, need to load to Provider memory on first run.

太多了. SubSonic不是您的工具.

That's too many. SubSonic isn't your tool.

这篇关于基准Linq2SQL，Subsonic2，Subsonic3-还有其他想法可以使它们更快?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！