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

[Former Member]

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.

[former_member205763]

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.

[Former Member]

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.

[former_member205763]

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

[former_member205763]

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

[raymond_giuseppi]

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

[former_member210621]

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