Can anyone think of how to improve this join?

Development (ABAP Development WorkBench, ABAP/4 programming)

Moderators: Snowy, thx4allthefish, YuriT, Gothmog

Post Reply
NAMTAR
Posts: 98
Joined: Wed Dec 18, 2002 9:58 am
Location: Closing in on Walldorf

Can anyone think of how to improve this join?

Post by NAMTAR » Wed Jul 09, 2008 8:57 am

Hi,

This select statement is taking ages to run:

Code: Select all

   SELECT vbap~vbeln vbap~spart vbap~prctr vbap~kwmeng vbap~vrkme   
     INTO TABLE i_vbap                                              
     FROM vbap                                                      
    INNER JOIN vbak                                                 
       ON vbap~vbeln EQ vbak~vbeln                                  
    WHERE vbak~auart IN s_stype   
      AND vbak~spart IN s_spart                                   
      AND vbap~erdat IN s_date                                      
      AND vbap~werks IN s_werks 
      AND vbap~prctr IN s_prctr                                     
      AND vbap~ernam IN s_user                                      
      AND vbap~abgru EQ space.     


I've improved it as much as I can but Ican't think of what else to do...
Does anyon here have any helpful advice?

Thanks in advance!

wilhitern1
Posts: 135
Joined: Thu May 15, 2008 4:25 pm
Location: Butterball LLC., Goldsboro, NC, USA
Contact:

Re: Can anyone think of how to improve this join?

Post by wilhitern1 » Wed Jul 09, 2008 9:02 am

Have you thought of adding an index?
Neal Wilhite (WilhiteRN1)Image

NAMTAR
Posts: 98
Joined: Wed Dec 18, 2002 9:58 am
Location: Closing in on Walldorf

Re: Can anyone think of how to improve this join?

Post by NAMTAR » Wed Jul 09, 2008 9:24 am

Hi,

Thanks for the reply:
VBAK and VBAP already have plenty of indexes on them and I don't really want to add another...

Any other ideas?

SHS
Posts: 483
Joined: Mon Mar 06, 2006 3:57 am

Re: Can anyone think of how to improve this join?

Post by SHS » Wed Jul 09, 2008 9:34 am

Which index are you using with this join? I mean, are you sure an index is being used?
Otherwise you might have to change the order of the fields in the WHERE-clause.
You also might want to consider leaving out WHERE-statements on non-index fields and deleting any unwanted lines from your internal table after the SELECT has finished. Usually a lot faster when selecting a lot of data.

NAMTAR
Posts: 98
Joined: Wed Dec 18, 2002 9:58 am
Location: Closing in on Walldorf

Re: Can anyone think of how to improve this join?

Post by NAMTAR » Wed Jul 09, 2008 9:47 am

Hi everyone,

I've just ahd an idea if i swap the join around then that should speed things up shouldn't it?

Code: Select all

   SELECT vbap~vbeln vbap~spart vbap~prctr vbap~kwmeng vbap~vrkme   
     INTO TABLE i_vbap                                              
     FROM vbak                                                      
    INNER JOIN vbap                                                 
       ON vbak~vbeln EQ vbap~vbeln                                  
    WHERE vbak~auart IN s_stype   
      AND vbak~spart IN s_spart                                   
      AND vbap~erdat IN s_date                                      
      AND vbap~werks IN s_werks 
      AND vbap~prctr IN s_prctr                                     
      AND vbap~ernam IN s_user                                      
      AND vbap~abgru EQ space.     
I think that'll be better - any objections?

Thanks in advance!

Dr Sidewalk
Posts: 3531
Joined: Mon Oct 21, 2002 11:06 am
Location: Everywhere...

Re: Can anyone think of how to improve this join?

Post by Dr Sidewalk » Thu Jul 10, 2008 12:45 pm

There's a bit of a science to deciding which is the driving table. Normally in a header/item situation you would expect the header to be the driving table, however, in this SQL you have neither of the tables primary keys and therefore unless you have a secondary index created you will probably be performing a full-table scan of either the header or item table. Needless to say it's better to full-table scan against a header table than item. :]

It's very uncommon not to have any secondary indicies against either tables VBAK or VBAP, or both. We have 5 against VBAK (2 SAP and 3 custom) and 1 against VBAP, for example. If you have any of these secondary indicies it would be helpful to know what fields they are against. Without knowing the details we can only make a best guess at the most effecient way of creating your SQL statement.

Assuming that you have the two standard SAP secondary indicies (AUD & ERD) against table VBAK then we might be able to use these.

Can s_date refer to the header date rather than item date?. If so we could use this format for the SQL, maybe:

Code: Select all

   SELECT vbap~vbeln vbap~spart vbap~prctr vbap~kwmeng vbap~vrkme   
     INTO TABLE i_vbap                                             
     FROM vbak                                                     
    INNER JOIN vbap ON vbap~vbeln EQ vbak~vbeln                                 
    WHERE vbak~erdat IN s_date                 "Secondary index ERD 
      AND vbak~auart IN s_stype   
      AND vbak~spart IN s_spart                                    
      AND vbap~werks IN s_werks
      AND vbap~prctr IN s_prctr                                     
      AND vbap~ernam IN s_user                                     
      AND vbap~abgru EQ space.
Worth a try I guess. This particular query depends a great deal on how many sales docs you create per day. If it's millions, then it's back to the drawing board I'm afraid. On average it's probably going to be just a few hundred, so it should be reasonably quick. Check the code produced using ST05 and look at the explain path to ensure it's using the ERD index for the VBAK access.


good luck

Dr Sidewalk
"In the middle of difficulty lies opportunity" - Albert Einstein
"Money isn't everything in life, unless you don't have it"
"Fail to plan, plan to fail"
"Success is a journey, not a destination."

baanbrecher
Posts: 105
Joined: Wed May 21, 2003 10:17 am

Re: Can anyone think of how to improve this join?

Post by baanbrecher » Fri Jul 11, 2008 6:47 am

NAMTAR wrote:Hi,

Code: Select all

   SELECT vbap~vbeln vbap~spart vbap~prctr vbap~kwmeng vbap~vrkme   
     INTO TABLE i_vbap                                              
     FROM vbap                                                      
    INNER JOIN vbak                                                 
       ON vbap~vbeln EQ vbak~vbeln                                  
    WHERE vbak~auart IN s_stype   
      AND vbak~spart IN s_spart                                   
      AND vbap~vbeln IN i_vbeln  "emtpy
      AND vbap~erdat IN s_date                                      
      AND vbap~werks IN s_werks 
      AND vbap~prctr IN s_prctr                                     
      AND vbap~ernam IN s_user                                      
      AND vbap~abgru EQ space.     
You might try adding
Select-options:
i_vbeln FOR wa_vbap-vbeln No-Display.

By adding all key fields to the selection like this, i've had some successes in improving performance. (old story no correct index found if not all key/index-fields supplied)
Besides that an index on vbak-auart seems helpful to me. (auart is more likely to be used often than a combo of auart & spart).

wilhitern1
Posts: 135
Joined: Thu May 15, 2008 4:25 pm
Location: Butterball LLC., Goldsboro, NC, USA
Contact:

Re: Can anyone think of how to improve this join?

Post by wilhitern1 » Fri Jul 11, 2008 7:03 am

NAMTAR,

Are you satisfied with what has been proposed here or should we go on making suggestions? I have further thoughts, but they are further onto the strange side, so I hesitate to give them unless you still want to look. - Neal
Neal Wilhite (WilhiteRN1)Image

Post Reply