Hi all,

IDS 9.4 UC3 / RH 9

I have a performance-question:
The quality of our application depends strongly on the execution-time of one
single SQL-Select-Statement. Of course, the parameters in it's Where-Clause
are different from time to time and from user to user, but we can use one
single prepared Satement for it.
This SELECT joins 2 really huge tables. The first time, you execute this
select, it takes about 20 seconds. This is too much. The second time, it
takes almost mothing !!! I don't know exactly why, but I assume this is the
consequence of loading table-data or index-data into the shared memory. But
a few minutes later, this select takes about 20 seconds again. It seems,
that the data in the shared-memory were overwritten by other SQL-Statements.

So this is my question: Is there a way, to force IDS to keep the data and
indices of one or two certain tables in the shared-memory?

Thanks for any advices!!

Bye
Markus

Hi,
Yes, there is a way :
set table my_table memory_resident;
set index my_table_index memory_resident;
That works fine.
HTH, regards,
OM
"Markus Bschorer" <mb@worxbox.com> a écrit dans le message de
news:c1v1s9$30s$05$1@news.t-online.com...
> Hi all,
>
> IDS 9.4 UC3 / RH 9
>
> I have a performance-question:
> The quality of our application depends strongly on the execution-time of
one
> single SQL-Select-Statement. Of course, the parameters in it's
Where-Clause
> are different from time to time and from user to user, but we can use one
> single prepared Satement for it.
> This SELECT joins 2 really huge tables. The first time, you execute this
> select, it takes about 20 seconds. This is too much. The second time, it
> takes almost mothing !!! I don't know exactly why, but I assume this is
the
> consequence of loading table-data or index-data into the shared memory.
But
> a few minutes later, this select takes about 20 seconds again. It seems,
> that the data in the shared-memory were overwritten by other
SQL-Statements.
>
> So this is my question: Is there a way, to force IDS to keep the data and
> indices of one or two certain tables in the shared-memory?
>
> Thanks for any advices!!
>
> Bye
> Markus
>
>

On Mon, 01 Mar 2004 05:07:19 -0500, Markus Bschorer wrote:

Increase the size of the buffer cache (BUFFERS onconfig file parameter) so
that more data is kept resident. The fact that after a few minutes the data
need to be reloaded is a clear indicator that the cache is too small.

Art S. Kagel

> Hi all,
>
> IDS 9.4 UC3 / RH 9
>
> I have a performance-question:
> The quality of our application depends strongly on the execution-time of one
> single SQL-Select-Statement. Of course, the parameters in it's Where-Clause
> are different from time to time and from user to user, but we can use one
> single prepared Satement for it.
> This SELECT joins 2 really huge tables. The first time, you execute this
> select, it takes about 20 seconds. This is too much. The second time, it
> takes almost mothing !!! I don't know exactly why, but I assume this is the
> consequence of loading table-data or index-data into the shared memory. But
> a few minutes later, this select takes about 20 seconds again. It seems,
> that the data in the shared-memory were overwritten by other SQL-Statements.
>
> So this is my question: Is there a way, to force IDS to keep the data and
> indices of one or two certain tables in the shared-memory?
>
> Thanks for any advices!!
>
> Bye
> Markus