Ace.12.0很难杀死我 [英] Ace.12.0 is very slow to kill me

问题描述

我正在使用Microsoft.Oledb.Jet.4.0处理openrowset。它在32位系统上运行良好。但是我已经使用Windows Server 2012 R2将系统更新到64位版本的SQL Server 2014群集服务器。



因为找不到Jet.4.0的64位版本我安装了Ace.12.0 64位版本。之后我发现查询执行时间显着减慢。我的DELL服务器配置是16核处理器，具有128 GB的RAM 2节点。带有SAN交换机的9 TB可用存储空间。



执行查询时，我发现内存使用率为93-97％，但处理器使用率为2-3％。



另一件事是我已将Access.mdb更新为Access.accdb。在Access.accdb上执行查询比在Access.mdb上花费更多时间。



对于包含Access.mdb和Access.accdb的相同数据，至少5秒执行时间。我必须在每个分支的100个表上执行查询，无论我有3000个分支。这意味着如果每张桌子需要5秒钟，我必须浪费100 * 3000 * 5 = 25000分钟。 Ace 12.0和Jet.4.0之间存在相同的差异。它杀了我。



请帮助我任何人。



我尝试过：



另一件事是我已将Access.mdb更新为Access.accdb。在Access.accdb上执行查询比在Access.mdb上花费更多时间。

I am working with openrowset using Microsoft.Oledb.Jet.4.0. It works fine on 32 bit systems. However I had updated system to 64 bit version of SQL Server 2014 cluster server with Windows Server 2012 R2.

Since there are no 64 bit version found for Jet.4.0 I have installed Ace.12.0 64 bit version. After that I found query execution time slow down dramatically. My DELL server configuration is 16 core processor with 128 GB of RAM 2 nodes. 9 TB usable storage with SAN switch.

When queries are executing I found 93-97% usage of RAM but processor usage is 2-3%.

Another thing is that I have updated my Access.mdb to Access.accdb. It takes more time to execute queries on Access.accdb than Access.mdb.

For same data containing Access.mdb and Access.accdb very 5 seconds execution time at least.I have to execute queries on 100 tables for each branch wherever I have 3000 branches. It means if each table takes 5 seconds more I have to waste 100*3000*5=25000 minutes. The same difference found between Ace 12.0 and Jet.4.0. It is killing me.

Please help me anybody.

What I have tried:

Another thing is that I have updated my Access.mdb to Access.accdb. It takes more time to execute queries on Access.accdb than Access.mdb.

推荐答案

如果你设置了一个SQL Server集群，那么想要它很奇怪直接使用Access进行任何操作，更不用说大规模数据库了。 Access基本上是单个用户，单一访问，小型系统数据库 - 当你尝试扩展时，它并不是很好。

如果你有SQL Server然后改变你的代码和使用直接使用SQL - 完全删除Access内容并迁移数据库。它更简单，几乎肯定更快，因为如果没有别的DB文件和处理它的软件在同一台PC上 - 服务器。当您使用Access时，即使文件位于您的服务器上，数据库引擎也会在您的PC上运行 - 因此每次使用时都必须通过网络传输文件详细信息。目前，使用16核和128 GB RAM的服务器被用作美化文件存储......：笑：

If you have an SQL Server cluster set up, then it is very odd to want to use Access directly for anything, let alone for a large scale DB. Access is basically a single user, single access, small system database - and it isn't very good at all when you try to expand beyond that.
If you have SQL Server then change your code and use SQL directly - drop the Access stuff completely and migrate the DB. It's a lot simpler and almost certainly faster, since if nothing else the DB file and the software processing it are on the same PC - the server. When you use Access, the DB engine runs on your PC even if the file is on your server - so the file detail has to be transfered over your network each time it's used. At the moment, your server with it's 16 cores and 128 GB of ram is being used as a glorified file store... :laugh:

这篇关于Ace.12.0很难杀死我的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！