COUNT(*) - FOR ALL ENTRIES IN using Native SQL

[Former Member]

Hi

I'm trying to tune an ABAP by using a count(*).

When I do a trace (explain plan) on the Query I see that the DBI retrieves the complete row from the database and then counts the result.

What I would like to do is have the rdbms count the records and return the result to the ABAP as it doesn't access the table, only the index, which is must faster, especially when it's being done several thousand times.

The problem I have is that the DBI (Open SQL) uses the FOR ALL ENTRIES IN and I'm not sure how I can easily convert this query to the Native SQL (EXEC SQL).

The query is something like this:

SELECT COUNT(*) INTO (counter) FROM xyz

FOR ALL ENTRIES IN itab

WHERE field = itab-field

AND field2 = p_field2 etc.

Thanks in advance.

Doug

[Jelena_Perfiljeva]

Is XYZ a transparent table? Have you tried COUNT (column) instead of COUNT(*)? Also using FOR ALL ENTRIES is not always justified. See if it can be replaced by a JOIN. Subquery is sometimes an option, although not necessarily a better one.

Also you might get better replies in the Performance forum. Ask moderators to move the post.

[Former Member]

Hi Jelena

Table XYZ (MAST) is transparent.

I tried the COUNT( tnlpr ) in the Open SQL but this produced a syntax error. In the Native SQL (MaxDB) the count(*) works like count(tlnpr) in that is does an index only access path.

I looked at the explain plan for joining the 2 tables (INNER JOIN) and it doesn't look very efficient (insert grumble about MaxDb and joins).

The old code was (psuedo code):

select * into lt_stpo from stpo where idnrk = p_matnr.
if sy-subrc EQ 0.
    loop at lt_stpo
        select * from mast where stlnr = lt_stpo-stlnr and werks = p_werks.
        if sy-subrc EQ 0
            set true flag
            exit
        else
            set false flag
        endif
    endloop
else
    set false flag
endif.

new code:

select stlnr into lt_stpo from stpo where idnrk = p_matnr.
if sy-subrc EQ 0
    select count(*) into (counter) from mast
        for all entries in lt_stpo
        where stlnr = lt_stpo-stlnr
        and werks = p_werks
    if counter > 0.
        set true flag
    else
        set false flag
    endif
else
    set false flag
endif

faster code:

please insert here

Thanks

Doug

[ravi_lanjewar]

Hi Doug,

Why you are wasting time to write this code ? You only want to check entry exit or not (i.e. True or false)

You can use join statement which will reduce the data transfer.

try the following way

  select single * from stpo INNER JOIN mast on  mast~stlnr = stpo~stlnr
    where werks = p_werks
        and idnrk = p_matnr. 
  if sy-subrc eq 0.
     set true flag.
  else.
     set false flag.
  endif.    

Rgds

Ravi Lanjewar

Edited by: Ravishankar Lanjewar on Jul 1, 2010 10:00 AM

[Former Member]

Hi Ravi

Short and sweet, much more elegant than my code but as I mentioned in my reply to Jelena, the MaxDB query plan for the inner join doesn't look very efficient.

This is the generated access plan (explain plan)

TABLE            INDEX                            STRATEGY                                                              PAGECOUNT
STPO              STPO~M                        INDEX SCAN                                                            6592
                                                            ONLY INDEX ACCESSED                     
MAST                                                   JOIN VIA KEY RANGE                                              312
                                                            TABLE HASHED                                                                                
STLNR        (USED COLUMN)                                                                                
RESULT IS COPIED   , COSTVALUE IS                      9604707

If I use individual queries on each table I can get both of them to perform index only queries.

I try using hints and see if that gets me any further.

Thanks for your help.

Doug

[Former Member]

what is the original problem?

COUNT(*) does not work with FOR ALL ENTRIES, because of the blockwise processing and because of the removal of the duplicates in the final result set.

=> use describe table

However, if you need only to check whether at least one record fulfills the WHERE-clause,

i.e. if counter > 0

then read only one record, not with SELECT SINGLE but SELECT UP TO 1 ROWS.

Finally, in most times a join can be better than the FOR ALL ENTRIES ... but this was said a thousand times.

Siegfried

[Former Member]

> to perform index only queries.

don't be confused, the index only is not so important and you will not gain so much in MaxDB and MSSQL.