How to get good statistics on a initially empty table?

[Former Member]

Hello,

I'm facing a performance issues on a SAP Netweaver Stack on a MaxDB 7.9.08.015.

The SAP report that is not performing well is using a table in a join spanning 3 tables. The table is empty initially. During the report execution it is filled with 20000+ records. Then the join is run and after the report execution the table is empty again.

I have found out that when I set a breakpoint right before the join is executed (the table is filled with 20000+ records) and then execute an "update statistics <tablename>", the join will finish in milliseconds.

If I run the "update statistics <tablename>" on the empty table (which is useless, I guess?) the join will run minutes - up to 20 minutes.

Do you have any recommendation how to approach this issue in a productive system where I can not set breakpoints?

Below you can find the explains for an update statistics on the empty and on the filled table.

Regards,

Jens

This is the explain with update statistics on the empty table RR00K00G03X_DQ:

SELECT
  DISTINCT  T_01 . "C5" "EXTRACTKEY" , T_00 . "DEVID" "DEVID"
FROM
  "RR00K00G03X_DQ" T_00 INNER JOIN "ZDOE00G03702" T_01 ON T_01 . "CLIENT" = ? AND T_01 . "EXTRACTKEY
  " = T_00 . "INSID" INNER JOIN "ZDOE00G03X00" T_02 ON T_02 . "CLIENT" = ? AND T_02 . "EXTRACTKEY" =
   T_01 . "C5"
WHERE
  T_00 . "CLIENT" = ? AND T_00 . "SEQNO" IN ( ? , ? , ? , ? , ? )

Execution Plan

OWNER              TABLENAME                      COLUMN OR INDEX                STRATEGY                                 PAGECOUNT

                   T_00                                                          RANGE CONDITION FOR KEY                           1
                                                  CLIENT                              (USED KEY COLUMN)
                                                  SEQNO                               (USED KEY COLUMN)
                   T_01                           ZDOE00G03702~EKI               JOIN VIA MULTIPLE INDEXED COLUMNS                51
                                                  CLIENT                              (USED INDEX COLUMN)
                                                  EXTRACTKEY                          (USED INDEX COLUMN)
                   T_02                           ZDOE00G03X00~EKI               JOIN VIA MULTIPLE INDEXED COLUMNS               237
                                                  CLIENT                              (USED INDEX COLUMN)
                                                  EXTRACTKEY                          (USED INDEX COLUMN)
                                                                                      NO TEMPORARY RESULTS CREATED
                                                                                      RESULT IS COPIED   , COSTVALUE IS            2
                   SHOW                                                          QUERYREWRITE : APPLIED RULES:
                   SHOW                                                             DistinctPushDown                               1
                   SHOW                                                             PushDownPredicates                             1

Explain End

This is the explain after the update statistics with 20000 records in the table RR00K00G03X_DQ.

Count of the tables:

SQL Statement

SELECT
  DISTINCT  T_01 . "C5" "EXTRACTKEY" , T_00 . "DEVID" "DEVID"
FROM
  "RR00K00G03X_DQ" T_00 INNER JOIN "ZDOE00G03702" T_01 ON T_01 . "CLIENT" = ? AND T_01 . "EXTRACTKEY
  " = T_00 . "INSID" INNER JOIN "ZDOE00G03X00" T_02 ON T_02 . "CLIENT" = ? AND T_02 . "EXTRACTKEY" =
   T_01 . "C5"
WHERE
  T_00 . "CLIENT" = ? AND T_00 . "SEQNO" IN ( ? , ? , ? , ? , ? )

Execution Plan

OWNER              TABLENAME                      COLUMN OR INDEX                STRATEGY                                 PAGECOUNT

                   T_00                                                          RANGE CONDITION FOR KEY                         564
                                                  CLIENT                              (USED KEY COLUMN)
                                                  SEQNO                               (USED KEY COLUMN)
                   T_01                           ZDOE00G03702~EKI               JOIN VIA MULTIPLE INDEXED COLUMNS                51
                                                  CLIENT                              (USED INDEX COLUMN)
                                                  EXTRACTKEY                          (USED INDEX COLUMN)
                   T_02                           ZDOE00G03X00~EKI               JOIN VIA MULTIPLE INDEXED COLUMNS               237
                                                  CLIENT                              (USED INDEX COLUMN)
                                                  EXTRACTKEY                          (USED INDEX COLUMN)
                                                                                      NO TEMPORARY RESULTS CREATED
                                                                                      RESULT IS COPIED   , COSTVALUE IS            2
                   SHOW                                                          QUERYREWRITE : APPLIED RULES:
                   SHOW                                                             DistinctPushDown                               1
                   SHOW                                                             PushDownPredicates                             1

Explain End

[Former Member · Accepted solution]

Hello Jens,

the application you are talking about is this a SAP standard Report or a customer owned report?

The only possibility to get valid statistics would be to add the update statistics into your ABAP coding just before the Join is executed.

The BW  application is using such a mechanism to get valid statistics for temp tables which are created and filled before the join is executed and afterwards the table content is deleted.

I will try to find out if the are function modules which can be used.

For me it would be interesting to get the REport/transaction which is used in your case.
If it is SAP standard we should contact the application development team as well.

Regards, Christiane

[Former Member · Accepted solution]

Hi Christiane,

thanks for the answer!

The tables are used in SAP Netweaver Mobile. The tables are generated, thats why they are in the "Z"-Namespace.

I will get in touch with the SAP development.

I had another idea : What about using a "hint" for the long running SQL?

Would that work without having updated statistics?

Regards,

Jens

[Former Member · Accepted solution]

Hi Jens,

you could use the HINT ORDERED in Join environment.

With this hint you can define the order in which the tables are processed ( T00/T01/T02) but you have to change the Join syntax as follows:

SELECT /*+ORDERED */

  DISTINCT  T_01 . "C5" "EXTRACTKEY" , T_00 . "DEVID" "DEVID"

FROM

  "RR00K00G03X_DQ" T_00 , "ZDOE00G03702" T_01, "ZDOE00G03X00" T_02

WHERE
T_01 . "CLIENT" = ? AND T_01 . "EXTRACTKEY  " = T_00 . "INSID"

AND

T_02 . "CLIENT" = ? AND T_02 . "EXTRACTKEY" = T_01 . "C5"

AND

  T_00 . "CLIENT" = ? AND T_00 . "SEQNO" IN ( ? , ? , ? , ? , ? )

But then this statement is always executed with the EXPLAIN plan independant from the values in the Qualification. So it could happen that depending on the Where qualification values a worse strategy is used.

You avoid the cost based optimizer.

Better would be to create a customer owned FORM-Routine with Native SQL which concatenates the update statistics command for the tables and executed

concatenate 'UPDATE STAT "'l_tablnm'" ESTIMATE'
into l_stmt.

Execute the created update statistics table command

concatenate
'UPDATE STAT COLUMN(*) FOR "'l_tablnm'" ESTIMATE'
into l_stmt.

==> Update Statistics column is important for the JOIN !!!

Regards, Christiane