Query performance

A

awolf

New Member
Progress 9.1C
Single-user

Query : for each
FIELDS() where [field]
Index is available on the [field]; not the primary
No of records returned ~ 30 000

Only connect to the db - via Data Dictionary - and open procedure editor.

First time takes 40 sec
Second time 1 sec

Close procedure editor and data dictionary - so close progress , restart AdminService for Progress.

Connect again and run the query : 1 sec

Restart the computer , connect and run query : 40 sec
Second time : 1 sec

:confused:

Do you have any explanation please.

Thanks.
 
I am guessing that although you said "Single-User", you started a DB server (eg. proserve) which is, of course, optional. If that is the case, restarting the Admin Service doesn't restart the database... so the 2nd query benefits from the cache effect of the first query. After you reboot the whole computer, assuming the DB is running on the same computer, the third query loses that benefit.
 
No db server, just single-user.

I was just wanted to close all possibilities that Progress would have anything cached.

Thanks for the reply.
 
1) Progress caches records even in single user mode (-B isn't just a server parameter).

2) Even if Progress doesn't cache the records the OS will. The first test read them from disk, the OS cached those disk blocks. Shutting down Progress has no impact on that. When you restart Progress the records are not in the Progress cache but they are still in the OS cache so no disk reads need to take place.

Both the Progress cache and the OS cache are multiple orders of magnitude faster than reading from disk but since you're dealing with a very small number of records and thus a small number of blocks so you cannot really see that the OS cache is a lot slower than the Progress cache -- to see that you need to read millions of records.
 
You must log in or register to reply here.
Back
Top