performance with where condition

[Former Member]

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

[Former Member]

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

[ThomasZloch]

>

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

I'm reading it a lot around here, that this would make a difference. Actually I cannot imagine that it does. Is there any proof that the sequence of fields in the WHERE-condition does make any difference on performance?

The real problem of that SELECT is that no primary or secondary key is being used. Unfortunately I did not find a way to speed this up, except the very last resort, creating a new secondary index.

Thomas

[Former Member]

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

[Former Member]

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

[Former Member]

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.

[Former Member]

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.

[Former Member]

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