c#DataSet.Fillbird 2.5充满可怕的性能问题 [英] c# DataSet.Fill dreadful performance issues with Firebird 2.5

问题描述

备注，我在探索各种选择并获得见解时完全重写了这个问题，我意识到问题的根源根本不是我的想法.

REMARK I completely rewrite the question as while exploring options and getting insights, I realized the origin of my problem was not at all what I thought.

我将Firebird用作数据库引擎，并使用标准.Net提供程序(v.5.11.0)使用以下代码来获取数据:

I use Firebird as a database engine and the standard .Net provider (v.5.11.0) to fetch data using following code:

// myBlob1 is BLOB SUB_TYPE 1 (text field) with some empty, but some
// VERY long stuff (xml-content) which exceeds VARCHAR(32765), but I removed
// those before performing my tests!!!
var tick = Stopwatch.StartNew();
DataTable dataTable = new DataTable();      
DbLive.Open();
FbCommand command = new FbCommand("SELECT myBlob FROM MY_TABLE", DbLive);
try {
    dataTable.BeginLoadData();
    FbDataAdapter fda = new FbDataAdapter(command);
    fda.Fill(dataTable);
    dataTable.EndLoadData();
}
command.Dispose();
DbLive.Close();
tick.Stop();
Console.WriteLine("Execution time: " + tick.ElapsedMilliseconds + " [ms]");

根据数据库工作台的统计数据，我总共要获取约3万行数据(根据数据库工作台的统计数据)，但只有约2万行具有非空数据.

I am fetching about 30k rows with, in total, about 16Mb of data (according to database workbench's statistics) but only 20k rows with non-null data.

因此，我要使用幼稚的方法来获取整个内容，然后使用cast (VARCHAR(8192))方法(请注意，在执行测试之前，我删除了所有行号超过8192个字符的行).现在，结果如下:

So I go fetching the whole thing using naive approach, then I used the cast (VARCHAR(8192)) approach (note that I removed all rows with > 8192 characters before doing the tests). Now, here are the results :

// Obtained when loading data over wifi, with a bandwidth of about 100Mbps)
// the performance on local machine did not make a big difference!)
No casting:  73287.0788 ms
With casting: 2360.2244 ms

在这里，Blobs确实存在一些问题.我使用Firebird 3和压缩功能测试了性能，但结果并没有好得多(虽然改进很小，但是差异仍然在相同的数量级上.)

There really is something bad going on with Blobs here. I tested performance using Firebird 3 and compression, but results weren't much better (a very small improvement, but difference is still of the same order of magnitude).

要点: 1.如果我使用其他提供程序(例如，建议使用www.ibprovider.com，但我无法对其进行测试)，我可以期望得到改善吗? 2.这是其他数据库引擎上的已知问题，还是希望通过更改为其他引擎来获得改进?

To the point: 1. Can I expect an improvement if I use another provider (e.g. www.ibprovider.com as suggested, but I was not able to test it) 2. Is this a known issue on other database engines, or is there hope to get improvements by changing to another engine?

推荐答案

我对以上内容有些困惑，所以让我澄清一些见解:

I got a bit confused about many things with the above, so let me clarify some insights:

实际上，

1. FbDataReader和Dapper方法不会加载blob字段，因此性能确实无关紧要；

1. FbDataReader and the Dapper approaches did, in fact, NOT load the blob fields, so performance was indeed irrelevant;

使用CAST到VARCHAR(4096)确实显示出显着的性能改进(请注意，在这里，我测试了text-blob的内容是否完全匹配，这确实有效):

Using the CAST to VARCHAR(4096) did show significant performance improvements (note that here, I tested the text-blob's content to fully match, which did work):

数据表未强制转换:73287.0788毫秒

DataTable no cast: 73287.0788 ms

FbDataReader投射:2224.1387毫秒

FbDataReader cast: 2224.1387 ms

DataTable类型转换:2360.2244 ms

DataTable cast: 2360.2244 ms

我什至尝试使用Firebird 3进行压缩，但没有明显改善.因此，确实是我使用BLOB而不是大的VARCHAR导致问题的事实.

I even tried with Firebird 3 using compression, without significant improvement. So it is indeed the fact I use BLOB instead of large VARCHAR that caused the problem.

请注意，我没有与其他提供商进行测试.

Note that I did not succeed in testing with another provider.

最重要的是:在这里使用BLOB SUBTYPE 1会破坏我的表现，我应该改用VARCHAR.

Bottom line : using BLOB SUBTYPE 1 kills my performance here, I should use VARCHAR instead.

这篇关于c#DataSet.Fillbird 2.5充满可怕的性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！