03-17-2009 4:40 PM
Hello Guruz,
select statement sucess in development is unsuccessful in production.plz advise:(
SELECT vbuk~vbeln
likp~vstel
likp~kunag
likp~wadat
likp~erdat
lips~lgort
lips~posnr
lips~matkl
vbfa~vbeln
vbpa~kunnr
ltak~bdatu
FROM ( vbuk
INNER JOIN likp
ON vbukvbeln = likpvbeln
INNER JOIN lips
ON likpvbeln = lipsvbeln
INNER JOIN vbfa
ON lipsvbeln = vbfavbelv
INNER JOIN vbpa
ON vbfavbelv = vbpavbeln
INNER JOIN ltak
ON ltaktanum = vbfavbeln )
INTO TABLE gt_deliveries
WHERE vbuk~lvstk = 'B'
AND vbuk~vbeln IN s_vbeln
AND likp~vstel IN s_vstel
AND lips~lgort IN s_lgort
AND vbpa~kunnr IN s_kunnr
AND ltak~bdatu IN s_bdatu
AND vbpa~parvw = '1C'
AND vbfa~posnn = '1'
AND vbfa~vbtyp_n = 'Q'.
Edited by: BrightSide on Mar 17, 2009 4:41 PM
03-17-2009 4:44 PM
03-17-2009 4:48 PM
it means sy-subrc ne 0. when it should be '0' for the delivery.
Edited by: BrightSide on Mar 17, 2009 4:48 PM
03-17-2009 6:15 PM
BrightSide,
1) Will it be possible to keep it simple, may be smaller Joins
2) If the Joins are written correctly then there must not be data in system which matched the selecion criteria, Pls try to execute JOIN manually, I mean perorm the same steps in SE16.
I am not sure to what extend it will cause Porformance Issue but its certainly a code complexity issue for developer. Pls go for simplfication if applcation is not Performance critical.
Regards
Shital
03-17-2009 8:19 PM
well, ive tried it manually and its fine , In production this is not getting no data back when it should be and got performance issue aswell...I wonder why when its all fine in development ..why it is so bad in production?
.
Edited by: BrightSide on Mar 17, 2009 8:20 PM
03-17-2009 9:11 PM
Hi,
Production server holds the real time data which are 100 times more than what we have in development system and they are more secured in terms of time ...
so due to this if its taking more of time to retrieve the data from the database it might go for timeout error...
so the best way to code would be splitting up all the five joins into five different select queries and then merging the required data in one single internal table which will increase the performance as well and also will get you the proper data you require....
Hope you get what I meant....
Regards,
Gurpreet
03-17-2009 4:47 PM
SELECT vbuk~vbeln
likp~vstel
likp~kunag
likp~wadat
likp~erdat
lips~lgort
lips~posnr
lips~matkl
vbfa~vbeln
vbpa~kunnr
ltak~bdatu
FROM ( vbuk
INNER JOIN likp
ON vbuk~vbeln = likp~vbeln
INNER JOIN lips
ON likp~vbeln = lips~vbeln
INNER JOIN vbfa
ON lips~vbeln = vbfa~vbelv
INNER JOIN vbpa
ON vbfa~vbelv = vbpa~vbeln
INNER JOIN ltak
ON ltak~tanum = vbfa~vbeln )
INTO TABLE gt_deliveries
WHERE vbuk~lvstk = 'B'
AND vbuk~vbeln IN s_vbeln
AND likp~vstel IN s_vstel
AND lips~lgort IN s_lgort
AND vbpa~kunnr IN s_kunnr
AND ltak~bdatu IN s_bdatu
AND vbpa~parvw = '1C'
AND vbfa~posnn = '1'
AND vbfa~vbtyp_n = 'Q'.
In you select there are five joins which will degrade performance (I.e Timeout and Dump)
1) Try to create secondary index at the database levels.
2) Split it into two of three Select statements and later on merger into internal table.
3) Create Database View for all the tables and fields you require in the select query( As views are Buffered Load on the database would be reduced).
Check matching records are there in Production system.
get some test data from Production system and check the avaiablility.
Regards,
Gurpreet
03-18-2009 2:17 AM
Hi Brightside,
I wonder could it be happen....
( vbuk
INNER JOIN likp
ON vbukvbeln = likpvbeln
this is a piece of your code,,,, I think that Likpvbeln is not equal as Vbukvbeln...!
and when you want to join these both VBUK's and LIKP and VBFA.... with such a huge 5 join conditions, it will be a big problem in production after 2 years, As I am a direct witness of this, and My role and resposibility is now the same, to make performance better,,,
My client has a huge database,, you really dont trust if I say that.... When I enter LIKPVBELN in VBFAVBELV..... In SE11,,,, and I always going to short dump due to request Timed out...
Since data base is 40 Million Records... for each table...
Worst com worst,,, we need to use Some more matching fields for retriving the data...
Here is a Piece of code How to use LIKP, LIPS & VBUK, VBEP, VBPA without help of VBFA....!
Also it contaings Secondary indexes,,,!
SELECT AVBELN APOSNR AVSTEL BBMENG D~LAND1 INTO
(VBELN, POSNR, SPOINTL , LFOI1 , SOCONTL)
FROM VBAP AS A
INNER JOIN VBEP AS B
ON AVBELN = BVBELN
AND APOSNR = BPOSNR
INNER JOIN VBPA AS D
ON AVBELN = DVBELN
INNER JOIN VBUP AS U
ON AVBELN = UVBELN
AND APOSNR = UPOSNR
INNER JOIN VBUP
ON VBUPVBELN = AVBELN AND
VBUPPOSNR = APOSNR
INNER JOIN LIPS
ON LIPSVGBEL = AVBELN AND
LIPSVGPOS = APOSNR
INNER JOIN LIKP
ON LIKPVBELN = LIPSVBELN
WHERE A~MATNR = P_MATNR
AND LIKP~LDDAT = P_ITAB9_DATE
AND D~PARVW = 'WE'
AND ( AVSTEL = 'M9RT' OR AVSTEL = 'M9IT' OR AVSTEL = 'M9FC' OR AVSTEL = 'M9IC' )
AND A~ABGRU = SPACE
AND VBUP~LFSTA <> 'A'.
What I Suggest you is Before transfering to PRD itself try to make a best performance statement.
Hope it should solve some part of your Issue...!
Thanks & Regards,
Dileep .C
03-18-2009 3:20 AM
Hi,
I have given sample query.First two tables you can use joins.Later with joins and for all entries
you can link other tables.
SELECT * FROM MSEG AS A INNER JOIN MKPF AS B ON A~MBLNR EQ B~MBLNR
AND A~MJAHR EQ B~MJAHR INTO CORRESPONDING FIELDS OF TABLE IT_MSEG
FOR ALL ENTRIES IN IT_FINAL WHERE A~MATNR EQ IT_FINAL-matnr and a~ebeln eq it_final-ebeln
AND A~LIFNR EQ IT_FINAL-LIFNR AND A~WERKS EQ IT_FINAL-WERKS
AND A~BWART IN ('542') AND A~SHKZG EQ 'S' AND B~BUDAT IN S_BUDAT.
For performance you can avoid *.Please check performance of this query also.
Regards,
Bathri..
03-18-2009 11:28 AM
>>>> it means sy-subrc ne 0. when it should be '0' for the delivery.
This is an effect of the data available not of the statement!!
funny layout, did not know ...
03-18-2009 2:45 PM
Hi,
Consider to analyse what was sent to database on production (using ST05) and HOW the users are using your SQL.
I can't see "problem" in performance if of of your parameters are filled and, of course S_VBELN have less than 100 equal values. BUUUUUTTTTT you are not in control.
If you not take control of this parameters / ranges look what can reach the database (user filled no parameter):
WHERE vbuk~lvstk = 'B' "user filled no parameter result in a select everything
AND vbpa~parvw = '1C' " the restrictions on Open SQL disappear on Native SQL
AND vbfa~posnn = '1'
AND vbfa~vbtyp_n = 'Q'.
If you can't guarantee this, if better to divide the access using a business strategy for that.
Regards,
Fernando Da Ró
03-18-2009 3:34 PM
something wrong with data in development system so couldnt figure that out in development, Anywayz I cant use that joins nomore coz the data in production is different.
Thanks you lot for Replies.
Edited by: BrightSide on Mar 18, 2009 3:34 PM
Edited by: BrightSide on Mar 18, 2009 3:36 PM