topic Re: Efficient Select in Application Development and Automation Discussions

Efficient Select

Former Member — Wed, 29 Dec 2010 11:52:07 GMT

Hi guys ,

I ' ve tried everything that I know and read in this forum but I stiil have the same problem . I have the follow select :

select  bukrs  belnr  gjahr
            blart  cpudt  bvorg
            tcode  waers
          from bkpf  into table  ti_bkpf
          where bukrs in p_bukrs and
                bstat = space    and
                blart in p_blart .

Im trying to use this index (is active of course) :

MANDT

BUKRS

BSTAT

BLART

which is a secondary index , but checking in the sm50 I can see that the reading of bkpf is still sequential , what I'm doing wrong? .

thank you for your advices.

Re: Efficient Select

anup_deshmukh4 — Wed, 29 Dec 2010 11:56:35 GMT

hello Just have a SQL trace on ( ST05 ) and check out is the select hitting the index or not...! OR Which index its hitting .

Hope this would clear out a few thing to you .

Re: Efficient Select

former_member182670 — Wed, 29 Dec 2010 13:01:39 GMT

What are your values in p_bukrs and p_blart?

Not all conditions will lead to index usage.

Re: Efficient Select

Former Member — Wed, 29 Dec 2010 13:24:48 GMT

Because ,

select-options: p_bukrs for bseg-bukrs,
                p_vbund for lfa1-vbund,
                p_valut for bseg-valut,
                p_blart for bkpf-blart.

I need the user to choose what he/she wants to see.

Re: Efficient Select

former_member182670 — Wed, 29 Dec 2010 13:28:07 GMT

I understand. But what are the values you are using to test the statement?

Depending on the number of select-options records and operators (=,<>, ...) you might get different execution plans for the SQL statement.

Re: Efficient Select

Former Member — Wed, 29 Dec 2010 13:34:06 GMT

What might have happened is that one of the 2 select-options is empty in which case the where-clause for that select-option is ignored.

Re: Efficient Select

Former Member — Wed, 29 Dec 2010 13:38:16 GMT

Well ,

for BLART = 'PE' must of the times and for BUKRS is only one company , however , you know things are you always need to consider some other scenarios.

Re: Efficient Select

Former Member — Wed, 29 Dec 2010 13:44:53 GMT

try giving a hint to the query.



select a~matnr a~werks a~charg sum( a~clabs )  b~cuobj_bm b~ersda c~matnr
       c~matkl c~mtart c~spart c~wrkst c~extwg f~ewbez c~groes c~volum
       c~meins d~wgbez
  from mchb as a
  join mch1 as b
  on   b~matnr = a~matnr and
       b~charg = a~charg
  join mara as c
  on   c~matnr = a~matnr
  join t023t as d
  on   d~matkl = c~matkl
  join twewt as f
  on   c~extwg = f~extwg and
       f~spras = 'E'
  into table i_mchb
  where a~matnr in s_matnr and
        a~werks in s_werks and
        a~lgort in s_lgort and
        a~lvorm eq ' '     and
        b~lvorm eq ' '     and
        c~matkl in s_matkl and
        c~mtart in s_mtart and
        c~spart in s_spart and
        c~wrkst in s_wrkst and
        c~extwg in s_extwg and
        c~lvorm eq ' '     and
        c~groes in p_size  and
        d~spras eq 'E'
  group by a~matnr a~werks a~charg b~cuobj_bm b~ersda c~matnr c~matkl
           c~mtart c~spart c~wrkst c~extwg    f~ewbez c~groes c~volum
           c~meins d~wgbez
  order by a~matnr a~werks
  %_HINTS MSSQLNT 'TABLE MARA INDEX([MARA~ZI1])'
          MSSQLNT 'TABLE MCHB INDEX([MCHB~ZI2])'
          MSSQLNT 'TABLE MCH1 INDEX([MCH1~ZT3])'.

Regards,

Abdullah

Re: Efficient Select

Former Member — Wed, 29 Dec 2010 13:56:56 GMT

Hi,

Try FROM bkpf INTO CORRESPONDING FIELDS OF TABLE tl_bkpf

In my case this is mutch faster.

Success,

Rob

Re: Efficient Select

Former Member — Wed, 29 Dec 2010 14:00:53 GMT

Hi,

See if this blog help you. [http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/7692] [original link is broken] [original link is broken] [original link is broken];

Regards,

Alok

Re: Efficient Select

Former Member — Wed, 29 Dec 2010 15:48:02 GMT


> hello Just have a SQL trace on ( ST05 ) and check out is the select hitting the index or not...! OR Which index its hitting .
> Hope this would clear out a few thing to you .

It'sfunny i did exactly what did you recommend me , ( ran St05) and I see this result :

SELECT
  "BUKRS" , "BELNR" , "GJAHR" , "BLART" , "CPUDT" , "BVORG" , "TCODE" , "WAERS"
FROM
  "BKPF"
WHERE
  "MANDT" = :A0 AND "BUKRS" = :A1 AND "BSTAT" = :A2 AND "BLART" = :A3

Execution Plan


 SELECT STATEMENT ( Estimated Costs = 5.321 , Estimated #Rows = 162.593 )

        2 TABLE ACCESS BY INDEX ROWID BKPF
          ( Estim. Costs = 5.321 , Estim. #Rows = 162.593 )
          Estim. CPU-Costs = 96.360.436 Estim. IO-Costs = 5.310

            1 INDEX RANGE SCAN BKPF~5
              ( Estim. Costs = 1.174 , Estim. #Rows = 162.593 )
              Search Columns: 3
              Estim. CPU-Costs = 47.452.066 Estim. IO-Costs = 1.168

it means that he using index 5 instead of 3 , if so ,why?, I dont have the fields of index 5 in my query :

MANDT

BUKRS

CPUDT

BSTAT

Re: Efficient Select

Clemenss — Wed, 29 Dec 2010 16:40:20 GMT

Hi José,

the use of the index is determined at runtime. It is most important what your selection-table look like the moment the statement is executed.

Unique Index BKPF~0 
MANDT 
BUKRS 
BELNR 
GJAHR 

Index BKPF~1 
MANDT 
BUKRS 
BSTAT 
XBLNR 

Index BKPF~2 
MANDT 
BUKRS 
BSTAT 
BUDAT 

Index BKPF~3 
MANDT 
BUKRS 
BSTAT 
BLART 

Index BKPF~4 
MANDT 
AWTYP 
AWKEY 
AWSYS 

Index BKPF~5 
MANDT 
BUKRS 
CPUDT 
BSTAT

What did you enter in the selections. If you gave, i.e. an exclusion (NE ' ') for BSTAT, but not one distinct value, this can not be used for this index.

Possibly index 5 still has the best distribution in your system, I don't know. Still 180.000 records are not really so many that we have to worry about index use.

Regards,

Clemens