Arturo Masero · Nov 3, 2021

How can I reset cached routines?


I don't know if the title is accurate enough. I have a legacy code that I need to optimize. It's a routine written in objectscript. It accepts 4 parameters and runs 6 nested FOR...$ORDER reading a big global.

The thing is when I run the routine the first time it takes around 60 seconds to run. If I run it again it takes 5 seconds. If I wait around 6 to 10 minutes to run it again, it takes 60 seconds again, but if I run it every 1, 2, 3... minutes it still takes only 5 seconds to run.

I can guess there's some cached memory stuff somewhere making this happen. I've tried purging SQL queries and purging not needed journals. I knew it was not related but I don't know any other purging tools and I needed to try something.

So, am I guessing right and there's some garbage collector or memory management doing the trick? Can I force it to empty/reset so my routine takes its 60 seconds to run on every call and not once every 10 minutes? How can I know the time setup for the reset besides try and error? Am I wrong in these assumptions?

Product version: Caché 2018.1
$ZV: Cache for Windows (x86-64) 2018.1.2 (Build 309_5) Wed Jun 12 2019 20:14:50 EDT
0 221
Discussion (9)2
Log in or sign up to continue

What you experience is the effect of the Global Buffer Pool.
The rule is to overwrite the least used buffer if a new is required.
So the older the buffer the higher the chance to be overwritten and later reloaded.
Purging queries only affects code not data

Possible option:  increase  your buffer pool (double or triple size)
or try this approach:
suggested by @Julius Kavay 

Try calling the ClearBuffers in the GLOBUFF system routine, e.g.:

Removed 65303 blocks

It's a funny request... everybody wants to get a job done as fast as possible, but you want the opposite ;-)

You can clear all buffers, as Vitaliy Serdtsev  suggested, with empty buffers you will get (more or less) constante times but keep in mind, this affects (slows down) all other jobs too!

Just my2cc

For debugging purposes and further query optimization, why not?

I have a legacy code that I need to optimize.

That part of the question was "out of my viewport". Yes, that's an important point.

You asked for "cached routines"... the above clear buffer function, as far, as I know, resets the global buffers only, not the routine buffers. Just for completeness. Maybe someone with a more detailed knowledge could me agree or disagre...

Thanks for the point Julius. Maybe removing that big global from the buffer has done the job because one of the problems the old routine had was over-iterating on those nested 'For-orders' and that bad design could be camouflaged by the buffering thing.

Dunno, I'm new to these concerns, but it worked!! :-D

Thank you all for your contributions. With Robert's and Vitaliy's answers I have managed to learn what I needed to solve this problem.

Kind regards :-)

Hi Arturo,

This scenario sounds like during the subsequent runs of the routine you are accessing Global buffers (AKA: Global buffer pool) which is used to optimise the running of the routines for frequent use or data access.

Your subsequent runs will be complimented by any new data that is added between runs with the "latest and greatest" data. The same applies to any data that has been killed between runs.

You don't need to purge anything. You can trust that you will always be getting the data you expect.

Kind regards,