topic Re: Querying from LIPS in Application Development and Automation Discussions

Querying from LIPS

Former Member — Tue, 20 Apr 2010 14:00:09 GMT

Hi experts, anyone have any idea how to improve my query.

Select vebln posnr from LIPS

where werks = p_werks

and lgort = p_lgort

I need to get data in lips based only on werks and lgort. I know it will be very heavy. But anyone know the workaround? Thank you in advances....

Re: Querying from LIPS

Former Member — Wed, 21 Apr 2010 00:02:42 GMT

Search for SAP Note about SD performance in customer programs...you may be able to utilize index table like VLKPA...there are other names, I can't recall at the moment.

Re: Querying from LIPS

Former Member — Wed, 21 Apr 2010 01:11:54 GMT

There are index table VLKPA and VLPMA. But both tabe have good index for Plant/Storage location.

Our company add index MANDT/MATNR/WERKS/LGORT in table Lips.

I think your case

MANDT/ WERKS/LGORT/MATNR or

MANDT/ WERKS/LGORT/VBELN.

Please study other abap program to use table LIPS.

Regards,

Gaito

Re: Querying from LIPS

Former Member — Wed, 21 Apr 2010 01:20:52 GMT

Hi all, thank you for the advices. I have looked at the VLKPA and VLPMA, both table don't have WERKS or LOGRT in it. How can i use them? Actually my actual SQL which is very heavy is like this:

SELECT lips_{werks lips}lgort lips_{vbeln lips}erdat INTO TABLE ta_po FROM lips

INNER JOIN ekes ON lips_{vbeln = ekes}vbeln

WHERE lips~lgort = p_gate

AND lips~werks = p_plant

AND lips~pstyv = 'ZBC'

AND ekes~dabmg = 0

AND ekes~eindt in s_date.

Basically, i want to get all data which is delivered by vendors and on the specific storage loc and plant.

In my LIPS, i have also already add index for mandt, logrt and werks.

Re: Querying from LIPS

HermannGahm — Thu, 22 Apr 2010 08:37:51 GMT

Hi,

> SELECT lips_{werks lips}lgort lips_{vbeln lips}erdat INTO TABLE ta_po FROM lips

> INNER JOIN ekes ON lips_{vbeln = ekes}vbeln

> WHERE lips~lgort = p_gate

> AND lips~werks = p_plant

> AND lips~pstyv = 'ZBC'

> AND ekes~dabmg = 0

> AND ekes~eindt in s_date.

> Basically, i want to get all data which is delivered by vendors and on the specific storage loc and plant.

> In my LIPS, i have also already add index for mandt, logrt and werks.

how many records does LIPS have?

how many records are selected with your query?

what ist the current execution plan used?

Kind regards,

Hermann

Re: Querying from LIPS

Former Member — Thu, 22 Apr 2010 08:47:45 GMT

There are 6.035.246 rows in my LIPS table.

This my current explain plan. This is in QA

SELECT STATEMENT ( Estimated Costs = 177 , Estimated #Rows = 531

Step 3 FILTER

Step 2 TABLE ACCESS BY INDEX ROWID LIPS

( Estim. Costs = 176 , Estim. #Rows = 531 )

Step 1 INDEX RANGE SCAN LIPS~Z39

( Estim. Costs = 27 , Estim. #Rows = 6.897 )

Search Columns: 3

Currently, it uses z39 index that i already created

NONUNIQUE Index LIPS~Z39

Column Name #Distinct

MANDT 5

LGORT 35

WERKS 5

Re: Querying from LIPS

HermannGahm — Thu, 22 Apr 2010 08:52:35 GMT

Hi,

so you havee 6 million rows in QA.

How does your statment look like?

Like this?

Select vebln posnr from LIPS

where werks = p_werks

and lgort = p_lgort .

What is missing now is the number of rows

in the result set.... .

A full table scan might be faster if the result set is big.

Kind regards,

Hermann

Re: Querying from LIPS

Former Member — Thu, 22 Apr 2010 09:02:36 GMT

I have tried 2 methods.

Firstly i have my sql like this:

SELECT lips_{werks lips}lgort lips_{vbeln lips}erdat INTO TABLE ta_po FROM lips

INNER JOIN ekes ON lips_{vbeln = ekes}vbeln

WHERE lips~lgort = p_gate

AND lips~werks = p_plant

AND lips~pstyv = 'ZBC'

AND ekes~dabmg = 0

AND ekes~eindt in s_date.

And the result was very heavy.

Then i tried to separate the SQL become 2 SQL. First is to select from LIPS by itself. Then select to EKES. This is the SQL to LIPS

select lips_{werks lips}lgort lips_{vbeln lips}erdat

into table l_ta_lips

from lips

where lips~mandt = sy-mandt

and lips~lgort = p_gate

and lips~werks = p_plant

and lips~pstyv = 'ZBC'.

describe table l_ta_lips.

if sy-tfill > 0.

select ekes~vbeln

into table l_ta_ekes

from ekes

for all entries in l_ta_lips

where vbeln = l_ta_lips-vbeln

and dabmg = 0

and eindt in s_date.

loop at l_ta_lips into l_wa_lips.

read table l_ta_ekes into l_wa_ekes with key vbeln = l_wa_lips-vbeln.

if sy-subrc = 0.

append l_wa_lips to ta_po.

endif.

endloop.

endif.

The explain sql trace that i gave you on my previous thread is for selecting to LIPS only. And the weird thing is that in my ST05, i saw lots of FETCH from LIPS. And all of them FETCHING 1911 rows.

LIPS FETCH 1.911

Re: Querying from LIPS

HermannGahm — Thu, 22 Apr 2010 09:19:56 GMT

Hi,

well, step by step.

Let's ignore the join for a moment.

> Then i tried to separate the SQL become 2 SQL. First is to select from LIPS by itself. Then select to EKES. This is the SQL to LIPS

> select lips_{werks lips}lgort lips_{vbeln lips}erdat

> into table l_ta_lips

> from lips

> where lips~mandt = sy-mandt

> and lips~lgort = p_gate

> and lips~werks = p_plant

> and lips~pstyv = 'ZBC'.

> The explain sql trace that i gave you on my previous thread is for selecting to LIPS only. And the weird thing is that in my ST05, i saw lots of FETCH from LIPS. And all of them FETCHING 1911 rows.

> LIPS FETCH 1.911

This is not weird since you fetch lots of data and the application server communicates in packets with the database

and obviously more than one packet is necessary for all of your data.

Again:

How many rows are selected in total from Lips?

How many rows do we have for:

> where lips~mandt = sy-mandt

> and lips~lgort = p_gate

> and lips~werks = p_plant

> and lips~pstyv = 'ZBC'.

and how many for:

> where lips~mandt = sy-mandt

> and lips~lgort = p_gate

> and lips~werks = p_plant

Kind regards,

Hermann

Re: Querying from LIPS

Former Member — Thu, 22 Apr 2010 09:55:43 GMT

Again:

> How many rows are selected in total from Lips? 71000

> How many rows do we have for: 71.755

where lips~mandt = sy-mandt

and lips~lgort = p_gate

and lips~werks = p_plant

and lips~pstyv = 'ZBC'.

and how many for: 71.756

where lips~mandt = sy-mandt

and lips~lgort = p_gate

and lips~werks = p_plant

Re: Querying from LIPS

Rui_Dantas — Thu, 22 Apr 2010 10:02:41 GMT

Hi.

Just to be sure that you are focusing on the right query, you have confirmed that EKES has an index by VBELN and that index is being used, right?

Re: Querying from LIPS

HermannGahm — Thu, 22 Apr 2010 10:45:14 GMT

Hi,

so you are selecting _{1 % (}70.000) out of 6 Million rows. Therfore a

full table scan will not help.

Looking at what you have said you have an optimal index (MANDT, LGORT, WERKS)

in place for your where condition:

where lips~mandt = sy-mandt

and lips~lgort = p_gate

and lips~werks = p_plant

and lips~pstyv = 'ZBC'.

pstyv does not further restrict the data.

The execution plan seems to be optimal. If we assume 10 milliseconds per row

your query for LIPS should run 700 seconds (11 minutes 40 seconds) - might

be faster or slower - depends on caching and the I/O system. If you want to

have it faster you have to start parallelization.

What are your run times for LIPS?

As Rui said, for the join, VBELN should be used in an index on EKES. Run times

will be slower you can add additional 10 ms for each matching row from EKES (rule of thumb).

Kind regards,

Hermann

As Rui said, maybe the problem for the join might be for

Re: Querying from LIPS

Former Member — Thu, 22 Apr 2010 12:55:19 GMT

Yes, i use VBELN index in EKES