Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-07-08
6
Medium Priority
?
760 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 51

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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

721 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