Solved

What is faster: Access frontend/SQL Express backend or Access split frontend/backend?

Posted on 2009-07-08
6
728 Views
Last Modified: 2012-06-27
I consider how to enhance performance of existing access application.
What should be faster:
Access frontend and SQL Express 2005 backend with linked tables using a DSN,
or current mdb file splitted into a backend and frontend and then linked?
0
Comment
Question by:trigeminus
6 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 24802056
I don't think this has a definitive response. There are many factors influencing performance and the consideration of a what backend is only part of the equation.

How you choose to use the backends, what type of development you have in the front end can all affect this answer.

SQL Server (whether Express or full) opens the opportunity for server side processing, which will give you performance gains (often considerable). It's a whole new development environment.

Generally, however, if you compare a n Access back end to SQL Server with no functionality moved to SQL, you probably haven't gained much (apart from a more stable envirnment for multi-user setup).

Kelvin
0
 

Author Comment

by:trigeminus
ID: 24802120
Current application is quite slow because of overfill data in backend mdb file.
I try to switch to SQL Express 2005, without changing of frontend source, but in this moment I do not see acceleration.
0
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 500 total points
ID: 24802202
How big is the backend file. Remember SQL EXpress has a 4 Gb datafile limit, whereas full SQL doesn't have a limit.

In such a setup, I wouldn't expect to see much difference, but believe the larger backend access databases are the greater the risk of database corruption. SQL (even Express) mitigates that risk a bit. If you have many database calculations in the access front end, you can recreate these into SQL server and stored procedures - this removes the need for data to travel to and from the app. There are significant performance gains to be had here - BUT, there is a learning curve.


Kelvin
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 24802319
In general, an mdb backend is faster with few users while an SQL Server backend is faster with many users.

Proper indexing may add greater improvement.
Wondering what "overfill" data is. Data is data. However, if you can delete and compact that will speed up and mdb.

/gustav
0
 
LVL 84
ID: 24802680
Please no points for me:

Agree with Kelvin and gustav - simply moving data to a SQL Server is no guarantee of improved performance, and in some cases can result in worse performance. You should be diligent about database maintenance, and explore the possibilities provided by the server (i.e. Stored Procedures, Views, etc).
0
 
LVL 57
ID: 24802873
<<Please no points for me:>>
Ditto here as well.
As Kelvin and gustav have said, "it depends".  There are a wide range of factors to consider.  And as Kelvin said, unless you re-write the app to take advantage of server side processing when moving to SQL Server, you may see little or a performance decrease.
Using a DSN is just a first step.  You then need to look at using pass-through queries, stored procedures, triggers, etc.  Also, the way an app executes.  For example, with a SQL BE, you typically would not bind a form to an entire table but would provide a search function for a user and let them fetch one record at a time.
JimD.
 
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Format vertical text in Access 2016 3 33
Unrecognized Database Format 8 93
Query to summarise data Like Pivot Table 3 29
Field Size - Double?  Want to display 0 5 30
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now