Skip to Content
avatar image
Former Member

performance with where condition

SELECT VBELN FKART WAERK VKORG VTWEG REGIO

KNUMV FKDAT KDGRP KUNAG KUNRG SPART

KURRF BSTNK_VF KALSM

FROM VBRK

INTO TABLE IT_VBRK

WHERE FKART IN ('F2','RE','G2','L2','S1','S2','ZKSF','ZRE','ZS1')

AND SPART = P_SPART

AND FKDAT IN S_FKDAT

AND VKORG = P_VKORG

AND VTWEG IN S_VTWEG.

this query lot time.. any way to improve for same where condition...

Edited by: Julius Bussche on Sep 11, 2008 10:15 AM

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • avatar image
    Former Member
    Sep 11, 2008 at 06:36 AM

    Hi malay gal,

    SELECT VBELN FKART WAERK VKORG VTWEG KALSM
    KNUMV FKDAT KDGRP KURRF 
    REGIO KUNRG  KUNAG SPART BSTNK_VF 
    FROM VBRK
    INTO TABLE IT_VBRK
    WHERE FKART IN ('F2','RE','G2','L2','S1','S2','ZKSF','ZRE','ZS1')
    AND VKORG = P_VKORG
    AND VTWEG IN S_VTWEG
    AND FKDAT IN S_FKDAT
    AND SPART = P_SPART.

    Please try to give the fields in order of the database table.

    Best regards,

    raam

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Thomas Zloch

      Hi Thomas,

      good point!

      if one really would benchmark this:

      Create some views with different orders of the fields and compare runtimes with a pure table access.

      (to be honest I never did that)

      But...

      From the database point of view data is stored in logical pieces (like blocks ) , organized into higher levels like segments (tables, indices) and they point at least to physical blocks on the hard disk.

      The order of fields in a SELECT query has no significancef for the runtime because the data is read by the database in mentioned logical blocks and transported to the output buffer.

      In a more theoretically background this aligns with one of the rules of relational databases that you should not know anything about the physical structure to access the data - only where to get it from.

      The number of fields is important...

      sure it's a good habit not to waste resources anyway (buffering a * selection with hundreds of fields is a waste if you only need 2 of them.

      sure if a table consist of hundreds of fields , that must be spread onto several blocks to store the rows.

      bye

      yk

  • avatar image
    Former Member
    Sep 11, 2008 at 08:35 AM

    Hi,

    1. for your SQL I have only a comment about the IN clauses:

    ...

    AND FKDAT IN S_FKDAT -> S_FKDAT should be filled with some values

    AND VKORG = P_VKORG

    AND VTWEG IN S_VTWEG. -> S_VTWEG should be filled with some values

    2. check table statistics, get a execution plan with ST05 and post it here.

    As Thomas said, an index access may helpful. So we don't no how your SQL

    is processed , we can give no further clue.

    bye

    yk

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 11, 2008 at 11:34 AM

    Hi

    SELECT VBELN FKART WAERK VKORG VTWEG KALSM

    KNUMV FKDAT KDGRP KURRF

    REGIO KUNRG KUNAG SPART BSTNK_VF

    FROM VBRK

    INTO TABLE IT_VBRK

    WHERE FKART IN ('F2','RE','G2','L2','S1','S2','ZKSF','ZRE','ZS1')

    AND VKORG = P_VKORG

    AND VTWEG IN S_VTWEG

    AND FKDAT IN S_FKDAT

    AND SPART = P_SPART.

    please try this code.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 12, 2008 at 08:24 AM

    Hi Malay,

    Please fetched the fields in the order they are in the database and another thing is the values,

    'F2','RE','G2','L2','S1','S2','ZKSF','ZRE','ZS1' can be put in a range.

    Hope these two things can help to improve the performance.

    Regards,

    Shobana.K.

    Add comment
    10|10000 characters needed characters exceeded

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

    Hi,

    I am wondering! none of the fields you are using in the WHERE clause are neither key fields nor fields that match with any indexe. I am not sure about the requirement of your query, but suggest you to add either VBELN or LCNUM fields in the WHERE clause. VBELN is a key field and LCNUM field has index on it.

    Regards,

    Venkat

    Add comment
    10|10000 characters needed characters exceeded