Performance statements

[former_member515329]

Hi..below select statements are taking huge time for execution..can anyone show me better syntax...

*************************************************************

SELECT appt_guid "appointment guid

appl_guid "application guid

appt_type "appointment type

tst_from "contract date

entry_tst "Time stamp of creation

FROM scapptseg

INTO TABLE it_scapptseg

FOR ALL ENTRIES IN it_crmdlink

WHERE appl_guid = it_crmdlink-guid_set

AND appt_type IN (c_contend,c_contstart,c_contstart_or).

************************************************

SELECT guid

header

item

partner_no INTO TABLE it_dealer

FROM crmd_order_index

FOR ALL ENTRIES IN it_contract

WHERE header = it_contract-header

AND item = it_contract-guid

AND pft_30 = 'X'.

************************************************

SELECT guid

created_by

header

product

parent

INTO TABLE lt_phy_guids

FROM crmd_orderadm_i

FOR ALL ENTRIES IN it_contract

WHERE object_type = gc_object_type_item-object

AND header = it_contract-header

AND parent = it_contract-guid.

*******************************************************

SELECT a~guid_hi

a~guid_set

b~partner_guid

b~partner_no

b~partner_fct

INTO TABLE it_dlr_link

FROM crmd_link AS a INNER JOIN crmd_partner AS b

ON a_{guid_set = b}guid

FOR ALL ENTRIES IN it_temp_dlr

WHERE a~guid_hi = it_temp_dlr-header

AND a~objtype_set = gc_object_type_convert-partner"'07'

AND a~objtype_hi = gc_object_type_convert-orderadm_h

AND b~partner_fct IN (c_dlrgua,c_corpgua,c_persgua).

[ravi_lanjewar]

Hi,

Put only one question at a time.

For you first SQL you can check identical select using st05 and if found any identical select remove from driver table ie using in for all entries. There is no any index issue with this table it is perfectly all rights but you are missing mandt index left fields. you can make some modification in SQL. Also check for it_crmdlink is not empty.

sort it_crmdlink by guid_set.

delete adjacent duplication from it_crmdlink  comparing guid_set.
if it_crmdlink [] is not initial.

SELECT appt_guid "appointment guid
appl_guid "application guid
appt_type "appointment type
tst_from "contract date
entry_tst "Time stamp of creation
FROM scapptseg client specified
INTO TABLE it_scapptseg
FOR ALL ENTRIES IN it_crmdlink
WHERE mandt = sy-mandt
      and appl_guid = it_crmdlink-guid_set
    AND appt_type IN (c_contend,c_contstart,c_contstart_or).

endif.

You can also modify Last SQL according the above way there is alos you missing the mandt fields of table and not check the driver table is empty.

Pl. do it.

Kind Rgds

Ravi

Edited by: Ravishankar Lanjewar on Jul 8, 2010 11:53 AM

[Former Member]

Dear Ravi,

when ever you use for all entries the most important thing to remember is "first we need to check if the internal table is not empty else it will select all the entries from the Database table".

Code is given for your better understanding.

*if  it_scapptseg[] is not initial.*
SELECT appt_guid "appointment guid
appl_guid "application guid
appt_type "appointment type
tst_from "contract date
entry_tst "Time stamp of creation
FROM scapptseg
INTO TABLE it_scapptseg
FOR ALL ENTRIES IN it_crmdlink
WHERE appl_guid = it_crmdlink-guid_set
AND appt_type IN (c_contend,c_contstart,c_contstart_or).
endif.

Hope this helps you.

Regards

Sajid

[Former Member]

INDEX UID should be used.

DATA: it_crmdlink_tmp LIKE TABLE OF it_crmdlink.

IF NOT it_crmdlink[] IS INITIAL.

  it_crmdlink_tmp[] = it_crmdlink[].

  SORT it_crmdlink_tmp BY guid_set.

  DELETE ADJACENT DUPLICATES FROM it_crmdlink_tmp COMPARING guid_set.

  SELECT appt_guid   "appointment guid
         appl_guid   "application guid
         appt_type   "appointment type
         tst_from    "contract date
         entry_tst   "Time stamp of creation
    FROM scapptseg
    INTO TABLE it_scapptseg
    FOR ALL ENTRIES IN it_crmdlink_tmp
    WHERE appl_guid EQ it_crmdlink_tmp-guid_set
    AND   appt_type IN (c_contend,c_contstart,c_contstart_or).

ENDIF.

INDEX HDR should be used.

DATA it_contract_tmp LIKE TABLE OF it_contract.

IF NOT it_contract[] IS INITIAL.

  it_contract_tmp[] = it_contract[].

  SORT it_contract_tmp BY header parent.

  DELETE ADJACENT DUPLICATES FROM it_contract_tmp
    COMPARING header parent.

  SELECT guid
         created_by
         header
         product
         parent
  INTO TABLE lt_phy_guids
  FROM crmd_orderadm_i
  FOR ALL ENTRIES IN it_contract_tmp
  WHERE header      EQ it_contract_tmp-header
  AND   parent      EQ it_contract_tmp-guid
  AND   object_type EQ gc_object_type_item-object.

ENDIF.

Table CRMD_LINK = partial primary key

Table CRMD_PARTNER = INDEX SET

DATA it_temp_dlr_tmp LIKE TABLE OF it_temp_dlr.

IF NOT it_temp_dlr[] IS INITIAL.

  it_temp_dlr_tmp[] = it_temp_dlr[].

  SORT it_temp_dlr_tmp BY header.

  DELETE ADJACENT DUPLICATES FROM it_temp_dlr_tmp
    COMPARING header.

  SELECT a~guid_hi
         a~guid_set
         b~partner_guid
         b~partner_no
         b~partner_fct
  INTO TABLE it_dlr_link
  FROM       crmd_link AS a
  INNER JOIN crmd_partner AS b
  ON a~guid_set = b~guid
  FOR ALL ENTRIES IN it_temp_dlr_tmp
  WHERE a~guid_hi     EQ it_temp_dlr_tmp-header
  AND   a~objtype_set EQ gc_object_type_convert-partner     "'07'
  AND   a~objtype_hi  EQ gc_object_type_convert-orderadm_h
  AND   b~partner_fct IN (c_dlrgua,c_corpgua,c_persgua).

ENDIF.

[former_member515329]

Thanks for ur replies..

But how to use these indexes?

INDEX UID should be used.

INDEX HDR should be used.

Table CRMD_LINK = partial primary key

Table CRMD_PARTNER = INDEX SET

[BH2408]

HI ,

We need to use the INDEX with Select quries while fetching the data from the database.

Hope in the above select statements they are consider the index.

We need to check which index is used in the Select in ST05(SQL Trace).

Regards,

Bharani

[former_member515329]

can u just write above select query using:

INDEX UID should be used.

INDEX HDR should be used.

Thx in advance.

[Former Member]

Ravi if you structure your where clause the way I have illustrated in my previous post the system will pick the correct address. Use transaction ST05 to trace your select statement during run time and verify if the desired index is being picked.

[former_member515329]

i have changed the below statement...will it work for performance point of view?

SELECT appt_guid "appointment guid

appl_guid "application guid

appt_type "appointment type

tst_from "contract date

entry_tst "Time stamp of creation

FROM scapptseg

INTO TABLE it_scapptseg

FOR ALL ENTRIES IN it_crmdlink

WHERE appl_guid = it_crmdlink-guid_set

AND appt_type IN (c_contend,c_contstart,c_contstart_or)

%_HINTS ORACLE 'INDEX("SCAPPTSEG" "SCAPPTSEG~UID")'.

[ravi_lanjewar]

Hi,

There is no need of the write %hint by default it will consider proper index, But some cases same fields contain in multiple index that time it create problem and not consider approprite index.

Please, Check for number of execution and the index statics update. Index statics update could not be update more than 1 one week old.

Kinds Rgds

Ravi Lanjewar

Edited by: Ravishankar Lanjewar on Jul 9, 2010 10:06 AM

[Former Member]

Ravi,

You will have to use transaction ST05 to determine what index is actually being picked.