11-06-2007 3:06 PM
hi
i need some help in performence because this select is running time is to much:
Thanks
SELECT vbrk~vbeln vbrk~vbtyp vbrk~fkdat vbrk~netwr vbrk~waerk vbrp~kvgr3 tvv3t~bezei dd07v~ddtext
FROM ( ( ( vbrk
INNER JOIN vbrp ON vbrp~vbeln EQ vbrk~vbeln
AND vbrp~posnr EQ '00010'
AND vbrp~ps_psp_pnr EQ l_project )
LEFT OUTER JOIN
tvv3t ON tvv3t~kvgr3 EQ vbrp~kvgr3
AND tvv3t~spras EQ sy-langu )
LEFT OUTER JOIN
dd07v ON dd07v~domname EQ 'VBTYP'
AND dd07v~ddlanguage EQ sy-langu
AND dd07v~domvalue_l EQ vbrk~vbtyp )
INTO TABLE sdtab
WHERE vbrk~rfbsk EQ 'C'
AND vbrk~fksto EQ ''
AND vbrk~sfakn EQ ''.
11-06-2007 8:07 PM
Hi,
Perhaps you can build custom tables (ZTABLE) which contain the following fields.
MANDT
VBELN
PS_PSP_PNR
Then you can write custom program to populate the above table which run at nightly basis based on current (today) date.
For initial load, you may run on the weekend (perhaps).
Logic can be something like this.
TABLES: VBRK, VBRP.
SELECT-OPTIONS: S_ERDAT FOR VBRK-ERDAT.
DATA: BEGIN OF ITAB OCCUR 0,
MANDT LIKE VBRK-MANDT,
VBELN LIKE VBRK-VBELN,
PS_PSP_PNR LIKE VBRP-PS_PSP_PNR.
DATA: END OF ITAB.
SELECT VBRK~MANDT VBRK~VBELN VBRK~PS_PSP
FROM VBRK
INNER JOIN VBRP ON VBRP~VBELN EQ VBRK~VBELN
AND VBRP~POSNR EQ '00010'
INTO TABLE ITAB
WHERE VBRK~VBELN NE SPACE
AND VBRK~ERDAT IN S_ERDAT
AND VBRK~RFBSK EQ 'C'
AND VBRK~FKSTO EQ ' '
AND VBRK~SFAKN EQ ' '.
IF NOT ITAB[] IS INITIAL.
MODIFY ZTABLE FROM TABLE ITAB.
ENDIF.
...
For daily run, you can create variant and default S_ERDAT to today's date.
Then you can use ZTABLE for your extraction program purposes.
Regards,
Ferry Lianto
11-06-2007 3:27 PM
Well, it's because you aren't using any key fields in the WHERE. I don't think there's much chance of improving it significantly, so try running it in the background.
Rob
11-06-2007 3:36 PM
hi rob
thanks for your replay i thihk about running that in background but for that i have to run it for all projects do u think it's good idea ?
how i can run it for all project from table vbrk?
<b>AND vbrp~ps_psp_pnr EQ l_project</b>
Regards[
11-06-2007 3:56 PM
11-06-2007 8:07 PM
Hi,
Perhaps you can build custom tables (ZTABLE) which contain the following fields.
MANDT
VBELN
PS_PSP_PNR
Then you can write custom program to populate the above table which run at nightly basis based on current (today) date.
For initial load, you may run on the weekend (perhaps).
Logic can be something like this.
TABLES: VBRK, VBRP.
SELECT-OPTIONS: S_ERDAT FOR VBRK-ERDAT.
DATA: BEGIN OF ITAB OCCUR 0,
MANDT LIKE VBRK-MANDT,
VBELN LIKE VBRK-VBELN,
PS_PSP_PNR LIKE VBRP-PS_PSP_PNR.
DATA: END OF ITAB.
SELECT VBRK~MANDT VBRK~VBELN VBRK~PS_PSP
FROM VBRK
INNER JOIN VBRP ON VBRP~VBELN EQ VBRK~VBELN
AND VBRP~POSNR EQ '00010'
INTO TABLE ITAB
WHERE VBRK~VBELN NE SPACE
AND VBRK~ERDAT IN S_ERDAT
AND VBRK~RFBSK EQ 'C'
AND VBRK~FKSTO EQ ' '
AND VBRK~SFAKN EQ ' '.
IF NOT ITAB[] IS INITIAL.
MODIFY ZTABLE FROM TABLE ITAB.
ENDIF.
...
For daily run, you can create variant and default S_ERDAT to today's date.
Then you can use ZTABLE for your extraction program purposes.
Regards,
Ferry Lianto
11-07-2007 9:35 AM