1
00:00:00,260 --> 00:00:02,220
So let's talk about tuning queries

2
00:00:02,220 --> 00:00:04,130
by using cache.

3
00:00:04,130 --> 00:00:04,963
In this lesson,

4
00:00:04,963 --> 00:00:07,090
we're going to start off by talking about the basics

5
00:00:07,090 --> 00:00:09,050
of result set caching.

6
00:00:09,050 --> 00:00:11,070
We're going to take a look at key command syntax,

7
00:00:11,070 --> 00:00:13,360
and talk about what's not cached.

8
00:00:13,360 --> 00:00:14,810
And the service that we're going to be using

9
00:00:14,810 --> 00:00:16,180
for this is Synapse.

10
00:00:16,180 --> 00:00:19,230
So keep that in mind as we move through the lesson.

11
00:00:19,230 --> 00:00:22,120
And then finally, we'll talk about cache management.

12
00:00:22,120 --> 00:00:24,610
So with that, let's dive in.

13
00:00:24,610 --> 00:00:28,000
So let's talk about the basics of result set caching.

14
00:00:28,000 --> 00:00:29,420
So in a SQL pool,

15
00:00:29,420 --> 00:00:31,320
and we're talking about Synapse here,

16
00:00:31,320 --> 00:00:34,930
you can auto-cache your query results in a user database

17
00:00:34,930 --> 00:00:36,610
for repeated use.

18
00:00:36,610 --> 00:00:38,730
Now, this is persisted cache,

19
00:00:38,730 --> 00:00:40,680
and what that's going to do is it's going to give you

20
00:00:40,680 --> 00:00:43,990
query performance, and it's going to give you less compute.

21
00:00:43,990 --> 00:00:47,340
Just like on your home computer or your laptop,

22
00:00:47,340 --> 00:00:48,670
you have cache,

23
00:00:48,670 --> 00:00:52,210
and that cache is used for frequently accessed data.

24
00:00:52,210 --> 00:00:54,870
It's faster to pull it from that cache.

25
00:00:54,870 --> 00:00:55,823
Same thing here.

26
00:00:56,860 --> 00:00:58,600
You must turn it on though.

27
00:00:58,600 --> 00:01:01,570
So that's true if you're going to do it for a user database,

28
00:01:01,570 --> 00:01:02,970
or it's also true if you're going to do it

29
00:01:02,970 --> 00:01:04,410
for just an individual session.

30
00:01:04,410 --> 00:01:06,400
You've got to turn on caching.

31
00:01:07,730 --> 00:01:10,650
So when we talk about caching, what's not cached?

32
00:01:10,650 --> 00:01:13,870
Well, user-defined functions are not cached.

33
00:01:13,870 --> 00:01:16,340
Row and column security is not cached.

34
00:01:16,340 --> 00:01:19,400
So if you've set a row security or column security,

35
00:01:19,400 --> 00:01:22,680
it's not going to cache those rows or columns.

36
00:01:22,680 --> 00:01:26,320
Anything larger than 64 kilobytes as a row,

37
00:01:26,320 --> 00:01:29,360
or if you have a total database over 10 gigabytes,

38
00:01:29,360 --> 00:01:31,403
it's not going to cache that either.

39
00:01:32,270 --> 00:01:35,220
And if you have built-in functions or run times

40
00:01:35,220 --> 00:01:37,270
that aren't deterministic.

41
00:01:37,270 --> 00:01:41,160
And non-deterministic functions are just functions

42
00:01:41,160 --> 00:01:44,170
that aren't always going to give you the same results.

43
00:01:44,170 --> 00:01:46,470
It's going to be dependent upon the input values

44
00:01:46,470 --> 00:01:47,490
that they're provided.

45
00:01:47,490 --> 00:01:50,660
So if you have non-deterministic functions,

46
00:01:50,660 --> 00:01:52,460
those aren't going to be cached either.

47
00:01:53,790 --> 00:01:55,820
So let's take a look at the syntax.

48
00:01:55,820 --> 00:02:00,020
To turn on or off result set caching for a user database,

49
00:02:00,020 --> 00:02:03,200
you're going to use this, over here on the right.

50
00:02:03,200 --> 00:02:04,500
Now, it's not this entire thing.

51
00:02:04,500 --> 00:02:05,760
I actually threw this in here

52
00:02:05,760 --> 00:02:08,650
so that you would see the rest of the code in context,

53
00:02:08,650 --> 00:02:10,660
or at least some of the code in context.

54
00:02:10,660 --> 00:02:13,320
But this is the piece that you're actually interested in,

55
00:02:13,320 --> 00:02:15,220
right down here on the right.

56
00:02:15,220 --> 00:02:16,170
Pretty simple.

57
00:02:16,170 --> 00:02:18,860
You've just got to make sure that you have that set.

58
00:02:18,860 --> 00:02:21,760
It's also true if you are caching for a session.

59
00:02:21,760 --> 00:02:24,140
You need to make sure that you include this line

60
00:02:24,140 --> 00:02:26,620
if you are caching for a session.

61
00:02:26,620 --> 00:02:29,783
So from a syntax standpoint, very, very simple.

62
00:02:32,230 --> 00:02:34,050
Now, cache management.

63
00:02:34,050 --> 00:02:36,910
When can your cache result be reused?

64
00:02:36,910 --> 00:02:40,010
Well, you first have to have access to the tables.

65
00:02:40,010 --> 00:02:41,030
Makes sense.

66
00:02:41,030 --> 00:02:42,840
But if you don't have access to the tables,

67
00:02:42,840 --> 00:02:44,620
it's not going to cache them.

68
00:02:44,620 --> 00:02:47,230
Next, you have to have an exact match

69
00:02:47,230 --> 00:02:49,240
between the old and the new query.

70
00:02:49,240 --> 00:02:53,250
Again, we are storing so that we can pull data

71
00:02:53,250 --> 00:02:54,700
for queries faster.

72
00:02:54,700 --> 00:02:56,330
It's not going to pull all the data.

73
00:02:56,330 --> 00:02:58,350
It's going to pull it for that specific query.

74
00:02:58,350 --> 00:03:00,140
So your query has to be the same

75
00:03:00,140 --> 00:03:03,130
in order for those cache results to be used.

76
00:03:03,130 --> 00:03:05,290
You also can't have any schema changes.

77
00:03:05,290 --> 00:03:09,050
If you change schema, that's also going to invalidate

78
00:03:09,050 --> 00:03:10,900
or stop your cache from being pulled.

79
00:03:11,760 --> 00:03:15,870
Cache is going to be dumped every 48 hours

80
00:03:15,870 --> 00:03:19,290
if you haven't ran a query that uses that cache.

81
00:03:19,290 --> 00:03:22,490
Also, if your cache approaches a terabyte in size,

82
00:03:22,490 --> 00:03:25,920
it's also going to dump the cache there as well.

83
00:03:25,920 --> 00:03:27,740
So just a couple of key points to remember

84
00:03:27,740 --> 00:03:29,530
as we wrap up this lesson.

85
00:03:29,530 --> 00:03:32,470
So syntax, we looked at Synapse.

86
00:03:32,470 --> 00:03:33,340
You need to make sure

87
00:03:33,340 --> 00:03:35,920
that you remember we are talking about Synapse,

88
00:03:35,920 --> 00:03:38,360
but this also can pertain to SQL Server,

89
00:03:38,360 --> 00:03:41,090
Managed Instance, and SQL DB.

90
00:03:41,090 --> 00:03:43,480
So you could use that in any of those places.

91
00:03:43,480 --> 00:03:45,760
However, those aren't on the DP-203,

92
00:03:45,760 --> 00:03:47,993
so we're talking about Synapse.

93
00:03:48,960 --> 00:03:49,830
Shuffling.

94
00:03:49,830 --> 00:03:52,190
So, shuffling has a large effect on performance.

95
00:03:52,190 --> 00:03:53,023
And in this course,

96
00:03:53,023 --> 00:03:55,020
we've talked about shuffling in data distribution

97
00:03:55,020 --> 00:03:56,140
quite a lot.

98
00:03:56,140 --> 00:03:59,510
Cache is very helpful in this regard, because again,

99
00:03:59,510 --> 00:04:01,700
it can help you to speed your queries

100
00:04:01,700 --> 00:04:04,640
and it's going to reduce your overall cost.

101
00:04:04,640 --> 00:04:06,150
However, you need to make sure

102
00:04:06,150 --> 00:04:08,420
that you're maintaining your cache,

103
00:04:08,420 --> 00:04:09,900
and that you've properly set it up

104
00:04:09,900 --> 00:04:11,930
for the queries that you want to run.

105
00:04:11,930 --> 00:04:14,260
All right, that's it for this lesson.

106
00:04:14,260 --> 00:04:15,510
I'll see you in the next.