topic Re: Rewrite SQL query to improve performance in Application Development and Automation Discussions

Rewrite SQL query to improve performance

Former Member — Thu, 04 Dec 2008 05:59:59 GMT

Hello,

The below queries are very time consuming.Could you please suggest how to improve performance for these 2 queries:

QUERY1:

SELECT a_{vbeln a}posnr a_{auart a}vkorg a_{vtweg a}spart

a_{vkbur a}kunnr b_{matnr b}kwmeng b~vrkme

b_{netwr b}werks b_{lgort b}vstel b_{abgru b}erdat b~ernam

c_{faksk c}ktext c_{vdatu c}zzbrsch c_{kvgr1 c}augru

INTO CORRESPONDING FIELDS OF TABLE g_t_sodata

FROM vapma AS a INNER JOIN vbap AS b ON

a_{vbeln = b}vbeln AND a_{posnr = b}posnr

INNER JOIN vbak AS c ON a_{vbeln = c}vbeln

WHERE a~vkorg IN so_vkorg

AND a~vtweg IN so_vtweg

AND a~vkbur IN so_vkbur

AND a~auart IN so_auart

AND a~vbeln IN so_vbeln

AND b~abgru IN so_abgru

AND c~faksk IN so_faksk

AND ( b~erdat GT g_f_zenkai_date OR

( b_{erdat EQ g_f_zenkai_date AND b}erzet GE g_f_zenkai_time ) )

AND ( b~erdat LT g_f_kaisi_date OR

( b_{erdat EQ g_f_kaisi_date AND b}erzet LT g_f_kaisi_time ) ).

QUERY2:

SELECT a_{vbeln a}posnr a_{auart a}vkorg a_{vtweg a}spart

a_{vkbur a}kunnr b_{matnr b}kwmeng b~vrkme

b~netwr

b_{werks b}lgort b_{vstel b}abgru b_{erdat b}ernam

c_{faksk c}ktext c_{vdatu c}zzbrsch c_{kvgr1 c}augru

INTO CORRESPONDING FIELDS OF TABLE g_t_sodata

FROM vapma AS a INNER JOIN vbap AS b ON

a_{vbeln = b}vbeln AND a_{posnr = b}posnr

INNER JOIN vbak AS c ON a_{vbeln = c}vbeln

WHERE a~vkorg IN so_vkorg

AND a~vtweg IN so_vtweg

AND a~vkbur IN so_vkbur

AND a~auart IN so_auart

AND a~vbeln IN so_vbeln

AND b~abgru IN so_abgru

AND c~faksk IN so_faksk.

Re: Rewrite SQL query to improve performance

Former Member — Thu, 04 Dec 2008 06:26:07 GMT

HI tips,

Hope these threads will help u on this,

Regard's,

Lokesh N B

Re: Rewrite SQL query to improve performance

Former Member — Thu, 04 Dec 2008 09:06:54 GMT

Questions like this a one of the favorites here in this forum.

I guess that the statements are o.k.

The problem is the usage! There are so many Ranges, if they are filled then some index should support it. If no range is filled then it is usually slow, you can not do anything.

You must find out, which are actually used and under which conditions it is slow and when it is o.k.

Ask again, when you can provide the actual cases.

Use SQL trace to check the performance:

SQL trace:

I know the probabilty is high, that you will ignore this recommendation and you will points to the

'Use FOR ALL ENTRIES' recommendations' ... but then I can not help you.

Siegfried

Re: Rewrite SQL query to improve performance

Former Member — Thu, 04 Dec 2008 09:11:35 GMT

Hi,

really try it out what Siegfried mentioned. Without an appropriate SQL monitoring you only shot in the dark.

It's explained easy
gives you a lot of information back
makes you richer (your boss will rise your salary when you deliver the data in no time)

bye

Re: Rewrite SQL query to improve performance

Former Member — Thu, 04 Dec 2008 09:13:53 GMT

Hi Siegfried,

you can lead a horse to the water, but you can't make him drink ...

bye

Re: Rewrite SQL query to improve performance

Former Member — Thu, 04 Dec 2008 11:04:23 GMT

Hi Siegfried,

I am definitely not ignoring your suggestion .But could you please explain why 'For all Entries' won't be of help in this case?

Re: Rewrite SQL query to improve performance

Former Member — Fri, 05 Dec 2008 18:51:14 GMT

It is not suggestable to use join on more than 2 tables. Try to break the join into 2 select stmts.

Also INTO CORRESPONDING FIELDS, is a performance constraint, unless and until it is a mandatory requrement never use it. try to maintain your internal table in the order of the data retreival (Select stmt), and then move the data.

This might help your performance issues to some extent.

Also make sure while use for all entries, never for get to use if not initial[] stmt on the table that is being used for all entries.

eg:

if not x[] is initail.

select a b c

from y

for all entries in x

where ......

Endif.

Not using the if not initial may cause application break down if the table is initial in any case, so it is always advisable to use the stmt.

Re: Rewrite SQL query to improve performance

Former Member — Mon, 08 Dec 2008 07:43:47 GMT

> It is not suggestable to use join on more than 2 tables.

that is simply nonsense!


WHERE a~vkorg IN so_vkorg
AND a~vtweg IN so_vtweg
AND a~vkbur IN so_vkbur
AND a~auart IN so_auart
AND a~vbeln IN so_vbeln
AND b~abgru IN so_abgru
AND c~faksk IN so_faksk

First you must understand the idea behind ranges.

Your statements will never use all ranges together, that nearly never the case => there are different

combinations, some are well supported by indexes, some probably not.

FOR ALL ENTRIES need also indexes, they can not automatically solve your problem. A well-defined

join is faster than a FAE even for more than 2 tables.

Siegfried

Re: Rewrite SQL query to improve performance

Former Member — Mon, 08 Dec 2008 14:09:22 GMT

Hi Raghava,

sorry - a big NO.

The FAE myth spreads around here for a long time. It can NEVER be a replacement for a join.

Joins will provide a way for the database to know in advance about WHAT data to look for.

If you break it up in several statements it can't provide an efficient execution plan because

you hold back important information (i.e. the reference tables to retrieve data from).

The key with joins is efficient indexing and knowing the data model.

if you can't locate the data in another table you may use FAE - but you must be aware of it's restrictions:

see: SAP Note 48230 - Parameters for the SELECT ... FOR ALL ENTRIES statement

Bye