SQL Server与Access的插入性能比较，特别是在使用GUID时。

Question

我想了解如何在使用Access 2007作为前端连接SQL Server 2008时，使用顺序GUID来提高SQL Server性能（请注意，这是我唯一感兴趣的上下文）。

我进行了一些测试，并获得了一些非常令人惊讶的结果，特别是在使用顺序GUID时：插入性能非常快地下降，而且它似乎不应该如此快地降低。

基本上，测试如下：

从Access前端仅使用VBA，在1000个记录的批次中顺序插入100,000条记录。

我尝试了使用自增ID和顺序GUID作为主键。 我在SQL Server 2008 Standard上进行了尝试（没有特殊调整，只是默认安装），并将其与一个Access 2007数据库作为后端链接回前端的所有表连接起来。

其中一些结果（更多结果及原始数据可在我的博客文章中找到）：

很明显，随着数据库的增长，插入性能会降低，但SQL Server在这方面表现得并不好。

http://blog.nkadesign.com/wp-content/uploads/2009/04/chart02.png

SQL Server的结果扩展视图： http://blog.nkadesign.com/wp-content/uploads/2009/04/chart03.png

编辑13APR2009

我发现了一个服务器配置问题，并更新了我的博客上的测试。
感谢大家的回复，对我帮助很大。

- Renaud Bompuis

Answer 1

这里有两个因素。首先，需要指出的是，SQL并不一定适用于特定的使用情况，开箱即用效果可能不太好。它是一款专业产品，需要由知道自己在做什么的人进行调整。

相比之下，Access旨在在大多数情况下无需任何配置即可良好运行。这种权衡的缺点涉及第二个因素：

SQL Server旨在实现可扩展性。请注意，Access在仅有10万条记录时就会严重降级。在一百万条记录之前，它可能会比SQL的线更陡峭地下降。相比之下，SQL服务器保持几乎完美的稳定状态，在约45,000条记录后波动稳定，并将继续保持在数百万条记录处。

编辑：我认为这里还可能有其他因素我们没有看到。我认为你的SQL数字看起来很糟糕，所以我进行了自己的测试。在我的桌面上运行Windows Vista 3.6 GHz和2GB RAM时，使用顺序GUID在SQL Server上执行插入操作的结果如下：

• 在0条记录时，平均每秒插入1382条

• 在500k条记录时，平均每秒插入1426条

• 从0到500k平均每秒插入1609.6条，平均最小值为992条/秒，平均最大值为1989条/秒。

考虑到在使用中的桌面上运行会产生正常的差异，我认为SQL Server插入基本上是从0到50万线性扩展的。在专用的、调整过的服务器上，我期望会有更一致的表现（更不用说更好的性能）：

Excel图表，每秒插入量 http://img24.imageshack.us/img24/9485/insertspersecond.jpg

- Rex M

---

嗯，好的，我同意需要调整SQL Server，但是当你考虑使用GUID时，你所说的并不成立：它的性能下降得太快了。我们只谈论这里的100k条记录，但我无法以超过66条记录/秒的速度插入！ - Renaud Bompuis

@Renaud，请查看我的修订答案。 - Rex M

@le dorfier 这也是我的观点，正如我在编辑中所述。一定有什么问题 - 性能这么低是非常可疑的。 - Rex M

请看我的回答中的进一步评论。 - dkretz

我认为我的新测试有助于支持你的观点，David。 - Renaud Bompuis

Answer 2

你意识到性能下降的部分原因是日志已经填满，GUID比int长40个字节？

但我不想纠结这个问题；看到有人采取实际指标而不是空谈，这是好事。点赞。

- tpdi

---

GUID实际上是16个字节。我没有考虑日志文件，但它不应该对性能产生太大影响，特别是当使用整数或GUID时，就日志文件而言，应该没有真正的区别，因此问题必须来自其他地方。谢谢。 - Renaud Bompuis

Answer 3

你从哪里获取数据？

如果使用Access导出菜单选项而不是逐条记录循环，是否会更改数字？

VBA对连接参数非常敏感，有许多选项并不一定直观。

如果可以接受标识列，为什么还要考虑顺序GUID（据我所知，这是MSSQL中的一个附加设施）。

---

编辑： 查看您的代码并简要审查MSDN上的Recordset文档，我发现您可以使用更有效的参数。例如，您的dbSeeChanges和dbOpenDynaset，如果您想允许其他用户干扰相同的行（或需要获取插入的IDENTITY值或可能的GUID），则是适当的，但我认为您不需要它们。实质上，每次INSERT或UPDATE后，您都将记录从数据库读取回VBA。仔细阅读这些连接配置设置，我敢打赌你会得到更令人满意的结果。

- dkretz

---

数据是通过在记录集上进行简单的addnew/update循环即时创建的。连接参数只是基本的ODBC DSN，具有默认值。我需要GUID用于未来的开发（在多个数据库和偶尔连接的客户端之间进行同步）。 - Renaud Bompuis

a. 我会检查来自 @Denis 的 GUID 插入问题。 b. 然后检查一下你是如何处理你的 1000 行批量事务的。你可能没有得到你期望的事务控制。你尝试过每个事务只处理一行作为最坏情况吗？ - dkretz

实际上，你对记录集选项的理解是正确的。dbDynaset是必须的，如果使用IDENTITY列作为主键，则dbSeeChanges也是必须的。但是，为了提高性能，更好的选择是使用dbAppendOnly，因为我们在插入记录后不需要读取记录。 - Renaud Bompuis

我认为如果使用IDENTITY并且需要在插入后使用记录集（否则IDENTITY值将无法填充），那么这些标志是必需的。但是我认为您不需要将其取回。 - dkretz

Answer 4

我的问题是你的测试设置是否代表了你的应用程序的实际情况。简而言之，你是否正在测试正确的东西？

你的应用程序是否会一次追加大量记录？

还是基于 SQL SELECT 追加批量记录？

如果是后者，你可以考虑尝试在服务器端完成所有操作，特别是如果 SELECT 中的源表位于服务器上。需要意识到，在 ODBC 中，批量追加将被发送到 SQL Server 作为每一行的单个插入（类似于你的测试代码中基于记录集的方法）。如果你将相同的过程完全移至服务器端，则可以将其作为批处理操作完成。

此外，你应该尝试使用 ADO 而不是 DAO 进行测试。它可能会完全不同地优化操作。

最后，有人在上周向我提出了这篇非常有用的文章，作者是 Andy Baron： Optimizing Microsoft Office Access Applications Linked to SQL Server 我仍在吸收那篇非常有用的文章的内容，它讨论了几个与非 GUID 特定主题相关的问题，这些问题可能会帮助你优化你的过程以达到最大效率。

- David-W-Fenton

---

目前我只是试图了解什么影响性能。最初我想测试GUID插入，因为它被认为是最昂贵的操作。结果测试表明，记录集选项要重要得多！感谢这篇文章，它将对我有很大帮助。 - Renaud Bompuis

选择了这个答案，因为你提供的链接对我非常有用。 - Renaud Bompuis

这确实是一篇很棒的文章--我已经将它加入书签，并在过去的一周内多次返回阅读！ - David-W-Fenton

Answer 5

我上次看到类似的情况（GUID PK插入速度非常慢）是因为日志文件已满。插入性能急剧下降，非常快（没有硬测量，只是查看实时跟踪，但它看起来确实有点对数）。这是历史数据预加载之前的情况。

转换为identity PK后，确保清理了日志文件，之后一切都变得更好了（第一个版本需要几个小时才能完成，而现在只需要几个小时）。

另外，只是一个想法，是否涉及任何事务？也许SQL Server事务会对性能造成很大影响，而Access并不真正面向并发访问。

- Denis Troller