1
00:00:00,060 --> 00:00:07,650
‫So, guys, we always talk about how it's a bad idea to work with a large set of ResultSet, so if you

2
00:00:07,650 --> 00:00:14,160
‫have a huge table like this example, we have a student grades and they are like, what, 12 million

3
00:00:14,370 --> 00:00:15,440
‫students here?

4
00:00:15,930 --> 00:00:22,550
‫Even if you add some sort of filters, let's say, hey, give me all the students that got grades between

5
00:00:22,640 --> 00:00:24,060
‫1990 and 100.

6
00:00:24,690 --> 00:00:27,330
‫And if you do that, yeah, you have a smaller results.

7
00:00:27,360 --> 00:00:29,270
‫But still, it's a million students.

8
00:00:29,580 --> 00:00:33,030
‫Sometimes there is no option but to work with the larger data sets.

9
00:00:33,040 --> 00:00:40,890
‫So if you actually execute, select the student ID from grades and you say, hey, give me all the students,

10
00:00:40,890 --> 00:00:41,520
‫you can do that.

11
00:00:41,520 --> 00:00:42,220
‫Definitely right.

12
00:00:42,240 --> 00:00:49,770
‫So you can say give me all the student IDs that God results from 90 to 100, and you can definitely

13
00:00:49,770 --> 00:00:50,150
‫do that.

14
00:00:50,430 --> 00:00:53,210
‫But good luck executing that query.

15
00:00:53,220 --> 00:01:01,620
‫And even if I execute, the database is doing a lot of work to pull, first of all, to do the execution

16
00:01:01,620 --> 00:01:04,320
‫plan to find out which indexes to use.

17
00:01:04,320 --> 00:01:04,500
‫Right.

18
00:01:04,860 --> 00:01:08,610
‫And then after that, it does the actual fishing.

19
00:01:08,890 --> 00:01:11,100
‫Then it compiles the results.

20
00:01:11,100 --> 00:01:19,920
‫And then if you're in another client machine, then that transmits that results into the Tsipi protocol,

21
00:01:20,130 --> 00:01:22,920
‫depends on the database protocol.

22
00:01:23,250 --> 00:01:25,110
‫It will shove that into the network.

23
00:01:25,320 --> 00:01:27,390
‫And that is a lot of work.

24
00:01:27,390 --> 00:01:32,030
‫And then the client have to wait for all the results to come back.

25
00:01:32,280 --> 00:01:33,780
‫Obviously, I consulted by him.

26
00:01:34,050 --> 00:01:40,950
‫Once you have all the results in the client, then the client have to have the memory to store all those

27
00:01:40,950 --> 00:01:41,480
‫results.

28
00:01:41,700 --> 00:01:45,930
‫So that is absolutely sometimes impossible to do.

29
00:01:46,350 --> 00:01:52,980
‫Meet Database Kershaw's and I'm going to talk specifically about server side cursors, cursors that

30
00:01:52,980 --> 00:01:56,940
‫you create on the database and says, hey, database, please.

31
00:01:57,690 --> 00:02:01,530
‫I'm about to fetch this result, but don't give me the result right now.

32
00:02:01,530 --> 00:02:02,700
‫Create a cursor.

33
00:02:02,910 --> 00:02:07,800
‫And then once you create that cursor, I'm going to ask you to fetch results from that Gursel.

34
00:02:07,980 --> 00:02:16,800
‫And only when I do that, you go and do the work, follow your plan, query you or do your index only

35
00:02:16,800 --> 00:02:26,220
‫scan or your bitmap index scan or whatever full table scan if you didn't have any indexes and then go

36
00:02:26,220 --> 00:02:30,630
‫and fetch those rolls for me and then return just the ones I asked for.

37
00:02:30,750 --> 00:02:31,350
‫How do we do that?

38
00:02:31,630 --> 00:02:32,730
‫Let's explain how to do that.

39
00:02:32,850 --> 00:02:34,520
‫It's very, very simple.

40
00:02:34,560 --> 00:02:40,230
‫First of all, Courcelles have to work within a transaction so we could do a begin transaction or just

41
00:02:40,230 --> 00:02:51,330
‫begin that does a transaction and then you can say declare cursor see as type cursor for select star.

42
00:02:52,340 --> 00:02:59,570
‫I just let's listen to the idea from grades where the grade is between eight and nine to one hundred,

43
00:02:59,570 --> 00:03:03,570
‫and then we need to do this as I declare the cursor it's good to go.

44
00:03:03,830 --> 00:03:06,910
‫You saw that they didn't actually execute the query.

45
00:03:06,920 --> 00:03:09,890
‫Technically, it just came up with the map.

46
00:03:10,040 --> 00:03:14,630
‫It came up with the plan to actually plan this query.

47
00:03:14,810 --> 00:03:17,130
‫However, they didn't execute it.

48
00:03:17,420 --> 00:03:28,550
‫So now if I do fish, see, that act will fetch the first row based on the query plan that have taken

49
00:03:28,550 --> 00:03:28,860
‫place.

50
00:03:28,880 --> 00:03:34,910
‫So if I do, then give me the first row, which C give me the second row and so on.

51
00:03:35,120 --> 00:03:41,030
‫You can go all one by one and you can play with this as you want.

52
00:03:41,630 --> 00:03:46,370
‫And you can, for example, there are many, many things that you can do, like, for example, Furch

53
00:03:46,640 --> 00:03:52,820
‫last give me the last entry for this for some reason, and you can give me the last entry and then you

54
00:03:52,820 --> 00:03:55,000
‫can see that the database is doing more work here.

55
00:03:55,040 --> 00:03:57,650
‫Is it OK to finish the last entry?

56
00:03:57,650 --> 00:04:02,760
‫I have to kind of scan through all of that stuff to determine what is the last entry, right.

57
00:04:02,980 --> 00:04:04,710
‫To pull that last entry.

58
00:04:04,940 --> 00:04:11,200
‫Sometimes the neighbors have knowledge of how to obtain the last entry in a very efficient manner.

59
00:04:11,420 --> 00:04:14,000
‫Sometimes it actually doesn't.

60
00:04:14,240 --> 00:04:20,840
‫And that's when that's when index backwords versus an index can become really, really useful.

61
00:04:21,410 --> 00:04:23,540
‫So, guys, what is the advantages?

62
00:04:24,720 --> 00:04:27,570
‫And disadvantages, what is the pros?

63
00:04:29,330 --> 00:04:38,030
‫Of cursors, so the beauty of curser is you save on memory and the client side, at least in your backend

64
00:04:38,030 --> 00:04:41,250
‫application, that connects to the database, right.

65
00:04:41,320 --> 00:04:47,220
‫So if you're if you have a been up by an application or JavaScript or go application, you want you

66
00:04:47,220 --> 00:04:48,700
‫want to process a lot of results.

67
00:04:48,950 --> 00:04:50,660
‫But do you want to do them piecemeal?

68
00:04:50,690 --> 00:04:52,330
‫You want to work with them slowly.

69
00:04:52,520 --> 00:04:58,610
‫You can open a cursor and then start pulling a number of arrows like, say, one hundred, let's start

70
00:04:58,610 --> 00:05:03,290
‫working on those hundred and then go and move to the next one, then do whatever you want to do.

71
00:05:03,290 --> 00:05:03,710
‫One hundred.

72
00:05:03,710 --> 00:05:03,960
‫Right.

73
00:05:04,220 --> 00:05:08,690
‫So you obviously and then discard the memory for the one hundred and then fix the next one.

74
00:05:09,110 --> 00:05:11,900
‫So it definitely saves a lot of memory, especially in the client side.

75
00:05:12,320 --> 00:05:15,620
‫Otherwise you cannot pull a hundred million rows in the memory.

76
00:05:15,620 --> 00:05:15,830
‫Right.

77
00:05:16,100 --> 00:05:24,840
‫That's why I always get confused of these questions in interviews like sort this error and I don't know,

78
00:05:24,860 --> 00:05:26,480
‫it has like a one billion road.

79
00:05:26,720 --> 00:05:27,380
‫It's like what?

80
00:05:27,380 --> 00:05:30,830
‫Why would where did a billion came into my memory to?

81
00:05:30,830 --> 00:05:32,900
‫Sort of that's a bad idea, essentially.

82
00:05:32,900 --> 00:05:33,150
‫Right.

83
00:05:33,440 --> 00:05:39,050
‫So I always, like, ask the interviewer because like, OK, where are these actual values or are they

84
00:05:39,050 --> 00:05:39,950
‫in a database somewhere?

85
00:05:39,950 --> 00:05:42,830
‫Are they did they came up from thin air?

86
00:05:42,860 --> 00:05:43,630
‫Of course not.

87
00:05:43,640 --> 00:05:43,900
‫Right.

88
00:05:44,150 --> 00:05:49,700
‫So if I can sort them while I pull them up, that's probably a better idea.

89
00:05:49,700 --> 00:05:50,030
‫Right.

90
00:05:50,240 --> 00:05:53,630
‫Then pull all of them in memory and then sold them in memory.

91
00:05:53,900 --> 00:05:56,510
‫So be careful from this question.

92
00:05:56,610 --> 00:06:01,130
‫You can actually flip the interview to the interviewer when you when you when you know, what are you

93
00:06:01,140 --> 00:06:01,680
‫talking about?

94
00:06:02,150 --> 00:06:05,660
‫So the second probe is streaming.

95
00:06:05,660 --> 00:06:08,850
‫Guys, streaming is a good idea, right?

96
00:06:08,870 --> 00:06:15,170
‫When you want to pull the rows and as you continue pulling the rows, you can stream them to another

97
00:06:15,500 --> 00:06:16,790
‫WebSocket connection.

98
00:06:16,790 --> 00:06:22,640
‫You can stream into a bunch of groups, you connection, and you can just continue pulling values.

99
00:06:23,270 --> 00:06:24,730
‫It can also be cancelled.

100
00:06:24,740 --> 00:06:25,100
‫Right.

101
00:06:25,190 --> 00:06:28,610
‫You can easily cancel a cursor.

102
00:06:28,760 --> 00:06:34,220
‫And the beauty of this is like, hey, I am you know, I know I'm about to pull a million real, but

103
00:06:34,250 --> 00:06:36,140
‫I only worked with one hundred thousand.

104
00:06:36,140 --> 00:06:37,370
‫I'm good counsel.

105
00:06:37,370 --> 00:06:39,560
‫That query roll back.

106
00:06:39,560 --> 00:06:40,040
‫I'm good.

107
00:06:40,040 --> 00:06:41,300
‫Now go move on.

108
00:06:41,990 --> 00:06:43,250
‫So counseling is a good thing.

109
00:06:43,460 --> 00:06:45,160
‫Paging is another good idea.

110
00:06:45,200 --> 00:06:45,500
‫Right.

111
00:06:45,510 --> 00:06:47,180
‫You can page with cursors.

112
00:06:47,510 --> 00:06:52,160
‫However, Will will learn that it's actually it's not as easy to page.

113
00:06:52,190 --> 00:06:56,630
‫You know what that application with courses because Garçons is stateful will come to that also can be

114
00:06:56,630 --> 00:07:02,570
‫used in the store procedure to write Playskool programming like a fully fledged programming languages

115
00:07:02,570 --> 00:07:03,310
‫with cursors.

116
00:07:03,320 --> 00:07:03,610
‫All right.

117
00:07:03,770 --> 00:07:05,720
‫So how about talking about McCown's?

118
00:07:05,720 --> 00:07:06,620
‫What's bad about this?

119
00:07:07,040 --> 00:07:09,770
‫The bad about this is it's actually essentially a stateful.

120
00:07:10,940 --> 00:07:11,810
‫It's a stateful.

121
00:07:12,110 --> 00:07:17,600
‫That means there is a memory allocated for it in the database.

122
00:07:17,600 --> 00:07:22,070
‫So and there is a corresponding transaction that points to that cursor.

123
00:07:22,310 --> 00:07:29,120
‫That means if you made another request to another server, to another process, that that process has

124
00:07:29,120 --> 00:07:30,680
‫no idea of your cursor.

125
00:07:31,190 --> 00:07:36,180
‫You cannot share cursors essentially, because that's that's just a property of the transaction.

126
00:07:36,440 --> 00:07:39,230
‫So stateful, this is a double edged sword.

127
00:07:39,230 --> 00:07:41,660
‫Sometimes it's an advantage, sometimes at a disadvantage.

128
00:07:41,660 --> 00:07:45,200
‫If you want to horizontally skell, obviously you cannot horizontally scale.

129
00:07:45,530 --> 00:07:50,000
‫You can do some tricks with proxy's if you know what you are doing.

130
00:07:50,000 --> 00:07:57,560
‫If you're savage, dev, op engineer, you can you can make your Web application, understand that there

131
00:07:57,560 --> 00:08:04,940
‫is a cursor level and send some sort of a variable that will take you back to that not only through

132
00:08:04,940 --> 00:08:08,440
‫the same server, but to the same transaction.

133
00:08:08,840 --> 00:08:15,560
‫However, that is extremely difficult to pull up once if you really wanted to do that and you can do

134
00:08:15,560 --> 00:08:22,290
‫paging with that approach, well, that is much, much better way of the kind of compared to the current

135
00:08:22,340 --> 00:08:27,920
‫paging that we do, which is essentially a stateless paging every request as goes onto another server.

136
00:08:28,070 --> 00:08:32,740
‫And that server does the Q&A again, which is painful to the neighbors.

137
00:08:32,750 --> 00:08:38,900
‫It's an insult to the database to execute the same query twice, executing ones and then loop through

138
00:08:38,900 --> 00:08:39,680
‫it with a cursor.

139
00:08:39,890 --> 00:08:40,580
‫That's even better.

140
00:08:40,580 --> 00:08:41,850
‫But sometimes we cannot.

141
00:08:41,870 --> 00:08:44,600
‫Obviously, sometimes it doesn't work that way.

142
00:08:44,960 --> 00:08:47,660
‫And another Konz is long running projections, guys.

143
00:08:48,260 --> 00:08:50,660
‫Cursors if you want to automate.

144
00:08:50,750 --> 00:08:56,330
‫So a cursor you have to do it through a transaction and that means your transaction is going to be running

145
00:08:56,330 --> 00:08:57,500
‫for a long time.

146
00:08:57,710 --> 00:09:01,730
‫And transactions running for a long time is is not a good idea for the databases.

147
00:09:02,000 --> 00:09:07,850
‫Databases cannot do indexing properly if you cannot do DDL on the table.

148
00:09:07,850 --> 00:09:14,960
‫If someone is connected right, you essentially stop people from doing normal work.

149
00:09:14,960 --> 00:09:20,840
‫Some some right operations can be stopped by long running transactions if you have acquired certain

150
00:09:20,840 --> 00:09:21,530
‫shared look.

151
00:09:21,530 --> 00:09:21,930
‫Right.

152
00:09:22,370 --> 00:09:28,100
‫All right, guys, that's it for me to do that was the pros and cons of database coursers demonstrated.

153
00:09:28,190 --> 00:09:31,520
‫In both cases, I'm going to see in the next show, you guys stay on the biol.