Gary Kjos
garykjos at gmail.com
Tue Oct 31 10:18:42 CST 2006
I have an application that has a couple gigabytes of data in it. It's a single user database though. Multiple use it but they each have their own copy. I think there are f people using it now. It's a database of extracted Oracle database info relating to customer orders that our marketing people do queries against. It used to be in a regular front end back end configuration - until there got to much data for that. I split the backend into seperate databases for each of the large tables and that has worked pretty well for the past several years now. Occaisionally we have to purge some older order detail records but it's about a once a year thing. The Customer Master Table in this database has almost 5 million records. My local copy of the application has orders in it since the first of January 2006 and has about 4 million rows in that table/database. We have about 500,000 item records in the item table. The customer phone number table has about 3.5 million rows in it. The Customer database is about 500 MB and the order database about 600Mb but I know the users have order databases over a gigabyte. All together it's about a two gigabyte application - depending on how many orders you keep. The users seem to love it. They prefer querying order data in this database to the native Oracle data or into a SQL Server version of the extracted data. I have prebuilt queries that they are able to tweak buy adding rows to "select tables" that are then joined into select queries to select orders for all the customers in a list of states or zip codes or a specific list of customers or items or coupon codes. They get flags indicating if the customer is a new customer - with the customer add date being the same date as the order date. This was originally created 6 years ago before we had the Oracle source system and was updated to extract from the Oracle system after we converted to using that for our main application system. GK On 10/31/06, JWColby <jwcolby at colbyconsulting.com> wrote: > Oh well. I have a client with a database with well over 500 mbytes of data. > I did break it down but I did it for technical reasons having to do with one > table having a bunch of memo fields and lots of activity, which was causing > bloating. I wanted to be able to compact and repair the pieces > individually. > > The main database container in this specific application currently sits at > about 250 mbytes, and has ~40 users in it all day. It is a mission critical > database for a disability insurance call center. I designed it from scratch > starting in March 2002 and it has been in operation since the first week of > August, 2002 (I just checked my billing database). They add about 100 > mbytes of data per year and (so far) have all the claims processed still on > line. In the past months we have brought up a claims payment module that > allows actually paying the claims through a payroll processing third party. > We are about to bring another insurance client on board that will require > similar claims payments processing. > > John W. Colby > Colby Consulting > www.ColbyConsulting.com > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gowey Mike W > Sent: Tuesday, October 31, 2006 9:21 AM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Record Maximum > > > Thanks guys for your input. I was just looking for some ways to explain to > the customer that the rumor that they heard about Access databases crashing > when the size of the database reached over 100mb. I already explained that > this was a vicious rumor and explained to them how it works, but they were > not totally convinced so I was looking for more amminition to though at them > to eleviate their concerns. > > Thanks again > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Hindman > Sent: Monday, October 30, 2006 5:36 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Record Maximum > > ...as JC pointed out, the "number" of records is rarely the driving factor > ...I'd add to JC's comments that the record length plays into the issue as > well ...if each record is 2k its a major factor in your considerations. > > ...and while record count IS a factor in archiving, its not THE major factor > ...I baseline archive a record whenever it meets certain client specific > milestones and only IF the client concurs on a record by record basis ...a > dead record is a dead record whether there are 2K or 2M of them > > William Hindman > > ----- Original Message ----- > From: "Gowey Mike W" <Mike.W.Gowey at doc.state.or.us> > To: "Access Developers discussion and problem solving" > <accessd at databaseadvisors.com> > Sent: Monday, October 30, 2006 4:38 PM > Subject: [AccessD] Record Maximum > > > > > > > > Hi Everyone, > > > > Just a quick question. What is the maximum or the most records that > > anyone has seen in a table before the database started to degrade in > > permormance. > > > > What would you consider the maximum record count before archiving? > > > > > > TIA for your thoughts, > > > > Mike Gowey MCP, MCDST, MCSA, A+, LME, NET+ Team Leader - SouthEast > > Region Information Systems Unit > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- Gary Kjos garykjos at gmail.com