Will adding a GIG of RAM make a big difference in query speed

[jeep2001]

I often use Access 2003 to run queries against a large DB2 database via ODBC. Sometimes the queries can take a while to process. My PC is a Pentium 4 2.7 GHZ with 1 GIG of RAM.
Will adding a GIG of RAM really help speeding up things in your opinions.....

Thanks

 

[Golom]

It may although you are probably talking to an AS400 over a network to do this and, particularly with ODBC, the network traffic is probably more of a factor than available local memory.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[evalesthy]

I think Golom makes a good point. In this instance ODBC may be the big factor.

Nonetheless, I have seen dramatic improvements in processing extremely large amounts of data in Access when RAM is increased.

Any chance you can add a Gig and do some controlled tests - or is that out of the question?

Post back if you get a chance.

 

[MichaelRed]

There are several important issues re the exection of queries.

Network and memory issues are cretainly important ones.

Queries via the Jet engine ALWAYS start with the 'cartesian' set of records. This is approximatly the join of ever table in the recordset without any conditionals. Jet then "parses" this superset to remove fields and records not in the request. In particular, if the basic tables are not included in the local db, then the network traffic is -generally- quite a bit more than for most 'industrial strength' relational database engines (Oracle, SqlServer, ... ), since these guys actually do the processing on the server and return only the filtered fields and records. Thus, the use of "paess-through' queries for the 'industrial strength' databases can dramatically increase the execution speed of many queries.

Sql Server (& et. al.) also provide the capability to use stored procedures. These 'act like' parameter queries in Jet, and thus permit the transmission of less information to the 'server', thus also reducing network traffic.

Another important consideration in the construct and use of queries is the importance of concurrancy. In transaction orientated apps (e.g point of sale, running inventory, ... ) the details of ALL transactions are important so access MUST
be to the currnt (live) data. Other aps (or ewven some parts of transaction apps) the process is to show trends or historical data fro a specifi period (or other criteria) which may render the need to access the "current a-and up to date- data superflous. In these later instances, it is quite possible and pratical to 'download a temporary copy of the necessary recordsets and use the cop[y|ies] as your recorsources for forms / reports. This cam also provide some increase in execution.

These are only a couple of the considerations you should explore. In broad terms, you need to study the various elements of the application, the database engine characteristics, the data location(s) and the source behaviours, the network topology and throughput (for non-local data) and others,



MichaelRed