topic Re: Select taking more time when selecting data fromVBRP in Application Development and Automation Discussions

Select taking more time when selecting data fromVBRP

Former Member — Thu, 03 May 2018 06:51:02 GMT

Hi All,

Below query is taking more time.

SELECT fkimg matnr werks edatu
FROM vbrp
INTO TABLE gt_vbrp
FOR ALL ENTRIES IN zimport_matnr
WHERE matnr = zimport_matnr-matnr
AND edatu >= zstart_edatu
AND edatu <= zend_edatu
AND fkimg > 0.

Can someone help in getting time reduced for the above query.

Re: Select taking more time when selecting data fromVBRP

gaurav09 — Thu, 03 May 2018 09:16:45 GMT

Are you Interested in single records or multiple records?

Re: Select taking more time when selecting data fromVBRP

Former Member — Thu, 03 May 2018 10:04:59 GMT

We are selecting multiple records for last 4 years.. We are selection year by year.

As you can see that we are having where condition on Non-key fields..

We even tried with secondary index. but not helpful.

Re: Select taking more time when selecting data fromVBRP

gaurav09 — Thu, 03 May 2018 11:52:01 GMT

Dear Vidhya,

Try this,

Data: zimp_mat type zimport_matnr-matnr.

SELECT fkimg matnr werks edatu

FROM vbrp
INTO TABLE gt_vbrp
FOR ALL ENTRIES IN zimport_matnr
WHERE matnr = zimp_mat

AND edatu >= zstart_edatu
AND edatu <= zend_edatu
AND fkimg > 0.

Re: Select taking more time when selecting data fromVBRP

roberto_forti — Sat, 05 May 2018 01:20:52 GMT

Hi Vidyaprasanna,

However, check the possibility to retrieve using primary key.

Depends on the scenario implement ABAP Inner JOIN reading header Vs Item.

Also consider working with SAP transaction ST05 (SQL trace) and analysing the explain.

After that you can determine which secondary index is better for this scenario as well as If will be possible to implement %_HINTS...

Regards,

Re: Select taking more time when selecting data fromVBRP

pjcools — Sat, 05 May 2018 02:27:57 GMT

This is always going to take a long time as you are reading VBRP without the main key fields - VBELN and POSNR. You should look to read VBRK first based on some selections you can make and then read the VBRP for the records you retrieved. This will speed your selection considerably.

Never a good idea to read an item table before reading the header table. You should read HEADER then ITEM or both if you have a join and have fields to select records on.

Thanks

Phil Cooley

Re: Select taking more time when selecting data fromVBRP

roberto_forti — Mon, 07 May 2018 01:42:01 GMT

exactly, we are talking about Best practices!

Regards

Re: Select taking more time when selecting data fromVBRP

pjcools — Tue, 08 May 2018 10:30:07 GMT

Good Luck. You need to change your requirement to read the VBRK record first as I stated previously. Going down this path will not get the results you want.