Filling fields from another table in mass

[DaveM]

Thanks for any speedier code in advance.

scenario.

main table has matching field
from table has index on the matching field
matching fields are not overwritten
38 matching fields to fill from one other table (1 field is a small rtfmemo)
By using operations and lookupc(), lookupL(), etc., it takes about 3.5 hours on a fast local machine but it does work well.
Joining gave a few unpredictable results, but a lot faster.
Currently using a function where the operations/update filled was copied from.

May be because there are just over 100,000 records and 38 fields.

Question is: Is there a faster method I missed and if so, some ideas?

[MoGrace]

Re: Filling fields from another table in mass

Hi Dave,
Are you using an append to update one table with data from another, or are you also adding new records at the same time?

[DaveM]

Re: Filling fields from another table in mass

Hi Robin,

I do an append to get the field values from a server on a lan.
Once that is done, I am combining the records from one table into empty fields in another one. all local db- if I tried the field update over the lan, it would be extremely slow.
It would be great if I could append from 2 tables at one time, but that does not happen. I tried to append from a set, but alas, that did not happen either.
After the appends are done with the many tables I have, the one tables records need to be brought to the main(keeper) table. The other table will be discarded after all is done.
38-65 minutes just to do all the appends. And add from 3 to 4.5 hours for the fields to be updated. >>> Long time!

Call them tbl1 and tbl 2
both have the right records.
running lookup() on each field in the

possibly, I could run a table intersect and the append from the created table name faster??

[MoGrace]

Re: Filling fields from another table in mass

What is the purpose of the look up and when are you doing that? Is it a separate process?

Still trying to get a handle on what you are doing....

[DaveM]

Re: Filling fields from another table in mass

It can be done by operations/field update. pick the correct table, Choose a field( I have 38 of them) and do the lookup() to fill that field from a corresponding field in another table. field is name, then
lookup("f", idnum, "name", "tablename", "idnum") should get that field filled by 100,000 times if there are 100,000 records. Do that 38 times for 38 fields??? = a lot.

I guess I am looking for something faster than lookup()'s

[Allen Klimeck]

Re: Filling fields from another table in mass

I would use table.open if I understand what you are doing.

tbl1 = table.open(main)
tbl1.index_primary_put("matching_field_index")
tbl2 = table.open(from)
tbl2.index_primary_put("matching_field_index")
tbl2.fetch_first()
while .not. tbl2.fetch_eof()
vnfind = tbl1.fetch_find(tbl2.matching_field.value)
if vnfind > 0
	tbl1.change_begin()
	tbl1.field1 = tbl2.field1.value
	...37 more fields...
	tbl1.change_end(.t.)
end if
tbl2.fetch_next()
end while

Should have added .BATCH_BEGIN()..... .BATCH_end() in the code

[DaveM]

Re: Filling fields from another table in mass

Thank You Allen, That looks like the way to go. I will test it later tonight.

[MoGrace]

Re: Filling fields from another table in mass

I think Allen needs to add a fetch for tbl1 in there too, else you will only update one record in tbl1 - or so it seems to me.

- or -

Perhaps I should actually go and look up the doc on fetch_ find....

[Al Buchholz]

Re: Filling fields from another table in mass

Copy the set to a table.

Use the append operation not xbasic enter.begin/end - much faster because it runs in C++

Using lookups will kill the speed.

[Tom Cone Jr]

Re: Filling fields from another table in mass

Dave, the lookup() function opens a fresh instance of the table each time it's called. this is among the slowest things alpha does. Opening the source table and looping through its records, as Allen suggests, should be much faster.

[Al Buchholz]

Re: Filling fields from another table in mass

Dave, the lookup() function opens a fresh instance of the table each time it's called. this is among the slowest things alpha does. Opening the source table and looping through its records, as Allen suggests, should be much faster.

Yes the method that Allen K recommends is faster than the lookup.

The append operation is another level faster and is worth the effort to build the appropriate pre-processing.

[DaveM]

Re: Filling fields from another table in mass

This is not an append operation candidate.
There are 45 fields in the first table that are already filled. The 38 new fields are blank to be filled from another table, I can find no way of doing this with an append.
I think Allen's method is going to be very much faster than using lookup(). However, this will only have to be done one time once in production environment and writing the code may take a lot longer.

[MoGrace]

Re: Filling fields from another table in mass

But Dave, if tbl1 and tbl2 have a matching key field, then why wouldn't an append work? In the mapping you only need to map the 38 fields you are updating.

[DaveM]

Re: Filling fields from another table in mass

an append adds to records so I would have:
name1
name2
name3
name4
address1
address2
address3
address4
instead of:
name1 address1
name2 address2
name3 address3
name4 address4

[Al Buchholz]

Re: Filling fields from another table in mass

an append adds to records so I would have:
name1
name2
name3
name4
address1
address2
address3
address4
instead of:
name1 address1
name2 address2
name3 address3
name4 address4

No that is incorrect ( or at least incomplete).

There is an option of the append operation to add unique, replace existing.

appendoptions.PNG

That way if a record doesn't exist in the target, the append adds it. Otherwise the values are updated.

On the other hand:

If you are 100% certain that the records exist in the target, you can make the target table the primary table of a set, link to the other tables and use an update operation to fill values in the parent table with values in the child table(s).