topic Re: database view and inner join in Application Development and Automation Discussions

database view and inner join

Former Member — Mon, 21 Aug 2006 06:33:58 GMT

Hi!

I have performance issues for a function pool where a select statement is causing a 'Time out' dump. The retrieval of records are from a Database View(Likpuk).

The Database View has 2 tables: VBUK and LIKP and no selection conditions.

PARAMETERS : custno TYPE kna1-kunnr.

types: begin of dlv_hdr_type,

vbeln like likp-vbeln,

vbtyp like likp-vbtyp,

end of dlv_hdr_type.

data open_dlv_hdr_t type standard table of dlv_hdr_type initial size 0 with header line.

SELECT vbeln vbtyp INTO TABLE open_dlv_hdr_t

FROM likpuk

WHERE kunnr = custno

AND wbstk IN ('A', 'C').

Now i have tried inner join but when i run the jobs using both Database View and Inner join the time taken by inner join is atleast 291 sec more(around 64000 recs)!!

I have used the following inner join instead of DB view:

SELECT A_{VBELN A}VBTYP INTO table open_dlv_hdr_t

FROM VBUK as A inner join likp as B

on A_{mandt = B}mandt and A_{vbeln = B}vbeln

where B_{kunnr = custno AND A}wbstk IN ('A','C').

So can anybody please tell me what should i use for faster retrieval of records to replace the DBview Likpuk?

Re: database view and inner join

Former Member — Mon, 21 Aug 2006 06:38:34 GMT

Hope you have already explored the use of all possible keys in the table. Is it possible to create some index on the table that can help you make if faster..refer to your DBA ?

Thanks !!

Regards,

Vishal Tyagi

Re: database view and inner join

Former Member — Mon, 21 Aug 2006 06:49:52 GMT

u may try FOR ALL ENTRIES IN.

Illustration :-

declate two internal table as

BEGIN OF wa_likp,

kunnr TYPE TYPE,

VBELN TYPE VBELN,

END OF wa_likp.

DATA it_likp LIKE TABLE OF wa_likp.

BEGIN OF wa_VBUK,

VBELN TYPE VBELN,

vbtyp TYPE vbtyp,

END OF wa_VBUK.

DATA it_vbuk LIKE TABLE OF wa_vbuk.

And replace your select statement with this code

SELECT vbeln kunnr FROM LIKP INTO TABLE it_likp

WHERE kunnr = custno.

IF SY-SUBRC EQ 0.

IF NOT it_likp IS INITIAL.

SELECT vbeln vbtyp FROM VBUK INTO TABLE it_vbuk

FOR ALL ENTRIES IN it_likp

WHERE vbeln = it_likp-vbeln AND

wbstk IN ('A','C').

ENDIF.

Regards

Alok Pathak