Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Poll: Largest Access Database?
- Thread starter bmoyer23
- Start date
bmoyer,
One that I'm working on now has 6 separate backend .mdb's some with just a single table, all of these are at or near the 1-gig limit, so a total of nearly 6-gig. It's for temporary data-warehousing, until Oracle enters the real world as far as per seat licensing. The front-end links all of these and does mostly reporting, but also the daily import from 5 different VAX boxes around the country. It's a stop-gap solution until the managment at this client waits-out Larry Ellison until he's desparate enough to lower the price of the Oracle licenses we need to roll this reporting/data-warehouse system to the necessary users.
It is quite fast, most reports over the main (order & ticket detail files) files, with well over 3 mil records, run quick--I can get a monthly order/volume summary, grouped by day, in about 30 seconds or less. When the largest table, with over 3 mil records, and about 900 meg, gets to the 1-gig limit, then it's time to split it, probably by date, into to .mdbs. Hopefully this client will be migrated to Oracle by then.
Al
One that I'm working on now has 6 separate backend .mdb's some with just a single table, all of these are at or near the 1-gig limit, so a total of nearly 6-gig. It's for temporary data-warehousing, until Oracle enters the real world as far as per seat licensing. The front-end links all of these and does mostly reporting, but also the daily import from 5 different VAX boxes around the country. It's a stop-gap solution until the managment at this client waits-out Larry Ellison until he's desparate enough to lower the price of the Oracle licenses we need to roll this reporting/data-warehouse system to the necessary users.
It is quite fast, most reports over the main (order & ticket detail files) files, with well over 3 mil records, run quick--I can get a monthly order/volume summary, grouped by day, in about 30 seconds or less. When the largest table, with over 3 mil records, and about 900 meg, gets to the 1-gig limit, then it's time to split it, probably by date, into to .mdbs. Hopefully this client will be migrated to Oracle by then.
Al
Jim has mentioned speed and the times it takes to load some of his items. How can speed be tested and analyzed, other than opening them and counting by Scooby-d-Doo's? Also, do you split out back-end mdb's for size reason's, or for security reasons? Sean.
perryman,
Size was the primary reason for splitting the backends, it was utterly necessary. Testing and analyzing is tough to pin down, since it depends largely on what the purpose of the db is.
As we speak, I'm writing this while waiting for a query to run, linking 4 tables, each from different .mdbs on the network. This one takes about 5 minutes, still not bad for over-the-lan performance of .mdbs this size.
The main thing you want to do is make sure the indexing is done carefully. Since Access is File-server as opposed to Client-server, if the joined fields, for example, are indexed, then the local (front-end) only has to schlep the indexes over the network to complete the join, rather than the entire table.
One way to test this is to run some test queries on the server machine, where it's local, then run the same query on your workstation, and compare performance. I'm able to do that here with VNC--I can vnc into the server, an NT box with Access running, then open one of the backend db's, link to the other backends (on the same machine) and run a query. Then I run the same query here on this machine. There will alway be a performance difference, but huge differences often mean an indexing problem (which can't always be helped--if the query has so many joins, it may be unavoidable that JET needs to drag entire tables over to the client to do the logic).
--Jim
Size was the primary reason for splitting the backends, it was utterly necessary. Testing and analyzing is tough to pin down, since it depends largely on what the purpose of the db is.
As we speak, I'm writing this while waiting for a query to run, linking 4 tables, each from different .mdbs on the network. This one takes about 5 minutes, still not bad for over-the-lan performance of .mdbs this size.
The main thing you want to do is make sure the indexing is done carefully. Since Access is File-server as opposed to Client-server, if the joined fields, for example, are indexed, then the local (front-end) only has to schlep the indexes over the network to complete the join, rather than the entire table.
One way to test this is to run some test queries on the server machine, where it's local, then run the same query on your workstation, and compare performance. I'm able to do that here with VNC--I can vnc into the server, an NT box with Access running, then open one of the backend db's, link to the other backends (on the same machine) and run a query. Then I run the same query here on this machine. There will alway be a performance difference, but huge differences often mean an indexing problem (which can't always be helped--if the query has so many joins, it may be unavoidable that JET needs to drag entire tables over to the client to do the logic).
--Jim
Perrymans, I split out backends so that many users can have a copy of the front end and when I make changes I can just copy to all without messing with the data. bmoyer, I am not answering your question because I am afraid, even uncompacted, that my largest database isn't over a few MB even with data 
Dawn
Dawn
- Status
Similar threads
- Locked
- Question