topic Re: problem with select statement in production in Application Development and Automation Discussions

problem with select statement in production

Former Member — Tue, 17 Mar 2009 16:40:55 GMT

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 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'.

Edited by: BrightSide on Mar 17, 2009 4:41 PM

Re: problem with select statement in production

ThomasZloch — Tue, 17 Mar 2009 16:44:05 GMT

what means "unsuccessful" in this context?

Re: problem with select statement in production

Former Member — Tue, 17 Mar 2009 16:47:00 GMT

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

Re: problem with select statement in production

Former Member — Tue, 17 Mar 2009 16:48:33 GMT

it means sy-subrc ne 0. when it should be '0' for the delivery.

Edited by: BrightSide on Mar 17, 2009 4:48 PM

Re: problem with select statement in production

Former Member — Tue, 17 Mar 2009 18:15:17 GMT

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

Re: problem with select statement in production

Former Member — Tue, 17 Mar 2009 20:19:51 GMT

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

Re: problem with select statement in production

Former Member — Tue, 17 Mar 2009 21:11:55 GMT

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

Re: problem with select statement in production

Former Member — Wed, 18 Mar 2009 02:17:07 GMT

Hi Brightside,

I wonder could it be happen....

( vbuk

INNER JOIN likp

ON vbuk_{vbeln = likp}vbeln

this is a piece of your code,,,, I think that Likp_{vbeln is not equal as Vbuk}vbeln...!

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 LIKP_{VBELN in VBFA}VBELV..... 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 A_{VBELN A}POSNR A_{VSTEL B}BMENG D~LAND1 INTO

(VBELN, POSNR, SPOINTL , LFOI1 , SOCONTL)

FROM VBAP AS A

INNER JOIN VBEP AS B

ON A_{VBELN = B}VBELN

AND A_{POSNR = B}POSNR

INNER JOIN VBPA AS D

ON A_{VBELN = D}VBELN

INNER JOIN VBUP AS U

ON A_{VBELN = U}VBELN

AND A_{POSNR = U}POSNR

INNER JOIN VBUP

ON VBUP_{VBELN = A}VBELN AND

VBUP_{POSNR = A}POSNR

INNER JOIN LIPS

ON LIPS_{VGBEL = A}VBELN AND

LIPS_{VGPOS = A}POSNR

INNER JOIN LIKP

ON LIKP_{VBELN = LIPS}VBELN

WHERE A~MATNR = P_MATNR

AND LIKP~LDDAT = P_ITAB9_DATE

AND D~PARVW = 'WE'

AND ( A_{VSTEL = 'M9RT' OR A}VSTEL = 'M9IT' OR A_{VSTEL = 'M9FC' OR A}VSTEL = '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

Re: problem with select statement in production

Former Member — Wed, 18 Mar 2009 03:20:50 GMT

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..

Re: problem with select statement in production

Former Member — Wed, 18 Mar 2009 11:28:23 GMT

>>>> 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 ...

Re: problem with select statement in production

Former Member — Wed, 18 Mar 2009 14:45:34 GMT

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ó

Re: problem with select statement in production

Former Member — Wed, 18 Mar 2009 15:34:18 GMT

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