Improved performance with MS-SQL server?

Improved performance with MS-SQL server?

Post by Crilles Jacob Jans » Fri, 26 Jul 1996 04:00:00



I have developed an MS Access 2.0 database using approx. 25 tables, 40 queries  
and 50 forms. It's a marketing database. Today, the database is shared as a
file on our local network and used by approx. 10 persons at the same time.
It all works fine, but the performance is poor. Users not having a pentium is  
almost falling to sleep while waiting for respons! How can I improve
performance? I have tried to analyse the database in Access in Win95 but without
any improvment. Thus I consider doing something else. Should I use MS-SQL server
to improve performance? Does anyone have experience with this kind of upgrade?


 
 
 

Improved performance with MS-SQL server?

Post by Damian Kori » Mon, 29 Jul 1996 04:00:00



>I have developed an MS Access 2.0 database using approx. 25 tables, 40 queries  
>and 50 forms. It's a marketing database. Today, the database is shared as a
>file on our local network and used by approx. 10 persons at the same time.
>It all works fine, but the performance is poor. Users not having a pentium is  
>almost falling to sleep while waiting for respons! How can I improve
>performance? I have tried to analyse the database in Access in Win95 but without
>any improvment. Thus I consider doing something else. Should I use MS-SQL server
>to improve performance? Does anyone have experience with this kind of upgrade?


You are pushing the performance of Access. Access pulls the data
across the network to perform all operations. Combine locking and it
gets worse. You can check the network cards, ISA has limited bandwidth
at the bus. PCI is much better. This may be the diffrence between
486's and pentiums.

SQL server can perform most of the same operations without heavy use
of the network. Locking is more robust, queries are performed on the
server not on the client pc's.

There are upsizing tools on the Microsoft site. MS SQL Server requires
Windows NT and more ram than Access. Overall you should find much
better performance.

Damian Korick, MCP


 
 
 

Improved performance with MS-SQL server?

Post by Garry Shami » Tue, 30 Jul 1996 04:00:00


whether or not you will get any performance improvements depends largely
on the structure of your application, things like:
        multi-table select queries
        amount of data processed with loops in code
        amount of data displayed on your forms
        etc.
So make sure you understand implications of client-server before you
shell out your money for SQL server...
If you are not sure you can go through the process of analysis and
probably rewriting pieces of the application yourself you should call on
somebody who is proficient in the subject.
Good Luck.
/Garry

 
 
 

Improved performance with MS-SQL server?

Post by Roger » Wed, 31 Jul 1996 04:00:00


Reply to:

Quote:>I have developed an MS Access 2.0 database using approx. 25 tables, 40
queries  
>and 50 forms. It's a marketing database. Today, the database is shared as
a
>file on our local network and used by approx. 10 persons at the same
time.
>It all works fine, but the performance is poor. Users not having a
pentium is  
>almost falling to sleep while waiting for respons! How can I improve
>performance? I have tried to analyse the database in Access in Win95 but
without
>any improvment. Thus I consider doing something else. Should I use MS-SQL
server
>to improve performance? Does anyone have experience with this kind of

upgrade?

I would do some other things first to try and reduce network traffic,
separate data.mdb from app.mdb, and move app to clients, move static
tables to clients, work on optimizing your form loads and queries if you
have not done these things already.  You may be able to make some minor
changes to improve your network like changing bindings, removing unneeded
protocols, etc.  Also, consider the database size, will it continue to
grow and will new users be added?  If so, then maybe SQL server is the
answer, but it is not a trivial exercise to migrate an application.

Good luck,

Roger Campbell
Alpine Software
Albuquerque, NM, USA