Solved

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

Posted on 2008-10-22
11
678 Views
Last Modified: 2013-11-29
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
Comment
Question by:SheahaST
11 Comments
 
LVL 18

Expert Comment

by:jmoss111
ID: 22781113
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
 
LVL 18

Expert Comment

by:jmoss111
ID: 22781129
What is the backend database, Access or SQL Server and what version of SQL Server if it is?
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22781151
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
 
LVL 18

Accepted Solution

by:
jmoss111 earned 300 total points
ID: 22781258
See attached illustration
sqlservervsfileserver.gif
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22781366
A lot has been written - Google - windows xp quad core
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 200 total points
ID: 22781425
>> 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
 
LVL 18

Expert Comment

by:jmoss111
ID: 22781427
What OS is on the client systems?
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22781576
Access 2007 will not bind to more than one cpu.
0
 

Author Comment

by:SheahaST
ID: 22788937
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 22789119
>> 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
 
LVL 38

Expert Comment

by:Jim P.
ID: 22876939
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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…

743 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

13 Experts available now in Live!

Get 1:1 Help Now