哪些技术对于通过LAN实现单独的前端和后端Access实现的Access性能至关重要？ [英] What techniques are key to Access performance with separate front-endand back-end Access implementation via a LAN?
问题描述
我的大多数Access数据库实现在数据量和并发用户数方面相当小。到目前为止，我还没有担心性能问题。 <敲木头>
但是我很好奇那些已经做过更高价值的b / b
卷访问实现的技术用于确保高性能的
数据库在多用户100mbps局域网中的实现???
谢谢
Bob Alston
Most of my Access database implementations have been fairly small in
terms of data volume and number of concurrent users. So far I haven''t
had performance issues to worry about. <knock on wood>
But I am curious about what techniques those of you who have done higher
volume access implementations use to ensure high performance of the
database in a multi-user 100mbps LAN implementation???
Thanks
Bob Alston
推荐答案
On Sun，2005年10月16日13:10:17 -0500，Bob Alston< tu ************* ***@cox.net>
写道：
On Sun, 16 Oct 2005 13:10:17 -0500, Bob Alston <tu****************@cox.net>
wrote:
我的大部分Access数据库实现在数据量和数据量方面相当小并发用户数。到目前为止，我还没有担心性能问题。 <敲木头>
但我很好奇那些已经做过更高级别的卷访问实现的技术用于确保多个数据库的高性能-user 100mbps局域网实施???
谢谢
Bob Alston
Most of my Access database implementations have been fairly small in
terms of data volume and number of concurrent users. So far I haven''t
had performance issues to worry about. <knock on wood>
But I am curious about what techniques those of you who have done higher
volume access implementations use to ensure high performance of the
database in a multi-user 100mbps LAN implementation???
Thanks
Bob Alston
老实说，如果是后端仍然是一个MDB，关于性能的问题并没有太大的不同。当你将
切换到服务器后端时，性能问题确实会发生变化，而你对MDB的最大问题可能是可靠性，而不是性能。无论你做什么，确保每个用户都运行他们自己的前端MDB副本，如果它看起来像是同时使用的b $ b用户的数量将会超过25，开始你的计划切换到数据库
服务器后端。
Honestly, if the back-end is still an MDB, there''s not much different to worry
about with performance. The performance issues really change when you switch
to a server back-end, and the biggest problems you''ll have with the MDB may be
reliability, not performance. Whatever you do, make sure each user runs their
own copy of the front-end MDB, and if it looks like the number of simultaneous
users is going to excede about 25, start your plan to switch to a database
Server back-end.
Steve Jorgensen写道：
Steve Jorgensen wrote:
在Sun，2005年10月16日13:10:17 -0500，Bob Alston< tu **************** @ cox.net>
写道：
On Sun, 16 Oct 2005 13:10:17 -0500, Bob Alston <tu****************@cox.net>
wrote:
我的大多数Access数据库实现在数据量和并发用户数方面相当小。到目前为止，我还没有担心性能问题。 <敲木头>
但我很好奇那些已经做过更高级别的卷访问实现的技术用于确保多个数据库的高性能-user 100mbps局域网实现???
谢谢
Bob Alston
Most of my Access database implementations have been fairly small in
terms of data volume and number of concurrent users. So far I haven''t
had performance issues to worry about. <knock on wood>
But I am curious about what techniques those of you who have done higher
volume access implementations use to ensure high performance of the
database in a multi-user 100mbps LAN implementation???
Thanks
Bob Alston
老实说，如果后端仍然是MDB ，对性能的担忧并没有太大的不同。当你切换到服务器后端时，性能问题确实会发生变化，而你对MDB的最大问题可能是可靠性，而不是性能。无论你做什么，确保每个用户都运行他们自己的前端MDB副本，如果看起来同时
用户的数量超过25，那么就开始计划切换到数据库
服务器后端。
Honestly, if the back-end is still an MDB, there''s not much different to worry
about with performance. The performance issues really change when you switch
to a server back-end, and the biggest problems you''ll have with the MDB may be
reliability, not performance. Whatever you do, make sure each user runs their
own copy of the front-end MDB, and if it looks like the number of simultaneous
users is going to excede about 25, start your plan to switch to a database
Server back-end.
谢谢史蒂夫。真的很好地体验你的体验。
你能告诉我你在服务器后端中遇到的可靠性问题的级别和频率吗？只访问应用程序？
我已经看过并试图帮助解决其中的一些问题，但我没有开发应用程序并且从未进入过很长时间应用程序如何工作
。
Bob
Thanks Steve. Really approciate your experience.
Can you tell me what level of and frequency of reliability problems you
have experienced in "server back-end" Access only applications?
I have seen and tried to help trouble shoot some of these but I did not
develop the application and never got very far into exactly how the app
worked.
Bob
我们经常在这里看到一个应用程序是一个用户太慢了。如果
应用程序对一个用户来说太慢了......那么当他们试图运行10个用户时，可以期待什么呢？这是现在要求的10倍..
一些事情：
拥有75k记录的表格非常小。让我们假设您有12个
用户。只有一个100％的文件库系统（jet），而且没有sql server，那么
该系统的性能应该真的让人尖叫。
之前微软开始真的销售sql server，他们评价JET可以轻松处理50个用户
。我们在这里有可靠的报告人们运行100个用户
。然而，在这些情况下，一切都必须是b $ b完美。
我有一些应用程序，有50个或60个高度相关的表格。 />
网络上有5到10个用户，响应时间是即时的。我认为任何
表单加载都需要超过一秒钟。这些60多张桌子中的许多都是高度的b
关系......以及50到75k的记录范围。
所以，我的5位用户......我明白了我无法在75,000记录范围内使用
这样的小桌子扩展到15个用户。
如果应用程序没有执行此类操作小表只有75k
记录..然后升级到sql server将绝对无需修复
性能问题。事实上，在sql server新闻组中你看到每周发布升级到sql的人发布的帖子实际上减慢了速度。
我甚至看起来很酷的数字显示一些查询实际上是什么？b $ b在JET然后sql server的网络使用方面效率更高。
（这很少见，而不是正常情况，但它确实指出了ms-access
并不总是将整个表拖到客户端。
我的观点是技术不会解决性能问题。
但是，精心设计，谨慎使用有限的带宽资源
是关键。因此，如果应用程序没有以良好的
性能编写，那么你的设计很糟糕！
我的意思是，当使用时一个JET文件共享，你从75k记录中获取发票
表...只有一条记录通过网络传输到文件共享
（和sql server）也只会转移一条记录）。所以，在这一点上，你通过升级到sql
服务器，确实不会发现任何性能差异。这里没有什么魔力。
Sql server是一个强大且可扩展性更强的产品，然后就是JET。并且，安全，
备份和主机的其他原因使得sql server成为一个不错的选择。
但是，sql server无法解决性能问题与处理
有75k记录这样的小表
当然，当努力利用sql server时，那么
可以实现性能的显着进步。
我会给出一些提示......这些适用于使用ms-access作为文件
共享（没有服务器），甚至是odbc到sql server：
**在加载表单之前询问用户需要什么！
上面这么简单，但是经常我看到上面这个概念被忽略了。
例如，当你走到一台即时取款机时，是不是每个账号下载
然后问你想要什么去做？在
访问中，打开连接到表格的表格是完全愚蠢的，没有
首先询问用户他们想要什么！因此，如果是客户发票，请获取发票号码，然后使用ONE记录加载表格（如何能够将b / b
的记录放慢！）。完成编辑记录后...表格已关闭，并且您回到提示准备与下一位客户进行战斗。你可以了解这个流动的方式。一个好的用户界面在这里工作（这个
适用于JET或sql server appcltions）：
http://www.members.shaw.ca/AlbertKal...rch/index.html
我这里唯一的一点是将表单限制为只有一个记录用户需要的b $ b。当然，子表格和细节记录不适用于这个规则，
但我总是感到沮丧，开发人员经常建立一个漂亮的表格，附上
它到一个大桌子，然后打开它......然后把这个表格附在
一些巨大的桌子上......然后告诉用户去玩得开心。不要我们对那些可怜的用户有任何关注吗？通常，用户不会知道如何搜索某些内容！ （所以，提示，并询问用户
在可用性方面也取得了巨大的飞跃。而且，大奖金减少了
网络流量也是如此！...天哪。 ..更好，更快，更少网络
流量....我们还想要什么！）。
**你可以继续使用绑定形式..但如上所述..将表格
限制为您需要的一条记录。您可以安全地打开一张发票，并且
甚至继续使用where openform的条款。绑定表格是方式
少工作然后不受限制的表格...而且表现一般都很好
无论如何做得好。
**大量装配组合框。一个组合框适用于约100
条目。在那之后..你正在折磨用户（什么......他们必须看看
到100'的条目）。所以，把组合框这样的东西放到最小尺寸的
。这更快......更重要的是它对你的用户来说是更好的。
毕竟，在一天结束时......我们真的想要为用户轻松制作好的东西......并且好好对待它们......似乎
很好地对待用户，并减少带宽
（数据量）齐头并进。所以，更好的应用程序
很好地对待usres ..并且运行得更快！ （这是个好消息！）
最后，网络是一个共享资源，如果你有两个用户
拉数据，你得到1 / 2 perfoamcne。所以，10个用户=速度的1/10。
我在这里谈论有限的带宽问题：
http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html
-
Albert D. Kallal（访问MVP）
加拿大艾伯塔省埃德蒙顿
PL ***************** @ msn.com
http://www.members.shaw.ca/ AlbertKallal
We often see posts here that a application is too slow with one user. If the
application is too slow with one user..then what can one expect when they
try and run 10 users. That is now 10 times the requirements..
A few things:
Having a table with 75k records is quite small. Lets assume you have 12
users. With a just a 100% file base system (jet), and no sql server, then
the performance of that system should really have screamed.
Before Microsoft started "really" selling sql server, they rated JET could
handle easily 50 users. We have credible reports here of people
running 100 users. however, in those cases everything must be
"perfect".
I have some applications out there with 50, or 60 HIGHLY related tables.
With 5 to 10 users on a network, response time is instant. I don''t think any
form load takes more then one second. Many of those 60+ tables are highly
relational..and in the 50 to 75k records range.
So, with my 5 users..I see no reason why I can''t scale to 15 users with
such small tables in the 75,000 record range.
If the application did not perform with such small tables of only 75k
records..then upsizing to sql server will do absolute nothing to fix
performance issues. In fact, in the sql server newsgroups you see weekly
posts by people who find that upgrading to sql actually slowed things down.
I even seem some very cool numbers showing that some queries where actually
MORE EFFICIENT in terms of network use by JET then sql server.
(this is rare, and not the normal case, but it does point out that ms-access
does NOT always drag the whole table to the client)
My point here is that technology will NOT solve performance problems.
However, good designs that make careful use of limited bandwidth resources
is the key here. So, if the application was not written with good
performance in mind..then you kind are stuck with a poor design!
I mean, when using a JET file share, you grab a invoice from the 75k record
table..only the one record is transferred down the network with a file share
(and, sql server will also only transfer one record). So, at this point, you
really will NOT notice any performance difference by upgrading to sql
server. There is no magic here.
Sql server is a robust and more scalable product then is JET. And, security,
backup and host of other reasons make sql server a good choice.
However, sql server will NOT solve a performance problem with dealing
with such small tables as 75k records
Of course, when efforts are made to utilize sql server, then
significant advances in performance can be realized.
I will give a few tips...these apply when using ms-access as a file
share (without a server), or even odbc to sql server:
** Ask the user what they need before you load a form!
The above is so simple, but so often I see the above concept ignored.
For example, when you walk up to a instant teller machine, does it
download every account number and THEN ASK YOU what you want to do? In
access, it is downright silly to open up form attached to a table WITHOUT
FIRST asking the user what they want! So, if it is a customer invoice, get
the invoice number, and then load up the form with the ONE record (how can
one record be slow!). When done editing the record...the form is closed, and
you are back to the prompt ready to do battle with the next customer. You
can read up on how this "flow" of a good user interface works here (and this
applies to both JET, or sql server appcltions):
http://www.members.shaw.ca/AlbertKal...rch/index.html
My only point here is restrict the form to only the ONE record the user
needs. Of course, sub-forms, and details records don''t apply to this rule,
but I am always dismayed how often a developer builds a nice form, attaches
it to a large table, and then opens it..and the throws this form attached to
some huge table..and then tells the users to go have at and have fun. Don''t
we have any kind of concern for those poor users? Often, the user will not
even know how to search for something ! (so, prompt, and asking the user
also makes a HUGE leap forward in usability. And, the big bonus is reduced
network traffic too!...Gosh...better and faster, and less network
traffic....what more do we want!).
** You can continue to use bound forms..but as mentioned..restrict the form
to the one record you need. You can safely open up to a single invoice, and
even continue to use the "where" clause of the openform. Bound forms are way
less work then un-bound forms...and performance is generally just is good
anyway when done right.
** Large loading of combo boxes. A combo box is good for about 100
entries. After that..you are torturing the user (what..they got to look
through 100''s of entries). So, keep things like combo boxes down
to a min size. This is both faster..and MORE importantly it is
kinder to your users.
After all, at the end of the day..what we really want is to make
things easy for the users...and treat them well.. It seems that
treating the users well, and reducfing the bandwith
(amount of data) goes hand in hand. So, better applications
treat the usres well..and run faster! (this is good news!)
Last by not least, networks are a shared resource, and if you got two users
pulling data, you got 1/2 the perfoamcne. So, 10 users = 1/10 the speed.
I talk about the limited bandiwth issue here:
http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal
这篇关于哪些技术对于通过LAN实现单独的前端和后端Access实现的Access性能至关重要？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
