topic Re: Optimizing select question .. in Application Development and Automation Discussions

Optimizing select question ..

Former Member — Mon, 14 Jul 2008 09:29:09 GMT

I have a select statement which takes an awful time to execute

the problem is that the only factor I know is the BUDAT

now I was looking if I could use an index. there is an index on the BKPF which contains the BUDAT, BSTAT and BUKRS

to make this index happen I have to add alt the available BSTATs into a range and all available BUKRS to a range and add it to the select

now I wonder if this would really enhance the performance of the select statement ??



  SELECT bukrs belnr gjahr FROM bkpf
           INTO CORRESPONDING FIELDS OF TABLE ta_bkpf
           WHERE budat GE p_budat.

kind regards

arthur de smidt

Re: Optimizing select question ..

Former Member — Mon, 14 Jul 2008 09:34:14 GMT

hi,

doing so will improve the performance for that if you want to explicitly refer the index name even ...also remove CORRESPONDING FIELDS OF TABLE to INTO TABLE statement ..


  SELECT bukrs belnr gjahr FROM bkpf
           INTO TABLE ta_bkpf
           WHERE budat GE p_budat and
                        bstat in s_bstat and
                        bukrs in s_bukrs 
            %_HINTS ORACLE 'INDEX("BKPF""Z02")'. " Z02 is the name of secondary index...

Re: Optimizing select question ..

Former Member — Mon, 14 Jul 2008 09:44:33 GMT

ah I will give it a ttry, but still looking how to select all the possible status for BSTAT , the domain points to fixed values ? is there anyway to select those so I can append them to the s_bstat ??

Re: Optimizing select question ..

ThomasZloch — Mon, 14 Jul 2008 09:55:12 GMT

Most of the time it is sufficient to select BSTAT = space, since this will give you all real documents with actual line items, the rest is parked documents, clearing documents etc. (see the domain values). You might want to analyse whether BSTAT = space is sufficient. Otherwise, domain values are stored in table DD07L.

As for BUKRS, how many different ones do you have? If just a few, putting them in a range as single values for the select should be beneficial.

Thomas

Re: Optimizing select question ..

Former Member — Mon, 14 Jul 2008 10:02:10 GMT

ah thanks that helps , when searching I found DD07D and I could not find related tables on the fiedtypes

for company codes I have around 30 company codes .

is it also allowed from an index point of view to use the BT in the range option or does the index only work when using EQ in the s_bukrs ??

ok I will ask if bstat = space should be sufficient. the requirement is to make a report which shows all the LIFNR which are not used in a certain period till now. so I start at bkpf with the budat , put those values in a table and select all the lifnr from BSAK and BSIK

kind regards

arthur

Edited by: A. de Smidt on Jul 14, 2008 12:05 PM

Re: Optimizing select question ..

ThomasZloch — Mon, 14 Jul 2008 10:38:43 GMT

> is it also allowed from an index point of view to use the BT in the range option or does the index only work when using EQ in the s_bukrs ??

I strongly recommend EQ instead of BT.

> the requirement is to make a report which shows all the LIFNR which are not used in a certain period till now.

Well, in this case it might be more efficient to check for vendor balances in table LFC1, you might not need to read single items at all.

Cheers

Thomas

Re: Optimizing select question ..

Former Member — Mon, 14 Jul 2008 12:15:48 GMT

hmmm I had checked the LFC1 table yesterday. problem is that we also have to calculate all the periodes for all companies where the vendor is created for and therefor has a record in LFC1. I have to add all the periodes to find if there are booking. when I look at the table I have 1 milion records but most records have no amount in the perriodes and there is no field in the LFC1 which shows a total or something..

guess I could use the table but I think I will transfer the problem from select delay to abap calculations over 1 milion records .

or is there a easy way to regocnize without calculation or clear empty rows in this table ?? I guess more thath 80% has 0 bookings in this table.

kind regards

arthur de smidt

Re: Optimizing select question ..

ThomasZloch — Mon, 14 Jul 2008 12:43:32 GMT

I see what you mean. It's difficult to make another suggestion from the distance for this special case. You might have to compare both options (LFC1 or BKPF/BSIK/BSAK). Make sure you always use qualified keys when working with database or internal tables, the rest is finetuning.

Greetings

Thomas

Re: Optimizing select question ..

Former Member — Mon, 14 Jul 2008 12:59:50 GMT

yep the condition on the BKPF really worked, only I tried to omplement it for the BSAK also and used index 5


  SELECT lifnr FROM bsak
           INTO TABLE ta_cred
           FOR ALL ENTRIES IN ta_bkpf
           WHERE bukrs EQ ta_bkpf-bukrs
           AND belnr EQ ta_bkpf-belnr
           AND gjahr EQ ta_bkpf-gjahr
           and BUZEI le '100'
           %_HINTS ORACLE 'INDEX("BKPF""Z05")'.

only this had a 4 times worst performance that just


  SELECT lifnr FROM bsak
           INTO TABLE ta_cred
           FOR ALL ENTRIES IN ta_bkpf
           WHERE bukrs EQ ta_bkpf-bukrs
           AND belnr EQ ta_bkpf-belnr
           AND gjahr EQ ta_bkpf-gjahr

I think the LFC1 is also nice but perhaps if we implement BW that we make a substract of that one for direct access.

kind regards

arthur de smidt

Re: Optimizing select question ..

Former Member — Mon, 14 Jul 2008 13:23:52 GMT

Please see:

[Using an Index When You Don't Have all of the Fields|]

and

[ Quickly Retrieving FI document Data from BSEG|]

Rob