06-22-2007 10:22 AM
Hi Experts,
In this following shown report Data base performance is consuming , can you please help me in this issue where can i increase my performance. Please help me urgent.
Report need performance
*********************************
----
Program Name : ZSD_QUOTE *
Functional Analyst : TOBY *
Programmer : Vijay Joseph *
Start date : 03/14/2007 (MM/DD/YYYY) *
Initial CTS : DEVK913353 *
Description : This program will generate the Quote detls *
----
Includes : None *
Function Modules : None *
Logical database : None *
Transaction Code : ZQUOTE *
External references : None *
----
Modification Log *
----
Date | Modified by | CTS number | Comments *
*----
*
03/14/2007|Vijay Joseph | DEVK913353 |Initial Development *
*----
*
REPORT ZSD_QUOTE
line-size 252
line-count 40(0)
no standard page heading . .
*Tables
TABLES : VBAK,
EQUI,
EKKO.
*TYPES
TYPES : BEGIN OF T_VBAP,
VBELN LIKE VBAK-VBELN,
ERDAT LIKE VBAK-ERDAT,
BNDDT LIKE VBAK-BNDDT,
NETWR LIKE VBAK-NETWR,
VKBUR LIKE VBAK-VKBUR,
BSTNK LIKE VBAK-BSTNK,
KUNNR LIKE VBAK-KUNNR,
POSNR LIKE VBAP-POSNR,
MATNR LIKE VBAP-MATNR,
PSTYV LIKE VBAP-PSTYV,
KWMENG LIKE VBAP-KWMENG,
VGBEL LIKE VBAP-VGBEL,
VGPOS LIKE VBAP-VGPOS,
WERKS LIKE VBAP-WERKS,
END OF T_VBAP.
*Types for the likp and lips
TYPES : BEGIN OF T_LIPS,
VBELN LIKE LIKP-VBELN,
LFDAT LIKE LIKP-LFDAT,
POSNR LIKE LIPS-POSNR,
PSTYV LIKE LIPS-PSTYV,
MATNR LIKE LIPS-MATNR,
WERKS LIKE LIPS-WERKS,
VGBEL LIKE LIPS-VGBEL,
VGPOS LIKE LIPS-VGPOS,
END OF T_LIPS.
*Types for the EQUI
TYPES : BEGIN OF T_EQUI,
EQUNR LIKE EQUI-EQUNR,
SERNR LIKE EQUI-SERNR,
KDAUF LIKE EQBS-KDAUF,
KDPOS LIKE EQBS-KDPOS,
END OF T_EQUI.
*Types for the KNA1
TYPES : BEGIN OF T_KNA1,
KUNNR LIKE KNA1-KUNNR,
NAME1 LIKE KNA1-NAME1,
END OF T_KNA1.
*Types for the MAKT
TYPES : BEGIN OF T_MAKT,
MATNR LIKE MAKT-MATNR,
MAKTX LIKE MAKT-MAKTX,
SPRAS LIKE MAKT-SPRAS,
END OF T_MAKT.
*types for VBFA
TYPES : BEGIN OF T_VBFA,
VBELV LIKE VBFA-VBELV,
POSNV LIKE VBFA-POSNV,
VBELN LIKE VBFA-VBELN,
POSNN LIKE VBFA-POSNN,
VBTYP_N LIKE VBFA-VBTYP_N,
END OF T_VBFA.
*types for the output
TYPES : BEGIN OF T_OUTPUT,
VBELV LIKE VBFA-VBELV,
ERDAT LIKE VBAK-ERDAT,
BNDDT LIKE VBAK-BNDDT,
NETWR(15) type C, " LIKE VBAK-NETWR,
VBELN LIKE VBAK-VBELN,
BSTNK LIKE VBAK-BSTNK,
KUNNR LIKE VBAK-KUNNR,
KWMENG(15) TYPE C, " LIKE VBAP-KWMENG,
NAME1 LIKE KNA1-NAME1,
VKBUR LIKE VBAK-VKBUR,
MATNR LIKE MAKT-MATNR,
MAKTX LIKE MAKT-MAKTX,
LFDAT LIKE LIKP-LFDAT,
SERNR LIKE EQUI-SERNR,
END OF T_OUTPUT.
*Types for the VBUP
TYPES : BEGIN OF T_VBUP,
vbeln LIKE VBUP-VBELN,
posnr LIKE VBUP-POSNR,
lfsta LIKE VBUP-LFSTA,
END OF T_VBUP.
*Internal Table
DATA : GIT_VBAP TYPE STANDARD TABLE OF T_VBAP,
GIT_LIPS TYPE STANDARD TABLE OF T_LIPS,
GIT_EQUI TYPE STANDARD TABLE OF T_EQUI,
GIT_KNA1 TYPE STANDARD TABLE OF T_KNA1,
GIT_MAKT TYPE STANDARD TABLE OF T_MAKT,
GIT_OUTPUT TYPE STANDARD TABLE OF T_OUTPUT,
GIT_VBUP TYPE STANDARD TABLE OF T_VBUP,
GIT_VBFA TYPE STANDARD TABLE OF T_VBFA.
*work Area
DATA : GWA_VBAP TYPE T_VBAP,
GWA_LIPS TYPE T_LIPS,
GWA_EQUI TYPE T_EQUI,
GWA_KNA1 TYPE T_KNA1,
GWA_MAKT TYPE T_MAKT,
GWA_OUTPUT TYPE T_OUTPUT,
GWA_VBUP TYPE T_VBUP,
GWA_VBFA TYPE T_VBFA.
*selection screen.
SELECTION-SCREEN : BEGIN OF BLOCK ZBLOCK WITH FRAME TITLE TEXT-015.
Select-options : S_VBELN FOR VBAK-VBELN,
S_ERDAT FOR VBAK-ERDAT, " OBLIGATORY,
S_EBELN FOR EKKO-EBELN MATCHCODE OBJECT MEKK,
S_SERNR FOR EQUI-SERNR MATCHCODE OBJECT EQSN.
PARAMETERS : P_WERKS LIKE VBAP-WERKS OBLIGATORY.
SELECTION-SCREEN : END OF BLOCK ZBLOCK.
**************top of page*********************************************
TOP-OF-PAGE.
PERFORM SAPSD_TOP_OF_PAGE.
**************At selection screen*************************************
at selection-screen.
*for validating the Sales Order
PERFORM SAPSD_SCREEN_VALIDATION_VBELN.
*for validating the plant
PERFORM SAPSD_SCREEN_VALIDATION_WERKS.
*for the validating the PO number
PERFORM SAPSD_SCREEN_VALIDATION_PO.
*for the validating the serial number
PERFORM SAPSD_SCREEN_VALIDATION_SERIAL.
***************strart of selection************************************
START-OF-SELECTION.
*Get the data
PERFORM SAPSD_FETCH_DATA.
*For the final output table
PERFORM SAPSD_OUTPUT.
&----
*& Form SAPSD_FETCH_DATA
&----
text
----
--> p1 text
<-- p2 text
----
FORM SAPSD_FETCH_DATA .
*FETCH FROM THE VBAK AND VBAP.
SELECT VBAK~VBELN
VBAK~ERDAT
VBAK~BNDDT
VBAK~NETWR
VBAK~VKBUR
VBAK~BSTNK
VBAK~KUNNR
VBAP~POSNR
VBAP~MATNR
VBAP~PSTYV
VBAP~KWMENG
VBAP~VGBEL
VBAP~VGPOS
VBAP~WERKS
FROM VBAK INNER JOIN VBAP
ON VBAKVBELN EQ VBAPVBELN
INTO TABLE GIT_VBAP
WHERE VBAK~VBELN IN S_VBELN
AND VBAK~ERDAT IN S_ERDAT
AND VBAK~BSTNK IN S_EBELN
AND VBAP~PSTYV EQ 'IRRA'
AND VBAP~WERKS EQ P_WERKS.
IF SY-SUBRC EQ 0.
SORT GIT_VBAP BY VBELN.
else.
message e022(z1).
ENDIF.
*from vbfa
select VBELV
POSNV
VBELN
POSNN
VBTYP_N
into table git_vbfa
from vbfa
for all entries in git_vbap
where vbelv eq git_vbap-vbeln
and posnv eq git_vbap-posnr.
*FETCH DATA FROM THE LIKP AND LIPS
IF NOT GIT_VBAP IS INITIAL.
SELECT LIKP~VBELN
LIKP~LFDAT
LIPS~POSNR
LIPS~PSTYV
LIPS~MATNR
LIPS~WERKS
LIPS~VGBEL
LIPS~VGPOS
FROM LIKP INNER JOIN LIPS
ON LIKPVBELN EQ LIPSVBELN
INTO TABLE GIT_LIPS
FOR ALL ENTRIES IN GIT_VBFA
WHERE LIPS~VBELN EQ GIT_VBFA-VBELN
and LIPS~POSNR EQ GIT_VBFA-POSNN.
AND LIPS~WERKS EQ GIT_VBAP-WERKS.
AND LIPS~MATNR EQ GIT_VBAP-MATNR.
AND LIPS~POSNR EQ GIT_VBAP-POSNR.
AND LIPS~PSTYV EQ 'IRRA'.
AND LIPS~VGPOS EQ GIT_VBAP-POSNR.
IF SY-SUBRC EQ 0.
SORT GIT_LIPS BY VBELN.
ENDIF.
ENDIF.
*for getting the delivery status(dont take the delivered document number
*take only 'open'.
if not git_lips is initial.
select VBELN
posnr
lfsta
from vbup
into table git_vbup
for all entries in git_lips
where vbeln eq git_lips-vbeln
and posnr eq git_lips-posnr.
and ( lfsta EQ 'A' ) OR
( lfsta EQ 'B' ) .
if sy-subrc eq 0.
sort git_vbup by vbeln.
endif.
endif.
*To get the equipment number
IF NOT GIT_VBAP IS INITIAL.
SELECT EQUI~EQUNR
EQUI~SERNR
EQBS~KDAUF
EQBS~KDPOS
FROM EQUI INNER JOIN EQBS
ON EQUIEQUNR EQ EQBSEQUNR
INTO TABLE GIT_EQUI
FOR ALL ENTRIES IN GIT_VBAP
WHERE EQUI~SERNR IN S_SERNR
AND EQBS~KDAUF EQ GIT_VBAP-VBELN.
IF SY-SUBRC EQ 0.
SORT GIT_EQUI BY EQUNR.
ENDIF.
ENDIF.
*To get the customer name
IF NOT GIT_VBAP IS INITIAL.
SELECT KUNNR
NAME1
INTO TABLE GIT_KNA1
FROM KNA1
FOR ALL ENTRIES IN GIT_VBAP
WHERE KUNNR EQ GIT_VBAP-KUNNR.
IF SY-SUBRC EQ 0.
SORT GIT_KNA1 BY KUNNR.
ENDIF.
ENDIF.
*to get the material number
if not git_vbap is initial.
SELECT MATNR
MAKTX
SPRAS
INTO TABLE GIT_MAKT
FROM MAKT
FOR ALL ENTRIES IN GIT_VBAP
WHERE MATNR EQ GIT_VBAP-MATNR
AND SPRAS EQ SY-LANGU.
IF SY-SUBRC EQ 0.
SORT GIT_MAKT BY MATNR.
ENDIF.
endif.
ENDFORM. " SAPSD_FETCH_DATA
&----
*& Form SAPSD_OUTPUT
&----
text
----
--> p1 text
<-- p2 text
----
FORM SAPSD_OUTPUT .
data : l_vbelv like vbfa-vbelv.
LOOP AT GIT_VBAP INTO GWA_VBAP.
*for getting the delivey date
clear : gwa_lips.
read table git_vbfa into gwa_vbfa with key vbelv = gwa_vbap-vbeln
posnv = gwa_vbap-posnr.
if sy-subrc eq 0.
read table git_lips into gwa_lips
with key VBELN = GWA_vbfa-Vbeln
POSNR = GWA_vbfa-posnn
PSTYV = 'IRRA'.
IF SY-SUBRC EQ 0.
GWA_OUTPUT-LFDAT = GWA_LIPS-LFDAT.
READ TABLE GIT_VBUP INTO GWA_VBUP
WITH KEY VBELN = GWA_LIPS-VBELN
POSNR = GWA_LIPS-POSNR.
IF SY-SUBRC EQ 0.
IF GWA_VBUP-LFSTA EQ 'A' OR GWA_VBUP-LFSTA EQ 'B'.
clear : l_vbelv.
*
select single vbelv
into l_vbelv
from vbfa
where VBELN EQ gwa_vbap-vbeln.
*Quote Number
if sy-subrc eq 0.
GWA_OUTPUT-VBELV = L_VBELV.
endif.
*Move the details to the final table
GWA_OUTPUT-VBELN = GWA_VBAP-VBELN.
GWA_OUTPUT-ERDAT = GWA_VBAP-ERDAT.
GWA_OUTPUT-BNDDT = GWA_VBAP-BNDDT.
GWA_OUTPUT-NETWR = GWA_VBAP-NETWR.
GWA_OUTPUT-KUNNR = GWA_VBAP-KUNNR.
GWA_OUTPUT-KWMENG = GWA_VBAP-KWMENG.
GWA_OUTPUT-BSTNK = GWA_VBAP-BSTNK.
for getting the name from kna1
CLEAR : GWA_KNA1.
READ TABLE GIT_KNA1 INTO GWA_KNA1
WITH KEY KUNNR = GWA_VBAP-KUNNR.
IF SY-SUBRC EQ 0.
GWA_OUTPUT-NAME1 = GWA_KNA1-NAME1.
ENDIF.
GWA_OUTPUT-VKBUR = GWA_VBAP-VKBUR.
*for getting mateial number and description
CLEAR : GWA_MAKT.
READ TABLE GIT_MAKT INTO GWA_MAKT
WITH KEY MATNR = GWA_VBAP-MATNR
SPRAS = SY-LANGU.
IF SY-SUBRC EQ 0.
GWA_OUTPUT-MATNR = GWA_MAKT-MATNR.
GWA_OUTPUT-MAKTX = GWA_MAKT-MAKTX.
ENDIF.
for getting the serial number
clear : gwa_equi.
read table git_equi into gwa_equi
with key kdauf = gwa_vbap-vbeln
kdpos = gwa_vbap-posnr.
IF SY-SUBRC EQ 0.
GWA_OUTPUT-SERNR = gwa_equi-sernr.
ENDIF.
append gwa_output to git_output.
ENDIF.
ENDIF.
ENDIF.
CLEAR : GWA_VBAP,
GWA_OUTPUT.
ENDLOOP.
*free and refres the internal table
clear : git_vbap,
git_lips,
git_makt,
git_equi.
refresh : git_vbap,
git_lips,
git_makt,
git_equi.
free: git_vbap,
git_lips,
git_makt,
git_equi.
loop at git_output into gwa_output.
FORMAT COLOR COL_NORMAL INTENSIFIED OFF INVERSE OFF.
WRITE : /1 sy-vline,
2 gwa_output-VBELV, "qte no
13 sy-vline,
14 gwa_output-ERDAT, "cr date
25 sy-vline,
26 gwa_output-BNDDT, "exp date
36 sy-vline,
37 gwa_output-NETWR, "qte value
53 sy-vline,
54 gwa_output-VBELN, "so
65 SY-VLINE,
66 gwa_output-BSTNK, "po
87 SY-VLINE,
88 gwa_output-KUNNR, "customer
99 SY-VLINE,
100 gwa_output-NAME1, "Name
136 sy-vline,
137 gwa_output-VKBUR, "S off
142 sy-vline,
143 gwa_output-MATNR, "Material
162 sy-vline,
163 gwa_output-MAKTX , "Description
204 sy-vline,
205 gwa_output-KWMENG, "Or Qty
221 sy-vline,
222 gwa_output-LFDAT, "Del Date
233 sy-vline,
234 gwa_output-SERNR, "Serial No
252 SY-VLINE.
uline.
clear : gwa_output.
endloop.
*free and refresh the internal table
refresh : git_output.
free : git_output.
ENDFORM. " SAPSD_OUTPUT
&----
*& Form SAPSD_TOP_OF_PAGE
&----
text
----
--> p1 text
<-- p2 text
----
FORM SAPSD_TOP_OF_PAGE .
write: /15 text-016, 30 sy-repid.
FORMAT COLOR COL_HEADING INTENSIFIED ON INVERSE OFF.
ULINE.
WRITE : /1 sy-vline,
2 text-001, "QTE No
13 sy-vline,
14 text-002, "CR Date
25 sy-vline,
26 text-003, "EX Date
36 sy-vline,
37 text-004, "QT Value
53 sy-vline,
54 text-005, "SO
65 SY-VLINE,
66 text-006, "PO
87 SY-VLINE,
88 text-007, "Customer
99 sy-vline,
100 text-008, "Name
136 sy-vline,
137 text-009, "S off
142 sy-vline,
143 text-010, "Material
162 sy-vline,
163 text-011 , "Description
204 sy-vline,
205 text-012, "Or Qty
221 sy-vline,
222 text-013, "Del Date
233 sy-vline,
234 text-014, "Serial No
252 SY-VLINE.
ULINE.
ENDFORM. " SAPSD_TOP_OF_PAG,
&----
*& Form SAPSD_SCREEN_VALIDATION_VBELN
&----
text
----
--> p1 text
<-- p2 text
----
FORM SAPSD_SCREEN_VALIDATION_VBELN .
IF NOT S_VBELN IS INITIAL.
*To check the plant.If entry is wrong the an error message displayed.
DATA : l_VBELN LIKE VBAK-VBELN. "SO
Validating SO in selection screen
SELECT SINGLE VBELN INTO l_VBELN FROM VBAK
WHERE VBELN IN S_VBELN.
IF sy-subrc NE 0.
MESSAGE e023(Z1). " Invalid SO
ENDIF.
endif.
ENDFORM. " SAPSD_SCREEN_VALIDATION_VBELN
&----
*& Form SAPSD_SCREEN_VALIDATION_WERKS
&----
text
----
--> p1 text
<-- p2 text
----
FORM SAPSD_SCREEN_VALIDATION_WERKS .
IF NOT P_WERKS IS INITIAL.
*To check the plant.
*If entry is wrong the an error message displayed.
DATA : l_WERKS LIKE T001W-WERKS. "Plant
Validating Plant in selection screen
SELECT SINGLE WERKS INTO l_WERKS FROM T001W
WHERE WERKS EQ P_WERKS.
IF sy-subrc NE 0.
MESSAGE e024(Z1). " Invalid Plant
ENDIF.
ENDIF.
ENDFORM. " SAPSD_SCREEN_VALIDATION_WERKS
&----
*& Form SAPSD_SCREEN_VALIDATION_PO
&----
text
----
--> p1 text
<-- p2 text
----
FORM SAPSD_SCREEN_VALIDATION_PO .
IF NOT S_EBELN IS INITIAL.
*To check the plant.
*If entry is wrong the an error message displayed.
DATA : l_EBELN LIKE EKKO-EBELN. "PO
Validating PO in selection screen
SELECT SINGLE EBELN INTO l_EBELN FROM EKKO
WHERE EBELN IN S_EBELN.
IF sy-subrc NE 0.
MESSAGE e025(Z1). " Invalid PO
ENDIF.
ENDIF.
ENDFORM. " SAPSD_SCREEN_VALIDATION_PO
&----
*& Form SAPSD_SCREEN_VALIDATION_SERIAL
&----
text
----
--> p1 text
<-- p2 text
----
FORM SAPSD_SCREEN_VALIDATION_SERIAL .
IF NOT S_SERNR IS INITIAL.
*To check the SERIAL NO.
*If entry is wrong the an error message displayed.
DATA : l_SERNR LIKE EQUI-SERNR. "Serial No
Validating Serial NO in selection screen
SELECT SINGLE SERNR INTO l_SERNR FROM EQUI
WHERE SERNR IN S_SERNR.
IF sy-subrc NE 0.
MESSAGE e026(Z1). " Invalid Serial No
ENDIF.
ENDIF.
ENDFORM. " SAPSD_SCREEN_VALIDATION_SERIAL
**************************************************************************
Please help me in this .
Thanks & Regards
Ahammad
06-22-2007 10:44 AM
Hi Shaik,
Please remove all the join select queries and use 'for all entries' varaiant of the select query. Check whether you can create and use indexes in ur queries.
Thanks and Regards,
Saurabh Chhatre
06-22-2007 10:45 AM
Hi Ahammad,
Tell me what are values we should give on the SELECTION SCREEN.
So that I can test,modify to increase performance for your program.OK
Regards,
V.Raghavender.
06-22-2007 3:28 PM
Hi sir,
We have to give plant that is enough.
Please help me.
Thanks
06-22-2007 3:40 PM
Well, the problem is in the first SELECT. I think if you make s_erdat mandatory, that will help. There is an index on vbak-erdat.
Rob