Solved

Which operating system and computer hardware should I use with Access 2016

Posted on 2016-11-08
15
26 Views
Last Modified: 2016-11-10
Hello,

I plan to update my workstations from 3MHz XP machines running Access2K to Access 2016 running on faster machines.  

The reason I'm upgrading is in hope that I can get better performance on the workstations side.  I have a split FE/BE (eight linked mdb databases, 3 MS SQL databases with millions of records).

The ONLY applications we use are Access, Outlook, and Chrome/IE.

The server is Windows 2003.  

(I realize there will be network and BE performance concerns but I'm not addressing them at this time.)



1. Windows 7 or something newer?
2. CPU: Any specific CPU? or # of Cores (4 or 8)?
3. RAM

Any suggestions would be appreciated if I left out anything important.  

Thanks
0
Comment
Question by:pcalabria
  • 4
  • 3
  • 3
  • +2
15 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41878843
  1. Windows 10
  2. i5 or better
  3. 2 GB min., 4 GB recommended

/gustav
0
 
LVL 57
ID: 41878845
1. Win 7 or Win 10....doesn't really matter.

2. No specific CPU, but obviously the faster/more core's, the better you'll do.  Keep in mind that all the DB processing is carried out client side, except possibly for some of the SQL stuff.   So the faster the station, the better off you'll be.

3. I'd do 8GB

Jim.
0
 
LVL 87

Assisted Solution

by:rindi
rindi earned 125 total points
ID: 41878912
Windows 10. M$ has stopped delivering OEM's with Windows 7 anyway. As for the rest I'd agree with JDettman's recommendations.
0
 

Author Comment

by:pcalabria
ID: 41879014
I am surprised to hear your recommendations.
(Dell still has Windows 7 machines for sale, at least advertised on their website.)

One of my applications takes about three weeks to process about 500,000 records when running 24/7.  (Stop laughing!)

The backend is a SQL Server running on a four-core 3MHz machine with 16GB and a SATA drive.  All search fields are indexed.

I was hoping extra cores, cache, or memory might make a difference.  I heard that 8 core machines have more cache, but they are so much more expensive (I would need about eight machines.)

Thanks
0
 
LVL 57
ID: 41879053
<<One of my applications takes about three weeks to process about 500,000 records when running 24/7.  (Stop laughing!)>>

 I'm not laughing, but am showing concern...something seems like it's way wrong there.   Don't know what your doing with those records, but that seems abnormal and not something hardware alone will handle.

<<I am surprised to hear your recommendations. >>

   In what way and why?   Keep in mind that Access is one thread, and JET maintains three by default for background processing.  Since your stations have a minimal number of apps running, more cores is not going to be a significant gain.   Memory will though, but again, JET's cache will only grow so large.

 In regards to SQL, with a Access FE, processing may still be carried out on the station side.   Without using pass-through queries, views, or stored procedures and triggers, your not always using the server processing.

  JET by default does try to send queries through to the back end if you don't, but it will not do that if:

a. You join to local tables.
b. You use JET specific SQL (Group By)
c. You use JET specific expressions (ie. make a VBA call).

  This can be a real performance hit when JET issues a single SQL call to the server for each row of your query.
 
  As for the stations, typically when I buy stations I buy a level or two down from the top processor, then take the money saved from that and buy more memory.  I find that memory by far gives you the most bang for the buck for typical office type apps.

Jim.
0
 
LVL 87

Expert Comment

by:rindi
ID: 41879072
Dell probably still has some Windows 7 Licenses they haven't sold yet, but they aren't getting any new ones from m$. Delivery was ended on the 31st of October. Besides, on 2020 it is EOL for Windows 7. Why buy PC's with Windows 7 now when you only have about 3 years left to use it?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41879074
And don't forget an SSD drive. That will be the best money spent.

/gustav
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 34

Expert Comment

by:PatHartman
ID: 41879179
One of my applications takes about three weeks to process about 500,000 records when running 24/7.  (Stop laughing!)
Hardware isn't going to solve this problem.  I regularly process million row recordsets both from SQL Server and ACE.  Sometimes the queries might take 20 minutes if the server is busy but three weeks?????  eeeeekk.

Start by reviewing your process.  If you are doing the process in a code loop rather than with update queries, examine the loop and make sure that you are not performing actions inside the loop that belong outside and if you are using domain functions inside the loop - stop immediately, do not pass go, do not collect $200 -- switch your queries to use joins.  The first long running process I built when I started using Access 20 years ago was a code loop similar to what I would have written in COBOL.  It took about 4 hours to process 109,000 records.  When I examined it, I realized that I could eliminate all the DLookup()s and replace them with left joins in the main query.  That single change got the process down to about 3 minutes.  You might actually find that running a series of update queries will be faster than trying to do everything in one and don't forget to index appropriately.

As to speed, there are two big issues.
1. Memory (get as much as the motherboard will support)
2. Band width (on a network, traffic can kill you and Access is a huge bandwidth hog.  In some cases, you can convert to SQL Server and reduce a lot of the traffic.  However, for an application built by an "Access-only" developer, just porting the BE will most likely result in increased time rather than lessened time so you will probably have to rethink the way many things operate and switch from using filters and finds to using queries with criteria.  For example, my search forms are never bound to an unfiltered recordset.  They are always bound to a query with criteria.  Each time the criteria changes, I requery the form to retrieve the selected records.  This is far more efficient and results in significantly less network traffic than bringing down entire tables and filtering them locally.)
3. CPU speed has much less impact.
4. Win 7 is more nimble than Win 10 and Win 8 isn't even a consideration.
0
 

Author Comment

by:pcalabria
ID: 41879377
Thanks all.

I don't have time to make code changes right now because I'm up to my ears in work trying to get the A2K code working on the new operating system and Access 2016... but I will after I get things working again.

So my consensus is that the machines should probably have 4 cores, SSD's, fast CPUs, and at least 8GB running Win7 if I don't mind upgrading a a couple years, or Win10.

I'm not sure how to give out points on this one.  Guess I'll give it a little time to see if there are any responses, and then split them up.  I was sort of hoping a hardware geek might chime in with AMD/Intel recommendations or graphics card thoughts.  By the way, I had a huge improvement in speed when I suppressed updating the display... not as much as Pat's but probably a factor of 5.

Thanks again
0
 
LVL 57
ID: 41879401
<< I was sort of hoping a hardware geek might chime in with AMD/Intel recommendations or graphics card thoughts.  >>

  Only thing there is make sure to get a discrete graphics card and that it supports dual monitors at least (on-board cards share memory with the system).

  As far as AMD vs Intel, I think your hair splitting there in terms of impact on your problem.

  You've got other problems besides hardware.

Jim.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 125 total points
ID: 41879548
You are planning on spending thousands of dollars and you are not going to get any real benefit since you are not addressing the actual problem.  

BTW - Multiple cores only come into play for multi-tasking.  I'm pretty sure that individual Office programs such as Access, Word, and Excel are not written to take advantage of multiple-CPUs although both Access and Excel do have some background processing that might benefit from being offloaded.  To utilize multiple cores, the app would have to break down a task and say - core 1 you do this instruction and core 2, you execute that one or do that subset of code.  It is better as Jim said to make sure your graphics card is separate so at least graphics stuff can be offloaded from the main processor.
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 125 total points
ID: 41880021
make sure to get a discrete graphics card

That is waste of money if you don't do CAD, gaming, or serious graphics work (high resolution pictures or video).

Just make sure you get a decent processor. Both my workstations have Xeon processors featuring native Intel HD Graphics P4000 and that is excellent for any Office and Visual Studio work.
So, if you wish to spend money, go for a better processor like i7. Should you ever need better graphics, you can always add it on at that time.

/gustav
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 125 total points
ID: 41880334
<<That is waste of money if you don't do CAD, gaming, or serious graphics work (high resolution pictures or video).>>

 I don't agree.   A discrete card has it's own memory and FPU, and off-loads a lot from the CPU.   A low end card is approx $100, where stepping up to an i7 running at a decent speed will cost you $300-400 extra.

Jim.
0
 

Author Closing Comment

by:pcalabria
ID: 41882771
Thanks all.  

I'm sure I can optimize my code for performance, but hopefully a faster machine as spec'd with your help will make a difference.

I'm going to pick up one machine and try it out to see how much of a difference is made.

Thanks again,
Calabria
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41882800
Please report back on any speed improvement.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

708 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

17 Experts available now in Live!

Get 1:1 Help Now