Skip to Content
avatar image
Former Member

Performance Improvement of a select query on a view

Hi Experts,

I have a select query on view HRVPADIC. This view sits on table HRP1001 and HRPADIC.

The select query is

SELECT * FROM HRVPADIC INTO CORRESPONDING FIELDS OF TABLE p_lt_1001

WHERE PLVAR = p_lv_plvar

AND RELAT = '291'

AND CONTRACT_APPL = p_iv_appl

AND CONTRACT_TYPE = '02'

AND CONTRACT_ID = p_fs_ctrtbuid.

I want to improve the performance of this query.

Field PLVAR and RELAT are one of the primary key fields of the table HRP1001.

Fields CONTRACT_APPL, CONTRACT_TYPE ,CONTRACT_ID are the non key fields of table HRPADIC.

From the application side, I cannot another condition to the query to improve the performance.

What are the different ways from the Database side to improve the performance of this query?

Will creating a secondary index on CONTRACT_APPL, CONTRACT_TYPE ,CONTRACT_ID fields in HRPADIC help to improve the performance of this select query which is on the view? Or are there are any different answers to this problem.

Thanks in Advance,

Raju

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Jun 27, 2010 at 08:06 PM

    Hi,

    check this conditions in SE16:

    PLVAR = = p_lv_plvar

    RELAT = '291'

    CONTRACT_APPL = p_iv_appl

    CONTRACT_TYPE = '02'

    CONTRACT_ID = p_fs_ctrtbuid.

    which of the condition limits the result set most?

    Try combinations as well.

    The field combination with the smalles result set

    should be indexed.

    Looking at the names CONTRACT_ID and CONTRACT_TYPE maybe

    together with COnTACT_APPL seems to be a good choice.

    Doublecheck it in SE16.

    Kind regards,

    Hermann

    Add comment
    10|10000 characters needed characters exceeded

    • > so, you suggest to created index on these three fields in the table and it would help to increase the performance of the query while selecting the records from view

      if the fields are specified with equal AND if they limiit the result set... most likely yes.

      But why don't you check with SE16 yourself before you create the index?

      Kind regards,

      Hermann

  • avatar image
    Former Member
    Jun 28, 2010 at 01:34 PM

    Hi Raju,

    Try specifying the following in the where clause of the select statement:

    1) Object Type (OTYPE). There could only be a finite number of object types applicable for this relationship.

    2) Restrict your select query to the Start and End dates (BEGDA and ENDDA). Usually it should be as follows:

    BEGDA LE sy-datum

    ENDDA GE sy-datum

    Regards,

    Mark

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 29, 2010 at 06:53 AM

    HI,

    If you check the view HRVPADIC join condition you can see that ADATANR is not a key fields of table HRP1001. So instead of selecting data from the view first you can select data from HRP1001 based on the key fields you have. Then select data from HRPADIC based on ADATANR from the above select. Here select will be done by the key fields. This may help to you. 😊

    Thanks

    Subhankar

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 21, 2010 at 09:18 AM

    Hi

    first you need to check this condition in SE11 wether there is a value for this selection or not

    1. do not use into corressponding fields.

    2. Why you need to check the values for

    CONTRACT_APPL = p_iv_appl

    AND CONTRACT_TYPE = '02'

    AND CONTRACT_ID = p_fs_ctrtbuid.

    as they are non primary key of table HRPADIC.

    you have created a view on tables HRP1001 and HRPADIC hence there must be a primary key and foreign key.

    so you must have fetched the data according to the primary key. Or must have uysed the primary key from both the tables to create a view.

    Thanks

    lalit Gupta

    Add comment
    10|10000 characters needed characters exceeded