topic Re: Select Query Takes more time in Application Development and Automation Discussions

Select Query Takes more time

Former Member — Tue, 08 Dec 2009 08:46:33 GMT

Hi All,

I have cloned KSB1 tcode to custom one as required by business.

Below query takes more time than excepted.

Here V_DB_TABLE = COVP.

Values in Where clause are as follows

OBNJR in ( KSBB010000001224 BT KSBB012157221571)

GJAHR in blank

VERSN in '000'

WRTTP in '04' and '11'

all others are blank

VT_VAR_COND = ( CPUDT BETWEEN '20091201' and '20091208' )

SELECT (VT_FIELDS) INTO CORRESPONDING FIELDS OF GS_COVP_EXT

FROM (V_DB_TABLE)

WHERE LEDNR = '00'

AND OBJNR IN LR_OBJNR

AND GJAHR IN GR_GJAHR

AND VERSN IN GR_VERSN

AND WRTTP IN GR_WRTTP

AND KSTAR IN LR_KSTAR

AND PERIO IN GR_PERIO

AND BUDAT IN GR_BUDAT

AND PAROB IN GR_PAROB

AND (VT_VAR_COND).

Checked in table for this condition it has only 92 entries.

But when i execute program takes long time as 3 Hrs.

Could any one help me on this

Re: Select Query Takes more time

Former Member — Tue, 08 Dec 2009 08:49:25 GMT

Select Query has form as follows

SELECT (VT_FIELDS) INTO CORRESPONDING FIELDS OF GS_COVP_EXT

FROM (V_DB_TABLE)

WHERE LEDNR = '00'

AND OBJNR IN LR_OBJNR

AND GJAHR IN GR_GJAHR

AND VERSN IN GR_VERSN

AND WRTTP IN GR_WRTTP

AND KSTAR IN LR_KSTAR

AND PERIO IN GR_PERIO

AND BUDAT IN GR_BUDAT

AND PAROB IN GR_PAROB

AND (VT_VAR_COND).

Some Processing like moving to internal table

ENDSELECT.

Re: Select Query Takes more time

Former Member — Tue, 08 Dec 2009 10:18:25 GMT

Hi Praveen,

Try to build the SELECT query with out into Corresponding Fields..

Into Corresponding Fields in one of time consuming statement.

Siva.

Re: Select Query Takes more time

Former Member — Tue, 08 Dec 2009 10:22:34 GMT

Thanks.

I tried to view values in COVP using SE16, passing entries what i have passed to select query. Here also it takes more time.

Re: Select Query Takes more time

Former Member — Tue, 08 Dec 2009 11:52:21 GMT

Hi,

Pls check this ...

~~1.Dont use SELECT/ENDSELECT instead use INTO TABLE addition~~

~~2.Avoid using corresponding addition.create a type and reference it.~~

If the select is going for dump beacause of storage limitations ,then use Cursors.

Edited by: A J Raj on Dec 11, 2009 3:07 PM

Re: Select Query Takes more time

Former Member — Tue, 08 Dec 2009 14:13:30 GMT

What does ST05 show? Which index is being used? Did you see ?

Rob

Re: Select Query Takes more time

Former Member — Tue, 08 Dec 2009 20:10:27 GMT

>1.Dont use SELECT/ENDSELECT instead use INTO TABLE addition .

> 2.Avoid using corresponding addition.create a type and reference it.

> If the select is going for dump beacause of storage limitations ,then use Cursors.

you got three large NOs .... all three recommendations are wrong!

The SE16 test is going in the right direction ... but what was filled. Nobody knows!!!!

Select options:

Did you ever try to trace the SE16? The generic statement has for every field an in-condition!

Without the information what was actually filled, nobody can say something there

are at least 2**n combinations possible!

Use ST05 for SE16 and check actual statement plus explain!

Re: Select Query Takes more time

Former Member — Tue, 08 Dec 2009 20:50:54 GMT

Well, actually, he does give some:

>OBNJR in ( KSBB010000001224 BT KSBB012157221571)

>GJAHR in blank

>VERSN in '000'

>WRTTP in '04' and '11'

>all others are blank

>VT_VAR_COND = ( CPUDT BETWEEN '20091201' and '20091208' )

I think the problem is in the first one.

Rob

Re: Select Query Takes more time

Former Member — Wed, 09 Dec 2009 05:06:14 GMT

Hi Praveen,

I think your approach to fetch the data is wrong.You are using a view COVP to get the data which is join of COBK and COEP. And for fetching the data you are not providing the key also.So system is taking all the time in the world to get this data.

Just check if it is possible for you to get the value of key fields.If not,then change the approach.First select data from COEP.You have objnr and it is there in secondary index.After fetching this get data from COBK by FOR ALL ENTRIES IN.I think that will work.

Hope it helps.

Re: Select Query Takes more time

Former Member — Wed, 09 Dec 2009 09:27:51 GMT

Rob, you are right ... in a not so optimal writing. I saw only the INs

Re: Select Query Takes more time

Former Member — Wed, 09 Dec 2009 10:57:07 GMT

Hi Rob,

U r right and i propossed same solution to business.

I had tested several cases.

Test 1: When I ran for retrieving 60 Days data it took 2 Hrs 27mins to complete job in background. No. od records here is 4 Lacks

Test2 : When I ran for retrieving for 8 Days data it took 2Hrs 40 Mins to complete job in background. No.of records heres is 1 lack.

Why is this difference.

Re: Select Query Takes more time

Former Member — Wed, 09 Dec 2009 13:47:02 GMT

I looked in my system at COEP (one of the tables included in the COVP view). There is an index provided by SAP on these fields (in this order):

OBJNR

KSTAR

GJAHR

PERIO

PAROB1

You should probably use this index by rearranging your where clause to this. Does it help?

WHERE OBJNR IN LR_OBJNR

AND KSTAR IN LR_KSTAR

AND GJAHR IN GR_GJAHR

AND PERIO IN GR_PERIO

AND LEDNR = '00'

AND VERSN IN GR_VERSN

AND WRTTP IN GR_WRTTP

AND BUDAT IN GR_BUDAT

AND PAROB IN GR_PAROB

AND (VT_VAR_COND).

Re: Select Query Takes more time

Former Member — Wed, 09 Dec 2009 14:25:37 GMT

Erik - this is index COEP_{2. But I think if you check, you'll see that the index exists in the data dictionary, but not in the database. But since the OP has included LEDNR in the SELECT, he is at least trying to use index COEP}1.

In the final analysis, I thjink the solution is to reduce the selection set by limiting OBJNR or just running it in the background and forget about it.

Rob

Edited by: Rob Burbank on Dec 9, 2009 9:29 AM

Re: Select Query Takes more time

Former Member — Thu, 10 Dec 2009 05:09:52 GMT

Hi Rob,

Thanks.

I am thninking to take data from COBK document numbers and Pass this on to COVP to retrieve the data.

Hope this also works fine.

Re: Select Query Takes more time

Former Member — Thu, 10 Dec 2009 06:44:04 GMT

you got three large NOs .... all three recommendations are wrong!
The SE16 test is going in the right direction ... but what was filled. Nobody knows!!!!

I just ran a quick performance test for select/Endselect and Into table .findings are below

There was a big difference in execution time

Runtime: 10,715,178 microseconds


Data  : t_bseg type table of BSEG.
Data  : w_bseg  type bseg.
SELECT * FROM bseg up to 100000 rows INTO w_bseg .
  append w_bseg to t_bseg.
ENDSELECT.

Runtime: 5,176,858 microseconds

Data  : t_bseg type table of bseg.
SELECT * FROM bseg up to 100000 rows INTO TABLE t_bseg.

Re: Select Query Takes more time

ThomasZloch — Thu, 10 Dec 2009 13:00:16 GMT

> I have cloned KSB1 tcode to custom one as required by business.

Your business should just explain their requirements and leave the solution to you

If this is a very critical query and you normally select by small creation date ranges, you could look into adding a secondary index for COBK-CPUDT. I have done this for a long running COVP select in an interface that requires "yesterdays" complete CO postings. Now there is a few extra GB space taken by that index but a runtime of three minutes compared to five hours.

If selection on creation date cannot be narrowed down by default, then don't create the index.

Thomas

Re: Select Query Takes more time

Former Member — Thu, 10 Dec 2009 14:28:37 GMT

 
> There was a big difference in execution time 
> Runtime: 10,715,178 microseconds 
> Runtime: 5,176,858 microseconds

I would argue that this is not a big difference. Changing a SELECT to use an index effectively can give a performance improvement of 20 to 30 times. Using an array SELECT rather than SELECT/ENDSELECT will give an improvement of maybe two times.

Nothing to be sneezed at, but when someone complains about a performance problem, this is generally not the problem or the solution.

Rob

Re: Select Query Takes more time

Former Member — Fri, 11 Dec 2009 09:26:24 GMT

> Runtime: 10,715,178 microseconds

> Data : t_bseg type table of BSEG.

> Data : w_bseg type bseg.

> SELECT * FROM bseg up to 100000 rows INTO w_bseg .

> append w_bseg to t_bseg.

> ENDSELECT.

> Runtime: 5,176,858 microseconds

> Data : t_bseg type table of bseg.

> SELECT * FROM bseg up to 100000 rows INTO TABLE t_bseg.

Your difference is much too large, I don't believe it.

Always execute it several times and PUT the one which you expect to be faster to the front, or add it again as number 3.

In your measurement I would assume that you measured the effect of database chaching.

Re: Select Query Takes more time

Former Member — Fri, 11 Dec 2009 09:37:26 GMT

thanks for the info.

i ran the comparison only once in performance examples..