topic Re: Performance Tuning on a Select Query in Application Development and Automation Discussions

Performance Tuning on a Select Query

Former Member — Thu, 04 May 2006 09:36:49 GMT

Hi,

We have a select statement as mentioned below.

SELECT loobj1 gpart vkont proid tdate lotyp lockr

INTO TABLE tb_dfkklocks

FROM dfkklocks

FOR ALL ENTRIES IN tb_fkkvkp

WHERE

lotyp = '02'

AND proid = '01'

AND lockr >= '0'

AND lockr <= '9'

AND gpart = tb_fkkvkp-gpart

AND vkont = tb_fkkvkp-vkont.

We have Primary key as shown below.

CLIENT

LOOBJ1

LOTYP

PROID

LOCKR

FDATE

TDATE

GPART

VKONT

We have a secondary index only on TDATE. What can be the ideal select stement to improve the performance?

Regards,

Lijo Joseph

Re: Performance Tuning on a Select Query

abdul_hakim — Thu, 04 May 2006 09:45:37 GMT

hi make use of your primary keys in the WHERE clause for improving the performance.Also note that it is advisable and good to check your itab before using it with the FOR ALL ENRTIES variant.Eg..

IF NOT <ur itab> IS INITIAL.

ENDIF.

Cheers,

Abdul

Re: Performance Tuning on a Select Query

Former Member — Thu, 04 May 2006 09:47:05 GMT

Hi,

If we use for all entries means all primary keys must be select.then only performance will be increase.

SELECT loobj1 lotyp proid lockr fdate tdate gpart vkont

INTO TABLE tb_dfkklocks

FROM dfkklocks

FOR ALL ENTRIES IN tb_fkkvkp

WHERE

lotyp = '02'

AND proid = '01'

AND lockr >= '0'

AND lockr <= '9'

AND gpart = tb_fkkvkp-gpart

AND vkont = tb_fkkvkp-vkont.

plz reward points if it is useful

thanks,

suma.

Re: Performance Tuning on a Select Query

Former Member — Thu, 04 May 2006 09:51:21 GMT

Hi,

I have mentioned partial keys from the primary Key. So

1) should i use partial keys or

2) use an index and then remove the other data from internal table.

Which method is best?

Regards,

Lijo Joseph

Re: Performance Tuning on a Select Query

abdul_hakim — Thu, 04 May 2006 09:54:02 GMT

it doesn't meant that you should make use of all of your key fields.use whatever is required in your scenario and leave the others provided your key should be so selective for eg fetching entires from MARA based on Material or Plant would be so selective and it will improve your performance.....

Cheers,

Abdul

Re: Performance Tuning on a Select Query

Former Member — Thu, 04 May 2006 10:00:16 GMT

Hi Lijo

Make sure you use as many of the primary key fields as possible in your WHERE clause. Also ensure that they appear in the WHERE clause in the same order as in the PK. If you can't use every PK field then try and use those that appear first. This should help to improve the performance of the select. Also, if CLIENT relates to the MANDT field e.g. Client 30 on your DEV server then I don;t think you need to include it as where condition as SAP will do that automatically.

You appear to have the where statements in the best possible order, you just need to insert the other PK fields if you can. Also, it is always recommended to check that the table you are finding all entries for has data i.e. CHECK NOT tb_fkkvkp IS INITIAL. If it is empty then rather than returning no rows, SAP will return ALL rows - as if the gpart and vkont criteria were not there.

Hope this is of help.

Andy

Re: Performance Tuning on a Select Query

Former Member — Thu, 04 May 2006 10:05:13 GMT

Andy,

Thanks for the info. But My question is Which mwthod is best using 5 fields from Primary key in the where statement or using one field which got an index and then delete the other data from the internal table?

Can you guide me on this?

Regards,

LIjo Joseph

Re: Performance Tuning on a Select Query

Former Member — Thu, 04 May 2006 10:10:02 GMT

Hi,

I would prefer your 2nd point(use an index and then remove the other data from internal table).

For all entries will work like ,it will remove the duplicate reords in the internal table automatically.so using the complete index is best preferable.suppose in your table 8 r the key fields.if you use 5 fields in select not in where condition.if we found 2 records with same data in corresponding 5 key fields and different data for 6,7 and 8 fields.system will select only 1 record instead of selecting the 2 records.suppose if we give complete index then we can select 2 records.

Thanks,

suma.

Re: Performance Tuning on a Select Query

Former Member — Thu, 04 May 2006 10:22:12 GMT

Hi Lijo

I suspect that as you are not using the full PK and specifcally not the leftmost PK fields then using the secondary index may well be more efficient in terms of DB load BUT you will more than likely end up reading an awful lot of data which you end up deleting. Therefore I think the best approach will depend on the data in the table. Consequently, it might be worth running some performance tests against both options.

Kind regards

Andy

Re: Performance Tuning on a Select Query

Former Member — Thu, 04 May 2006 10:53:57 GMT

Hello Lijo,

As your select doesn't contain the first part of the primary key (LOOBJ1)it is going to read seq. however if you have index build already then using the alternate index read can be better. However my fear is it may fetch you lot of data in internal table. But second option is better considering limited options.

Re: Performance Tuning on a Select Query

Former Member — Thu, 04 May 2006 10:57:38 GMT

Hai Lijo

give the primary key fields in top of the where clause because it will retrive all the common fields form both the table and then filter the next conditions

otherwise it retrives the total fields it will take a lot of memory

check it the following modification.

SELECT loobj1 gpart vkont proid tdate lotyp lockr

INTO TABLE tb_dfkklocks

FROM dfkklocks

FOR ALL ENTRIES IN tb_fkkvkp

WHERE

gpart = tb_fkkvkp-gpart

AND vkont = tb_fkkvkp-vkont

AND lotyp = '02'

AND proid = '01'

AND lockr >= '0'

AND lockr <= '9'.

Thanks & Regards

Sreenivasulu P.

Message was edited by: Sreenivasulu Ponnadi

Re: Performance Tuning on a Select Query

Former Member — Thu, 04 May 2006 11:10:19 GMT

ranges: LOOBJ1 like dfkklocks-LOOBJ1,

FDATE like dfkklocks-FDATE,

tDATE like dfkklocks-tDATE.

if not tb_fkkvkp[] is initial.

SELECT loobj1 gpart vkont proid tdate lotyp lockr

INTO TABLE tb_dfkklocks package size 100

FROM dfkklocks

FOR ALL ENTRIES IN tb_fkkvkp

WHERE LOOBJ1 in r_LOOBJ1

and lotyp = '02'

and proid = '01'

AND lockr >= '0'

AND lockr <= '9'

and FDATE in r_FDATE

and tDATE in r_tDATE

AND gpart = tb_fkkvkp-gpart

AND vkont = tb_fkkvkp-vkont.

endif.

Re: Performance Tuning on a Select Query

Former Member — Thu, 04 May 2006 11:35:36 GMT

Hi Sravanthi,

Mentioning package size 100 will impriove the performance?

Regardsl

Lijo Joseph

Re: Performance Tuning on a Select Query

Former Member — Thu, 04 May 2006 11:43:52 GMT

1) Make sure that TB_FKKVKP is not empty.

2) Remove duplicate entries from TB_FKKVKP comparing GPART and VKONT. If need be transfer these values to a seperate sub table, (and FREE that after use).

3) Combine the LOCKR conditions into a single line, either using the BETWEEN option or a new RANGE.

4) Make sure that the fields LOTYP, PROID and LOCKR are of the same type as the constants you are checking. You should be using CONSTANTS for the fixed values '02', '01', '0' and '9' anyway. Changing field types should be avoided.

You have not told us the field definitions of the primary key fields. Neither do we know how much variation there is for each field. So we cannot advise which order to construct any new index, if that is to be tried.

MattG

Re: Performance Tuning on a Select Query

Former Member — Thu, 04 May 2006 12:44:40 GMT

Hi Lijo,

Couple of pointers from my end.

1). Try avoiding a FOR ALL ENTRIES if the base table is huge. Instead use a array fetch and pick up more data instead of trying to restrict the data using a FOR ALL ENTRIES as this does a LOOP on the database while selecting the data.

So i would suggest not use FOR ALL ENTRIES if the base table is huge. So analyse before using a select query.

2). It is actually very difficult to mention which option is better. I would say that it all depends on the scenario. What amount of data is to be read, from which table it is being read, what kind of processing is required on database,..... and many more analysis goes to write a an efficient select statement.

3). If i am not wrong i think if you are using a FOR ALL ENTRIES and dont specify the key completely then there is no gaurentee that you fetch all the unique records. The system will compare all the character fields as a KEY field and it will not fetch all the records. So while using a FOR ALL ENTRIES its recommended to use all the key fields.

4). If you would like to really spend time and work on a efficient select statement then try using the tcode ST05 --> Select the button EXPLAIN SQL REQUEST.

type in your select query and see what is the cost of the select statement fired on the database. This will help you to select the best statement. This is a very useful tool if we have complex select statements.

Hope this info is helpful.

Cheers

Message was edited by: Vijayendra Rao

Re: Performance Tuning on a Select Query

Former Member — Thu, 04 May 2006 13:22:58 GMT

Hi Lijo,

DFKKLOCKS is a monster table, do not use FOR ALL ENTRIES.

Thanks

Sanju