Solved

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

Posted on 2009-07-08
6
746 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 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 50

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

Back Up Your Microsoft Windows Server®

Back up 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.

Question has a verified solution.

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

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

739 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