Hello experts,

My first post here :)

I have created a query that searches through two large tables. This takes a lot of time.

Is there anyway I can increase the performance of this query?

select
tfgld482.idtc,
tfgld482.leac,
tfgld482.amnt,
tfgld483.elva
from tfgld482, tfgld483

where
tfgld482.guid = tfgld483.guid and
tfgld482.dbcr = tfgld483.dbcr and
tfgld482.guid = tfgld483.guid and
tfgld482.idtc = '10061067' and
tfgld482.fyer = 2014 and
tfgld482.fprd = 6 and
tfgld482.dbcr = 2 and
tfgld483.elcd = '315100000000'

I've searched the progguide and this forum, and my conculusion is that everybody writes query's different :) I have seen, Refers to, joins, nested querys, and where statements (witch I use in de code below).

Can someone explain to me in plain english what to use in witch situation?

Hi,

Yes. The first thing you need is to use Index of the tables in your where condition. You can use refers to as well. And, if you are just picking a value from another table you can use Sub-routines i.e. functions. But, its a query so you can't use functions.

So, you can try one of this:-

select
tfgld482.idtc,
tfgld482.leac,
tfgld482.amnt,
tfgld483.elva
from tfgld482, tfgld483

where
tfgld482._index1 = {tfgld483.guid,tfgld483.dbcr} and
tfgld482.idtc = '10061067' and
tfgld482.fyer = 2014 and
tfgld482.fprd = 6 and
tfgld482.dbcr = 2 and
tfgld483.elcd = '315100000000'

OR

select
tfgld482.idtc,
tfgld482.leac,
tfgld482.amnt,
tfgld483.elva
from tfgld482, tfgld483

where
tfgld482._index1 refers to tfgld483 and
tfgld482.idtc = '10061067' and
tfgld482.fyer = 2014 and
tfgld482.fprd = 6 and
tfgld482.dbcr = 2 and
tfgld483.elcd = '315100000000'

Thank you for your reply bhushanchanda,

The strangest thing happened. The query finished in about 2 seconds. I was expecting that it would run for an hour, maybe more.

tfgld482 has almost 12 million rows, and tfgld843 has over 60.000 rows.
The result of the query was only one row. This has to be more, in our opinion.

Can someone explain what went wrong?

I analyzed the query, multiple times, but I don’t see anything that could go wrong. Only thing I am not 100% sure of if there really is a relation between tfgld482 and tfgld483.

Hello,

there is no relation from tfgld483 to tfgld482 over index in table definition.

Try to use the relation via fields tfgld482.guid - tfgld483.guid
tfgld482.dbcr - tfgld483.dbcr
tfgld482.secd - tfgld483.elcd

Regards

You can get a performance tracing tuning document from Infor. At least they have to use one. What I would do for the above query is break it down to see what slows it up. I recommend using indexes as mentioned. Since from the query above I do not really see a link between the tables I would break it up.

Select tfgld483.*
from tfgld483
where tfgld483.elcd = '315100000000' | Use index if you have one for this field.
selectdo
Select tfgld482.idtc, tfgld482.leac, tfgld482.amnt,
tfgld483.elva
from tfgld482
where tfgld482._index1 = {:tfgld483.guid, :tfgld483.dbcr}
and tfgld482.guid = :tfgld483.guid
and tfgld482.idtc = '10061067'
and tfgld482.fyer = 2014
and tfgld482.fprd = 6
and tfgld482.dbcr = 2
selectdo
| do stuff here
endselect
| do stuff here.
endselect


We do not have these tables so it based off speculation.

Hello,

there is no relation from tfgld483 to tfgld482 over index in table definition.

Try to use the relation via fields tfgld482.guid - tfgld483.guid
tfgld482.dbcr - tfgld483.dbcr
tfgld482.secd - tfgld483.elcd

Regards

Mark_h,

Thanks for your reply!

If there is no relationship, odds are that the .guid is'nt the same for both tables. Maybe that is why I only get one record.

tfgld483.elcd does not exists in our db.

Can some one tell me if there is a different relation between both tables? Maybe through another extra table?

Mark_h,

Thanks for your reply!

If there is no relationship, odds are that the .guid is'nt the same for both tables. Maybe that is why I only get one record.

tfgld483.elcd does not exists in our db.

Can some one tell me if there is a different relation between both tables? Maybe through another extra table?

Hello,

attached the Tabledef and a record from both tables.

It's a 1:1 relation between the both tables (guid , dbcr), but not with index !

guid is a GenericUniqueID generatet by LN !

Hope this Information helps a Little bit !

Regards

Thank you guys for your responses. What ever I tried, I only got one row as result.

With a colleague I looked further and viewed the data in ERP LN. After viewing the data, the viewed data appeared as results in my query. Strange huh.

As I can view the data, there must be a relation between them. Maybe ERP LN does something with the data when joining (trimming spaces for example, but this not the case) or it uses a different multi-table relation to show the required data.