topic Z table count issue. Database or code in Application Development and Automation Discussions

Z table count issue. Database or code

Former Member — Fri, 09 Oct 2009 12:37:01 GMT

Hi,

I've got an issue with counting records in a Z table.

I select all the valid entries into an internal table. My program must then count the occurance of 3 fields (ie Field 1, A, B) where the content is the same. If there are 10 occurances then update the count field at each occurance.

Based on a condition, the program also does the same but for Field 2 instead of Field 1.

The problem is that the count for Field 2, A, B is taking considerable longer (even though it has less records) than the count for Field 1, A, B.

Field 1 and Field 2 are set as keys in the table and there are no indexes.

In my SQL trace I can see that the duration of the initial select is in red (149.239) to retrieve 40,000 records. However, why would the count for (Field 1, A, B) be less?

Can anyone help?

Re: Z table count issue. Database or code

Former Member — Fri, 09 Oct 2009 13:42:36 GMT

It is hard to understand your problem.

Please don't get confused by the red color of the SQL-Trace, just ignore it, 150msec for 40.000 is not an issue.

The meaning of this time depends highly on your used database, there are bigs differences between the platforms.

So what is your problem, how much time does the total execution need?

Use the Summary by SQL statements (see menu trace-list), there you see totals and one line per statement, it is the much better display!

Siegfried

Re: Z table count issue. Database or code

Former Member — Fri, 09 Oct 2009 14:09:39 GMT

TYPES: BEGIN OF ty_ztable,
         f1(30)    TYPE c,
         f2(30)    TYPE c,
         fa(30)    TYPE c,
         fb(30)    TYPE c,
         cnt_cmb1  TYPE c,
         cnt_cmb2  TYPE c,
       END OF ty_ztable,

       BEGIN OF ty_comb1,
         f1(30) TYPE c,
         fa(30) TYPE c,
         fb(30) TYPE c,
         count  TYPE i,
       END OF ty_comb1,

       BEGIN OF ty_comb2,
         f2(30) TYPE c,
         fa(30) TYPE c,
         fb(30) TYPE c,
         count  TYPE i,
       END OF ty_comb2.

DATA: w_ztable TYPE                ty_ztable,
      w_comb1  TYPE                ty_comb1 ,
      w_comb2  TYPE                ty_comb2 ,
      w_index  TYPE                sy-tabix ,

      t_ztable TYPE TABLE OF ty_ztable,
      t_comb1  TYPE SORTED TABLE OF ty_comb1
        WITH UNIQUE KEY f1 fa fb,
      t_comb2  TYPE SORTED TABLE OF ty_comb2
        WITH UNIQUE KEY f2 fa fb.


SELECT f1
       f2
       fa
       fb
  FROM ztable
  INTO TABLE t_ztable.

LOOP AT t_ztable INTO w_ztable.

  READ TABLE t_comb1 INTO w_comb1
    WITH KEY f1 = w_ztable-f1
             fa = w_ztable-fa
             fb = w_ztable-fb.

  IF sy-subrc EQ 0.

    w_index = sy-tabix.

    ADD 1 TO w_comb1-count.

    MODIFY t_comb1 FROM w_comb1
      INDEX w_index
      TRANSPORTING
        count.

  ELSE.

    w_comb1-f1    = w_ztable-f1.
    w_comb1-fa    = w_ztable-fa.
    w_comb1-fb    = w_ztable-fb.
    w_comb1-count = 1          .

    INSERT w_comb1 INTO TABLE t_comb1.

    CLEAR w_comb1.

  ENDIF.

  READ TABLE t_comb2 INTO w_comb2
    WITH KEY f2 = w_ztable-f2
             fa = w_ztable-fa
             fb = w_ztable-fb.

  IF sy-subrc EQ 0.

    w_index = sy-tabix.

    ADD 1 TO w_comb2-count.

    MODIFY t_comb2 FROM w_comb2
      INDEX w_index
      TRANSPORTING
        count.

  ELSE.

    w_comb2-f2    = w_ztable-f2.
    w_comb2-fa    = w_ztable-fa.
    w_comb2-fb    = w_ztable-fb.
    w_comb2-count = 1          .

    INSERT w_comb2 INTO TABLE t_comb2.

    CLEAR w_comb2.

  ENDIF.

ENDLOOP.


LOOP AT t_comb1 INTO w_comb1.

  UPDATE ztable
    SET cnt_cmb1 = w_comb1-count
    WHERE f1 = w_comb1-f1
    AND   fa = w_comb1-fa
    AND   fb = w_comb1-fb.

ENDLOOP.


LOOP AT t_comb2 INTO w_comb2.

  UPDATE ztable
    SET cnt_cmb2 = w_comb2-count
    WHERE f2 = w_comb1-f2
    AND   fa = w_comb1-fa
    AND   fb = w_comb1-fb.

ENDLOOP.

Re: Z table count issue. Database or code

Former Member — Fri, 09 Oct 2009 14:10:09 GMT

When I loop through these records I need to count where the data in Field 1 , A and B are the same and where Field 2, A and b are the same.

For example this is what my internal table would be like,


*bold* Field 1  Field 2     A       B      Count
              XY                        D3     3        2
              XY                        D3     3        2
              XY                        D2     4        1
                             FF         D3     6        2
                             FF         D3     6        2

Then i loop though it

IF B < 5

count field 1, A and B

ELSE

count Field 2, A and B

ENDIF.

When I count the combination of XY, D3, A3 or XY, D2, J3 there is no problem

When I count the combination of FF, D3, S6 my program can take up to 6/7 times longer to process.

Re: Z table count issue. Database or code

Former Member — Fri, 09 Oct 2009 14:11:08 GMT

Damien,

You need to share your code with us for us to help you. In the mean time you can also try using the logic I have shown in my previous post and see it helps any.

Re: Z table count issue. Database or code

Former Member — Fri, 09 Oct 2009 14:24:23 GMT

Mark,

Thanks very much for that reply. I'm sorry but I can't post my code.My code is very similar so I'll compare them and see if your code is quicker.

Re: Z table count issue. Database or code

Former Member — Fri, 09 Oct 2009 15:17:58 GMT

Mark,

Your code runs twice as fast as mine so in my DEV system. Thanks for your help.