Memory using Access 
Author Message
 Memory using Access

Using Access97, I am processing a large table using macros and stored
querydefs.  The application was freezing up until I wrote an iterative
procedure in VBA to process the table in smaller chunks, writing the output
to tables.

I suspect a memory problem.  When I pull up the Task Manager I notice the
Mem Usage for the MSACCESS.EXE process does not go above 24MB.  The machine
has 128MB of RAM.  How can I get Access to use more memory?

Jarrod Goentzel



Sun, 23 Jul 2000 03:00:00 GMT  
 Memory using Access

I am so dissatisfied with the speed and memory demands of running queries
that I am currently rewriting an application with one rule: No queries and
No macros! (or is that two rules?).
Queries give immediacy, but what is the advantage of a query giving a
dynamic immediate view of data which takes say, two minutes to run, over a
carefully constructed module which produces a static recordset view of the
data and takes, perhaps, ten seconds to run? If you program a call to the
sub at the appropriate times, the only problem I can see is that your
database will grow and need to be compacted frequently.
All the memory in the world won't help the fact that queries are handicapped
by their single mindedness ,and accomplishing the accommodation of
processing many different fields for many different conditions usually
requires running query after query after query, many of them calling other
queries, which means you are scanning through tables time after time, while
programming with objects and VB allow one pass through the table. I think MS
did its Access customers a disservice by emphasizing query building, because
a reliance on queries disguises just how fast and powerful the Jet engine
can be.
--
Lyle Fairfield

Quote:

>Using Access97, I am processing a large table using macros and stored
>querydefs.  The application was freezing up until I wrote an iterative
>procedure in VBA to process the table in smaller chunks, writing the output
>to tables.

>I suspect a memory problem.  When I pull up the Task Manager I notice the
>Mem Usage for the MSACCESS.EXE process does not go above 24MB.  The machine
>has 128MB of RAM.  How can I get Access to use more memory?

>Jarrod Goentzel




Mon, 24 Jul 2000 03:00:00 GMT  
 Memory using Access

a) There is a knowledge base articel about the JET engine and its registry
parameters. One of these parameters controls the usage of RAM. (I dont know
the article id right now, sorry).

b) Freezing: Could it be that you run out of locks? Try opening the MDB in
exclusive mode. This is especially a problem with NetWare-Servers.



Mon, 24 Jul 2000 03:00:00 GMT  
 Memory using Access

Lyle, the Jet Engine is faster at getting data out than any code you can
possibly write.  Even that "static recordset" you discuss... if you have a
saved query and use it for your recordset in code than your code will be 1-4
seconds faster since Jet will not have to compile your hard-coded SQL
statement.

I think you must be comparing running a query dynamically from the UI
(getting a datasheet) to your code, because in any fair comparison a saved
query will be faster than hard-coded SQL.

Michael

Quote:

>I am so dissatisfied with the speed and memory demands of running queries
>that I am currently rewriting an application with one rule: No queries and
>No macros! (or is that two rules?).
>Queries give immediacy, but what is the advantage of a query giving a
>dynamic immediate view of data which takes say, two minutes to run, over a
>carefully constructed module which produces a static recordset view of the
>data and takes, perhaps, ten seconds to run? If you program a call to the
>sub at the appropriate times, the only problem I can see is that your
>database will grow and need to be compacted frequently.
>All the memory in the world won't help the fact that queries are
handicapped
>by their single mindedness ,and accomplishing the accommodation of
>processing many different fields for many different conditions usually
>requires running query after query after query, many of them calling other
>queries, which means you are scanning through tables time after time, while
>programming with objects and VB allow one pass through the table. I think
MS
>did its Access customers a disservice by emphasizing query building,
because
>a reliance on queries disguises just how fast and powerful the Jet engine
>can be.
>--
>Lyle Fairfield




- Show quoted text -

Quote:
>>Using Access97, I am processing a large table using macros and stored
>>querydefs.  The application was freezing up until I wrote an iterative
>>procedure in VBA to process the table in smaller chunks, writing the
output
>>to tables.

>>I suspect a memory problem.  When I pull up the Task Manager I notice the
>>Mem Usage for the MSACCESS.EXE process does not go above 24MB.  The
machine
>>has 128MB of RAM.  How can I get Access to use more memory?

>>Jarrod Goentzel




Mon, 24 Jul 2000 03:00:00 GMT  
 Memory using Access

make sure if you are moving threw a RecordSet, you didn't forget a MoveNext
or something else causing an endless loop.
Toss in a bunch of Debug.Prints, and watch the Debug window and see if
anything freezes up...  IF it is a memory problem, with 128 Meg of Ram,
Yikes, unless your out of Hard Drive space...

--
\                                           /
  \   Brian Mailloux              /

/                                          \

Quote:

>Using Access97, I am processing a large table using macros and stored
>querydefs.  The application was freezing up until I wrote an iterative
>procedure in VBA to process the table in smaller chunks, writing the output
>to tables.

>I suspect a memory problem.  When I pull up the Task Manager I notice the
>Mem Usage for the MSACCESS.EXE process does not go above 24MB.  The machine
>has 128MB of RAM.  How can I get Access to use more memory?

>Jarrod Goentzel




Mon, 24 Jul 2000 03:00:00 GMT  
 Memory using Access

Thanks, I must be missing something!
I am creating a staffing projection for a School Board with about
twenty-five thousand elementary students in sixty eight schools organized
into twenty-five class types based on maximum school enrolment averages for
each class type. We get a projected enrolment for each school. There are
four subsets of these classes that may share students between contiguous
classes and two individual classes that may not share with any other class.
After the classes have been organized staff must be assigned according to a
rule for each class type, for example so much music  and so much {*filter*}French
for this class, so much Design and Technology and {*filter*}French for that one,
and then planning time must be added to that depending upon the type of
staff added, and then school dependent and school enrolment dependent staff
must be calculated. The outcome of all that is to produce a table of
sixty-eight school organizations of about 1100 classes with about 1375 staff
assignments. With code, but no SQL strings, we do this in about twenty-two
seconds on a Pentium 133, 32 meg memory machine.
I wanted to do this with a query but stopped when I realized the query
string would approach the thirty some thousand character limit and be
virtually unintelligible for anyone else, and that it would take many
minutes to run, or that I would have to do it with many many smaller queries
which would pass through the enrolment data time after time and take, who
knows, maybe hours to run.
--
Lyle Fairfield

Quote:

>Lyle, the Jet Engine is faster at getting data out than any code you can
>possibly write.  Even that "static recordset" you discuss... if you have a
>saved query and use it for your recordset in code than your code will be
1-4
>seconds faster since Jet will not have to compile your hard-coded SQL
>statement.



Mon, 24 Jul 2000 03:00:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Memory using Access

2. Memory management using Access 97 under WinNT

3. Access using up the memory

4. memory leak using VBA6 and file access

5. memory leak using VBA6 and file access

6. How i can get the Used Memory and Available Memory

7. Memory, Memory and more Memory

8. Memory leaks using shell from VBA with winAPI kernel32

9. Using up to much Memory

10. Oracle - Memory Allocation error using jet

11. timer using up memory

12. How can I make a subform visible by using memory variable

 

 
Powered by phpBB® Forum Software