Select statement is taking lot of time for the first time Execution.?

[Former Member]

Hi Experts,

I am facing the following issue. I am using one select statement to retrieve all the contracts from the table CACS_CTRTBU according to FOR ALL ENTRIES restriction.

if p_lt_zcacs[] is not initial.

SELECT

appl ctrtbu_id version gpart

busi_begin busi_end tech_begin tech_end

flg_cancel_obj flg_cancel_vers int_title

FROM cacs_ctrtbu INTO TABLE lt_cacs FOR ALL ENTRIES IN p_lt_zcacs

WHERE

appl EQ gv_appl

AND ctrtbu_id EQ p_lt_zcacs-ctrtbu_id

AND ( flg_cancel_vers EQ '' OR version EQ '000000' )

AND flg_cancel_obj EQ ''

AND busi_begin LE p_busbegin

AND busi_end GT p_busbegin.

endif.

The WHERE condition is in order with the available Index. The index has APPL,CTRTBU_ID,FLG_CANCEL_VERS and FLG_CANCEL_OBJ.

The technical settings of table CACS_CTRTBU says that the "Buffering is not allowed"

Now the problem is , for the first time execution of this select statement, with 1.5 lakh entries in P_LT_ZCACS table, the select statement takes 3 minutes.

If I execute this select statement again, in another run with Exactly the same parameter values and number of entries in P_LT_ZCACS ( i.e 1.5 lakh entries), it gets executed in 3-4 seconds.

What can be the issue in this case? Why first execution takes longer time?.. Or is there any way to modify the Select statemnt to get better performance.

Thanks in advance

Sreejith A P

[former_member194613]

what do you define as first execution ?

+ after system start,

+ first execution in the morning

+ first execution after a few hours.

How did you measure? SQL Trace, STAD, watch, response?

In principle there is nothing you can do, if the second execution is fine the everything is o.k.

How often is this thing executed anyway?

Siegfried

[Former Member]

Hi All,

Thanks for the response.

This happens in all the 3 scenarios.

First execution after system start

First execution of the application

First execution after few hours.

I measure the response by executing the Function module which contains this select statement.

Do you need any more details?

Thanks & Regards,

Sreejith

[former_member192616]

Hi,

>

> What can be the issue in this case? Why first execution takes longer time?.. 
> Sreejith A P

Sounds like caching or buffering in some layer down the i/o stack. Your first execution

seems to do the "physical I/O" where your following executions can use the caches / buffers

that are filled by your first exectution.

>

> Or is there any way to modify the Select statemnt to get better performance.
> Sreejith A P

If modifying your SELECTS statement or your indexes could help depends on your access details:

does your internal table P_LT_ZCACS contain duplicates?

how do your indexes look like?

how does your execution plan look like?

what are your execution figures in ST05 - Statement Summary?

(nr. of executions, records in total, total time, time per execuiton, records per execution, time per record,...)

Kind regards,

Hermann

[former_member194613]

of course it will happen in all three cases, but not to the same extent.

Please measure with the SQL-Trace. How many records does the SELECT return?

The number of entries in the for all entries table is not so important,

it is the number of entries which come back.

Maybe you get really a lot which fit into the cache only for a short time.

O.k., but the main question is, what is it good for? Millions of records are read once, and not every 10minutes.