Solved

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

Posted on 2009-07-08
6
731 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

816 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

12 Experts available now in Live!

Get 1:1 Help Now