Subject Re: Relation among 3 files running slowly From Mervyn Bick <invalid@invalid.invalid> Date Sat, 25 Jul 2015 18:42:59 +0200 Newsgroups dbase.getting-started On 2015/07/24 09:53 PM, BobM wrote: > A fairly straightforward operation is running very slowly, no idea why.  Looking for suggestions! > > I have three dbase files, one fairly large: "A" with 1.5 million records, file "B" with 1,000 records; and file "C" with 1,000 records.  Want to replace values in file A with a ratio of other items in A, B, and C.  All key values are unique. > > Here's what I am doing: > > Select a > use file_A > Select  b > use file_B exclusive > index on key1 tag key1 > set order to key1 > Select c > use file_C exclusive > index on key2 tag key2 > set order to key2 > select b > set relation to key2 into C > select a > set relation to key1 into B > > repl all field1 with field2 * (b->field1/b->field2) * c->field3 > > I expected this to take minutes or hours but after 2 days it was still running. > > I am running dBase 9 plus; OS is Windows 8.1, 64 bit, 2.4 GHZ; 16GB RAM; files are stored on a 500GB SSD. > > Any ideas where the slowdown might be coming from? > > Bob > It's years since I worked with DML so I wouldn't like to guess why your program is taking so long.  Mind you, churning through 1.5 million records could take a while even on a SSD. You can try the following.  It is, however, completely untested so it may not work at all. Joins in a query render the rowset read-only.  I have, therefore used two queries. One to create a rowset with the appropriate values in each record and the other to be able to write the new values into field1 of table_a. q = new query() q.sql = 'select * from file_a order by key' q.active = true q1. = new query() q1.sql = 'select a.*,b.field1 f1,b.field2 f2,c.field3 f3 from file_a a ' q1.sql +='inner join file_b b on (a.key=b.key1) ' q1.sql +='inner join file_c c on (b.key1 = c.key2) order by a.key' q.active = true qrf = q.rowset.fields q1rf = q1.rowset.fields n = 1 nSec = seconds() do while not q1.rowset.endofset     qrf["field1"].value = q1rf["f2"].value / q1rf["f2"].value * q1rf["f3"].value     q.rowset.next()     q1.rowset.next()     n++     if n%5000 = 0       ? n, seconds() - nSec     endif enddo q.active = false q1.active = false You will need to replace the "key" in the SQL statements with the actual field names. To give you some idea of progress the program will print the number of records  processed after every 5000 records and the number of seconds taken.  dBASE may stop printing after a while but it should continue working in the background.  The time taken to process records before printing stops should give you an idea of how long the whole process will take. Caveat emptor!  The code is untested.  Only try it after you have made sure you have the tables safely backed up. Mervyn.