Hi All,
I know that many people are probably tired of seeing discussions on Inner Join VS for all entries but i'm having performance problems and I was hoping someone might be able to point me in the right direction in terms of what's wrong with my system.
I have a report that is running slowly and I've been doing some basic optimizations, nothing fancy. I came accross two select statements:
SELECT DISTINCT
vbfa~vbeln AS belnr
vbak~kunnr
bkpf~budat
vbap~ps_psp_pnr
INTO CORRESPONDING FIELDS OF TABLE lt_hb_info2
FROM vbap
JOIN vbfa
ON vbfa~vbelv EQ vbap~vbeln
JOIN vbak
ON vbap~vbeln EQ vbak~vbeln
JOIN bkpf
ON bkpf~belnr EQ vbfa~vbeln
FOR ALL entries IN lt_hb_amounts
WHERE vbfa~vbeln EQ lt_hb_amounts-belnr
AND bkpf~budat LE s_date.
SELECT DISTINCT
proj~pspid
proj~post1
prps~pspnr
INTO CORRESPONDING FIELDS OF TABLE t_proj
FROM prps
JOIN proj
ON prps~psphi EQ proj~pspnr
FOR ALL entries IN lt_hb_info2
WHERE prps~pspnr EQ lt_hb_info2-ps_psp_pnr.
which goes on to loop them together. Since we're pulling more data than we need I was resonably certain that combining these two statements with an inner join would be faster given that all of the relevant fields here are index fields. (we've added vbap~ps_psp_pnr)
I've changed these two statements to the following:
SELECT
PROJ~PSPID
PROJ~POST1
VBFA~VBELN AS BELNR
VBAK~KUNNR
BKPF~BUDAT
INTO CORRESPONDING FIELDS OF TABLE LT_HB_INFO
FROM VBAP
JOIN PRPS
ON VBAP~PS_PSP_PNR EQ PRPS~PSPNR
JOIN PROJ
ON PRPS~PSPHI EQ PROJ~PSPNR
JOIN VBFA
ON VBFA~VBELV EQ VBAP~VBELN
JOIN VBAK
ON VBAP~VBELN EQ VBAK~VBELN
JOIN BKPF
ON BKPF~BELNR EQ VBFA~VBELN
FOR ALL ENTRIES IN LT_HB_AMOUNTS
WHERE VBFA~VBELN EQ LT_HB_AMOUNTS-BELNR
* AND PROJ~VBUKR EQ P_BUKRS
AND (LV_COND).
Now I wasn't expecting any major improvements but not only did it make it worst, it made the run time go from 4 seconds with the loop to over 32 seconds for the new statement.
I'm trying to understand how it's possible that the database system is so much worst at performing this task than the application server. I've read http://scn.sap.com/thread/1174072 which seemed like a very well informed post and confirmed what I knew about databases. But even if you don't agree with that post what could be causing such a dramatic difference in run time?
I've run my tests multiple times an of course the difference is more stark when I wait a day between tests but regardless of circumstances the difference is always very very large.
Does anyone have any insight they could share?
Bluesky
