Speed: Access DB vs MS SQL Server

[Andrew Gabb]

How is speed likely to vary using MS SQL Server as opposed to an
Access backend for the same DB on a Windows network. Using Access as
the front end in both situations.

I'm only looking for a rough idea, particularly for displaying full
sorted lists of say 50,000 items.

Andrew

 

[Albert D. Kallal]

Well, stand alone, on the same machine without a network, then a jet based
applications is considerably faster then
sql server (as much as 200% faster).

with a network, then things change.

In both cases, if you request one record, and a index is available fro that
request, then again in both cases only the ONE record is transferred across
the network, and again you will not see much difference. (the Jet based file
share will also transfer some of the index data..but, not much).

I'm only looking for a rough idea, particularly for displaying full sorted
lists of say 50,000 items.

It is hard to tell. Transferring 50,000 records does take time..and sql
server has to use a socket connection, where as a JET file share is actually
reading data over the network. this is not a very good example, as it tends
to favour a JET based example here. This would especially be so of the
sorted order is a primary key.

Where sql server is really going to shine is if you have to do a on each of
the 50,000 records.

Note while in the first example, both a JET based system, and a sql server
based system can update one record, and the whole table is NOT TRANSFERRED
to the local pc.

*However*, in the case where you need to do a update on all of the records,
JET will transfer 50,000 records to the pc...update them...and send them
back (we just incurred 100,000 records of bandwidth here!!). In the case of
sql server, your update query can update all records, and NO RECORDS need be
transferred to the local pc. In this example, it is conceivable that the sql
server based application is roughly 50,000 times faster!!

Of course, one does not often update every record, so this example favours
sql server.

Further, if your sql is messy, or your query actually sorts the data on
field without a index, then the penalty in traffic is again MUCH less in sql
server (the sort can be done BEFORE data starts to flow to the client).

So, some operations don't speed up much when you go to sql server. However,
sql server SCALES better. So, while a aircraft carrier can actually do 40
miles per hour on the water, so can a small speed boat. (however, one can
carry 5000 people and a bunch of planes and still do 40 miles per hour). So,
while that small 4 person speed boat is as fast as that big boat, it is a
issue of capacity...not how fast you can go on the water. This concept of
speed also applies when comparing JET and sql server. Often, it is not a
question of how much faster, but the fact that you can have more users
hitting the database at the same time...

Further, unless you design, and take into account that you have sql server
available, then ms-access might NOT run much faster if you move the data
from a back end mdb file to a sql server.

Ms-access makes a great front end to sql server, but much of the performance
benefits are only realized with good sound designs. Simply "moving" the data
to sql server does not of it's self guarantee a increase in performance..and
often you see the reverse.

In *most* cases, with proper designs, then ms-access + sql server will run
circles around ms-access + jet file share. However, it not always cut and
dry....

So, the answer is it depends..and in the 50,000 record example, in both
cases you are transferring a LOT of data...and that is going to be slow.....

 

[Arvin Meyer [MVP]]

It really depends upon what you are doing. If you are retrieving 50K records
from a table of 50K records, the Jet database is likely to be faster. If you
are retrieving 50K records from a table of 250K records, the speed
difference is negligible if the proper indexes are used. If you are
retrieving 50K records from a table of several million records, Access is
not likely to be able to hold that many and if the records were small and it
could, SQL-Server would likely be faster. On a gigabit network, with less
than 20 users, Access is usually faster. On a dedicated beefy server,
SQL-Server is usually faster especially with higher numbers of records and
users.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Andrew Gabb]

So, the answer is it depends..and in the 50,000 record example, in both
cases you are transferring a LOT of data...and that is going to be slow.....

Wow, thanks for the dissertation, Albert. Interesting and much
appreciated.

Does this mean that Access must have 50,000 records transferred,
even though only 30 are showing on the list form at one time?

Do you know of any languages/platforms which use caches or whatever
to reduce the traffic load? What about VB.NET?

Obviously I could code it in Access VBA, but it sounds like a lot of
work.

Andrew

 

[Arvin Meyer [MVP]]

Access processing takes place on the workstation. So if you ask for 50K
records and there is an appropriate index, Access will request the index of
all the records over the network, then choose the 50K it wants, and request
only those from the server. The speed difference occurs when there is no
index, there is a huge difference between the actual number of records and
those requested, a slow network, or the way a query is constructed to take
advantage of the sql language. The language used to request the records has
only minor speed differences, in my experience VBA and VB6 are faster than
VB.NET, but only when looping through records, not when requesting them
through the sql language.

If all you want is 30 records out of 50K, just ask for them. You won't
notice the speed difference unless you have an excruciatingly slow network,
or you've forgotten to index the table. I typically can pull 300 records out
of about 90,000 of them with 50 people using Access on the network in under
a second. On my test server, which is on the same network, but I'm the only
user on a much slower server, the time is still under a second.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Albert D. Kallal]

Does this mean that Access must have 50,000 records transferred, even

though only 30 are showing on the list form at one time?

No, but then again, you would have to admit that having a form
bound to a huge table is going to perform slow. You would NOT
bind a form to a large table, and then think your work for the day
is done.

In other words, why would a developer try and load a form
that is attached to a table with 50,000 records? You have
to admit this is a type of brain freeze...don't you think?

Do you know of any languages/platforms which use caches or whatever to
reduce the traffic load? What about VB.NET?

C++, assembler, VB.net...none of them can pull data faster from sql server.

It becomes YOUR good/bad design that is responsible for performance here.

DO NOT try and blame your tools...you must blame the developer....

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.

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.

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.

** 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!).

 

[Tony Toews]

Access processing takes place on the workstation. So if you ask for 50K
records and there is an appropriate index, Access will request the index of
all the records over the network, then choose the 50K it wants, and request
only those from the server.

I'm going to slightly disagree with your explanation. I suspect Jet
is smart enough to pull in only those pages of the index which it
needs and not all pages of the index. Jet will then download the
pages containing the 50K records rather than just the records
themselves.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm