Does anyone know how exactly the join works in 4GL? A document would be a great resource
We are asked by a developer about this simple query on a table to just count for 1000. when it runs by itself it takes 30ms. then we put an table inside of the first one joined by an unique index, then it runs 10 times longer. something like this.
def var i int no-undo.
etime(true).
for each tab1 no-lock
i = i + 1.
if i > 1000 then leave.
end.
message etime view-as alert-box.
def var i int no-undo.
etime(true).
for each tab1 no-lock,
first tab2 no-lock
where tab2.key = tab1.key:
i = i + 1.
if i > 1000 then leave.
end.
message etime view-as alert-box.
is the join expected to take that long? if it is, how does join work(my opening question)? if not, what can we do to improve it?
thank in advance.
You need to verify that the indexes on tab2 support this join. To ensure the join performs well you need to ensure that you have an index on tab2 where the "key" field is the first field in the index.
For diagnosis, post the relevant parts of the definitions of the tables and do a COMPILE XREF to see what it says about indexes.
Is the FIRST really meaningful? Or is it there out of reflex?
You say that the tables are being joined by a unique index so I would think that it has no meaning.