Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-07-08
6
Medium Priority
?
764 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 1500 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 52

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 85
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 58
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

824 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