Hi Team

Recently I found the query which is used in a report session is having performance problem. I checked through trace.

Can you suggest simpler way of below query?


select tdbos715.*
from tdbos715
where tdbos715._index2 = {:tdsls041.orno,:tdsls041.pono}
and tdbos715._compnr = :ncmp
order by tdbos715.date desc
as set with 2 rows

Keep in mind that using a sort order implies that you use the same key for selecting records, regardless of what you explicit in the where clause...

Maybe this generates a full table scan. Try. Remove the sort and see what happens.

Hello Sir,

This Query Stated BElow will fetch all the columns for the table and then will do the sorting that too in desc.

Instead if we fetch the specific columns then it may be a fast query.

Once all the sorting is done then it will give u the first 2 records "as set with 2 rows"

Best Regards

Darpan Bhansali

I think so, what zardoz has written. Often the Problem is the different between the fields of where clause and sorting statement.

You can check your query as following.


Logon to your ERP-Server
Change into your $BSE/bin directory
Call the command “fillenv”.
Change into any “tmp” directory.
Call the command “set DBSLOG=01770”
Copy the problem query into a text file, for example “query.txt”.
Start the command “qptool –f query.txt -c100”
Fillin the values for your variables. In your example tdsls041.orno,tdsls041.pono and ncmp.


After the query execute correctly you can take a look into the file dbs.log in your tmp directory and there you can see which query the dbms has used actually.

best regards
michael

Hi All

Thanks for your reply.

Michael - For some reason dbs.log is not generating at my end, we are on AIX 5.3. Is there any quick fix?

Darpan - I need all the columns so no point to mention specific.

Zardoz - Can you give a example here?

Thanks

-Suhas

The problem is that: specifing the order by clause, you tell to the db driver to use it also for selecting data, regardless of what you specify in the where clause.
If the tdbos715.date is not in the first part of any of the indexes of the tdbos715 table, this results in a full table scan.
Possible solutions:
1) in Baan IV or LN: create a new index with orno, pono, date and use it in both where and sort clause.
2) only LN: use the HINT clause to force SQL to use the index 2, by example:
hint use index2 on tdbos715