A theoretical question: how best to manage perpetual recordset needs?

[Neil]

I'm looking for some theoretical advice about the best way to manage
resources with a recordset situation I have.

The database in question is going to run on a server and receive inputs from
another device. No user input. It, theoretically, will run for days or weeks
without being closed.

In one part of the code, after a record is written to a particular table, my
code has to look up an ID value in the Devices table, based on the input
record's Client ID and Device Number. Client ID and Device Number are unique
within a Device record, so only one ID value from the Devices table will
match those.

The question is: how best to do the lookups to minimize resource usage,
since the database will need to be running continuously for extended periods
of time. Some ideas:

1) Have a global recordset set to the Devices table, and do a FindFirst each
time a lookup is needed, never closing the recordset.

2) Have a global recordset based on Devices, with a Where clause set to
Client ID and Device Number (so that the recordset only returns one record).
Leave the recordset variable open at all times, but reset it each time to
whatever the current Client ID and Device Number are.

3) Same as #2, only use a local recordset variable which goes out of scope
each time, and is recreated each time a lookup is needed (being sure to
close the recordset and set it to Nothing before exiting the procedure).

Now, #3 might seem like the logical choice, since it opens a tiny
(one-record) recordset, and then goes away at the end of the proc, freeing
up those resources.

However, in the past where I've done that, opening up multiple one-record
recordsets, the program eventually ran out of system resources. In that
case, though, it wasn't with a recordset going out of scope. It was more
with a loop, where the same recordset variable was set to a different
one-record recordset with each iteration of the loop. After a few thousand
iterations, Access gave an error.

Interestingly, clearing the error allowed the loop to continue, so it may
have been due to the fact that these iterations were immediately after each
other, and Access didn't have a chance to "reset" whatever needed to be
reset? In the current situation, there won't be a loop, and the resetting of
the recordset variables will happen at least seconds, if not minutes or even
hours, apart. So it's a different situation.

So that is what I'm wondering. Given a perpetually running database, what's
the best way to manage a recordset that needs to continually be used to look
up a single record value? (I hope someone doesn't say, "Just use DLookup!"
;-) )

Thanks for any advice.

Neil

 

[Access Developer]

If you are talking about an "Access" database (Jet or the newer ACE), those
are file-server databases, which recover "released disk space" and certain
other system resources only when you do a Compact and Repair operation *...
they are not designed nor intended for 24/7/365 operation as you describe.
So, any suggestions you receive, including mine will just be for "making the
best of a bad situation".

* that is why, in the previous experience you
described, you encountered running out of
system resources

Often, you'll see some of us here "defending" Jet/ACE databases against
those who contend they are useless. But, if you read carefully, you'll see
that we suggest "appropriate tools for the environment", not "use Jet/ACE
whenever and wherever you have need for a database engine".

What do you mean by "run on a server and receive inputs from another
device"? And, indeed, are you asking specifically about Jet or ACE
databases? How will the database "receive inputs from another device"?

For the database to detect "when a record is written to a specific table",
unless it is your code doing the writing, the database will have to support
a feature called "triggers" -- Jet does not support triggers, and they are
new in ACE, but, even so, I have not read of many, if any, "issues" with
them.

I'm guessing... just guessing from what you wrote... that either the
database application is still to be defined, or the database engine is not
yet selected. If so, then a server database would be a better choice than
either Jet or ACE. You do not describe what the purpose of the data will
be... surely it's not a "write-once, read never" thing -- that would not be
useful, unless to perhaps meet arbitrary requirements for saving data that
do not specify the saved data will ever be retrieved/used.

If you can clarify some of the points I mention here, you will vastly
improve the probability that someone can either offer useful suggestions or
direct you to a better source of information.

Because of the way Access, Jet, and ACE operate, to avoid the potential of
DB corruption, I avoid having recordsets "just sitting around open"... I
make use of recordsets as compact and contained as possible, often (and that
would certainly be the case where you do not know when, or how often,
activity will be needed), I open the Table/Query, read/write the Record, and
then immediately close the Table/Query to force the update to take place.
That would eliminate from my consideration an "always-open" Recordset as you
describe... having correct (uncorrupted) data is essential; performance is
worthless if the data is not correct.

 

[Neil]

Hi, Larry.

What do you mean by "run on a server and receive inputs from another
device"? And, indeed, are you asking specifically about Jet or ACE
databases? How will the database "receive inputs from another device"?

It's a 2010 ACCDB file. There is Windows-based software called KEPServer
which will receive data from external devices via the Internet, and then
will write it to a database of the user's choosing. The KEPServer will be
writing the values to my ACCDB file, one record at a time.

For the database to detect "when a record is written to a specific table",
unless it is your code doing the writing, the database will have to
support a feature called "triggers" -- Jet does not support triggers, and
they are new in ACE, but, even so, I have not read of many, if any,
"issues" with them.

Yes, will be using the ACCDB AfterInsert Data Macro.

I'm guessing... just guessing from what you wrote... that either the
database application is still to be defined, or the database engine is not
yet selected. If so, then a server database would be a better choice than
either Jet or ACE. You do not describe what the purpose of the data will
be... surely it's not a "write-once, read never" thing -- that would not
be useful, unless to perhaps meet arbitrary requirements for saving data
that do not specify the saved data will ever be retrieved/used.

Once the data is written to the raw data table by KEPServer, the AfterInsert
macro will copy the record to another table in normalized format. Here is
where the lookup is needed, since it needs to look up the device ID based on
the information passed to it by KEPServer. The routine will also perform
calculations and write them to a seperate table. These two tables (the
normalized data and the separate calculation table) will be used for
reporting purposes only. No editing of the data. So it's a write-once,
read-many thing. (The only data the users will be editing are the lookup
tables, client lists, etc.)

If you can clarify some of the points I mention here, you will vastly
improve the probability that someone can either offer useful suggestions
or direct you to a better source of information.

Hopefully the above notes clarify. Sorry for not specifying the database
format originally. Should have. If you need more info, let me know.

Because of the way Access, Jet, and ACE operate, to avoid the potential of
DB corruption, I avoid having recordsets "just sitting around open"... I
make use of recordsets as compact and contained as possible, often (and
that would certainly be the case where you do not know when, or how often,
activity will be needed), I open the Table/Query, read/write the Record,
and then immediately close the Table/Query to force the update to take
place. That would eliminate from my consideration an "always-open"
Recordset as you describe... having correct (uncorrupted) data is
essential; performance is worthless if the data is not correct.

You make an excellent point. And I may just do it that way for that reason
alone. However, I do want to point out that the recordset I was talking
about leaving open continuously would be a snapshot recordset of a lookup
table, used only for looking up values. No editing or writing to the
recordset would be performed. So it's a slightly different situation than
what you describe. Still, you make a valid point about corruption.

Thanks, Larry!

Neil

 

[ralph]

Hi, Larry.


It's a 2010 ACCDB file. There is Windows-based software called KEPServer
which will receive data from external devices via the Internet, and then
will write it to a database of the user's choosing. The KEPServer will be
writing the values to my ACCDB file, one record at a time.


Yes, will be using the ACCDB AfterInsert Data Macro.


Once the data is written to the raw data table by KEPServer, the AfterInsert
macro will copy the record to another table in normalized format. Here is
where the lookup is needed, since it needs to look up the device ID based on
the information passed to it by KEPServer. The routine will also perform
calculations and write them to a seperate table. These two tables (the
normalized data and the separate calculation table) will be used for
reporting purposes only. No editing of the data. So it's a write-once,
read-many thing. (The only data the users will be editing are the lookup
tables, client lists, etc.)



Hopefully the above notes clarify. Sorry for not specifying the database
format originally. Should have. If you need more info, let me know.



You make an excellent point. And I may just do it that way for that reason
alone. However, I do want to point out that the recordset I was talking
about leaving open continuously would be a snapshot recordset of a lookup
table, used only for looking up values. No editing or writing to the
recordset would be performed. So it's a slightly different situation than
what you describe. Still, you make a valid point about corruption.

For pure static views with long life-times and presenting a minimal
impact on an active database file or server, you might checkout using
a "disconnected Recordset". Plenty of examples on the web.

-ralph

 

[agiamb]

If the lookup set is rarely updated, another option (also depending on
number of rows), would be to use GetRows to put the data into an array. Then
do all your lookups in the array. You'll be surprised at how fast it is.

I built a similar project some years ago in Access 2000.
Data input was via serial port, continuously streaming in.
I had two local be files (be1, be2) with the same structure.
The data was inserted into be1 from the fe, then periodically the fe would
switch to inserting the data into the be2 and the data from be1 would be
copied to a network file, then deleted.
be1 would be then compacted.
Periodically, the same would happen with be2.

In the case of the network file being unavailable, the system would simply
try again later, never missing a beat.
This has been running at an electric utility for about 10 years now.

 

[Neil]

For pure static views with long life-times and presenting a minimal
impact on an active database file or server, you might checkout using
a "disconnected Recordset". Plenty of examples on the web.

I searched for that. But, from what I can tell, that's purely an ADO
feature, right? I'm using DAO.

Thanks.

 

[Neil]

Very nice! Tell me: it's been running for 10 years - but does the database
or Access itself need to be restarted once in a while? Seems to me that
there would be memory issues with a continuously-running program, without
restarting it periodically. Shoot, I can't even imagine Windows itself,
without any apps running, going for too long without needing to be
restarted.

So can you tell me what their protocol is? Do they periodically reboot the
machine or at least restart Access?

Re. the array - that's a great idea, actually! I was originally thinking
that an array would be essentially the same as an open recordset, but I
guess not. Thinking about it, the recordset might not have all the items in
memory at all times, whereas the array would have to. So that probably would
be much faster than an open recordset.

Still, my main concern isn't with speed as much as system resources. But,
again, an array might be better at using fewer system resources.

Thanks!

 

[agiamb]

I have no idea if they restart or reboot, but if they were having a problem
I would surely have been contacted.
I do know that they have two dedicated machines running the app, just in
case one fails, they won't lose any data.
I've only worked on the system twice since it was initially stabilized. Once
when they got new equipment feeding the data and the line termination
characters changed. And once when they replaced a machine and linked the app
to the wrong back end.

As far as resources, etc., that's why I mentioned the number of rows.
Actually, it should be rows and columns (fields).
Several thousand, I probably wouldn't worry about, but millions, maybe.
You'll need to test it out.
I mentioned speed as it can be important depending on the frequency of the
lookups.
I first used the array method in a VB app about 12 years ago. We changed
from an Access be to SQL Server and went from DAO to ADO. There was a lot of
processing to be done in recordsets that could not be done in a set based
operation within SQL Server. Performance tanked going from DAO to ADO. I
started using arrays instead of recordsets and got all my performance back,
and then some.

 

[ralph]

I searched for that. But, from what I can tell, that's purely an ADO
feature, right? I'm using DAO.

Yes, a "disconnected Recordset" is purely an ADO technique. Should
have asked what data library you were using, but with a quick read of
your question it appeared you were having trouble with "variable
references", therefore I erroneously assumed ADO. (Which is how most
assumptions turn out. <g>)

DAO requires a connection to a database.

For situations where a client needed to maintain a data set with
fields completely divorced from other tables using DAO, TableDef that
is populated with the associated data is often used. You could
probably just as easily resolve problems by restructuring the
database.

However, GetRows appears to be a good solution. (Wish I had thought of
it before I posted. <g>)

Both DAO and ADO have a GetRows method. The difference between them is
DAO's GetRows(n) requires a recordnumber, and ADO's GetRows() will
retrieve all rows by default.

-ralph

 

[Access Developer]

Certain types of error can cause variables to lose their value... arrays are
variables... that's not an option that is safe; as you could well lose (LOSE
FOREVER) all the data you store in the array.

And, you must make provision to save the array's contents when you shut down
and to restore it when you restart.

 

[agiamb]

The OP indicated that he needed a lookup, so saving is not an issue.
Of course there needs to be a method to fill the array initially.
What I typically do is, each time the array is accessed, check its Ubound.
If none, then reload the array. All transparent to the user (or absent
user).

 

[Access Developer]

Very nice! Tell me: it's been running for 10 years -
but does the database or Access itself need to be
restarted once in a while? Seems to me that there
would be memory issues with a continuously-running
program, without restarting it periodically.

I think I mentioned in an earlier post, the Jet and ACE database engines do
not return "system resources" that they have obtained until the DB is closed
and a Compact and Repair operation performed -- that it was not designed
for, and can't be used as, an "always-on 24/7/365" database store as can
many server databases (including MS SQL Server, Oracle, MySQL, and others).

Larry Linson, Microsoft Office Access MVP

 

[Access Developer]

I wouldn't want to argue the point, because I do not fully understand the
poster's requirements... _but_ there's been no specific mention of saving
not being an issue. Also, if the array is to be _reloaded_, the data must be
saved somewhere. And, depending on the circumstances, reloading the array
every time the database app is started may or may not be appropriate.

 

[The Frog]

Using something like MySQL could be very useful here. Maybe the OP
can make use of the 'Memory' database engine for the lookup scenario,
and a more traditional engine for the storage. If saving the array
locally is a requirement, then if I remember correctly it should be
possible to do so as an ADO recordset to a file, then reload on
application startup, putting the recordset back to an array using
Getrows.

The 24/7/365 for receiving data from hardware is not uncommon, but
you do need to pick the tool for the job. We had a lot of success
with Firebird, MySql, and MSDE in a former company I worked at, but
each was chosen for different reasons for each use case scenario. We
also had a dedicated piece of software to handle the receiving of the
data (ie/ it ran as a separate process). This gave us a lot of
flexibility in our design and implementation. We also drafted
comprehensive specifications for each component: FE, Receiving
Server, DB, and how they would interact with each other. This made
design and implementation much easier, as well as maintenance. We
could also bring in outside resources if needed and test their work
against the specification.

Just my 2cents

 

[agiamb]

Hi Larry,
Yes, the OP left out a lot of details, but was also only asking for
theoritical ideas. Of course, he will have many issues to consider. My
understanding is that it is basically for a 'lookup', however, in one post
he also mentions that the data is updated by another app. So, depending on
how often that happens, he will need to re-load his data anyway. Using an
array is not a fix-all. It has it's place, like everything else.

 

[Neil]

The data that is loaded from the other app does not affect the lookup. The
lookup is of the device that the data pertains to. This device would need to
be manually added to the Devices table by the user. In that case, the lookup
would be affected. This would be a rare occurrence, and, if it did happen,
the table's AfterInsert macro could run code to clear the array, forcing the
array to be reset at next use. Or the code could be modified so that if a
lookup item is not found, first try a reset of the array before giving an
error. That would probably be the best way to go.

Either way, yes, the array will be for all intents and purposes static -
with the exception of an occasional addition to the lookup table (again,
very rare).

Neil

 

[Neil]

The number of records (rows) in the lookup table will be very small.
Probably about 20 per client. Right now there's only one client that the
system will be used with. Potentially more later. So, either way, very, very
small numbers.

So performance really isn't an issue. I'm sure I wouldn't see any difference
in looking a lookup in an open recordset vs. an array. With just a few
hundred records at most, it'll be virtually instantaneous either way.

My main concern is the drain on resources with a perpetually-open recordset
vs. a recordset that is continually opened and closed vs. (new
consideration) a perpetual array. Which one would tax resources the least?
That's my main concern.

Thanks.

 

[Neil]

The system resources I'm referring to would only be in memory. There would
be writing of data, so compact and repair is not an issue. I'm only
discussing the memory resources that are used when a recordset is opened and
reopened (snapshot type).

 

[Neil]

If saving the array
locally is a requirement,

Saving the array locally is not a requirement. The array would only be for a
lookup table, as noted in the original post. No saving of the array
required.

Neil

 

[ralph]

The number of records (rows) in the lookup table will be very small.
Probably about 20 per client. Right now there's only one client that the
system will be used with. Potentially more later. So, either way, very, very
small numbers.

So performance really isn't an issue. I'm sure I wouldn't see any difference
in looking a lookup in an open recordset vs. an array. With just a few
hundred records at most, it'll be virtually instantaneous either way.

My main concern is the drain on resources with a perpetually-open recordset
vs. a recordset that is continually opened and closed vs. (new
consideration) a perpetual array. Which one would tax resources the least?
That's my main concern.

'Access Developer' is actually answering most of your questions, but
it doesn't seem to be sticking. So perhaps it would be helpful if you
provided more details of your target domain - the clients and data
stacks involved - program and control flow, ..., then he and others
can better address specific issues with your design.

As for a "drain on resources", that is always possible with any client
application that continually opens and closes an object, and with any
object. One always runs the risk of eventual fragmentation or worse
accidently leaving a reference behind. Doesn't necessarily follow it
will occur, but it might.

Creating an Array of a fixed 'maximum' length and reusing it can
mitigate that issue.

-ralph