topic Re: performance with where condition in Application Development and Automation Discussions

performance with where condition

Former Member — Thu, 11 Sep 2008 06:27:04 GMT

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

Re: performance with where condition

Former Member — Thu, 11 Sep 2008 06:36:52 GMT

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

Re: performance with where condition

ThomasZloch — Thu, 11 Sep 2008 07:29:39 GMT


> 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

Re: performance with where condition

Former Member — Thu, 11 Sep 2008 08:18:25 GMT

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

Re: performance with where condition

Former Member — Thu, 11 Sep 2008 08:35:18 GMT

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

Re: performance with where condition

Former Member — Thu, 11 Sep 2008 11:34:00 GMT

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.

Re: performance with where condition

Former Member — Fri, 12 Sep 2008 08:24:28 GMT

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.

Re: performance with where condition

Former Member — Fri, 12 Sep 2008 14:02:38 GMT

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