possible to clear a database cache?

D

dschuchman

New Member
is it possible to clear a progress database cache?
have a query that runs slow the first time and then fast afterwards since the records are all cached.
trying to determine why the initial query is slow (doing a FOR EACH thru 300 or so records and some other FINDs in the loop)
it takes around 30 seconds first time and then less than a second after that.

is there some proutil command i can give my database to clear the cache?
tried just doing a for each thru a large table and having it run for an hour and then doing my query, but that' s kinda a painfully slow way to debug my first query.
running my db in multiuser/server mode in unix.

any help would be appreciated. thanks, Dan
 
The quick answer is "no".

Of course you could also just fill it with something else -- which would have the same effect as clearing it.

But I'm confused. You say that you know the query is slow because the first time through the data is not cached. When the data is cached it is fast. So what is the point of clearing the cache? How does that help you figure anything out?

To debug such a situation is fairly straight-forward. You already have the problem query -- that's a big step forward. Most of the time finding it is a needle in the haystack kind of a problem. To now evaluate alternative versions of the query do something like this:

Code: 
define variable i  as integer no-undo.
define variable lr as integer no-undo.

find _myconnection no-lock.
find _userio no-lock where
     _userio-usr = _myconn-userid no-error.
lr = _userio._userio-dbaccess.
etime( yes ).

/*** put the query to test here ***/

find <table> no-lock where <whatever> no-error. 

/*** ***/

find _userio no-lock where
     _userio-usr = _myconn-userid no-error.
display i ( _userio-dbaccess - lr ) etime().

Ignore the etime() result. It will vary based on what is (or isn't) in the cache. (The reason that it is in this particular code sample is to show why it is a bad way to evaluate query efficiency.)

Look at the ratio between "i" ("useful records") and "lr" (logical reads"). For an efficient query the ratio should be roughly 1:2. This is because Progress reads at least an index block and a data block for every record read (unless you are doing RECID reads). If the ratio is 1:10 or 1:100 or 1:1000 or worse then your query will scale poorly and users will (eventually) complain.

You do not need to clear your cache to use this technique to test alternative queries.
 
The example is a FIND. By "query" I mean any statement that reads records. FIND, FOR EACH, OPEN QUERY etc...
 
It counts the "useful" records returned to the 4GL.

Ah... I see the problem -- it isn't being set. Oh, you said that. I guess that's what I get for responding pre-coffee :blush1:

In an example which is simply a unique FIND it would be 1. If the query being tested returns multiple records then you would do something like:

Code: 
FOR EACH customer NO-LOCK WHERE discount = 10:
  i = i + 1.
END.
 
You must log in or register to reply here.
Back
Top