Skip to Content
avatar image
Former Member

Slect query taking time when executed

Hai Friends,

The following is my select query

Select zmatnr

zshift

kwmeng

zdate

from ztable

into table it_table

where zmatnr = so_matnr

and zdate = so_date

and delnum = '0'

and prtnr > '0'.

Here zmatnr and zdate are only the key fields. The other two are not. This query takes time. Shall i index those fields. Is it right. Explain the right situation to index the table.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • avatar image
    Former Member
    Sep 13, 2008 at 05:39 AM

    Hi ... you must put only 2 fields into the query because zmatnr and zdate have only a register

    Select zmatnr

    zshift

    kwmeng

    zdate

    from ztable

    into table it_table

    where zmatnr = so_matnr

    and zdate = so_date.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 15, 2008 at 08:13 AM

    >

    > Hai Friends,

    >

    > The following is my select query

    >

    > Select zmatnr

    > zshift

    > kwmeng

    > zdate

    > from ztable

    > into table it_table

    > where zmatnr = so_matnr

    > and zdate = so_date

    > and delnum = '0'

    > and prtnr > '0'.

    >

    > Here zmatnr and zdate are only the key fields. The other two are not. This query takes time. Shall i index those fields. Is it right. Explain the right situation to index the table.

    Hi,

    in the WHERE clause you have to restrict your selection as much as possible.

    The index selection depends on key fields AND statistics about the data, wich means that an index may choosen if the database optimizer comes to the conclusion that only a small part of the rows will be in the selection.

    I would suggest:

    • Check table statistics (current = represents the data in the table)

    • Check execution plan in ST05: is the statement using an index?

    • Check if you can create a secondary index adding the other 2 fields in your WHERE to the indexed key fields

    if the query could discard a significant amount of rows because of the filter condition the new index should be choosen.

    Be aware of table updates where an additional index can cause a performance degradation)

    bye

    yk

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 15, 2008 at 08:16 AM

    HI,

    please mension the main tables from which u are fetching the data and which are the key fields and order of the key fields.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 15, 2008 at 12:08 PM

    Hi T Vennila,

    Make sure that you are following the below two points.

    1.The order of feilds in the select query and in where condition should be in the order of database table.

    2.Modify your select query as :

    Select zmatnr

    zshift

    kwmeng

    zdate

    from ztable

    into table it_table

    where zmatnr = so_matnr

    and zdate = so_date.

    delete it_table where delnum NE '0' and prtnr LE '0'.

    And Process the internal table as you want.

    Hope this helps you.

    Regards,

    Rama.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi,

      this is wrong: why select more data than necessary?

      The 1st rule in tuning a SELECT is to restrict it as much as possible

      • Consider the query would return 100.000 rows more because of a non-selective filter

      • Consider this would be a query executed by many users

      You would create a huge workload - and to make matters worse - you delete the data immediatly.

      Again: if an index is accessed depends on

      • the right order of INDEX keys in the index (and not in the query's SELECT list or WHERE clause fields)

      • the data's attributes and distribution (table and index statistics)

      databases do their physical or cached reads on complete data blocks (or pages - depends on the database vendors wording) either over an index access or a table read; either from disk or a cache in memory. They NEVER read data by row (wich could imply that a kind of "SELECT order" would speed up - wich is not) .The filter is applied on the rows in the block - the database has it's metadata (the rowid) to know where a certain information resides in wich block .

      Your implication of an order in the SELECT violates one of the holy rules of relational databases wich is:

      Thou should not know anything about the physical structure! (i.e. the order of fields in the table).

      It's all about where you can find the information (wich table, wich field) not how : this is "database optimizer work". Did you know that some databases rewrite queries because of this optimization process?

      Nowadays databases are not a big data dump anymore - they know a lot about the data (if you let them know) : even about the order in wich fields build the table's structure 😉

      bye

      yk