What is the Difference?

[Former Member]

Hello All,

I had a select statement like this.

OPTION 1:

SELECT

vbak~kunnr

vbak~vbeln

vbak~ernam

vbak~autlf

vbak~lifsk

vbap~posnr AS posnv

vbap~posnr AS posnn

vbap~matnr

vbap~pstyv

vbap~prodh

vbap~werks

vbap~abgru

vbap~fixmg

vbap~kwmeng

vbuk~gbstk

INTO TABLE g_t_nso_sales

FROM vbak

INNER JOIN vbap

ON vbak_{vbeln = vbap}vbeln "AND

INNER JOIN vbuk

ON vbak_{vbeln = vbuk}vbeln

WHERE vbak~kunnr IN s_kunnr AND

vbak~vbeln IN s_vbeln AND

vbak~auart = v_auart AND

vbak~vkorg = v_vkorg AND

vbak~ernam IN r_uname AND

vbak~audat IN s_audat AND

vbap~vbeln in s_vbeln AND

vbap~pstyv IN s_pstyv AND

vbap~werks IN s_werks AND

vbuk~vbeln in s_vbeln and

vbuk~gbstk IN r_gbstk.

we had issues with this select in terms of performance.

it was changed to following.

SELECT vbak~kunnr

vbak~vbeln

vbak~ernam

vbak_{autlf vbak}lifsk vbap~posnr AS posnv

vbap~posnr AS posnn

vbap~matnr

vbap~pstyv

vbap~prodh

vbap~werks

vbap~abgru

vbap~fixmg

vbap~kwmeng

vbuk~gbstk

vbak~bstnk

INTO TABLE g_t_nso_sales

FROM vbak

INNER JOIN vbap

ON vbak_{vbeln = vbap}vbeln "AND

INNER JOIN vbuk

ON vbak_{vbeln = vbuk}vbeln

WHERE vbak~kunnr IN s_kunnr AND

vbak~vbeln IN s_vbeln AND

vbak~auart = v_auart AND

vbak~vkorg = v_vkorg AND

vbak~ernam IN r_uname AND

vbak~audat IN s_audat AND

vbak~bstnk IN s_bstkd AND

vbap~pstyv IN s_pstyv AND

vbuk~gbstk IN r_gbstk.

As you can see the only difference is removing ,

vbuk~vbeln in s_vbeln

vbap~vbeln in s_vbeln from where conditions.

It has improved performance, but at the cost of not fectghing few records.

Query 1 fetched 5K records, where later picked only 3,500

records.

I count really understand the difference in queries.

am sure am missing something ova here, can some pl help me to understand this.

Rgds,

ö

[hymavathi_oruganti]

USE THE FIRST ONE AND MAKE S_VBELN AS OBLIGATORY THEN U CAN SEE THE PERFORMANCE OF 1ST IS ALSO GOOD.

THE DIFFERENCE IS , A SELECT OPTION, IF IT IS BLANK RETRIEVES ALL RECORDS

[Former Member]

if u make it mandatory, then there is no point of those conditions in where conditions (as those conditions are checked in innner join)

[Former Member]

Hello All,

As per Business req it canot be MANDATORY!!

We have sales org already as reuqired field.

Thanks,

Ö

[Former Member]

Hello,

There is a INNER join on VBELN field for the specified tables.

Rgds,

Ö

[Former Member]

Ideally these two selects should get same result, unless s_vbeln is empty.

[Former Member]

hi,

the missed out records possibly could be the records satisfying these conditions

vbuk~vbeln in s_vbeln

vbap~vbeln in s_vbeln.... since u have removed those conditions in u'r second select statements the records which exists with above conditions are not retrieved from DB......

Regards,

Santosh

[Former Member]

vbuk~vbeln in s_vbeln

it means u given select option for vbeln.

in 1st code it select only combination for vbeln which are present in select option.

in 2nd code it show all the combination excluding vbeln...

for example u requried values from combination of three field.

and some values from combination of two field.

now think mathmaticaly .