Member Avatar for mtyide

I need to get a set of results from the db (server) but at times I would get the same result back and I would like to cache the result instead of making the same call to the db. Any ideas?

  • 3 Contributors
  • 6 Replies
  • 380 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by rproffitt

Dani AI

Generated

Short answer: prefer an application-level cache (cache-aside) for result sets, and only cache queries that are expensive, frequently repeated, and tolerant of some staleness. ’s question is best solved by deciding which queries meet those criteria and then picking a straightforward invalidation strategy.

A practical pattern (cache-aside) is: check cache, on miss run the DB query, serialize and store the result with a sensible TTL, return the result. Keep cache keys explicit (include query parameters and a version token when useful) so different argument combinations never collide. Example key form and pseudocode:

key = "orders:customer:" + customerId + ":v" + tableVersions["orders"]
value = cache.get(key)
if value == null:
    value = db.query(...)
    cache.set(key, serialize(value), ttl=60)
return deserialize(value)

For SQL Server-specific invalidation options, consider Query Notifications (SqlDependency) for simple, supported queries — but be aware they have limitations and require Service Broker. Change Tracking or Change Data Capture are alternatives for detecting writes. A robust, low-complexity approach is a per-table version counter that the write path increments (or a trigger increments); include that version in the cache key so writes implicitly invalidate affected cached results without scanning keys.

Operational checklist: measure query latency and frequency before adding cache; estimate serialized size and memory footprint; prefer a distributed cache (Redis/Memcached) if multiple app nodes exist; choose TTLs to balance freshness vs. churn; monitor hit rate, miss rate, evictions and serialization cost. Finally, avoid caching tiny, cheap or highly volatile queries — sometimes SQL Server’s buffer pool and plan cache already make repeated reads cheap enough, as noted by .

Recommended Answers

All 6 Replies

Member Avatar for Dani

The query cache, that rproffitt linked to, used to cache MySQL result sets for situations where an identical query is made and there have been no table writes to any of the tables involved in the query. However, it has since been deprecated.

I recommend using something like Memcached to cache query results. We do that here at DaniWeb with great success. However, when using caching systems such as Memcached or Redis, it can always take some playing around to get the optimal hit/miss ratio. Otherwise, it could end up slowing you down more than speeding you up. In other words, it takes resources to save to the cache, so if you’re saving to the cache more than you’re utilizing what’s in the cache, that’s not a good thing. Plus, caches like Memcached only know how to store data for a particular duration of time. You will have to manually clear the cache each time a table is updated. It isn’t like the built-in query cache that automatically invalidates queries for tables that are modified.

commented: Oh my. Times change so I must too! +16
Member Avatar for Dani

As I read https://dev.mysql.com/doc/refman/5.7/en/query-cache.html my view/answer is I wouldn't bother optimizing in this situation

Also I think we both missed that they tagged SQL Server so they aren’t even using MySQL. That being said, I don’t know of any caching solutions built into SQL Server, but it’s not my forte.

Member Avatar for rproffitt

I recall the caching of queries is something from about the 1990's. But hey, there is query caching (apparently) in SQL Server 2008. Plenty on the web about it such as https://dba.stackexchange.com/questions/43572/how-to-see-what-is-cached-in-memory-in-sql-server-2008 where with a little code you can see what is in the cache.

My heavy work on SQL was in the late 1990's till about 2010 when we did the end of life on that product line. So for now I cruise along on what I recall from those days. That is, we found that the overhead of a repeated query was too low to optimize away/out with more code or an addon product. That is, we learned to let the SQL Server do what it did best and work on the product side.

Member Avatar for Dani

Sometimes it makes sense to cache query results using third-party solutions such as Memcached.

For example, querying the database for related topics is expensive, is needed on every page load, and they almost never change. It makes sense for us to cache the result set for each forum thread in something like Memcached.

Member Avatar for rproffitt

Our scenario was a product tester. Also known as Automatic Test. The SQL Server was on the network and there's only a dozen test machines which would spend about 10 or so minutes per device under test. So the load was never worth optimizing. The IT department would fret about having automatic tests overload their SQL Server but they could never show any increase in load during production runs. In other words, negligible load.
What was up on the SQL Server of interest? The order details. The product was configured to the client's order during this phase of final testing. So it was either have a human type all that in or automate it and go get it from the order database. You can guess what's cheaper.

Edited by rproffitt
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.