topic Re: Performance issues with inner joins... in Application Development and Automation Discussions

Performance issues with inner joins...

Former Member — Mon, 24 Nov 2008 18:01:40 GMT

Hi,

I have a query which needs to be performance tuned... Its taking long to execute in background. Please see if we can tune this query by any chance..

select vbak_{kunnr vbak}vbeln vbap_{posnr vbak}vdatu vbak~bstnk

vbak_{netwr kna1}stras kna1_{ort01 kna1}regio kna1~pstlz

vbak_{vkbur vbap}matnr vbap_{arktx vbap}kwmeng

vbap_{netpr vbap}netwr

into table i_order

from vbak

inner join vbpa on vbpa_{vbeln eq vbak}vbeln

inner join kna1 on kna1_{kunnr eq vbpa}kunnr

inner join vbap on vbap_{vbeln eq vbak}vbeln

inner join vbup on vbup_{vbeln eq vbap}vbeln

and vbup_{posnr eq vbap}posnr

inner join vbep on vbep_{vbeln eq vbup}vbeln

and vbep_{posnr eq vbup}posnr

where vbak~auart in r_auart

and vbak~kunnr in r_kunnr

and vbak~vkbur in r_vkbur

and vbpa~parvw eq 'WE'

and vbap~kwmeng gt 0

and vbap~abgru eq space

and vbep~bmeng gt 0

and vbup~lfsta ne c_completely_processed.

Thanks,

Vinod.

Re: Performance issues with inner joins...

ThomasZloch — Mon, 24 Nov 2008 21:33:13 GMT

> and vbak~kunnr in r_kunnr

I see a slight chance here. Should r_kunnr typically hold a very narrow range of ship-to's, then you could try adding table VAKPA to the join and hope that the optimizer does the right thing...

Thomas

Re: Performance issues with inner joins...

Former Member — Tue, 25 Nov 2008 06:00:35 GMT

Hi ,

the select can be split into 4 or 5 different select statements:

select distinct vbak_{vbeln vbap}posnr into table itab1 from vbak

inner join vbap on vbap_{vbeln = vbak}vbeln

inner join vbup on vbup_{vbeln = vbak}vbeln

and vbup_{posnr = vbap}posnr

where

vbak~auart in r_auart

and vbak~kunnr in r_kunnr

and vbak~vkbur in r_vkbur

and vbap~kwmeng gt 0

and vbap~abgru eq space

and vbup~lfsta ne c_completely_processed.

if not itab1 is initial.

select distinct vbak_{vbeln vbap}posnr into table itab2 from vbak

inner join vbap on vbap_{vbeln = vbak}vbeln

inner join vbep on vbep_{vbeln = vbak}vbeln

and vbep_{posnr = vbap}posnr

for all entries of tABLE itab1

where

vbak~vbeln = itab1-vbeln

and vbap~posnr = itab1-posnr

and and vbep~bmeng gt 0

endif.

if not itab2 is initial.

select vbak_{kunnr vbak}vbeln vbap_{posnr vbak}vdatu vbak~bstnk

vbak_{netwr kna1}stras kna1_{ort01 kna1}regio kna1~pstlz

vbak_{vkbur vbap}matnr vbap_{arktx vbap}kwmeng

vbap_{netpr vbap}netwr

into table i_order

from vbak

inner join vbpa on vbpa_{vbeln eq vbak}vbeln

inner join kna1 on kna1_{kunnr eq vbpa}kunnr

inner join vbap on vbap_{vbeln eq vbak}vbeln

for all entries of table itab2

where

vbak~vbeln = itab2-vbeln

and vbap~posnr = itab2-posnr

and and vbpa~parvw eq 'WE'.

endif.

Re: Performance issues with inner joins...

Former Member — Tue, 25 Nov 2008 14:12:28 GMT

Hi Srinivas,

what you say is:

3 SQL statements are faster than 1.

Sounds a bit strange , because

IF the joined tables contain data you have to go through all your statements.

If the driving tables contain a lot of rows the database has to do redundant work

and the FAE table becomes quite large and problematic

IF the joined tables contain no data you skip your SQL while the database also

don't read data that not exists

bye

Re: Performance issues with inner joins...

Former Member — Tue, 25 Nov 2008 14:13:54 GMT

Hi,

try to limit the data as Thomas implied
check that all the joined fields are supported by an index
make sure table statsitics are current to have a good execution plan (see in ST05)

Bye

Re: Performance issues with inner joins...

Former Member — Wed, 26 Nov 2008 04:46:06 GMT

Hi YukonKid ,

I think joining 6 tables in one select statement is not advisible. so you can split that join into 3 where the records fetched get restricted with each select statement. out of a set you would be picking up the desired ones.

I would say the code I provided might not give you the correct result at first. but playing around with the statements will defnitely fetch you good results.

Re: Performance issues with inner joins...

Former Member — Wed, 26 Nov 2008 07:29:39 GMT

Hi,

Use for all entries than joins. you will see better performance

Regards

Rasheed

Re: Performance issues with inner joins...

JozsefSzikszai — Wed, 26 Nov 2008 07:37:55 GMT


> Hi,
> Use for all entries than joins. you will see better performance
> 
> Regards
> Rasheed

this was discussed at least a thousand times, there are many sides of the problem, however not your statement was the outcome...

Re: Performance issues with inner joins...

Former Member — Thu, 27 Nov 2008 06:11:50 GMT

My Dear,

Break your joins & use for all entries by selecting two tables one by one.


   for eg: if you got t1,t2,t3, t4.
    select from t1 into it_t1.
    select from t2 into it_t2 for all entries in it_t1.
   select from t3 into it_t3 for all entries in it_t2.
   select from t4 into it_t4 for all entries in it_t3
 
now it_t4 contains your final req data.

Thanks & Regards,

Krishna....

Re: Performance issues with inner joins...

ThomasZloch — Thu, 27 Nov 2008 08:35:50 GMT

> Break your joins & use for all entries by selecting two tables one by one.

This level of ignorance is certainly stunning.

Re: Performance issues with inner joins...

Former Member — Thu, 27 Nov 2008 14:53:45 GMT


> This level of ignorance is certainly stunning.

On the other hand, this is the internet - you get what you pay for.

Rob

Re: Performance issues with inner joins...

Former Member — Thu, 27 Nov 2008 16:01:19 GMT

Hi Rob,

exactly - I will get payed when I tune up those source lines left by an FAE "expert"

bye

Re: Performance issues with inner joins...

Former Member — Thu, 27 Nov 2008 16:10:29 GMT

Hi,

I saw statements with many more joined tables.

You know, some data models can be very complex (i.e OLTP systems try to reduce redundancy by pushing Normalization to the extreme).

It can be discussed if this kind of avoiding redundancy will cost performance at the end.

But, normallly in OLTP you have indexed lookups of small sets of data, so complexity (should) not be an issue. Redundancy is, because you have to mess around with unsynchronized master data sources.

In data warehouse environments this kind of modelling will kill you. You would build your tables USING a kind of redundancy to keep the complexity small.

At the end it's the question if you abuse a OLTP data model on data warehouse volumes.

It's not the number of joins but the data model you throw it on.

bye

Re: Performance issues with inner joins...

Former Member — Wed, 03 Dec 2008 07:10:48 GMT

Hi,

If you want to see the performance issue in your prog, better to use for all entries.



   select x y z 
      from mara
      into it_mara
      for all entries in it_table
      where x = it_table-x.

Thanks & Regards,

Krishna..

Re: Performance issues with inner joins...

ThomasZloch — Wed, 03 Dec 2008 08:52:36 GMT

I don't know what bugs me more:

- OP never comes back to comment on the replies to his question

- repetitive "use FAE" answers by people who obviously don't spend a minute to read and understand the discussion so far

any votes?

Re: Performance issues with inner joins...

Former Member — Wed, 03 Dec 2008 13:15:36 GMT


> I don't know what bugs me more:
> 
> - OP never comes back to comment on the replies to his question
> - repetitive "use FAE" answers by people who obviously don't spend a minute to read and understand the discussion so far
> 
> any votes?

How about both? Do we have a majority on that one?

Re: Performance issues with inner joins...

Former Member — Wed, 03 Dec 2008 13:55:13 GMT


> I don't know what bugs me more:
> 
> - OP never comes back to comment on the replies to his question
> - repetitive "use FAE" answers by people who obviously don't spend a minute to read and understand the discussion so far
> 
> any votes?

FAE is well understood, so OP not getting back gets my vote (along with duplicate/cross posts).

Rob