topic Re: SQL performance tuning - select code is taking so long to execute in Application Development and Automation Discussions

SQL performance tuning - select code is taking so long to execute

Former Member — Mon, 02 Jun 2008 08:45:44 GMT

Hi,

Do you have any suggestion on how to improve the performance of this code? It is taking 3 minutes and returns only one record.

SELECT DISTINCT vbak~vbeln

vbkd~zzvextzau

vbak~auart

INTO TABLE zauth_itab

FROM vbak

INNER JOIN vbap

ON vbap_{mandt = vbak}mandt

AND vbap_{vbeln = vbak}vbeln

INNER JOIN vbup

ON vbup_{mandt = vbak}mandt

AND vbup_{vbeln = vbak}vbeln

INNER JOIN vbkd

ON vbkd_{mandt = vbak}mandt

AND vbkd_{vbeln = vbak}vbeln

AND vbkd~posnr = 0

WHERE vbak~vbeln IN s_vbeln

AND vbap~matnr IN s_matnr

AND vbap~kondm IN s_kondm

AND vbak~auart IN s_auart

AND vbak~vkorg IN s_vkorg

AND vbak~guebg IN r_guebg

AND ( vbak~gueen > sy-datum OR

vbak~gueen = 0 )

AND vbup~rfsta EQ 'C'.

Thanks!

Che

Re: SQL performance tuning - select code is taking so long to execute

Former Member — Mon, 02 Jun 2008 08:54:04 GMT

Hi,

Avoid SELECT DISTINCT....instead delete the duplicates after data fetch.

OR condition canbe avoided.

check if u r populating all the select options....

regards,

madhu

Re: SQL performance tuning - select code is taking so long to execute

Former Member — Mon, 02 Jun 2008 09:17:52 GMT

Hi Cherryl Ann Cruz,

First Select all the VBELN into correspodning fields ZAUTH_ITAB from VBAK,

Then sort ZAUTH_ITAB internal table and DELETE Adjacent duplicates.

Then Get data ZZEXTZAU from VBUP based on Internal table ZAUTH_ITAB and MODIFY the internal table ZAUTH_ITAB for field ZZEXTZAU where VBELN = ZAUTH_ITAB-VBELN.

Similarly get data for AUART from VBKD based on internal table ZAUTH_ITAB and modify the internal table ZAUTH_ITAB for field AUART where VBELN = ZAUTH_ITAB-VBELN.

This would definitely reduce your accessibility time.

Hope this reduces the time taken helps you.

if yes reward suitably.

Thanks

Venugopal

Re: SQL performance tuning - select code is taking so long to execute

Former Member — Mon, 02 Jun 2008 09:36:55 GMT

Thanks!