Help: Problem with Index Fields

apple

apple

New Member
im new in progress programming and im working on a program to retrieve data from two tables.

i used "for each" loops and it worked properly. however, i have a problem with the processing time. it took 1min to process 30 records... 3 hrs to process 4 thousand records... this is very unacceptable.

i heard joining tables would make processing faster and i was introduced to outer-join. however it will only work with the "open query" syntax. so i tried using the statement. here comes the problem: it will always give me a compiler message "Index fields of table 1 must be fields in table 2"

here's my code:

open query q1 for each subsmst_f,
each tbytdsum outer-join of subsmst_f
where tbytdsum.idnum=subsmst_f.idnum no-lock.

i hope you could help me on this.
 
"Index fields of table 1 must be fields in table 2" means that EACH table2 of TABLE1 works only if:
1. Field names are the same in both tables.
2. There is unique index in table1 and foreign index in table2
Instead you always can you standart sql systax
table2 of table1 is the same to: where table2.JoinField = table1.Joinfield

open query q1 for each subsmst_f,
each tbytdsum outer-join where
where tbytdsum.idnum=subsmst_f.idnum no-lock.
 
... and that's most likely where you unacceptable performance comes from - the query can't be solved with an index ...

Regards, RealHeavyDude.
 
Thanks a lot MaximMonin. It works! I don't get any error message anymore.

but the query still processes too slow. I don't know whether it's because of my query structure or because of my poor system memory and processor. Either way, is there a better query structure that would give results faster?
 
As RHD said
You have to add index on idnum in both tables.
If you do so you can use more simle syntax for your query

open query q1 for each subsmst_f no-lock,
each tbytdsum outer-join of subsmst_f no-lock.
 
The best thing to do if you can't work out a better query would be to provide index information on he tables involved and the exact query you're doing. Then someone can advise.
 
You must log in or register to reply here.
Back
Top