A
Anthony
What does EVERYONE recommend I do to make my database run as close to
lightning speed as possible?
lightning speed as possible?
S
Stefan Hoffmann
hi Anthony,
mfG
--> stefan <--
That's outdated: Access can go Warp 4.5.
mfG
--> stefan <--
M
ManningFan
Use a SQL Server backend. And index your tables properly.
6
'69 Camaro
That's outdated: Access can go Warp 4.5.
Check your dilithium crystals for surface contaminants. Mine goes Warp 8
when I put the metal to the floor.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
Check your dilithium crystals for surface contaminants. Mine goes Warp 8
when I put the metal to the floor.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
S
Stefan Hoffmann
hi Gunny,
mfG
--> stefan <--
Oh, come on, only near the sun, eh?'69 Camaro said:
mfG
--> stefan <--
6
'69 Camaro
That's outdated: Access can go Warp 4.5.
Right. Without the gravity assist, it can only do Warp 7.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
Right. Without the gravity assist, it can only do Warp 7.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
J
John Spencer
Quote from Tony Toews
The three most common performance problems in Access 2000 are:
- LDB locking which a persistent recordset connection fixes
- sub datasheet Name property set to [Auto] should be [None]
- Track name AutoCorrect should be off
(Tools: Options: General Uncheck all name AutoCorrect features)
Other reasons are
- New format of Access 2000 MDB
- Place backend MDB on the root of the network share rather than several
folders down
- Shorten the name of the backend MDB
- Miscellaneous Performance Suggestions
End quote
Check out the following URLs for a more complete discussion.
http://www.granite.ab.ca/access/performancefaq.htm
http://support.microsoft.com/?id=209126
Also
How to speed up slow queries
http://support.microsoft.com/kb/209126/en-us
Entitled: Information about query performance in an Access database
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
The three most common performance problems in Access 2000 are:
- LDB locking which a persistent recordset connection fixes
- sub datasheet Name property set to [Auto] should be [None]
- Track name AutoCorrect should be off
(Tools: Options: General Uncheck all name AutoCorrect features)
Other reasons are
- New format of Access 2000 MDB
- Place backend MDB on the root of the network share rather than several
folders down
- Shorten the name of the backend MDB
- Miscellaneous Performance Suggestions
End quote
Check out the following URLs for a more complete discussion.
http://www.granite.ab.ca/access/performancefaq.htm
http://support.microsoft.com/?id=209126
Also
How to speed up slow queries
http://support.microsoft.com/kb/209126/en-us
Entitled: Information about query performance in an Access database
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
D
Douglas J. Steele
I don't believe Access can take advantage of multi-threading.
G
Guest
That was interesting, John, thank you.
Could you please tell me where I can find the "sub datasheet name property"?
Could you please tell me where I can find the "sub datasheet name property"?
A
Albert D. Kallal
Niniel said:I know.
But can [other] SQL dbs?
Well, they might, but it actually been known for a LONG time that most
databases are not processing bound. What that means is that if you use 1, or
100 processors, you will NOT notice a speed increase in he application (even
if it was 100% threaded).
Why?
the reason for this is that the speed of your hard disk is not changed, and
is the limiting factor here. We are not processing bound in ms-access, but
in fact i/o (input/output) bound. That means the speed of your disk drive,
or network is the limiting factor. So, in fact, if you have an existing
machine, and put in a whole bunch more of processors, it not going to help.
The same applies to sql server, and often adding more processors will NOT
speed things up, since your bottle neck is reading the data from the disk
drive...and more processors don't help that.....
It is really the same thing as saying that if you purchase a new computer,
it will download things faster from the internet..and it not going to make
one bit of difference unless you speed up you internet connection....-- this
same rule applies to ms-access also...
D
Douglas J. Steele
Hey, stop getting fresh with Crystal! <g>
A
Albert D. Kallal
As others have said, this subject is bit much for a single newsgroup post.
however, MOST performance gains are accomplished with good designs.
For example, I asked a 92 year old grandmother if it was really stupid that
bank machine would first download EVERYONE'S account number, and then YOU
ENTER you account number (PIN) to use the teller machine.
that 92 year old grandmother INSTANTLY could recognize that it is MUCH
BETTER to first ask the user their PIN number, and then ONLY download that
persons information into the teller machine.
So, if 92 year old grandmothers can tell me that is REALLY dumb to load up a
bunch of information you don't need, then you going to have to show me that
your forms don't load up unnecessary data.
In other words, if any of your forms simply load up and are attached to a
large table, then you have a bad design. Why would you attached a form to a
large table, and then load the form? It is downright crazy to do this, and
even 92 year old grandmothers can grasp this concept. yet, time and time
again, I look at a ms-access applications, and sure enough...the user has
forms attached directly to large tables, and has MADE NO EFFORT to restrict
the records loaded. Terrible designs.....
If you write your application in c++, or VB or in your case with ms-access,
in GENERAL the performance of all of these tools will BE THE SAME.
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..
The other issue is how well is the database setup?
Further..how well are the forms designed?
How well does the application work with 5 users..and then when you jump to
10 users...how much a slow down to you notice?
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.
So, ok..small tiny tables of 75,000 reocrds, and 5 users, and sure, things
should aboslore screem in terms of perfoamcne.
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.
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/AlbertKallal/Search/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,a 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!)
however, MOST performance gains are accomplished with good designs.
For example, I asked a 92 year old grandmother if it was really stupid that
bank machine would first download EVERYONE'S account number, and then YOU
ENTER you account number (PIN) to use the teller machine.
that 92 year old grandmother INSTANTLY could recognize that it is MUCH
BETTER to first ask the user their PIN number, and then ONLY download that
persons information into the teller machine.
So, if 92 year old grandmothers can tell me that is REALLY dumb to load up a
bunch of information you don't need, then you going to have to show me that
your forms don't load up unnecessary data.
In other words, if any of your forms simply load up and are attached to a
large table, then you have a bad design. Why would you attached a form to a
large table, and then load the form? It is downright crazy to do this, and
even 92 year old grandmothers can grasp this concept. yet, time and time
again, I look at a ms-access applications, and sure enough...the user has
forms attached directly to large tables, and has MADE NO EFFORT to restrict
the records loaded. Terrible designs.....
If you write your application in c++, or VB or in your case with ms-access,
in GENERAL the performance of all of these tools will BE THE SAME.
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..
The other issue is how well is the database setup?
Further..how well are the forms designed?
How well does the application work with 5 users..and then when you jump to
10 users...how much a slow down to you notice?
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.
So, ok..small tiny tables of 75,000 reocrds, and 5 users, and sure, things
should aboslore screem in terms of perfoamcne.
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.
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/AlbertKallal/Search/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,a 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!)
J
John Nurick
Single user, not split, on a RAM drive on a superfast machine.
And it's even faster if you delete all the data: that way, you don't
even need to open the database to know the answer it will give you.
6
'69 Camaro
It's the hull number of the USS Enterprise: NCC-1701-D (Next Generation
version)
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
version)
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
6
'69 Camaro
And it's even faster if you delete all the data: that way, you don't
Don't forget to compact it afterwards to squeeze all the excess air out so
you get the best aerodynamics for that hyperspeed.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
Don't forget to compact it afterwards to squeeze all the excess air out so
you get the best aerodynamics for that hyperspeed.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
J
John Spencer
Open up a table in design view and Select View: Properties from the menu.
There is code that will step through all the tables and set the property to
None
'Source: MS Knowledge Base #275085 (See the following URL)
http://support.microsoft.com/Default.aspx?id=275085
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
There is code that will step through all the tables and set the property to
None
'Source: MS Knowledge Base #275085 (See the following URL)
http://support.microsoft.com/Default.aspx?id=275085
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
A
Anthony
damn!
'69 Camaro said:
R
Rick
Direct answer. Delete the records.
G
Guest
Use a SQL Server backend.
No, than does not make my application run faster.
Perhaps you mean 'rebuild your application entirely, using
client-server principles'?
No, that does not make my application run faster.
One of the regular posters here has a relevant comparison:
A sports car is faster than a bus. But a bus can get 30 people
from A to B faster than the sports car.
The bus is SQL Server. It is not faster. Jet is faster.
But SQL Server can do more transactions on a larger
database in the same time.
One of the reasons Jet is faster is that it is a distributed
database engine. Most of the processing takes place on
your workstation. You don't have to share your processor
and memory with all the other people doing transactions.
One of the reasons SQL Server can do more transactions
on a larger database in the same time is that the Jet records
remain locked for longer. Jet does automatic retries when
there is a locking conflict, but it limits the throughput of the
system.
(david)
No, than does not make my application run faster.
Perhaps you mean 'rebuild your application entirely, using
client-server principles'?
No, that does not make my application run faster.
One of the regular posters here has a relevant comparison:
A sports car is faster than a bus. But a bus can get 30 people
from A to B faster than the sports car.
The bus is SQL Server. It is not faster. Jet is faster.
But SQL Server can do more transactions on a larger
database in the same time.
One of the reasons Jet is faster is that it is a distributed
database engine. Most of the processing takes place on
your workstation. You don't have to share your processor
and memory with all the other people doing transactions.
One of the reasons SQL Server can do more transactions
on a larger database in the same time is that the Jet records
remain locked for longer. Jet does automatic retries when
there is a locking conflict, but it limits the throughput of the
system.
(david)
G
Guest
I don't believe Access can take advantage of multi-threading.
Jet is multi-threaded. The default number of threads is 3. This
causes a problem with IIS. IIS tries to create multiple database
threads: inside the thread creation code, Jet tries to create
multiple threads. Windows thread creation code is not re-entrant,
and it crashes. Solutions are to restrict IIS to a single database
thread, use a special database method that I don't understand, or
(my idea) change the registry setting so that Jet only uses one thread.
Also, Windows can take advantage of multi-threading, and Jet uses
Windows as part of the database engine. By default, applications
run on one processor, and the OS runs on the other, so all of your
SQL would be on one processor, and all your file operations would
be on the other. Right?
(david)
Jet is multi-threaded. The default number of threads is 3. This
causes a problem with IIS. IIS tries to create multiple database
threads: inside the thread creation code, Jet tries to create
multiple threads. Windows thread creation code is not re-entrant,
and it crashes. Solutions are to restrict IIS to a single database
thread, use a special database method that I don't understand, or
(my idea) change the registry setting so that Jet only uses one thread.
Also, Windows can take advantage of multi-threading, and Jet uses
Windows as part of the database engine. By default, applications
run on one processor, and the OS runs on the other, so all of your
SQL would be on one processor, and all your file operations would
be on the other. Right?
(david)