Bob,
Run your archiving routine early in the day and limit the selection to (addday(#date, - 1)). Emmitt Dove Converting Systems Architect Evergreen Packaging, Inc. [email protected] (203) 214-5683 m (203) 643-8022 o (203) 643-8086 f [email protected] From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Tuesday, December 21, 2010 13:52 To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Archiving logic Thanks for everyone's response. I will consider the alternatives and do some testing. Using a date in this situation is a bit risky as orders are entered virtually 24 hours a day via EDI. So if I ran the process at 6:00pm and new orders came in at 6:30 for example, I would run into issues. To be 100% accurate, I would have to be able to shut down any type of order entry and that would not be feasible in this case. Otherwise, dates would work well. Here I need to use unique indexes. As far as speed goes, I will do some timing. Currently, if I dump both tables via a Project Temp command, it takes about 1 hour. (This is on fast equipment, gig nics, etc. etc.) I believe the issue is that the foreign database's ODBC is simply slow. It is a client/server system based on the Pervasive Database. I can call small data sets very fast, but in the past, trying to link to large data sets seemed to bring it to it's knees. If simply dumping data takes 1 1/2 hours, I would think that doing a comparison operation between two 100,000 + tables would take for ever. It would have to compare every record in both tables to find the ones to be added. I will give it a test run and see. It certainly would be the "cleanest" option, but I am afraid it would very slow. As others have suggested, I can dump all records and then use the faster speed of Rbase to do the comparison. If I can prevent it, I like to avoid handling data twice in such a manner, but in this case it might be the best best. Thanks again. -Bob ----- Original Message ----- From: "Lawrence Lustig" <[email protected]> To: "RBASE-L Mailing List" <[email protected]> Sent: Tuesday, December 21, 2010 8:35:03 AM Subject: [RBASE-L] - Re: Archiving logic << but this is not efficient nor probably even possible. The Rbase table will have well over 200,000 rows and will grow while the ODBC table will have 100,000+ rows. It would probably take hours to run the above if it would run at all. I cannot flag the ODBC records in any way as I cannot change the data structure or data in the ODBC database. What makes this difficult is that the unique index on the foreign data base is order number, but it is not numeric, it is alphanumeric. So an order could be OR123456 and the next order could be AB123456. >> Why do you think it will take so long? The search (assuming the order id is indexed in both tables) shouldn't be bad. The amount of time that it will take to execute the command will depend on how many orders have to be added to the archive each time you run. Assuming that's small (a couple of hundred, let's say), I don't see why this should take a lot of time. For greatest speed in archiving, keep the target archive table free of any indexes except the order id. -- Larry
