Am I wrong in assuming the project should be faster?
Yes, you are. As a general rule, JET is about 50 to 200% faster then sql
server. However, this comparison would be when NO NETWORK is involved.
When you introduce a network, then sql server *usually* can beat JET as a
file share. However, those gains don't appear out of the blue, and you must
DESIGN you application to minimizes network traffic. For example, you never
should just open a form attached to a large table. I asked a 90 year old
lady if you think it would be dumb that a instant teller machine loads
everyone's account number, AND THEN asks the user what account to work on.
If a 90' year old lady can figure out that, you would think a developer (or
a want to be developer) could also figure this out. This has nothing to do
with software development, but to do with common sense.
So, even when writing JET based applications, I NEVER simply open up a form
to a large table. What you do is ASK the user FIRST as to what customer
name, or number etc. you want to work on, then load the form to that ONE
record. You simply use the "where" clause of the open form to restrict the
records loaded (so, to be really clear, you can have a form attached to a
large table, but ALWAYS restrict the records loaded to this form. This
approach is certainly more critical when a network is involved,and hits
advice applies to both sql server, and a JET file share.
Having a table with 75,000 records is quite small. Lets assume you have 12
users. With a just a 100% file base system (jet), 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.
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. If the application was not written with good
performance in mind, then moving to sql server will not fix anything, and
often you will see worse performance.
Of course, the above JET example is with such small tables in the 75,000
record range, then those files are not nearly large enough for you to
experience a slowdown. As you get more tables, and larger tables, and more
users, then sql server will certainly perform better.
However, you have to think of this in terms of capacity. A aircraft carrier
can carry 5000+ people and planes with ease..and can go 40+ knots. A small
speed boat can carry 4 people..and also go 40+ knots. Thus, sql server is
not always about speed, but capacity.
So, at the end of the day, on limited bandwidth situations, even with 1 or 2
users, a ADP project can certainly outperform a JET file share. However, the
real answer is you must have good designs in the first place that limits the
transfer of records...and that applies to ANY application you make for ANY
system.
