CODEWITHSUNDEEP
sqldatabaseoracle-database
Robert Bain
Robert Bain

Reputation: 9586

Is it reasonable to rely on an RDBMS buffer cache?

I'm writing a RESTful API which queries an RDBMS. The first time the SQL query in question is run, it's taking around 10 seconds. Subsequent runs of the query with the same bind variables take milliseconds. I believe this is due to the results being present in the buffer cache.

I don't want to store the results of this query in the application layer due to space considerations and would rather run it every time I need it. For performance reasons, I'm relying on the fact that the results will be retrieved through a buffer cache hit. Subsequent query runs will be seconds or minutes apart and it appears that the results stay present in the buffer cache for longer than this.

Is it a reasonable design decision to leverage the buffer cache in this manner?

Upvotes: 0

Views: 89

Answers (2)

Neville Kuyt
Neville Kuyt

Reputation: 29649

Is this a reasonable design decision?

I would suggest you'd want to do some fairly extensive load and performance testing to answer that question.

I am a pessimist, but when you have queries that take 10 seconds, it's not unusual for them to slow down over time (you're probably not hitting an index, so as the data grows, the performance gets worse). Those queries also tend to struggle with contention for resources, so performance degrades as the number of concurrent queries increases.

I'd also want to look at which business events would cause a cache refresh in Oracle, and their frequency. If you're querying orders in a web shop for instance, you may get new data every second, so your cache will not have a long time-to-live.

If you rely on the buffer cache to keep the application performant, I'd want to understand exactly what happens when the cache is missed, how many concurrent queries you can handle, and what happens when 10 seconds becomes 20 or 30 or 60.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271111

I would suggest wrapping the query logic at the application layer in some sort of function. Then, implement the function using a query, which can rely on the query cache.

In the future, you may want to change the implementation to locally cache the results. Encapsulating the functionality makes it easy to change the implementation, without affecting other code.

Upvotes: 3

Related Questions