Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Stoneedge Access Database Migration Performance Degradation 1

Status
Not open for further replies.
sircles

sircles

IS-IT--Management
Nov 9, 2008
34
GB
I was at an office the other day where they had migrated their MS Access based order management application from being completely MS Access to having an SQL back end. Because of various considerations they decided to use SQL Express 2005 on a Windows 2008 server. The SQL Express version obviously has a limit of 1 CPU core and 1GB RAM. Even with this in mind the data flow is quite quick. The problem is that the main menu itself takes forever to respond inside of the application and we cannot understand why. I do not have a huge amount of information but I can tell you that there are about 20 users on a gigabit network and that the MS Access DB is on the same server as the SQL Express server and the network itself seems to work swiftly enough. Does anyone have idea how the menu would be working in this case inside of MS Access - would it now be querying SQL for the menu choices?

Sorry to be so vague but I have not seen this Stoneedge system before.

sircles.net Support - find us at:
Henley-on-Thames Computer Support
 
Sort by date Sort by votes
Access is a file server in that the client reads the file in an optionally central location. It is generally sower than RDMS systems like SQL because it has to get more data where as with SQL you just ask for less data.

However, if you dumb convert an application to SQL server, Access may decide to read ALL the data in the table and perform the query logic locally and can in fact be slower than using Access natively because it is not picking at any indexes or anything.

A place to start, look into "SQL Passthrough Queries" and convert the queries that are used most often first. But using Access with SQL server is a broad topic and there are books devoted to the subject.
 
Upvote 0 Downvote
Thank you for responding regarding this, I was interested to stay focussed though, on the fact that it is the main menu alone in this case that is extremely slow. The data tables are working well. It is probably also worth mentioning that Stone Edge is designed to be used on SQL in this Enterprise version that they are using and so I would have imagined that the menus would be stored locally within the DB that Access is using on the local machine.

I would like to understand how this application works and when it does lookups on the SQL server and when it used the local MS Access DB - has anyone heard of Stone Edge Order Management or had any dealing with it?

sircles.net Support - find us at:
Henley-on-Thames Computer Support
 
Upvote 0 Downvote
The only other comment I have is that the application file or Access file should be on the client or local for best performance.
 
Upvote 0 Downvote
Unfortunately it appears locked somehow and we cannot view in design view. The DB is local that the clients use (local to the machine I mean)

Is there a way we can monitor what MSAccess.exe is actually doing whilst we wait 3 seconds for the main menu to respond?

It doesn't appear to be querying SQL...

sircles.net Support - find us at:
Henley-on-Thames Computer Support
 
Upvote 0 Downvote
Two thoughts, you can pull the network cable before you open the access file. If you get an error, it will probably reference SQL. This is the low tech test...

The other thing you can try it to run a SQL Trace. I've never actually done it but you should be able to find out if your client is running any SQL.

This will only test for SQL / Network use. You can't really see what Access is doing.... At least I don't know of any tools that sophisticated.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

breezett93
  • Locked
  • Question
Brand new lock violations errors all over my database
Replies
11
Views
2K
dhookom
dhookom
lameid
  • Locked
  • Question
SELECT FROM Table in Another Access File
Replies
2
Views
2K
lameid
lameid
JK Dutta
  • Locked
  • Question
requet for form an access query for report
Replies
2
Views
2K
dhookom
dhookom
monkeysee
  • Locked
  • Question
Automatic Invoicing in Access 1
Replies
6
Views
235
monkeysee
monkeysee
TheresAlwaysAWay
  • Locked
  • Question
Can not get Access 2016 queries to accept form value input or TempVars! input 2
2
Replies
21
Views
597
TheresAlwaysAWay
TheresAlwaysAWay

Part and Inventory Search

Sponsor

Back
Top