Improve that SELECT statement:

[Marcal_Oliveras]

Hello,

I'm in CRM and I have to get the partners with some activity from a given date. The date format is a time stamp (DEC 15 field), so if the user enters a very old date, for example year 2007 date, the operation date_1 > p_date is very very slow and the programp generates a dump.

Maybe if I select the whole table and check the values later in a LOOP the performance will improve but I'm not sure because there are 17.000.000 entries on table crmd_order_index and my possible result is around 40.000 entries.

What do you think?

  SELECT DISTINCT a~partner_no
    INTO TABLE lt_partners32
    FROM crmd_order_index AS a INNER JOIN crm_jest AS b
    ON a~header = b~objnr
    WHERE a~object_type     =  'BUS2000126' "Activity
      AND a~process_type_ix IN ('0000','Z000')
      AND a~STAT_OPEN      <> 'X' "Closed
      AND a~date_1               >= p_date
      AND b~inact                  =  SPACE
      AND b~stat                   =  'E0003'.

Edited by: Marshal on Mar 13, 2009 3:01 PM

[Former Member]

What indexes are there for crmd_order_index?

Rob

[Former Member]

SELECT DISTINCT --->It will increase the load on the database server.

Instead Remove Distinct.

SELECT  a~partner_no
    INTO TABLE lt_partners32
    FROM crmd_order_index AS a INNER JOIN crm_jest AS b
    ON a~header = b~objnr
    WHERE a~object_type     =  'BUS2000126' "Activity
      AND a~process_type_ix IN ('0000','Z000')
      AND a~STAT_OPEN       'X' "Closed
      AND a~date_1               >= p_date
      AND b~inact                  =  SPACE
      AND b~stat                   =  'E0003'.

 
  SORT lt_partners32.

 DELETE ADJACENT DUPLICATES FROM lt_partners32.

This will reduce load on database server.

Regards,

Gurpreet

[Former Member]

Can you try using the package size option in the select statement (say packages of 10000 records) .

Mathews.

[Former Member]

> Maybe if I select the whole table and check the values later in a LOOP the performance will improve

> but I'm not sure because there are 17.000.000 entries on table crmd_order_index and my possible

> result is around 40.000 entries.

1. of April is still weeks ahead!!!

You can not improve performance by transferring more data then you need!

Same holds for the recommendation with the removal of the DISTINCT. Leave it where it is.

How many data are actually selected? What performance do you expect. Tables are large if you select a lot then it can need some time.

Otherwise check the join, are the right indexes used. Sorry, I can not explain you in detail how this works.

Siegfried

[Former Member]

You can try out few steps.

1. Write 2 separate SELECT statements which will reduce load of inner join since table "crm_jest" will have more entries. In this case second SELECT statement will take less time.

2. Check if you can create a secondary index on table crmd_order_index with required fields of your report. Before this check with Basis person if he can help you in this and also find out how many indexes are alreay created.

3. Other option is to create a database view for both the tables.

Hope this will give some help to improve performance.

Regards,

Sunil

[Former Member]

Hi Marshal,

If I understood you question correctly, you are obtaining the partners who have an CRM object in some situation.

Check if you can change the table access strategy. If the amount of records on CRM_JEST with INACT = space and STAT = E0003, don't increase for long date selects concluding that is a temporary situation, you can try something like this:

SELECT DISTINCT objnr INTO lt_objnr
FROM crm_jest
WHERE inact = space
  AND 'E0003'.

IF NOT lt_objnr[] IS INITIAL.
  SELECT DISTINCT partner_no INTO TABLE lt_partners32
  FROM crmd_order_index
  FOR ALL ENTRIES IN lt_objnr
  WHERE header = lt_objnr-objnr
    AND object_type     =  'BUS2000126' "Activity
    AND process_type_ix IN ('0000','Z000')
    AND STAT_OPEN       = 'X' "Closed
    AND date_1          >= p_date.
ENDIF.

Regards,

Fernando Da Ró

[Former Member]

Hi,

The major case in which SELECT DISTINCT is a serious performance penalty is when the volume of data to be sorted is exceedingly large (for example TEMP disk storage area may be used). It uses DELETE ADYACENT DUPLICATE instead of SELECT DISTINCT and use Range for the values ('0000','Z000').

Additionally you must verify the correct use of the indexes of the tables to improve the access to Data Base.

Hope this information is help to you.

Regards,

José