The ATEN VM0404HA 4x4 4K HDMI Matrix Switch supports 4K resolutions of UHD (3840 x 2160) and DCI (4096 x 2160) with refresh rates of 30 Hz (4:4:4) and 60 Hz (4:2:0). It is ideal for applications where the routing of 4K digital signals is required.
Solved
ms access performance and SSD
Posted on 2016-08-18
Hi Folks.
I need to increase an MS Access database performance. (10 concurrent and 10 other users)
The BE (200Mb) is on a shared drive on a newish virtual server (sharing MS Exchange)
Assuming all the normal database optimizations are done, two questions:
1. will a dedicated server improve things much?
2. will an SSD (D: drive) on the new server (just for the Access BE) improve things (much) more (than a 10,000 rpm SATA drive) on this new box.
I realize the existing server specs are vague, but all expert comment and personal experiences are welcome.
thanks
I need to increase an MS Access database performance. (10 concurrent and 10 other users)
The BE (200Mb) is on a shared drive on a newish virtual server (sharing MS Exchange)
Assuming all the normal database optimizations are done, two questions:
1. will a dedicated server improve things much?
2. will an SSD (D: drive) on the new server (just for the Access BE) improve things (much) more (than a 10,000 rpm SATA drive) on this new box.
I realize the existing server specs are vague, but all expert comment and personal experiences are welcome.
thanks
[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
2
4 Comments
Active today
The two things that most effect Access database performance are:
1. LAN speed and available band width
2. Local Memory
Since all Access processing takes place on the local PC, the server used to host it is almost immaterial.
Make sure the application is split properly - meaning that each user has his own personal copy of the FE.
Make sure that the forms are bound to queries with select criteria to minimize the records selected.
Make sure none of your queries include domain functions such as DSum()
Make sure that your code loops are optimized
Use update queries rather than code loops for bulk updates.
Consider moving the BE to SQL Server - BUT - you will need to do all of the above and possibly other things to actually achieve better performance from SQL Server. If your app is slow now, simply converting the BE to SQL Server will most likely make it slower. You need to optimize the app first.
Strangely enough, moving to Citrix might improve performance. When you use Citrix to host the app, ALL processing happens on the Citrix server (assuming the BE is there also) and so network latency doesn't come into play. The only communication between the local PC and the Citrix server is the local PC sends keystrokes to the server and the server sends pictures to the local PC. Data isn't pushed around the network at all. This is why Citrix works so well for remote users.
1. LAN speed and available band width
2. Local Memory
Since all Access processing takes place on the local PC, the server used to host it is almost immaterial.
Make sure the application is split properly - meaning that each user has his own personal copy of the FE.
Make sure that the forms are bound to queries with select criteria to minimize the records selected.
Make sure none of your queries include domain functions such as DSum()
Make sure that your code loops are optimized
Use update queries rather than code loops for bulk updates.
Consider moving the BE to SQL Server - BUT - you will need to do all of the above and possibly other things to actually achieve better performance from SQL Server. If your app is slow now, simply converting the BE to SQL Server will most likely make it slower. You need to optimize the app first.
Strangely enough, moving to Citrix might improve performance. When you use Citrix to host the app, ALL processing happens on the Citrix server (assuming the BE is there also) and so network latency doesn't come into play. The only communication between the local PC and the Citrix server is the local PC sends keystrokes to the server and the server sends pictures to the local PC. Data isn't pushed around the network at all. This is why Citrix works so well for remote users.
Active today
<<1. will a dedicated server improve things much?>>
Probably not.
<<2. will an SSD (D: drive) on the new server (just for the Access BE) improve things (much) more (than a 10,000 rpm SATA drive) on this new box.>>
That's hard to say. Depends on the client stations and the network. Remember, with a JET/ACE BE, all DB processing is done by the client. The server is just a file share. It does also take care of holding locks, so if you have a CPU that is bottle necked, you would be better with a faster/seperate server.
<<Assuming all the normal database optimizations are done, two questions:>>
I would focus first on all possible optimizations in the code. For example, do you hold open a persistent connection to the BE for the life of the app? If not, depending on the app, this can give a good jump in performance.
Some other things:
1. Do you have "UseTransactions" set to no on queries where it doesn't matter if they are re-run?
2. Do you open recordsets as snapshots?
3. Do you use CurrentDB() in a loop at all?
4. Are you using a lot of Domain functions (ie. Dlookup) or use them in queries?
5. Compact and repair on a regular basis?
6. checked indexing?
7. Modified all queries so they recost since the DB has grown?
Food for thought...
Jim.
Probably not.
<<2. will an SSD (D: drive) on the new server (just for the Access BE) improve things (much) more (than a 10,000 rpm SATA drive) on this new box.>>
That's hard to say. Depends on the client stations and the network. Remember, with a JET/ACE BE, all DB processing is done by the client. The server is just a file share. It does also take care of holding locks, so if you have a CPU that is bottle necked, you would be better with a faster/seperate server.
<<Assuming all the normal database optimizations are done, two questions:>>
I would focus first on all possible optimizations in the code. For example, do you hold open a persistent connection to the BE for the life of the app? If not, depending on the app, this can give a good jump in performance.
Some other things:
1. Do you have "UseTransactions" set to no on queries where it doesn't matter if they are re-run?
2. Do you open recordsets as snapshots?
3. Do you use CurrentDB() in a loop at all?
4. Are you using a lot of Domain functions (ie. Dlookup) or use them in queries?
5. Compact and repair on a regular basis?
6. checked indexing?
7. Modified all queries so they recost since the DB has grown?
Food for thought...
Jim.
Thanks Pat and Jim; valuable input.
Jim, 2 questions:
1. what does 'Modified all queries so they recost since the DB has grown?' mean?
2. Is the best way to implement 'a persistent connection to the BE' via a bound field on the main form to a 'dummy' BE table?
thanks
Jim, 2 questions:
1. what does 'Modified all queries so they recost since the DB has grown?' mean?
2. Is the best way to implement 'a persistent connection to the BE' via a bound field on the main form to a 'dummy' BE table?
thanks
Active today
1. When you create a query and save it, Access calculates an execution plan for how to perform the query. The best execution plan could change over time as table row counts change and indexes are added. So, depending on how rapidly your BE grows or what design changes you make to the BE, you might want to consider compacting the FE periodically to force it to recalculate the execution plans.
2. I use my login form to do this. The form is bound to the user table and when the user logs in, I open the menu but I don't close the login form, I just set its .visible property to False.
2. I use my login form to do this. The form is bound to the user table and when the user logs in, I open the menu but I don't close the login form, I just set its .visible property to False.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder.
But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship.
Add the tables:
Create the relationship:
Decide if you’re going to set referential integrity:
Decide if you want cascade upda…
Suggested Courses
Course of the Month10 days, 2 hours left to enroll
722 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.