BCB环境下，Dbisam与通过Dao访问的Access 数据记录速度对比(50分)

印象里Dbisam是一个比较快速的数据库引擎，而且还获得了2002年度的Delphi
Information的数据库引擎类评比第一名。以前印象里也看到过有他的速度快捷的
宣传，但近来上他的网站上查了一下，没发现有这方面的内容。
只好自己做了一个测试对比。对比的对象是使用Dao访问的Access97格式数据
库，新建项目，各自放一个Query、Session、Database，对应属性设置成一样。
Access使用ODBC设置好DatabaseName。测试是这样的，通过点击按钮，对各自格
式的一个有220个字段表进行记录，各自记录一百条。字段有日期、数据值等，记
录使用SQL语言写成，主要部分如下：
qry->Close();
qry->SQL->Clear();
para_text = "INSERT INTO TEST1 (DBDATE, DBTIME_MARK ";
text = "VALUES a1, :a2 ";

for(i=0;i<220;i++)
{
para_text += " ," + tag;
text += " ," + AnsiString(i);
}

para_text += ") ";
qry->SQL->Add(para_text);
text += ") ";
qry->SQL->Add(text);

qry->Params->Items[count++]->AsDateTime = db_date;
qry->Params->Items[count++]->AsInteger = 1;
for(i=0;i<220;i++)
{
qry->Params->Items[count++]->AsFloat = Now();
}
qry->ExecSQL();

然后对比总时间。结果是：
主机CPU(256MB内存) AMD Athon 700 AMD AthonXP1600＋
Dbisam测试结果 3分27秒 1分34秒
Access测试结果 3分19秒 1分26秒
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
怎么样？这个结果很让我失望。本来打算换到Dbisam平台上的，但是这个
性能问题...

你的测试代码大大地有问题,得出的结果当然是错误的...DBISAM的速度比Access快N倍是事实

因为正在使用Access97作为数据库，对他的反应速度和数据库文件容量问题比较关心。
上面测试代码取自旧程序，一直能正常工作。
除了Query一个是TQuery，一个是TDBISAMQuery之外，两种引擎使用了完全相同的代码。
事实上，点击按钮做完一个测试后，再点击另一个按钮测另一种引擎而已，这是很容易的事
，大家自己可以做一下验证。我想要的就是大数据量的SQL的数据记录性能，这个指标对仪
器仪表行业应用很重要！
附带说明一下，Dbisam使用的是3.19 C／S版本，Access使用的是97版本，ODBC里面的
Access驱动则是比较新的（装了OfficeXP）。
这个测试的目的在于：是否有必要装换到Dbisam，还是升级到2000版？
如果这个测试能说明问题，那么：Access具有不差的访问速度，驱动引擎使用
方便，寻找容易。新版本的2000／2002使用ADO在开发工具方面支持得不错，还部分
克服了大数据量的问题，可以支持<＝2GB的数据库文件，一定程度上改善了97版只
支持1GB数据库文件的问题，还支持了XML、SQL Sever集成之类的新玩意。使用多年，
稳定性还可以。
那么，Dbisam这个产品的定位在哪里呢？欢迎高手指教！

忘记说明：上述两种格式的数据库中，表的格式一一对应定义相同，以便
对应相同的SQL访问语句。并都有一个主字段索引。

尝试调节Dbisamde若干参数，想提高测试速度。但没有明显结果。观察得到
的现象就是：Dbisam记录的时候硬盘比较有规律但频繁的闪烁；而Access记录的
时候是隔一会才动作的，看起来微软的东西利用OS效率更好一些。

这里只反映了数据库记录的速度，当然不能代表一切。数据库性能指标
应当是多方面的。比如稳定性、多用户访问、与其它系统接口、驱动程序等
都很重要。
没甚么人跟帖。是不是错发到“数据库-文件型 ”分类了 ？

我以前试过,如果用dao+access相比,dbisam的速度还要慢一些的.
dbisam的存储效率也不好,我个人觉得没有什么必要用这个引擎.

记录速度冠军！快到不可思议！！
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
再次做了一个测试，使用BCB自带的ADO控件：TADOConnect和TADOQuery，使用与上面
完全相同的数据库、表、程序和SQL语句，记录速度测试结果如下：
主机CPU(256MB内存) AMD Athon 700
Dbisam测试结果 3分27秒
ODBC+Access测试结果 3分19秒
ADO+Access测试结果 12秒

没错！12秒！
TADOConnect的ConnectString如下：
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/data/Test1.mdb;Mode=ReadWrite;Persist Security Info=False

再次确认，在另外一台AMD Athon 1600＋的计算机上，100条记录的ADO保存
速度可达7秒！

你的测试我感觉是对的,我在实践中发觉DBISAM速度也不是很快,特别是有些SQL语句需要
进行优化,否则速度慢的要命.
不过如果你想知道为什么DBISAM比ACCESS还慢,建议你将这篇文章简化成英文,发到DBISAM
开发者的论坛上去,也许会有答案,开发者们回答问题还是积极的,说不定能刺激他们改进
DBISAM,因为如果比不上access,他们的日子就难过了.
http://www.elevatesoft.com/scripts/viewmsgs?group=5&page=1
论坛上某开发者的比较:
I'm in the process of converting one application to from
Access to DBISAM now, and actively using it on and off
for single user applications, so I can speak to that.

In particular, I use DBISAM or Access for fairly small
sized applications consisting of:
-- half dozen main tables with from a few thousand rows
to no more than 20,000.
-- another half dozen secondary tables with no more than
a thousand rows
-- a dozen small ancillary/lookup tables with a few to a
few hundred rows.

I have been using Access with ODBCExpress. I did not
like the Access/ADO combination.

So, In a quick jumble of thoughts here are my comments.

Access is faster by about 20-30% for my queries.
However, I've made little attempt to optimize as with
so few rows both solutions are plenty fast.

Both products have little gotchas ... things that crop
up along the way that you don't expect to spend time
working on but take more time than creating the DB code
itself. In Access, once you've found a workaround it
tends to be something you can then push into a library
and forget. With DBISAM they change as point releases
are posted.

Access provides a much better interface during the
development process. You can model your database with
their built in relationship view. DBISAM does not
provide a tool for this.

In MS Access you can build, test, and save queries in
the .mdb file and use the queries as views to simplify
development. DBISAM has a similar capability in it's in
memory table. However, the Access queries are really
equivalent to views whereas in DBISAM they are
snapshots of the database.

DBISAM does not have built in referential integrity. I
thought this would bother me as I use it so freely in
other DBs, including Access, but in practice this hasn't
been a handicap for me.

Access stores all tables, indexes, queries, etc. in a
single .mdb file. I really like this for my users and is
one of my only complaints with DBISAM. But to each his
own on deciding if this is an issue or not.

DB corruption. I've seen a number of posts about DBISAM
tables/indexes getting out of synch. I remember this from
the old Clipper/DBase days with dread having spent way too
much time trying to talk users through recovery steps.
Gladly, I've yet to have this happen with one of my apps.
However, I don't even hear of it with Access. And it still
weighs on my mind with DBISAM.

Finally, installation. I'm currently porting another
application from Access to DBISAM because supporting all
the variations in drivers for Access has become too much
of a pain for apps targeted toward novice users.

So, overall I'd say I like working with Access better
during the development process and I like that Access
is single file based and has views but I am switching
apps and will be developing new ones because it works
just as well and has no deployment issues at all.

BTW: Switching between Access and DBISAM is non-trivial
with changes needed in too many places to make designing
in Access and deploying in DBISAM a realistic route for me.

Hope this helps.
--Robert Gesswein

再补充一个,不同SQL语句下速度的对比.
>SELECT *
> FROM std_base
> WHERE (((std_base.BE_BIK) Like '19%') AND ((std_base.BE_PRV)='NH' Or
> (std_base.BE_PRV)='ZH') AND ((std_base.BE_RVM)=2 Or (std_base.BE_RVM)=7));
>
> Access runs within the sec, DBISAM takes 3 secs
> Now this query which returns a result of nearly 46000 records:
>
> SELECT *
> FROM std_base
> WHERE (((std_base.BE_PRV)='NH' Or (std_base.BE_PRV)='GL' Or
> (std_base.BE_PRV)='UT' Or (std_base.BE_PRV)='ZH') AND ((std_base.BE_KWP)=5
> Or (std_base.BE_KWP)=6 Or (std_base.BE_KWP)=7 Or (std_base.BE_KWP)=10 Or
> (std_base.BE_KWP)=11));
>
> Access: way onder 1 sec (The timing label doesn't show miliseconds so I
> don't know exactly)
> DBISAM: 5 secs
>
> The difference is not much but still Access wins on every single query!

Both timings look a bit (very) slow to me. These are some comparative 'live'
result set figures I just did with Access using TADOQuery:

=============================================
DBISAM Query Benchmark Utility V0.7
07/09/2000 16:06:47 Record Count On
RequestLive On - Record Range: 5000
----------------------------------------------------------------------------
----
Query MS Access ADO Driver DBISAM V2 Driver
--------- --------------------------------- --------------------- ----------
-----
1 1 in 34 ms. 1 in 13 ms.
2 5001 in 255 ms. 5001 in 20 ms.
3 69 in 30 ms. 69 in 25 ms.
4 80 in 48 ms. 80 in 25 ms.
5 175 in 36 ms. 175 in 29 ms.
----------------------------------------------------------------------------
----
Total 403 ms. 112 ms.

....as you can see, neither engine came anywhere near taking a whole second
to complete any of the queries (you can find the SQL involved at
http://www.elevatesoft.com/benchmrk.htm BTW). Increase the average result
set size by a factor of 10 and I get:

=============================================
DBISAM Query Benchmark Utility V0.7
07/09/2000 16:19:00 Record Count On
RequestLive On - Record Range: 50000
----------------------------------------------------------------------------
----
Query MS Access ADO Driver DBISAM V2 Beta Driver
-------- -------------------------------- --------------------------------
-----
1 1 in 24 ms. 1 in 14 ms.
2 50001 in 2359 ms. 50001 in 403 ms.
3 679 in 79 ms. 679 in 70 ms.
4 769 in 96 ms. 769 in 72 ms.
5 1756 in 108 ms. 1756 in 82 ms.
----------------------------------------------------------------------------
----
Total 2666 ms. 641 ms.

....still nowere near as slow as you're seeing.

Obviously the main difference is that your example uses many more WHERE
conditions than any of ours ....I'll see if I can cook up a similar level of
AND/OR against the tables I'm using.

我觉得 DBISAM 和其它的类似的数据库的定位都是作为 BDE 的替代，这方便的软件的发布，
因为DBISAM不需额外的运行库。

另外，你也可以试一试 TurboPower 的 FlashFiler 2 ，我做单机数据库应用程序一般都是用它
（看重它方便的可自定义加密系统，这对单机系统是非常重要的），网络则用 MS-SQL 2000。

bkfx, FlashFler 哪里有下载呢? 它是像Access一样所有的表都存在一个文件里面吗?
支持SQL吗?

有人作过数据读取速度测试吗？

当你还在做测试的时候，别人已经把系统做好了。[]

哪位兄弟即用过Dbisam，又用过TurboPower 的 FlashFiler

相比之下，哪个更好些？

TurboPower 的 FlashFiler 下载地址：
http://asp.5135.com.cn/0228c/MyDown/sort.asp?zhuid=17

谢谢大家的参与，这个讨论只是要寻找一个更快、占用CPU更少的本地数据库驱动。
我认为这个结果对大家可能有用，所以提出来交流一下。李维的那本ADO书最近翻了一
下，内容当然是全面的了，但没有发现这方面的讨论。
如果只顾上做项目赚钱，当然用不着在这里费口舌。发出来只希望对大家的工作有所
帮助，就象这里的帖子曾对我的帮助一样。
特别感谢ZHTX的参与，使我们能够了解到更多！
结束吧。