SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Caching


Caching

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (329K reputation)SSC Guru (329K reputation)SSC Guru (329K reputation)SSC Guru (329K reputation)SSC Guru (329K reputation)SSC Guru (329K reputation)SSC Guru (329K reputation)SSC Guru (329K reputation)

Group: Administrators
Points: 329488 Visits: 20109
Comments posted to this topic are about the item Caching

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (507K reputation)SSC Guru (507K reputation)SSC Guru (507K reputation)SSC Guru (507K reputation)SSC Guru (507K reputation)SSC Guru (507K reputation)SSC Guru (507K reputation)SSC Guru (507K reputation)

Group: General Forum Members
Points: 507597 Visits: 44277
I've had the Developers cache semi-static and static data several times rather than calling stored procedures or doing embedded T-SQL to get the data. For the things I've used it for (caching data for way-to-common calls), it has made a huge difference in performance.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Gary Varga
Gary Varga
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46178 Visits: 6562
There are so many types of caching too. Most developers do at least one type without paying any attention to it: memoization. An example of this is to get a value outside of a loop that is used within it as opposed to within it. Jeff mentioned the caching of static (or at least relatively static) data which is another extremely common use.

Caching should always be considered when developing applications. In this forum many of us might be considering maintaining consistency with the current state of the database i.e. what if it changes and we don't have the latest. Of course, getting the latest might be exactly the wrong thing to do. This is where the classic "it depends" applies: sometimes you want to ensure that the state of the data is the same for the whole of a particular context. That context might be the whole of the duration of a system is running (process), a service remaining active, an application domain's lifetime (AppDomain in .NET), an operation, a thread or, perhaps, a transaction.

This is an example of why experience counts. Also, it is an example of why some people will always be better at software development than others. There will always be some developers who will never consider these types of questions. It may matter. Or it may not.

Those of us who do consider these facets of software development have a responsibility to the projects we work on to highlight when and where we need considerations such as caching to be made.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
call.copse
call.copse
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8917 Visits: 2157
I've often used caching to be fair.

A good example is database driven multi lingual translation of web pages (i.e. not in resx files) which can make sense when you want to allow translation of a site through an admin interface (but don't want to generate resource files on the fly). You can cache the translations by page to avoid chatty db hits all the time, invalidating cache when an edit is made.
EricEyster
EricEyster
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1538 Visits: 520
I have used caching more for search pages, but not with more recent hardware. The old days of submitting a request and waiting 15 minutes are gone. We would save the search results to a caching table so you did not wait 15 more minutes to scroll to the next page of 20 lines of output.
Brent Ozar
Brent Ozar
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1080 Visits: 546
It's funny how often this happens to me in real life. Working with a customer yesterday, their #1 most resource-consuming query was running every 5 minutes to fetch all new data from the last 60 minutes. Doh!



Eric M Russell
Eric M Russell
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62526 Visits: 12784
I was once tasked with improving the performance of a stored procedure that ran for fifteen minutes on a good day and more than an hour on occasion. It was 5,000 lines of legacy dog poop that still ran for more than 10 minutes after optimizing the top 10 statements.

So I rearchitected the stored procedure to insert the resultset into a summary table keyed on Run_Date. Each time the procedure was called, it would first check if a resultset keyed on the current date was already in the summary table, and if so it would simply bypass the main body of T-SQL code and return summary records where Run_Date = @Run_Date. I even scheduled a job to kick off the procedure at 2am each morning, so the first user in the office didn't have to wait. After knocking the load time of the report down from 15 minutes to 2 seconds; they thought I was a database optimization genius.

This technique works great for scenarios where the resultset is expected to be static for a given hourly, daily, etc. period of time and you need a quick fix without resorting to a complete re-write of the procedure code. Also, the resultsets contained in a summary table don't necessarily have to be used for temporary "caching"; they can be retained permanently and leveraged to facilitate monthly and yearly aggregate reporting.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
herbaltea001-winter
herbaltea001-winter
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 Visits: 69
I had a similar scenario with a web server generating reports from a rapidly changing, real time database. The original system used a ridiculous number of queries that ran every minute. Of course this hammered the database server so I redesigned it to run a single query once every five minutes (no one noticed the latency :-)). I used a simple xml document as a cache and this allowed me to display the results using xslt. As a bonus I was able to extract more useful reports and make it pretty too.
EricEyster
EricEyster
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1538 Visits: 520
I didn't consider summary data as caching, but if that is the case, then our systems would never function without it. Analyzing customer trends on TB's of raw data would take hours.
Eric M Russell
Eric M Russell
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62526 Visits: 12784
EricEyster (2/26/2014)
I didn't consider summary data as caching, but if that is the case, then our systems would never function without it. Analyzing customer trends on TB's of raw data would take hours.

I'm thinking that any time we persist data in a more accessible secondary location for re-use, wether it's pages in memory buffer cache, derived resultsets in a summary table or materialized view, in-memory tables (xVelocity), or memcached data in the middle tier, then it's all various forms of caching.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search