代码之家 › 专栏 › 技术社区 › dummzeuch Stijn Sanders

如何将800000条记录插入MS Access表?

delphi-2007 ado ms-access delphi

dummzeuch Stijn Sanders · 技术社区 · 15 年前

我需要在MS访问表中插入800000条记录。我使用的是Delphi2007和 TAdoXxxx 组件。该表包含一些整数字段、一个浮点字段和一个只有一个字符的文本字段。其中一个整数字段(不是autoinc)上有一个主键,另一个整数和float字段上有两个索引。

插入数据时使用 AdoTable.AppendRecord(...) 花费10分钟,这是不可接受的,因为每次用户开始使用程序中的新数据库时都会这样做。我不能预先填充表,因为数据来自另一个数据库(不能通过 ADO )

我把记录写到一个以制表符分隔的文本文件中,然后使用 tAdoCommand 要执行的对象

insert into table (...) select * from [filename.txt] in "c:\somedir" "Text;HDR=Yes"

但我不喜欢这个的开销。

我想一定有更好的办法。

编辑:

一些附加信息:

之所以选择MS Access,是因为它不需要在目标计算机上进行任何其他安装,并且整个数据库包含在一个文件中,该文件可以轻松复制。
这是一个单用户应用程序。
数据将只插入一次,并且在数据库的生存期内不会更改。但是,该表包含一个附加字段,用作标志,指示另一个数据库中的相应记录已由用户处理。
一分钟是可以接受(最多3分钟)并且我的解决方案可以工作,但对我来说似乎太复杂了,所以我认为应该有一种更简单的方法来做到这一点。
一旦插入数据,表的性能就相当好。
当我开始计划/实现使用Access数据库的程序功能时,不需要表。后来,当客户请求另一个功能时,才有必要使用它。(这不总是这样吗?)

编辑:

从迄今为止我得到的所有答案来看,似乎我已经找到了将这么多数据插入访问表的最快方法。谢谢大家,谢谢你们的帮助。

16 回复 | 直到 9 年前

Fabricio Araujo 14 年前

您的基于文本的解决方案似乎是最快的,但如果您可以获得一个预先分配的MS访问,在接近结尾的大小,您可以更快地得到它。您可以通过填充一个典型的用户数据库、关闭应用程序(以便刷新缓冲区)和手动删除该大表的所有记录来完成这项工作,但不能收缩/压缩它。

因此,使用该文件开始真正的填充-访问不会请求任何(或很少)额外的磁盘空间。不记得MS Access是否有自动实现这一点的方法,但它可以帮助很多…

JosephStyons 15 年前

既然您说过800K记录的数据在数据库的生命周期内不会改变,那么我建议将文本文件作为一个表链接,并跳过插入操作。

如果您坚持将其拉入数据库,那么一分钟内800000条记录超过13000/秒。我不认为你会在微软Access中打败它。

如果您希望它对用户更具响应性,那么您可能需要考虑加载一些最小的数据集,并设置一个后台线程来在其他线程工作时加载它们。

Community Egal 7 年前

如果没有索引,速度会更快。导入后可以添加吗?

有许多建议可能对此线程感兴趣 Slow MSAccess disk writing

Marc Gravell 15 年前

跳过文本文件并使用ODBC或OLEDB直接从源表导入会怎么样?这意味着要更改FROM子句以使用源表名和适当的连接字符串作为FROM子句的in“”部分。

编辑: 实际上,我看到你说的原始格式是XBase,所以应该可以使用XBase ISAM,它是Jet的一部分,而不需要ODBC或OLEDB。看起来像这样:

INSERT INTO table (...) 
SELECT * 
FROM tablename IN 'c:\somedir\'[dBase 5.0;HDR=NO;IMEX=2;];

您可能需要对此进行调整——我只是获取了指向DBF文件的链接表的连接字符串,因此参数可能略有不同。

pfunk 15 年前

另一种安排怎么样…

是否可以选择复制现有的具有此表的Access数据库文件,然后只删除其中除此大表之外的所有其他数据(不知道Access是否与SQL Server中的“truncate table”类似)?

Mohammed Nasman 15 年前

我会用另一个数据库替换MS Access,我看到了 Sqlite 是最好的选择,它不需要任何安装到客户机,它是非常快速的数据库和最好的嵌入式数据库解决方案之一。

您可以通过两种方式在Delphi中使用它:

可以从下载数据库引擎dll SQLite 网站和使用免费的Delphi组件访问它 Delphi SQLite components 或 SQLite4Delphi
使用 DISQLite3 它内置了引擎,您不必将DLL与应用程序一起分发,它们有一个免费的版本;-)

如果仍然需要使用MS Access,请尝试直接使用tadocommand和sql insert语句,而不是使用tadotable,这应该比使用tadotable.append更快;

Community Egal 7 年前

正如有人提到的,您不会在一分钟内导入80万条记录;这是 真的? 已经快了。

但是,如果使用正确的方法(DAO记录集)进行插入,则可以跳过恼人的“转换为文本文件”步骤。请参阅我在StackOverflow上提出并回答的前一个问题: MS Access: Why is ADODB.Recordset.BatchUpdate so much slower than Application.ImportXML?

即使使用刀也不要使用insert-into,因为它很慢。也不要使用ADO;它很慢。但是直接(而不是通过access.application对象)实例化dbengine com对象的dao+delphi+recordsets+会给你很大的速度。

Yes - that Jake. 15 年前

你从一个方面看的是正确的方向。使用单个语句进行大容量插入要比尝试遍历数据并逐行插入快。作为一个基于文件的数据库,访问在迭代写操作中会非常缓慢。

问题是,Access正在处理如何在内部优化写操作,实际上没有任何方法来控制它。您可能已经达到了insert语句的最大效率。为了提高速度,您应该在每次启动应用程序时评估是否有任何方法可以将800000条记录写入数据库。

Diodeus - James MacFarlane 15 年前

得到 SQL Server Express (空闲)并通过访问外部表连接到它。SQL Express比MS Access快得多。

Adam Davis 15 年前

我将预先填充数据库,并将文件本身交给它们,而不是填充现有(但为空)的数据库。

如果必须填充更改的数据,则使用少量代码在服务器上保持ODBC Access数据库(mdb文件)同步,以查看主数据库中的更改并将其复制到Access数据库。

当用户请求一个新的数据库压缩MDB时,将其传输给他们,然后打开它。

或者,您可以找到直接打开数据并将数据插入数据库的代码。

或者,您也可以找到另一种格式(而不是csv),访问可以更快地导入。

-亚当

MatthewMartin muthu 15 年前

还要检查复制文件需要多长时间。这将是数据写入速度的下限。在与SQL类似的数据库中,通常需要大容量加载实用程序才能接近这个速度。据我所知,MS从未像BCP那样创建直接写入MS访问表的工具。专门的ETL工具还将优化围绕插入的一些步骤,例如ssis在内存中进行转换的方式,dts也有一些优化。

Tmdean 15 年前

也许您可以用锁模式打开ADO记录集到表中。 adLockBatchOptimistic 和光标位置 adUseClient ,将所有数据写入记录集,然后执行批更新( rs.UpdateBatch )

dkretz 15 年前

如果它来自数据库,您可以复制数据和索引文件,直接附加而不加载吗?应该是相当有效的(来自那些给你带来FoxPro的人),我想它也会使用现有的索引。

至少,它应该是一个非常有效的单命令导入。

Toby Allen mercator 15 年前

80万条记录从一个创建到下一个创建的变化是多少?在创建新数据库时,是否可以预先填充记录,然后只更新外部数据库中已更改的记录?

这可以使您更快地创建新的数据库文件。

Walter Mitty 15 年前

你的磁盘转动有多快?如果是7200rpm,那么3分钟内800000行仍然是每盘旋转37行。我认为你不会做得更好。

同时,如果目标是简化流程,那么表链接呢?

你说你不能通过ADO访问源数据库。可以在MS Access中设置到源数据库中的表或视图的表链接吗?然后,来自表链接的简单追加查询将数据从源数据库复制到目标数据库。我不确定,但我想这会很快。

如果在运行时之前无法设置表链接,可能可以通过ADO以编程方式生成表链接,然后以编程方式生成追加查询,然后调用追加查询。

-3

tiphooo 14 年前

夏威夷群岛最好的方法是按他们所说的从TXT文件批量插入您应该将记录插入到txt文件中,然后将txt文件大容量插入到表中。该时间应小于3秒。