Hi All,

select
tfgld106.fyer, | Fiscal Year
tfgld106.fprd, | Fiscal Period
tfgld106.otyp, | Transaction Type
tfgld106.odoc, | Document
tfgld106.olin, | Line Number
tfgld106.leac, | Ledger Account

from
tfgld106 | Finalized Transactions
where
tfgld106.leac >= tfgld106.leac.f and
tfgld106.leac <= tfgld106.leac.t and
tfgld106.fyer >= tfgld106.fyer.f and
tfgld106.fyer <= tfgld106.fyer.t and
tfgld106.fprd >= tfgld106.fprd.f and
tfgld106.fprd <= tfgld106.fprd.t and

I have this query in Query data ( ttadv3580m000) . Problem is , query performs very slow ( around 8 mins) during day time. But same query is very quick ( just 10 seconds) after 6PM. If you have any idea, Can you please let me know why is it so?

I guess DBA might have set some restrictions on this table till 6PM. Once these restrictions are disabled after 6PM, query completes in seconds. If it is so, then how can we know these restrictions without asking DBA?

Are indexes used internally or not used from my query ?

Can we use indexes and hints in query data tool like
tfgld106._index3 inrange {:leac.f} and {:leac.t},
hint use index 3 on tfgld106 . I am getting compile errors when I tried like this.

Thanks already in advance for your help.

Best regards,
Raj

Yes you can use indexes in the query, and no too hints. I believe your query will use and index - I know with oracle the driver will include them.

I do not know enough about databases to know what restrictions dba's could set. Your slowness could simply be system load during the daytime. Do you have anything that looks or measures overall system performance?

I have this query in Query data ( ttadv3580m000) . Problem is , query performs very slow ( around 8 mins) during day time. But same query is very quick ( just 10 seconds) after 6PM. If you have any idea, Can you please let me know why is it so?
Most DBA would not put restrictions during the day time business hours when peak system performance is expected. You should initiate a discussion with your DBA, you cannot investigate the system parameters/limits without involving the System Administrators and without proper privileges.
Are indexes used internally or not used from my query ?
- Yes
Can we use indexes and hints in query data tool like
tfgld106._index3 inrange {:leac.f} and {:leac.t},
hint use index 3 on tfgld106 . I am getting compile errors when I tried like this.
Post your complete code and the compilation error.
If you are on LN release, the Index3 has Ledger Account, Document while Index4 has Ledger Account, Fiscal Year, Fiscal Period, Category, Document - which seems to be a better one to use.

Can we use indexes and hints in query data tool like
tfgld106._index3 inrange {:leac.f} and {:leac.t},


Indexes can be used, but no, Not this way(in a SQL Query). leac.f and leac.t are undefined for query. So, instead use tfgld106.leac.f and tfgld106.leac.t

I guess all your other questions are answered already. Though, to add some, its logical that the query will be slower as this is Finalized Transactions table, which is often huge. And, may be the users at your end might be finalizing the transactions in heavy amount. But, yes, optimizing the query is an option here to boost the performance.