如何使用SQL后端加速Access数据库。 [英] How can I speed up an Access database with SQL backend.

问题描述

这是一个场景。我们有一个包含患者病例信息的数据库。

此数据库以前仅用于Access。问题是我们

用户从大型网络中的位置

连接到此数据库（位于加利福尼亚州）。加州到弗吉尼亚州当然，东部海湾地区的运行速度非常慢。我们决定使用SQL后端迁移到

Access前端。该数据库包含几个

表格，每个表格中有多个子表格。在某些情况下，子表格

具有相当大的查询作为其记录集。因此，我们严格在Access中的recordset属性值中使用

SELECT语句。这似乎是b / b
成为数据库的瓶颈。我已尝试使用Pass Through查询存储过程

调用并将它们设置为记录集但是

这些表单需要可编辑且返回记录集不是

可编辑。

我的问题是，是否有更有效的方法可以在这些表单中获得可编辑的

记录集。速度是我们主要关心的问题

Here is the scenario. We have a database with patient case information.
This database was previously solely used in Access. The problem is our
users connect to this database (located in California) from locations
across a large network. California to Virginia. Naturally the east
coast locations were running very slow. We decided to migrate to an
Access front end with an SQL backend. The database contains several
forms with multiple sub-forms in each. In some instances the sub-form
has a fairly large Query as its recordset. As is, we are strictly using
SELECT statements in the recordset property value in Access. This seems
to be the bottleneck of the database. I have tried Store Procedure
calls using Pass Through queries and set them as the recordset but
these forms need to be editable and the return recordset is not
editable.
My question is, Is there a more efficient way to get an editable
recordset into these forms. Speed is our main concern

推荐答案

Ma ************* @ gmail.com 写道：

这是场景。我们有一个患者病例数据库

信息。此数据库以前仅在Access中使用。

问题是我们的用户连接到这个数据库（位于加利福尼亚州）

来自大型网络的位置。加利福尼亚州到弗吉尼亚州。

当然，东海岸地区的运行速度非常慢。我们决定将

迁移到带有SQL后端的Access前端。数据库

包含多个表单，每个表单中有多个子表单。在一些

实例中，子表单具有相当大的Query作为其记录集。由于

是，我们严格使用记录集属性中的SELECT语句

值。这似乎是数据库的瓶颈。我使用Pass Through查询尝试了Store Procedure调用并设置了

它们作为记录集但是这些表单需要可编辑并且

返回记录集是不可编辑的。

我的问题是，是否有更有效的方法可以在这些表单中获得可编辑的

记录集。速度是我们主要考虑的因素

Here is the scenario. We have a database with patient case
information. This database was previously solely used in Access. The
problem is our users connect to this database (located in California)
from locations across a large network. California to Virginia.
Naturally the east coast locations were running very slow. We decided
to migrate to an Access front end with an SQL backend. The database
contains several forms with multiple sub-forms in each. In some
instances the sub-form has a fairly large Query as its recordset. As
is, we are strictly using SELECT statements in the recordset property
value in Access. This seems to be the bottleneck of the database. I
have tried Store Procedure calls using Pass Through queries and set
them as the recordset but these forms need to be editable and the
return recordset is not editable.
My question is, Is there a more efficient way to get an editable
recordset into these forms. Speed is our main concern

您需要更改为一个系统，用户指示他们需要的记录是什么，然后检索一个记录编辑。如果ONE不可能

那么你使用的标准将拉动执行

工作所需的绝对最小值。一次一个地拉出子表单的记录，并且仅在需要

时。


带有SomeBigNumber的记录1的标准访问表单在导航栏中

不是通过WAN连接的方式。


-

Rick Brandt，微软访问MVP

电子邮件（视情况而定）至...

来自Hunter dot的RBrandt

You need to change to a system where the user indicates what ONE RECORD they
need and then retrieve that one record for editing. If ONE is not possible
then you use criteria that will pull the absolute minimum required to do the
job. Pull the records for the subforms one at a time and only when
required.

Standard Access forms with "Record 1 of SomeBigNumber" in the navigation bar
are just not the way to go over a WAN connection.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Ma ************* @ gmail.com 写道：

这是一个场景。我们有一个包含患者病例信息的数据库。

此数据库以前仅用于Access。问题是我们

用户从大型网络中的位置

连接到此数据库（位于加利福尼亚州）。加州到弗吉尼亚州当然，东部海湾地区的运行速度非常慢。我们决定使用SQL后端迁移到

Access前端。该数据库包含几个

表格，每个表格中有多个子表格。在某些情况下，子表格

具有相当大的查询作为其记录集。因此，我们严格在Access中的recordset属性值中使用

SELECT语句。这似乎是b / b
成为数据库的瓶颈。我已尝试使用Pass Through查询存储过程

调用并将它们设置为记录集但是

这些表单需要可编辑且返回记录集不是

可编辑。

我的问题是，是否有更有效的方法可以在这些表单中获得可编辑的

记录集。速度是我们主要考虑的问题

Here is the scenario. We have a database with patient case information.
This database was previously solely used in Access. The problem is our
users connect to this database (located in California) from locations
across a large network. California to Virginia. Naturally the east
coast locations were running very slow. We decided to migrate to an
Access front end with an SQL backend. The database contains several
forms with multiple sub-forms in each. In some instances the sub-form
has a fairly large Query as its recordset. As is, we are strictly using
SELECT statements in the recordset property value in Access. This seems
to be the bottleneck of the database. I have tried Store Procedure
calls using Pass Through queries and set them as the recordset but
these forms need to be editable and the return recordset is not
editable.
My question is, Is there a more efficient way to get an editable
recordset into these forms. Speed is our main concern

您是否已将访问数据库拆分为前端并返回

结束。如果不是那么首先。


其次，在任何网络上运行速度更快的方法是减少网络流量。
减少网络流量。换句话说，使用数据

选择减少主要和/或
子查询中查询的数据量。


第三，Access数据库在WANS上运行不正常，因为如果链接失败可能会导致损坏。


我想知道数据库复制是否适用于您？关键是

必须为所有用户提供更新的速度以及同时更新相同数据的

潜力。


关于你在做什么的更多信息会有所帮助。


Bob

Have you already split your access database into a front end and back
end. If not do that first.

Second, the way to make things run faster across any network is to
reduce the amount of network traffic. In other words, use data
selection to reduce the quantity of data being queried in main and/or
sub queries.

Thirdly, Access databases do not work well over WANS because of the
potential for corruption if the link fails.

I wonder if database replication could work for you? The key would be
the speed in which updates must be available to all users and the
potential for concurrent updating of the same data.

More info on what you are doing would be helpful.

Bob

对于纯粹的速度，我会沟通在服务器上访问并使用ASP / ASP.NET托管

。这将是您最快的解决方案，但您的设置成本

会更高一些。如果数据库只有几种形式，那么

应该会降低成本。

Chris Nebinger
Ma ************* @ gmail.com 写道：

For purely speed, I would ditch Access and go with ASP/ASP.NET hosted
on a server. That would be your fastest solution, but your setup costs
will be a bit higher. If the database is only a couple of forms, that
should keep costs down.
Chris Nebinger
Ma*************@gmail.com wrote:

这是一个场景。我们有一个包含患者病例信息的数据库。

此数据库以前仅用于Access。问题是我们

用户从大型网络中的位置

连接到此数据库（位于加利福尼亚州）。加州到弗吉尼亚州当然，东部海湾地区的运行速度非常慢。我们决定使用SQL后端迁移到

Access前端。该数据库包含几个

表格，每个表格中有多个子表格。在某些情况下，子表格

具有相当大的查询作为其记录集。因此，我们严格在Access中的recordset属性值中使用

SELECT语句。这似乎是b / b
成为数据库的瓶颈。我已尝试使用Pass Through查询存储过程

调用并将它们设置为记录集但是

这些表单需要可编辑且返回记录集不是

可编辑。

我的问题是，是否有更有效的方法可以在这些表单中获得可编辑的

记录集。速度是我们主要考虑的因素

Here is the scenario. We have a database with patient case information.
This database was previously solely used in Access. The problem is our
users connect to this database (located in California) from locations
across a large network. California to Virginia. Naturally the east
coast locations were running very slow. We decided to migrate to an
Access front end with an SQL backend. The database contains several
forms with multiple sub-forms in each. In some instances the sub-form
has a fairly large Query as its recordset. As is, we are strictly using
SELECT statements in the recordset property value in Access. This seems
to be the bottleneck of the database. I have tried Store Procedure
calls using Pass Through queries and set them as the recordset but
these forms need to be editable and the return recordset is not
editable.
My question is, Is there a more efficient way to get an editable
recordset into these forms. Speed is our main concern

这篇关于如何使用SQL后端加速Access数据库。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！