Help speeding this up

T

taylormade

Member
This is very basic but so am I.

I have a table "A" with 100,000 header records.
The detail table "B" has 350,000 detail records.

I'm looking for all records (headers and detail) with "datafield" >= yesterday.

My script:

for each A where A_date >= (today - 1) no-lock:
for each B where B_nbr = A_nbr no-lock.
display
A_nbr B_nbr A_inv_date
with 1 col side-labels down.

I also tried:

for each A where A_date >= (today - 1) no-lock:
find B where B_nbr = A_nbr no-error.
display
A_nbr B_nbr A_inv_date
with 1 col side-labels down.

Still I can't get any speed out of this.
Is there a programming trick or two I could try.
(I have a couple if indexes on the tables but using them didn't seem to speed them up at all)
 
I am the compiler

If I would be the compiler and see for each statement with today function I would think I have to find out what is the value of today function for each row I fetch from the database. It might be time consuming...

If there would be a constant value it would be a lot easier: calculate once and use that value for all records.

I really don't know if this will fix your problem and since I'm home right now can't check against any real world case - consider this as a thought only.
 
speed

you say you have indexs? if file A is not indexed on the a_date field or file b not indexed on the a_nbr field then its going to be slow.
 
Hi,

how long does your query take and what kind of setup are you running on. A Client-Server or Server based or .....

save this as a program and compile it with a xref and check whether its using any index or the whole index.

HTH
 
Isn't this what a JOIN is used for, so you should have something like this:

FOR EACH a WHERE a.A_date >= (today -1) NO-LOCK, EACH b WHERE a.A_nbr = b.B_nbr NO-LOCK:
DISPLAY a.A_nbr b.B_nbr a.A_inv_date WITH 1 col side-labels down.
END.

You just gotta make sure that when there are ambiguous field or table names, that you use the database qualifier.
 
?? Simple 4 Gl ??

I do not know how your DB is configured, best bet is to speak to your DBA regarding this, though this sounds simple 4GL stuff here.
Basically all you require is
FOR EACH {&table_1_name} WHERE {&criteria}, EACH {&table_2_name} OF {&table_1_name} LOCK-TYPE:
DISP {&table_1_name_field}
{&table_2_name_field}
END.
This is just basic 4GL joins here, your DB should be configured correctly to pick up as required. This IS NOT exact syntax, though more just a pointer to the direction you should be headed, your organisation has paid HUGE costs for the 4GL, USE it.
Regards
 
You must log in or register to reply here.
Back
Top