‫So, guys, we always talk about how it's a bad idea to work with a large set of ResultSet, so if you
‫have a huge table like this example, we have a student grades and they are like, what, 12 million
‫students here?
‫Even if you add some sort of filters, let's say, hey, give me all the students that got grades between
‫1990 and 100.
‫And if you do that, yeah, you have a smaller results.
‫But still, it's a million students.
‫Sometimes there is no option but to work with the larger data sets.
‫So if you actually execute, select the student ID from grades and you say, hey, give me all the students,
‫you can do that.
‫Definitely right.
‫So you can say give me all the student IDs that God results from 90 to 100, and you can definitely
‫do that.
‫But good luck executing that query.
‫And even if I execute, the database is doing a lot of work to pull, first of all, to do the execution
‫plan to find out which indexes to use.
‫Right.
‫And then after that, it does the actual fishing.
‫Then it compiles the results.
‫And then if you're in another client machine, then that transmits that results into the Tsipi protocol,
‫depends on the database protocol.
‫It will shove that into the network.
‫And that is a lot of work.
‫And then the client have to wait for all the results to come back.
‫Obviously, I consulted by him.
‫Once you have all the results in the client, then the client have to have the memory to store all those
‫results.
‫So that is absolutely sometimes impossible to do.
‫Meet Database Kershaw's and I'm going to talk specifically about server side cursors, cursors that
‫you create on the database and says, hey, database, please.
‫I'm about to fetch this result, but don't give me the result right now.
‫Create a cursor.
‫And then once you create that cursor, I'm going to ask you to fetch results from that Gursel.
‫And only when I do that, you go and do the work, follow your plan, query you or do your index only
‫scan or your bitmap index scan or whatever full table scan if you didn't have any indexes and then go
‫and fetch those rolls for me and then return just the ones I asked for.
‫How do we do that?
‫Let's explain how to do that.
‫It's very, very simple.
‫First of all, Courcelles have to work within a transaction so we could do a begin transaction or just
‫begin that does a transaction and then you can say declare cursor see as type cursor for select star.
‫I just let's listen to the idea from grades where the grade is between eight and nine to one hundred,
‫and then we need to do this as I declare the cursor it's good to go.
‫You saw that they didn't actually execute the query.
‫Technically, it just came up with the map.
‫It came up with the plan to actually plan this query.
‫However, they didn't execute it.
‫So now if I do fish, see, that act will fetch the first row based on the query plan that have taken
‫place.
‫So if I do, then give me the first row, which C give me the second row and so on.
‫You can go all one by one and you can play with this as you want.
‫And you can, for example, there are many, many things that you can do, like, for example, Furch
‫last give me the last entry for this for some reason, and you can give me the last entry and then you
‫can see that the database is doing more work here.
‫Is it OK to finish the last entry?
‫I have to kind of scan through all of that stuff to determine what is the last entry, right.
‫To pull that last entry.
‫Sometimes the neighbors have knowledge of how to obtain the last entry in a very efficient manner.
‫Sometimes it actually doesn't.
‫And that's when that's when index backwords versus an index can become really, really useful.
‫So, guys, what is the advantages?
‫And disadvantages, what is the pros?
‫Of cursors, so the beauty of curser is you save on memory and the client side, at least in your backend
‫application, that connects to the database, right.
‫So if you're if you have a been up by an application or JavaScript or go application, you want you
‫want to process a lot of results.
‫But do you want to do them piecemeal?
‫You want to work with them slowly.
‫You can open a cursor and then start pulling a number of arrows like, say, one hundred, let's start
‫working on those hundred and then go and move to the next one, then do whatever you want to do.
‫One hundred.
‫Right.
‫So you obviously and then discard the memory for the one hundred and then fix the next one.
‫So it definitely saves a lot of memory, especially in the client side.
‫Otherwise you cannot pull a hundred million rows in the memory.
‫Right.
‫That's why I always get confused of these questions in interviews like sort this error and I don't know,
‫it has like a one billion road.
‫It's like what?
‫Why would where did a billion came into my memory to?
‫Sort of that's a bad idea, essentially.
‫Right.
‫So I always, like, ask the interviewer because like, OK, where are these actual values or are they
‫in a database somewhere?
‫Are they did they came up from thin air?
‫Of course not.
‫Right.
‫So if I can sort them while I pull them up, that's probably a better idea.
‫Right.
‫Then pull all of them in memory and then sold them in memory.
‫So be careful from this question.
‫You can actually flip the interview to the interviewer when you when you when you know, what are you
‫talking about?
‫So the second probe is streaming.
‫Guys, streaming is a good idea, right?
‫When you want to pull the rows and as you continue pulling the rows, you can stream them to another
‫WebSocket connection.
‫You can stream into a bunch of groups, you connection, and you can just continue pulling values.
‫It can also be cancelled.
‫Right.
‫You can easily cancel a cursor.
‫And the beauty of this is like, hey, I am you know, I know I'm about to pull a million real, but
‫I only worked with one hundred thousand.
‫I'm good counsel.
‫That query roll back.
‫I'm good.
‫Now go move on.
‫So counseling is a good thing.
‫Paging is another good idea.
‫Right.
‫You can page with cursors.
‫However, Will will learn that it's actually it's not as easy to page.
‫You know what that application with courses because Garçons is stateful will come to that also can be
‫used in the store procedure to write Playskool programming like a fully fledged programming languages
‫with cursors.
‫All right.
‫So how about talking about McCown's?
‫What's bad about this?
‫The bad about this is it's actually essentially a stateful.
‫It's a stateful.
‫That means there is a memory allocated for it in the database.
‫So and there is a corresponding transaction that points to that cursor.
‫That means if you made another request to another server, to another process, that that process has
‫no idea of your cursor.
‫You cannot share cursors essentially, because that's that's just a property of the transaction.
‫So stateful, this is a double edged sword.
‫Sometimes it's an advantage, sometimes at a disadvantage.
‫If you want to horizontally skell, obviously you cannot horizontally scale.
‫You can do some tricks with proxy's if you know what you are doing.
‫If you're savage, dev, op engineer, you can you can make your Web application, understand that there
‫is a cursor level and send some sort of a variable that will take you back to that not only through
‫the same server, but to the same transaction.
‫However, that is extremely difficult to pull up once if you really wanted to do that and you can do
‫paging with that approach, well, that is much, much better way of the kind of compared to the current
‫paging that we do, which is essentially a stateless paging every request as goes onto another server.
‫And that server does the Q&A again, which is painful to the neighbors.
‫It's an insult to the database to execute the same query twice, executing ones and then loop through
‫it with a cursor.
‫That's even better.
‫But sometimes we cannot.
‫Obviously, sometimes it doesn't work that way.
‫And another Konz is long running projections, guys.
‫Cursors if you want to automate.
‫So a cursor you have to do it through a transaction and that means your transaction is going to be running
‫for a long time.
‫And transactions running for a long time is is not a good idea for the databases.
‫Databases cannot do indexing properly if you cannot do DDL on the table.
‫If someone is connected right, you essentially stop people from doing normal work.
‫Some some right operations can be stopped by long running transactions if you have acquired certain
‫shared look.
‫Right.
‫All right, guys, that's it for me to do that was the pros and cons of database coursers demonstrated.
‫In both cases, I'm going to see in the next show, you guys stay on the biol.