Well, I’m a self taught ABAP’er, so this might be obvious to many of you out there. But today, I found out just how painful an inner join statement can be. I was doing some code to extract deliveries for a group of sales order. So, I put together a select statement using inner join of LIKP and LIPS. You would think this is nothing too major. When, I executed it, and it ran for a couple minutes (this was entering about 300 sales order line items). The total volume in both LIKP and LIPS was under 10,000 records. So, obviously, this seemed poor, to say the least.
So, I tried the first trick of adding an index to LIPS for the VBELV & POSNV fields I was using to link the sales order. No change.
Then, I decided to break up the statement into separate reads (putting the values into internal tables). Suddenly, the time from 143 seconds, down to about 2 seconds. Incredible.
Now, to my programmer friends, is this a case that the inner join can’t take advantage of an index? or are inner joins really that costly in ABAP. Regardless, I have a new initiative in my code, to start checking every inner join, and replace it with multiple reads of a single table. If this performance is at all indicative, well, then I can make some big improvements in my code 🙂
Thanks for reading,
Mike
Mike, can you post the code you wrote? It might help to understand why that inner join wouldn’t perform well even with the index. Also, sometimes the index is created in SAP but not ‘Active’ in the database itself; you have to make sure that’s done (not sure how you activate it at the database level).
I deleted the exact code… but here’s approximately what it was:
select b~auart a~vbeln a~posnr from vbap as a
inner join vbak as b on a~vbeln = b~vbeln
into corresponding fields of lt_output
for all entries in lt_table
where a~vbeln = lt_table-kdauf and
a~posnr = lt_table-kdpos.
or something like that.