-
Oct 25th, 2020, 06:41 AM
#1
Thread Starter
Member
A bit of advice - maximum number of DAO recordsets open at once?
I'm doing a bit of work on a client's POS system. At present it uses about 65 DAO recordsets. This corresponds to many different tables, most of which are opened twice, once as a table and once as a dynaset as some bits of the system use indexes to locate things and others use find first...find next. These tables are housed in 10 database files, so there are also 10 databases. All of these are opened at once on launch and remain open the entire time the program is running, even if not being actively used. The system is also multi user (usually 2 copies open at once but can be 3)
So far, this works fine. However, I am now looking at making it a pseudo-multisite system by having a copy of all the datafiles up to the previous day hosted at each site so each copy of the system has access to the data. So far I've got some simple apps which make use of this but they only need a couple of recordsets from each copy of the system open to do their work. Now I'm looking at integrating this all into the main program, and want to be able to access all the data.
I'm concerned as to how well the current implementation would scale up if I simply added all the extra databases and recordsets in, this would mean 70 databases and 455 recordsets all open at the same time. Is this even a problem or should I be looking at better ways of doing this? I've already considered moving all the tables into one file - they were split up originally out of concern for the 2GB limit but it is clear that they are not going to get anywhere near that during the life of the system so that's easily done. Doesn't alter how many tables there would be though. Possibly some middleware which merges the same tables from different copies of the system together into one big table?
Or as I said, if it will work fine I'll just go with having the huge number of recordsets.
-
Oct 28th, 2020, 04:54 PM
#2
Re: A bit of advice - maximum number of DAO recordsets open at once?
I'm concerned as to how well the current implementation would scale up if I simply added all the extra databases and recordsets in, this would mean 70 databases and 455 recordsets all open at the same time.
Have one database, with the appropriate number of tables in it... in addition, add a SiteID to each table, or at least to the parent tables, so that you can determine the "site owner" of the data... Then each site would get data that's associated with that siteid.
Example: Let's say you have a Sales table... it has the list of sales.
It might look like this:
Code:
ID - int (PKEy)
SiteId - int (FKey to Site table)
Amount - money
ItemId - int (FKey to Item table)
.. and so on
Now the Item table... if all sites have the same items...
Code:
ID - int (PKey)
Name - string
Description - string
Price - money
-- and so on
but if each site could have different items for sale:
Code:
ID - int (PKey)
SiteId - int (PKey to Site table)
Name - string
Description - string
Price - money
-- and so on
-tg
-
Nov 4th, 2020, 06:53 PM
#3
New Member
Re: A bit of advice - maximum number of DAO recordsets open at once?
there doesn't seem to be an actual limit, but probably not a good idea, one crash will destroy them all
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|