Skip to Content
-1

SELECT QUERY for bsis Table taking to much time

May 28, 2017 at 07:11 PM

201

avatar image
Former Member

Hi All

I am facing serious performance issue in my Select query in Production client as i want to get records of Profit center for the certain period having year and Gl range in my selection criteria.

SELECT DISTINCT PRCTR INTO TABLE BSIS_PRCTR FROM BSIS WHERE BUKRS EQ COMP AND GJAHR EQ ZYEAR AND hkont in ZZRACCT and BUDAT IN SO_BUDAT.

i am just selecting the profit center but it taking to much time.

so please guide me how to optimized this query or give me some alternative way to achieve this record.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Mike Pokraka May 28, 2017 at 08:03 PM
0

Everything apart from BUDAT is part of the primary index. How many entries are there in your IN clauses? I would suggest two quick options to try:

1. Order your query fields as per table. I don't have high hopes this will work, but sometimes it just might make enough difference for the optimiser to decide to use the index. So select by BUKRS, HKONT, GJAHR, BUDAT in that order.

2. Leave out BUDAT and filter it in your internal table. Then all the SELECT criteria are part of the primary index:
SELECT PRCTR, BUDAT INTO...
then delete all entries with irrelevant BUDAT.

It depends hugely on your criteria and result volumes on whether the second option is viable - it may work very well or make things worse...

If neither of those work, please look at the indexes and see if there is anything suitable you can model your query around. If all the result fields are part of the index, the execution should be fast, as the DB doesn't even access the table.

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member May 31, 2017 at 02:36 PM
0

Hello David,

First of all, how many rows are being returned from this SQL? Remember that if you have many thousands or even millions of rows in your result set an index is not useful - it´s even worse than a full table scan - and you should consider run it as a batch job or break the SQL into smaller pieces. Second, if your result set is small check the index the optimizer is using - maybe it´s not the best option - it happens sometimes. In this case or you use a hint or change the way the statistics are running for this table - maybe increase the sample.

Regards

Carlos

Share
10 |10000 characters needed characters left characters exceeded