So let's talk about tuning queries
by using cache.
In this lesson,
we're going to start off by talking about the basics
of result set caching.
We're going to take a look at key command syntax,
and talk about what's not cached.
And the service that we're going to be using
for this is Synapse.
So keep that in mind as we move through the lesson.
And then finally, we'll talk about cache management.
So with that, let's dive in.
So let's talk about the basics of result set caching.
So in a SQL pool,
and we're talking about Synapse here,
you can auto-cache your query results in a user database
for repeated use.
Now, this is persisted cache,
and what that's going to do is it's going to give you
query performance, and it's going to give you less compute.
Just like on your home computer or your laptop,
you have cache,
and that cache is used for frequently accessed data.
It's faster to pull it from that cache.
Same thing here.
You must turn it on though.
So that's true if you're going to do it for a user database,
or it's also true if you're going to do it
for just an individual session.
You've got to turn on caching.
So when we talk about caching, what's not cached?
Well, user-defined functions are not cached.
Row and column security is not cached.
So if you've set a row security or column security,
it's not going to cache those rows or columns.
Anything larger than 64 kilobytes as a row,
or if you have a total database over 10 gigabytes,
it's not going to cache that either.
And if you have built-in functions or run times
that aren't deterministic.
And non-deterministic functions are just functions
that aren't always going to give you the same results.
It's going to be dependent upon the input values
that they're provided.
So if you have non-deterministic functions,
those aren't going to be cached either.
So let's take a look at the syntax.
To turn on or off result set caching for a user database,
you're going to use this, over here on the right.
Now, it's not this entire thing.
I actually threw this in here
so that you would see the rest of the code in context,
or at least some of the code in context.
But this is the piece that you're actually interested in,
right down here on the right.
Pretty simple.
You've just got to make sure that you have that set.
It's also true if you are caching for a session.
You need to make sure that you include this line
if you are caching for a session.
So from a syntax standpoint, very, very simple.
Now, cache management.
When can your cache result be reused?
Well, you first have to have access to the tables.
Makes sense.
But if you don't have access to the tables,
it's not going to cache them.
Next, you have to have an exact match
between the old and the new query.
Again, we are storing so that we can pull data
for queries faster.
It's not going to pull all the data.
It's going to pull it for that specific query.
So your query has to be the same
in order for those cache results to be used.
You also can't have any schema changes.
If you change schema, that's also going to invalidate
or stop your cache from being pulled.
Cache is going to be dumped every 48 hours
if you haven't ran a query that uses that cache.
Also, if your cache approaches a terabyte in size,
it's also going to dump the cache there as well.
So just a couple of key points to remember
as we wrap up this lesson.
So syntax, we looked at Synapse.
You need to make sure
that you remember we are talking about Synapse,
but this also can pertain to SQL Server,
Managed Instance, and SQL DB.
So you could use that in any of those places.
However, those aren't on the DP-203,
so we're talking about Synapse.
Shuffling.
So, shuffling has a large effect on performance.
And in this course,
we've talked about shuffling in data distribution
quite a lot.
Cache is very helpful in this regard, because again,
it can help you to speed your queries
and it's going to reduce your overall cost.
However, you need to make sure
that you're maintaining your cache,
and that you've properly set it up
for the queries that you want to run.
All right, that's it for this lesson.
I'll see you in the next.