• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 699
  • Last Modified:

MS Access 2007 - Does this product take advantage of multiple processors?

MS Access 2007 - Does this product take advantage of multiple processors? Does SQL Server 2005?  I have a database that now has upto 6 agents using it at once and the database is starting to run slow.  My backend database resides on a dual processor XP machine with 2GB of RAM.  My customer would probably buy a 4 quad processor if I asked them, but does Access even support these extra processors.

Thanks
0
SheahaST
Asked:
SheahaST
2 Solutions
 
jmoss111Commented:
It really doesn't that matter much about the CPU on the backend; make sure that you have a decent network connection and lots of RAM and goood CPU on the client though.

Jim
0
 
jmoss111Commented:
What is the backend database, Access or SQL Server and what version of SQL Server if it is?
0
 
jmoss111Commented:
Access does no processing on the machine that holds the database. You run the query which is passed to the file server and all the data is returned to the workstation for processing. SQL Server is a different story.
0
Technology Partners: 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!

 
jmoss111Commented:
See attached illustration
sqlservervsfileserver.gif
0
 
GRayLCommented:
A lot has been written - Google - windows xp quad core
0
 
Jim P.Commented:
>> SQL Server is a different story.

There are two stories for a SQL Server back-end. If you are using an ADO solution then the majority of the processing is done on the back-end and it delivers only the requested records/dataset, provided you take the time to refine the dataset you requested.

If you open an ADO recordset as "SELECT * FROM EmpTable" and have 10K rows in the table it will try to return all 10K rows. Modify it to "SELECT * FROM EmpTable WHERE ActiveEmp = 1" it will only return the current 500 active employees.

If you are using DAO and linked tables it will always return all 10K rows and then it is client side that processes the rest of the query.

Regardless, CPU is generally never the bottle neck in DB processing on server or client side, it is memory. If you crank the memory to 4GB you will always get more bang for the buck than CPU -- note that is my opinion from experience.
0
 
jmoss111Commented:
What OS is on the client systems?
0
 
jmoss111Commented:
Access 2007 will not bind to more than one cpu.
0
 
SheahaSTAuthor Commented:
Thanks for all the help. The client machines and the backend machine are all XPs.  The backend is Access 2007.  Does SQL 2005 use more than one cpu?  How do I know if I am using ADO or DAO?  I am definitely using linked tables.  Thanks again.
0
 
Jim P.Commented:
>> Does SQL 2005 use more than one cpu?

Workgroup edition can do 2, Standard edition does 4 and Enterprise is how many you can throw at it. Note that Standard and above can be licensed per physical socket -- not the number of actual CPU's So if you have 2 dou core or a single Quad Core you would need 2 licenses vs 1 license.

SQL Server 2005 Features Comparison
http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx

>> I am definitely using linked tables.

That means that you should be using DAO. I haven't had a crack at Acc07 though.

Another way to cut down on how much is pulled to the local machine is to create simple views in the SQL db and then from there link to the view. From my example above

Create View Active_Emp
As
 SELECT *
 FROM EmpTable
 WHERE ActiveEmp = 1

and it can be linked in and worked like a table.
0
 
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now