topic Re: SELECT COUNT(*) FROM VBAK-VBAP-VBUK - performance in Application Development and Automation Discussions

SELECT COUNT(*) FROM VBAK-VBAP-VBUK - performance

Former Member — Fri, 03 Oct 2014 05:35:31 GMT

Hi Experts,

The following SQL will try to get record count of Sale order.

SELECT COUNT(*)

FROM vbak AS ak

INNER JOIN vbap AS ap

ON ak~vbeln = ap~vbeln

INNER JOIN vbuk AS uk

ON ak~vbeln = uk~vbeln

INTO vl_count

FOR ALL ENTRIES IN tl_matnr

WHERE ak~vbeln IN u_vbeln

AND ak~auart IN u_auart

AND ak~vkorg IN u_vkorg

AND ak~kunnr IN u_kunso

AND ap~matnr = tl_matnr-matnr

AND ap~kdmat IN u_kdmat

AND ap~pstyv IN u_pstyv

AND ap~lgort IN u_lgort

AND ak~vkbur IN u_vkbur

AND ak~vkgrp IN u_vkgrp

AND ap~invoicenum IN u_invono

AND ak~endusrponum IN u_enduno

AND ak~erdat IN u_erdat

AND ak~ernam IN u_ernam

AND ak~aedat IN u_aedat

AND ap~lastitemchangeuser IN u_chuser

AND ak~lifsk IN u_lifsk

AND ak~faksk IN u_faksk

AND ap~abgru IN u_abgru

AND ak~augru IN u_augru

AND ap~ktgrm IN u_ktgrm

AND uk~lfstk IN u_lfstk

AND ak~needtoapproveflg IN u_appflg

AND ap~ps_psp_pnr IN tl_rpspnr.

In this case tl_matnr-matnr has about 10000 records of material.

And I can't reduce Where statement.

I think I can try to change INNER JOIN or ordering of WHERE statement.

But I could'nt improve performance.

Is there any performance improve suggestion,?

Now It runs very slowly.

Thank you.

Re: SELECT COUNT(*) FROM VBAK-VBAP-VBUK - performance

Former Member — Fri, 03 Oct 2014 06:10:28 GMT

try to use the database view : VBAKUK

and do you have values in u_vbeln, if not and if you intend to do the selection via material only then use VAPMA instead of vbap.

Re: SELECT COUNT(*) FROM VBAK-VBAP-VBUK - performance

Former Member — Fri, 03 Oct 2014 06:54:46 GMT

Thank you Kartik.

I tried to use VBAKUK Like this.

    SELECT COUNT(*)

      FROM VBAKUK AS ak                                      "販売伝票：ヘッダデータ

     INNER JOIN vbap AS ap                                 "販売伝票：明細データ

     ON ak~vbeln = ap~vbeln

      INTO vl_count

           FOR ALL ENTRIES IN tl_matnr

-------

But VBAKUK doest't have VBAP-Endusrponum and VBAK-Aedat, VBAK - Needtoapproveflg.

And I use have values in u_vbeln.

Re: SELECT COUNT(*) FROM VBAK-VBAP-VBUK - performance

Former Member — Fri, 03 Oct 2014 07:19:37 GMT

Can you check if VAPMA has all the fields that you need, since you are accessing VBAP primarily with MATNR

Add in where clause vbap-vbeln as well. can you take a SQL trace n post the screenshot

Message was edited by: Kartik Tarla

Re: SELECT COUNT(*) FROM VBAK-VBAP-VBUK - performance

Former Member — Fri, 03 Oct 2014 07:23:23 GMT

Also I would suggest to use open SQL, get contents in internal table and then use DESCRIBE TABLE to get the count

Re: SELECT COUNT(*) FROM VBAK-VBAP-VBUK - performance

RaymondGiuseppi — Fri, 03 Oct 2014 08:06:34 GMT

To access VBAP from material number, you must there use VAPMA (Read also 185530 - Performance: Customer developments in SD) add this table in the JOIN list.

Also move as much where criteria to this single table.


SELECT COUNT(*)
FROM vapma AS ma
INNER JOIN vbap AS ap
  ON ap~vbeln = ma~vbeln
JOIN vbak AS ak
  ON ak~vbeln = ap~vbeln
INNER JOIN vbuk AS uk
  ON uk~vbeln = ak~vbeln
INTO vl_count
FOR ALL ENTRIES IN  tl_matnr
WHERE ma~matnr = tl_matnr-matnr
  AND ma~vbeln IN u_vbeln
  AND ma~vkorg IN u_vkorg
  AND ma~vkbur IN u_vkbur
  AND ma~vkgrp IN u_vkgrp
  AND ap~kdmat IN u_kdmat
  AND ap~pstyv IN u_pstyv
  AND ap~lgort IN u_lgort
  AND ap~invoicenum IN u_invono
  AND ap~lastitemchangeuser IN u_chuser
  AND ap~ktgrm IN u_ktgrm
  AND ap~ps_psp_pnr IN tl_rpspnr
  AND ak~auart IN u_auart
  AND ak~kunnr IN u_kunso
  AND ak~endusrponum IN u_enduno
  AND ak~erdat IN u_erdat
  AND ak~ernam IN u_ernam
  AND ak~aedat IN u_aedat
  AND ak~lifsk IN u_lifsk
  AND ak~faksk IN u_faksk
  AND ap~abgru IN u_abgru
  AND ak~augru IN u_augru
  AND ak~needtoapproveflg IN u_appflg
  AND uk~lfstk IN u_lfstk.

Regards,

Raymond

Re: SELECT COUNT(*) FROM VBAK-VBAP-VBUK - performance

former_member210621 — Thu, 09 Oct 2014 07:53:20 GMT

Hi,

As you said, you need to count sales order for about 10000 materials, you need to split your select statement and execute it seperately. For such a large number of materials, there might be huge number of slaes orders. So try to use 'PACKAGE SIZE' or 'SELECT -- CURSOR' while fetching data. It will execute faster that join statements. Also avoid using aggregate function 'COUNT' in selec. First get all the records in internal table and use 'Describe' statement to count the number of records.

-Regards,

Ketan