topic Re: Optimize SQL ? in Application Development and Automation Discussions

Optimize SQL ?

Former Member — Fri, 19 Oct 2007 16:37:41 GMT

Hi, Expert:

The following sql statement take long time to run. how to optimize it.

SELECT a_{mandt a}vbeln a_{vkgrp a}vkbur a~guebg

a_{gueen a}ktext a_{auart b}bzirk b~kdgrp

INTO TABLE pi_record

FROM vbak AS a

INNER JOIN vbkd AS b

ON a_{mandt EQ b}mandt

AND a_{vbeln EQ b}vbeln

WHERE b~posnr EQ c_posnr

AND a~auart IN ls_contract.

Thanks,

Re: Optimize SQL ?

Former Member — Fri, 19 Oct 2007 16:41:19 GMT

use <b>FOR ALL ENTRIES</b> to optimize it...

Here's an example:

SELECT matnr mtart

FROM mara

INTO TABLE t_mara

WHERE matnr IN s_matnr.

IF NOT t_mara[] IS INITIAL.

SELECT matnr maktx

FROM makt

INTO TABLE t_makt

FOR ALL ENTRIES IN t_mara

WHERE matnr EQ t_mara-matnr.

ENDIF.

Thanks & Regards

ilesh 24x7

Re: Optimize SQL ?

Former Member — Fri, 19 Oct 2007 16:50:53 GMT

As you are not passing any primary keys in the SELECT, it is taking long time.

First get all Sales orders from VBAK using condition AUART IN LS_CONTRACT.

Then using this data, fetch data from VBKD.

This will help to optimize the performance. For more analysis, do the SQL trace and check if it helps.

ashish

Re: Optimize SQL ?

former_member195698 — Fri, 19 Oct 2007 18:14:11 GMT

Check removing the Join on the table, if it doesn't help then do an SQL trace for the Query. Try Creating an Index on the tables based on the query if it doesn't exist. Creating an index will improve the performance of the query.

Will you be fetching the Documents for all the Sales Org? If not, then introduce Sales Org also in the where condition.

Regards,

Abhishek

Re: Optimize SQL ?

Former Member — Sun, 21 Oct 2007 09:47:08 GMT

You can use transaction ST05 for obtain perfomances sentences in SQL, create index in the tables ,etc . Watch this blog /people/siegfried.boes/blog/2007/09/05/the-sql-trace-st05-150-quick-and-easy

Try not to use join sentences. Access the database the minimum. Ask for the minimum data

Re: Optimize SQL ?

Former Member — Sun, 21 Oct 2007 15:47:14 GMT

So long as you don't use a key field in the WHERE, it will be slow.

Rob

Re: Optimize SQL ?

Former Member — Mon, 22 Oct 2007 02:43:04 GMT

Hi Yunfa,

Just wondering why is posnr a constant? C_POSNR. Is that correct?

The join is simple, For All Entries will not benefit. No key fields in your WHERE clause so whether you Select each table separately, or join as you have, it will be slow.

Only thing to suggest is a secondary index on AUART in VBAK. It will greatly improve your performance but you may need to justify the creation of an index.

You can run ST05 performance trace to see if any indexes are being used.

Hope this helps.

Filler