Memory Performance?

A have a question, something that has been bugging me for some time across all platforms....

Generally speaking, let's suppose....

1. you have a SQL statement that performs a full table scan
2. you run it once, it comes back in say 60 seconds.
3. you run it again, and lets say it comes back in 30 seconds, a 200% improvement.
4. Ok, now you decide to implement a KEEP pool large enough to hold the entire table.
5. Shutdown and restart the instance/server/etc, run the same statement again.

The timings are identical.

Therefor, I am forcing the table to be in RAM. I can verify it with consistent gets. I am on a local wire, so the network performance is not an issue. I am on a quiet box, so the box is not loaded. RAM is ok on the box.

If RAM is supposed to be at least 1000x faster than DISK (not adding in rotational delay, etc.), why don't I get at least 1000x the performance???

Let me also say that from my experience, I have also seen this disparity. I have several production systems utilizing multiple buffer pools, and every other conceivable performance parameter in them with 99.9% hit ratios and the performance isn't that different from when the instance first starts or after a week after it has been running.

This has plagued me since DBASE 3+ for a PC. For example, I setup a virtual drive large enough to hold the databases, indexes and application code, and again performance never knocked my socks off.

Any insight as to why this is so?

-Jerry

P.S. Can you please reply to my email in addition to the newsgroup? I do not check the news groups regularly. Received on Thu Mar 16 2000 - 00:00:00 CST