Troy and Frank In 7.0 speed of accessing an sconnected / sattached table is still quite slow between two databases. However, with one minor adjustment to Troy's plan, you get GREAT speed. Instead of
1. CONNECT HISTORY database 2. OUT FILENAME 3. UNLOAD DATA FOR THISTABLE_HIS 4. OUTPUT SCREEN 5. CONNECT daily database 6. CREATE TEMP TABLE THISTABLE_HIS 7. LOAD THISTABLE_HIS FROM filename I do this 1. CONNECT dailydatabase 2. SCONNECT THISTABLE_HIS 3. PROJECT TEMP TempTHISTABLE_HIS FROM THISTABLE_HIS USING ALL 4. Create any indexes needed In my current live sample, moving about 14000 rows into this temp table and creating indexes takes about 10 seconds and it's done and the table's ready to use with fast access and fast linking to other tables. David Blocker 1. Create temp table THISTABLE_HIS 2. ----- Original Message ----- From: "Troy Sosamon" <[EMAIL PROTECTED]> To: "RBASE-L Mailing List" <[EMAIL PROTECTED]> Sent: Monday, September 15, 2003 1:39 PM Subject: [RBASE-L] - Re: Database capacity constraints? > Frank, > > You have a lot invested in R:base. There are ways around the 2 gig limit by > getting a little creative. > I need to know more about your data structure and what you are storing to > get specific on how to store the data. > > Some things to consider. If you have large text fields, change the to note > fields, they use less space. If you have lots of note fields and your size > problem is in the #2 file, look and changing them to varchar fields and this > will move them to the #4 file instead. > > Do you really need instant access to all of the data? > I usually find on really large tables, that a lot of the information is old > and not used very often. One method I use in this situation is to store the > current data in THISTABLE, put the history in another database in a table > called THISTABLE_HIS, create a perminant temp table in your current database > called THISTABLE_HIS, create a view between THISTABLE and THISTABLE_HIS, > create a view called THISTABLE_ALL using a union so all rows are availave > from THISTABLE and THISTABLE_HIS. Store all of your old information in the > second database in THISTABLE_HIS and when you need the data from that table, > connect to the second db, unload the data from the history db and load it > into the current db. Now you can access everything through your view. > Delete the rows out of your THISTABLE_HIS in the current db every night. > > I have found the performance is unacceptable when dealing with large > datasets via Sconnected tables. I have not tried it in version 7.0, 7.0 may > have this fixed. The sconnect method is ok if you don't need a lot of data. > When you need a lot of data, it is faster to connect to the foreign database > and unload it, connect to your main db, and then load it to a temp table in > your main database. > > Troy Sosamon > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Bill > Downall > Sent: Sunday, September 14, 2003 10:05 PM > To: RBASE-L Mailing List > Subject: [RBASE-L] - Re: Database capacity constraints? > > > Frank, > > Troy Sosamon and others on this list have worked with database > applications that broke very large data sets into multiple databases. There > could be a number of strategies that would help you beat this limitation, > any of which would be way less work than rewriting the whole application > on a new platform. > > Bill > > On Mon, 15 Sep 2003 15:48:59 +1200, van der Zwaag, Frank wrote: > > >Looks like I'm a bit stuck now. I have done so much programming work in > >Rbase, I mean there are countless modules that do very clever things, > >and I would hate to loose all that and have to start converting this to > some > >obscure language like VB or RB. > > >How about using some other database engine let's say mySQL or > ORACLE and > >then use Rbase to talk to it through ODBC could that be a viable option for > >me to pursue? Anybody out there who has done this? > > >Thanks > > >Frank > > > > >-----Original Message----- > >From: Bob Simms [mailto:[EMAIL PROTECTED] > >Sent: Monday, 15 September 2003 15:22 > >To: [EMAIL PROTECTED] > >Subject: [RBASE-L] - Re: Database capacity constraints? > > > >Frank, > > >The size limit is 2 GB per file. Most typically, the limit is > >reached with the RB2 file, that is, the data file, the RB3 file being > >used for the indexes. This is a hard limit, and when it is exceeded, your > >database pretty much turns into hash. This has been my experience. > >Oterro won't help with this. > > >A question for the dream team: would it be possible to extend the > >capacity of RBASE databases (in, say, version 7.5) by allowing multiple > >RB2, 3, and 4 files for a single database? The first version of this > >scheme might > >constrain tables to being wholly contained in a single RB2 file. The RB1 > >file would have to > >keep track of which RB2 file contains each table, but after that, I/O > >operations would > >be the same as before, and best of all, no application programming > changes > >would be required. > >This could keep users like Frank from having to migrate to some odious > >Brand X database. > > >. . . Bob S > > > >At 11:56 AM 9/15/03, you wrote: > >>Good morning everybody. > >> > >>I developed a system for the NZ Police for criminal profiling in Rbase 6.5 > > >>This system is going into its 8th year and was originally written as a > >small > >>system in 1994 with Rbase 4.5. > >> > >>The database consists now of 46 tables with 310 fields. We have > currently > >>loaded 11,743,000 records. One table contains slightly over 4,000,000 > >>records. > >> > >>We tried to add extra data and noticed that the system stops after > loading > >>an additional 470,000 records (approximately). > >> > >>Apart from the fact that this is hindering an important piece of work, we > >>are currently loading an additional 30 - 40,000 records to the database > >each > >>month, which implies that we would be running out of space within the > next > >>year. > >> > >>Is there a capacity limit within Rbase? Is Oterro a viable alternative? Or > >>should we go to something like mySQL and use Rbase as a front end > through > >>ODBC? > >> > >>Thanks > >> > >> > >>Frank van der Zwaag > >> > >> > >> > __________________________________________________________ > __________ > >>CAUTION - This message may contain privileged and confidential > >>information intended only for the use of the addressee named above. > >>If you are not the intended recipient of this message you are hereby > >>notified that any use, dissemination, distribution or reproduction > >>of this message is prohibited. If you have received this message in > >>error please notify Air New Zealand immediately. Any views expressed > >>in this message are those of the individual sender and may not > >>necessarily reflect the views of Air New Zealand. > >> > __________________________________________________________ > ___________ > >>For more information on the Air New Zealand Group, visit us online > >>at http://www.airnewzealand.com > >> > __________________________________________________________ > ___________ > > > > >------------------------------------------------------------ > > Bob Simms > > Robert A. Simms and Co., Los Angeles > > 818-345-5306 Fax 818-345-5136 > > E-Mail: mailto:[EMAIL PROTECTED] > > URL: http://www.pacificnet.net/simms > >------------------------------------------------------------- > > >_________________________________________________________ > ___________ > >CAUTION - This message may contain privileged and confidential > >information intended only for the use of the addressee named above. > >If you are not the intended recipient of this message you are hereby > >notified that any use, dissemination, distribution or reproduction > >of this message is prohibited. If you have received this message in > >error please notify Air New Zealand immediately. Any views expressed > >in this message are those of the individual sender and may not > >necessarily reflect the views of Air New Zealand. > >_________________________________________________________ > ____________ > >For more information on the Air New Zealand Group, visit us online > >at http://www.airnewzealand.com > >_________________________________________________________ > ____________ > >
