How SQL Database Engines Work

hello everybody i'm richard and i have 85 slides to go through 45 minutes so i'm going to start punctually thank you i am i was given the task of talking on how sql database engines work in general which is a a daunting task in this much that i don't really know i only know how sqlite works which is the one i wrote and about the others i'm pretty much guessing based on advice from gm and ann and bruce and brian and and and others but i'm going to give you my best go and and we're going to try and go through this the target audience here is people who uh use sql sql in their their daily work and they've been using it for years and what i've found is that when i go out and give talks to people is there's a lot of people that know so much about sql but the database engine itself is a black box to them they don't understand what is going on internally they know they need to create indices they don't really understand what the indices are doing and why they're important and a lot of people have talked to they don't even really understand that sql is a programming language so i'm going to go over all these concepts and even if you already know all this stuff hopefully some of the diagrams i'll show you will give you a new insight a new way of looking at things and uh give you help you to better understand and be able to use sql better and i'm hoping that the experts here will correct me when i go wrong now i do have a lot of slides so pay attention fasten your seat belts and hang with me we're going to be drinking from a fire hose key concepts the sql people say is a query language it's really a programming language it's a peculiar programming language though because instead of describing what you're going to do like you would to do in c or instead of describing how you're going to do it you tell it what you want to do and then you let the compiler figure out how to go about doing it so really an sql database engine consists of two parts there's a compiler that takes your program which is a statement of sql and compiles it down into procedures they're going to be run and then there's a virtual machine underneath that actually runs the procedures so for example if you have a simple query like select everything from table 1 this might get translated into a program that would look something like this you would open the database file that contains the table and you rewind to the beginning of the file and then you'd start looping through the file reading out columns and returning them to the user until you reach the end of the file and then you close the file and you say well that's really simple i could have done that myself and a few lines of c code and you're probably right but the usefulness of sql comes into play when you get more complex queries and there are you know perhaps a few thousand ways you might have implemented something like this and you really don't want to take the time to write the several thousand lines of c code that would take to do this manually nor do you want to have to worry about all the different algorithms that you might employ and then get close to delivery and discover that you want to rewrite it with a different algorithm and spend a couple of weeks redoing it so the beauty of sql is it does this for you automatically a few lines of sql will automatically generate a ton of procedural code for you and and and simply by adding index to your schema you can completely select it you can select a completely different set of algorithms the the task of selecting which algorithm to use to access your data is the job of the query optimizer and so that's where all the voodoo is happening so here is kind of a diagram that's showing the ins and outs of an sql database engine you've got some sql statements that are going in and this goes into a compiler it compiles sql into some procedures and that generates a prepared statement the prepared statement is really a computer program and sort of a virtual language and then this gets sent into the execution engine which actually goes and does all the actual querying and returns the results to you so the the first part there the front half is the compiler and this is basically the same techniques that were developed in the 70s and are well documented in the dragon book there's there's nothing really extra new going on there and then in the middle the prepared statement is the program that's going to run now uh i think in all of the database engines that are represented by developers here except for mine sqli everybody uses for the prepared statement of structure and c or a tree of structures um is there question corrections on this jim are there corrections on on that now sqli actually generates by code which more closely resembles uh what you would find in uh java or or usb uh past p p code for pascal or our parent it generates by code and you can actually get kind of an assembly language listing of this bytecode when you use explain but you know what's the same idea behind them all i actually clarified sure when i said i used a tree of structures um i'm speaking um firebird falcon nimbus not mysql mysql uses a forest of good flags okay well yeah now there was you know and i can't remember the name but there is one there was a um a database at one point that actually generated native uh assembly language for our native machine code for 8086 and so yeah that was that was kind of weird because you could you could do that but it doesn't really buy you anything in speed and it it causes portability problems so anyway you get your prepared state and you send it into a virtual machine so here's the architecture of sqlite and i'm showing you this just to kind of uh give you some more detailed views of things the first three things up on the top of the sqlite structure diagram well that's just the dragon book right that's the compiler and stuff i'm not really going to say any more about that other than to tell you that it's equivalent to my original diagram and then the bottom part is the part that runs the program and i want to kind of break that out a little bit now in sqlite the virtual machine because sqlite uses bytecode the virtual machine consists of a big loop with a switch statement and it slid switches on the op codes and it runs the various op codes in turn now as i said other most other database engines actually use a structure or trio structures and when they their virtual machine just actually walks the structures um but it varies from place to place now sqlite uses exclusively now underneath the virtual machine is your your layer that's going to be doing the database access and sqlite we're only doing b trees because sqlite is supposed to be small uh most of the other big engines you have b trees and you have hash tables and you have our trees and and other schemes i mean there's often a a selection of things that you can do here let's just for right now let's just assume that they're going to be b trees all the same principles apply b trees are well documented by knew and in my volume of canoe that's volume 2 but he's renumbered the volumes now volume three starting searching and sorting um and then below that there's a layer that's gonna handle your your uh your cache of your database and the atomic commit stuff and sqlite has an os portability layer because it has to run on lots of strange things like ipods and cell phones and where the operating system is not unix and so there needs to be some portability factor in there let me go back and point out that in like mysql the bottom three layers here the b tree the pager and the os that that whole assembly is what gets plugged in and out when you use a different back-end from isql and there's what 55 of those now i you know who who's counting um then in postgres for example they've got the bee tree engine they've got hashing they've got archery maybe there's some others firebird what does it use all is 100 detriment for everything what about for the main tables does it just use file offsets or does it use b trees to find interest there well i'll clarify that in a second okay so when you create a table in sql you generally get something on disk that looks approximately like this here i've got a table with three columns and um in the in the database file itself you've got the the data stored in the three columns shown in blue but you've also got a unique key on every row in the database file now i'm calling it row id here because that's what sqlite uses uh postgres calls it a c tid i think a ct id what is it in firebird number a record number and an sql exposes the variety you can always use it you can set it and in fact in sqlite the row ids are numbered sequentially one two three four five in this example i i've put non-sequential numbers to kind of give you the idea that this might be just a file offset to find the the record id but it varies from system to system but the concept is generally always there now so so we've got the key on the on the left and the data on the right the key has to be unique the data doesn't necessarily have to be the key has to be unique so if you do a query like this where you want to get the the price of peaches one way to find that answer is to start at the beginning of the table loop through record by record looking for cases where the column fruit is equal to peach and then when you find one it just goes over and pulls the unit price and spits it out that's a full table scan if your table only has seven entries in it this is fine you know actually if your table only has seven entries anything you do is going to be plenty fast but if you have 700 million entries this is going to take longer and so this doesn't really work so well well i know what one way you could do it faster at least in sqli is that you could query by row id so in sqlite you could say select the enterprise from the table or the row id is equal equal to 93. and in that case sqlite would do a binary search find the row and spit the thing out and it could do that very quickly regardless of how many numbers now on other database engines uh which would use a file offset it didn't even have to do the binary search you could just seek immediately to that row and spit the number out for you is the row id accessible in firebird i don't know the rule id is accessible it's called an rdbd dollar tv key yeah when you're actually referencing it can you use it in a query this way it's set by the system you can use it in a query it's not guaranteed stable across transactions okay so so we can shift across transactions and firebirds it probably would not be a good idea to do this quickly there actually it is stable but we say it's not what i mean is that it can be reused if the record is deleted and right right yeah um but you could do this it actually is if you can't do it by that number that number represents um a a of the file number the offset in the file and the record much more complicated two level index yeah so it's it's it's kind of complicated and but the really key point is here nobody really cares what the unit price is for railway 93 i mean that is not something that an end user cares about you want to know what the unit price is for peaches so for this we need an index and an index typically looks something like this thing over here on the left index one where we're indexing the fruit column and so what happens is it builds kind of a separate table often in a separate file and it puts all of the values for fruit there in in order and it also records their row id so there's a the key points remember there's a one-to-one mapping between entities in the index and the main table and the thing that you're indexing becomes the first column of the table and you also add this extra column which is the row id and then the combination of the thing you're indexing in the row editing has to be unique and that's never a problem because the row robotic is always going to be unique so now what happens when you want to get the unit price where fruit is equal to peach is that you can do a binary search to find the unit the entry where where fruit is equal to peach and then you can look up the that gives you the row id and once you have the row id you can use the row id to look into your main table to figure out what the unit price is and so this is the basic idea behind indexing and this this works even if you have multiple terms in your where clause so here's an example as we want to find the unit price of all the of all california oranges we want to find the unit price of california oranges all right so we've got this table on on fruit and so we could use that first term of the where clause fruit equals orange and so we know that fruit has to be equal to orange we'll use their table and we'll do a binary search for orange in our index and there's actually two entries where where the fruit is equal to orange and then we'll take each one of these row ids and we'll use that to look up into our main table and that the first one is row id zero so it looks that up in the main table and says oh well the gronin is now florida and that doesn't that doesn't work so we have to kind of reject that path but on the second one we it is californian so we can output the result and so this is how you would do a lookup if you had two terms in the where clause of course you might have also had another index called say index two on the grown in column and it works just like index one except for here instead of being an index on fruit it's an index on grown in and now we have the grown in fields sorted and we could use that with this exactly the same query and it would use the grown in to look up their two entries in the groningen index but once again we're getting two entries out and one of them really isn't successful it blocks when it goes down to the main table and you're only getting one result so this suggests we'll maybe we'll create an index with two columns now so let's talk about a multi-column index and see how that works i feel like i'm going really really fast what time is it um yeah i'm gonna finish that's okay we can ask questions i shouldn't have deleted quite so many slides this morning when i was pruning my tall the uh so here's here's index this should say index three that's here on the slide we've got two columns on the index now and the way this works is it just creates an index where it has the two columns that you've named in the order that you've named them and the we sort primarily by the first column and then in the case of a tie we use the second column to sort the only in this sample set the only case where we have a tie is on oranges and there we see that california comes first so now when we want to find the unit price for california oranges we can do a binary search on the two column index find the one entry where they both match use the row id there to go look in the main table and from the main table pull out the unit price and so this is a little bit faster than the other approach now i talked about four ways to get the answer on that one query so far there's a few more coming up let's just do a quick review though we can do one we can do a full table scan which works great on a seven table entry but a seven entry table but is a little bit slow on a 700 million entry table and we could use our single column index on one term of our where clause and that works okay but then we're still pulling in some rows and having to check them in cases where um the other term of the where clause didn't work out or we could do a single column index on the other term of the where clause and that also works but once again we're we're failing some cases where the other term of our where clause didn't work out or we could use a two column index on both terms of the where clause and you can get right to the answer you need right away and that is generally a little bit faster well what would happen do you suppose if we had a three column index here i've got an index for fruit grown in and unit price and as you've seen before the index is built just by copying the values of the individual fields into the index in sorted order sorted according to the order the indexes appear now in this case if we do our query here's example number five it does the binary search for cal for oranges in california but it doesn't have to do the additional lookup over into the main table in order to get the data the data sitting right there in the index and it can output it straight away now if you were at the last talk in this room greg mentioned that this doesn't work for us for postgres because in postgres the visibility information is in the main table and so you don't know if this row is visible or not you still have to go do a lookup in the main table in order to get the visibility information but and i guess the uh is greg still here yeah there you are so i guess when you were talking about your you're moving toward heavy indices you would put the visibility information in the index and then then postgres be able to do this right that's what you were saying um for what it's worth fireworks like postgres the falcon engine in mysql is like current postgres a firebird maria has heavy indexes by the fact okay cool now i understand the reason falcon won't do this or at least brian was telling me the reason falcon won't do this is that the indices are like prefix indices they don't contain a complete copy of the actual data so now you know it's useful to think about these these these white indices all the same though and notice that for example you don't have to use every column in the index here we're just want to get the unit price for oranges and i made this slide this morning before coffee and i see immediately that there's a mistake in it and that it should actually be pulling out two rows instead of one but you kind of get the idea so this also works you could do that sort of thing uh you could also do a thing here where um uh you're selecting say the middle column of the index and it's using the further column out to just do some additional pruning so really we're only using the fruit equals orange term of the where clause to do our lookup and then we're having to scan two entries and then we're we're also filtering based on unit price but we can do the filtering right there in the index we don't have to do this additional lookup over into the main table and in fact um postgres i'm told by bruce will do this very thing because to do the filtering on that first row of california oranges there because they're too expensive you don't need nvcc information in order to pull that off it's able to do that filtering and therefore eliminate one trip over to the main table does firebird do that no yes there is disagreement amongst the experts okay does it look up orange california does it look up both oranges in the data table okay we'll do some checking on firebird to figure out exactly what it does in a little bit so now we talked a little about searching yes jim tell us what our idea here um firebird and the internal falcon engine of might as well maybe in the future has another way of doing this type of query and that is i've got number six coming up do you want to hold the question level but for the for the time being you know you know i showed you a picture of canoes you know tone here earlier and you know the title of this one is actually searching and sorting and that's not really a coincidence because searching and sorting are have a lot in common you know you kind of think of them as separate things but they really have a lot in common and so the indices can come into play here too now if you're just doing a query like this select uh star from table order by fruit one way to do that is to walk through the entire table pull out all the records and run them through a sword and that works the sorter can be headed the sorter has to store copies of everything it has to do sorting and you see in my world of sqlite i'm running on cell phones and sorters take a lot of memory and cell phones don't have a lot of memory so this is a very bad problem for cell phones and ipods and stuff so we'd like to avoid it now you could say select star from table ordered by row id and then the query engine would know hey it's going to be coming out in row id order all by itself i don't need to run it through this order and this makes it run a lot faster this is linear time rather than log in and you could even say select star from tab order by row id decreasing and this is the same thing it just starts at the end of the table and moves forward rather than starting at the beginning of the table and moving backward but you know really nobody really cares anything about ordering things by row id that that's just not an interesting thing we really want to order by the fruit so one way you could do this is to use an index uh we could use the the index on fruit and we could start at the beginning of the index and just march down through that index and pull out row ids and then for every row id we could look up that row in the main table and go and then pull that row out and so we can go through and walk through and pull out everything in sorted order without having to build a temporary table to store the results and so it really takes about the same amount of time but it uses a lot less memory if you're on big iron you don't care if you're on a cell phone this is a huge win a huge win and of course you could do the same thing with a decreasing quarterback simply by starting at the end of the table and moving toward the beginning rather than starting at the beginning and moving toward the back now if you happen to have one of these uh indices that contains all of the terms in it and you're sorting by the first column of that index then this gets really simple you can just start at the beginning of the index and work your way forward and you know that everything's going to appear in sorted order by fruit walk down through the index pull your values out send them out to the user now in an mvcc system unless you've got the heavy indices you'd still have to make a little visit over to the main table to check your visibility information but i guess if you have the visibility the the heavy indices that's in the index already and you don't even have to do that so this is really fast way of getting an order by and notice that this same order by works if you're ordered by decreasing as well and the same order by works if you're ordering by the first two terms of the index or if you're ordering by the first three terms of the index it also works and it works if you're ordering by the first two terms decreasing or the first three terms decreasing all of this works it works great now but this doesn't work if if if you put the first term decreasing in the second descending in the first one ascending that doesn't work now you can declare your index such that it you know when you're saying create index you can say creating and you can put the descending and ascending keywords after each term in the index and if you declared it to be descending ascending then this would work but if you declared your index would both be ascending which is the default then this wouldn't work because things would appear out of order the other thing that wouldn't work is that if you skip a column in your index here we're trying to order by fruit and unit price and because the grohnian is in between there things would not come out in the right order if we just used our end index so we'd have to use one of the previous order by techniques and the other thing is that we can't use an index to sort if the thing we're sorting by is a middle term in the index it's got to be the leftmost term in the index in order for any of this to work now there are um some other tricks you can play though so here we've got a case where we want to get some stuff out of the table and we're restricting our fruit our research to where fruit is equal to orange but we're worried by the state that it's growing in well i said a minute ago that you can't do order by groningen and using this particular index because groningen is the second column it's not the left most column but we've constrained the leftmost column to be a particular value so in this case it works because the left because the leftmost column is constrained we know that everything is going to come out of the right order naturally and no sorting has to be done so here's a kind of a general rule and we go over this rule once a week or so i think on the sqlite mailing list so hopefully it'll be applicable to other data extension people as well when you're creating an index to serve a query you look at your where clause terms and the where clause terms go first in the index and then after that you put your order by terms now the where clause terms and the order by terms can overlap but there can't be any gaps you can't have an extra column in there and then finally over here on your result set terms you want to put all of them in the index too but there can be gaps there can be overlaps it doesn't really matter as long as they're all in the index okay question in the back yeah mysql if your uh your query doesn't contain i might maybe i have this your key card if you're only if you're using the first key part does it use that index does yours work differently like is this what you're saying right here yeah oh okay so it does continue yeah do you have the same situation yeah pretty much everybody works the same way here yeah so let's let's go down and you know all the examples i've given by giving to you now had a where clause constructed with amps what if you put an or in there now this this is a whole different universe this this is this is this is getting usually um you know the easy way to do it of course a full table scan always works right we're asking for all of everything the unit price of everything that's an orange or that's grown in california so we can do a full table skin of course and that still works full table skin is kind of it always works it's always slow though too so we like to avoid it the way this normally works when you have an ore is that you've got a separate index on the fruit and a separate index on the grown in and so we can look up all of the entries that are grown in california using the index up on top there and we get their row ids and then we look up all the uh things that are in orange and get their row ids on the bottom is we've got two sets of row ids then we have to take a union of those two sets and we get the end result as three different row ids and then we can use those row ids to go look up what we want in the table now there's a couple of ways actually there's quite a few different ways that we can do this now if you've got in this particular case we've got single column indices and we can stream those row light and the row ids are coming out of the index in sorted order so these row ids are coming out of each index sorted and so you can merge them together with no temporary storage always take the smallest draw id next and and merge them together and you don't have any temporary storage to kind of hold all this stuff together and that works really really well if like you're on a cell phone and you don't have any memory but usually what people do with this is they create something along the lines of a bitmap index where they uh for each of the two as a transient index they search each index and they have a bit mask and they set bits and maybe it's compressed somehow but they set bits for each entry that actually matches and then you end up with two bitmaps and then you just pour them together and then they walk the bitmap in order to get the entries in the table what does firebird do firework does that doesn't matter does it have that fours and ends does it do the merge thing yeah it doesn't i mean it creates two bitmaps and creates two bitmaps and does the merge um postgres i'm told doesn't it logically kind of does that but it really doesn't create two bitmaps it creates one bitmap and then modifies it according to the second index but same idea same idea now the merging thing works okay as long as you have single column indices but if if you had a two column index on fruit the row ids wouldn't come out in the right order and so you have to sort them in order to do the merge but of course the bitmap thing still works out okay now this is the typically the way you handle where clauses that are done with ores but you know what if that union were turned in book did i press the wrong button i did if you turn that union into an intersection you could also use this approach to do the ands that we're saying so here's here's method number six of solving our our query this the ongoing example for this talk um we just look up all instances of california and one debt index all instances of orange and the other and then you pick the entries that they have in common which in this case is number 204 and that immediately gives you your result now this works and it works great but as you can might imagine is it's going to be a little bit more work than if you have a two column index and it's certainly more work than if you had a covering index like this where you could just look it up without going to the table at all but the advantage is you don't need these multi-column indices you can have a smaller set of indices and you're still going to get reasonable performance it's not the peak performance but it still works pretty well and the other advantage is is that you can have really bizarre where clause things like this kind of arbitrary boolean expressions here i'm asking for the unit price of california oranges and anything grown in north carolina and so you can have a tree of intersections and unions on these bitmap indices that'll go in and pull out the data you want just like that i ran through this really fast what time is it 28 oh man i've got 10 minutes for questions so the key points that i wanted to bring across to you today are that sql is a programming language there is a compiler there is a virtual machine indices are not magic they have to make sense uh so often on the sqlite mailing list i don't know if this is happening on the mailing list for the other database entries or not people get on and say well i created a bunch of indices but my queries are real slow why um they're not magic they use the the query still has to make sense and the other thing is avoid creating too many indices in your in your schema because you know as as we've seen even for a simple query there were six different ways of running the the query and that was torsional i haven't even talked about joins i cut all of those slides i haven't talked about uh sub queries and and things like that i cut all of that and but we had six different ways of doing a relatively simple query when you start talking about joins and other things the number of combinations of ways to solve this problem really mushrooms really fast and if you have a lot of indices that gives the lot the the optimizer more things to choose from and that gives it many more opportunities to make a mistake so you really should try and keep your indices to a minimum those are my slides and i burned through this a little bit faster than i thought but we've got time for questions if there are any yes jim um to fill in with what we've been wondering about wait when they do an index for people they scan the index getting record numbers which are dense and said even bitmap the there are three or four reasons it doesn't one is the end of the wire that you talked about which works very well um the second is it doesn't matter in a cell phone you have a single level store but in in computers the disc is infinitely slow compared to memory and if if you can access the disc in sequential order it's vastly faster yeah so we don't have this problem with flashing bitmap order which is physical order and the third is that it vastly simplifies the optimizer because you can use all of the indexes that you've got you don't have to choose them that's the thought you just you're always the way that was once they're completely redundant but you use everything you got you hand to know them together and you end up with you're saying falcon really prefers to do this method over one of these simply because it's really simple to figure out which method you're going to be using when you do it this way and and your argument is that on on server hardware it's going to be just as fast because your access because things are streaming off the disk in sequential order and and that's going to be faster than doing uh random accesses is that people overestimate the possible sort sort is cheap you can sort a gazillion records in the time it takes to do one disk access on server hardware you don't have disks on cell phones now you don't if you have a disk on a cell phone you can still sort a million records and they're going to take down the disk so using the sort to eliminate disk is good okay good point yes great you know sql like can you trick the planner to use the first column if you just want to sort them out by saying like where fruit equals fruit or where fruit is not equal to banana or no um no you can't because um that sorting trick here where was that i called pastor no don't hear this yeah no if if in fact if you say um where fruit in orange comma a gray or something like that that it's still got to sort it manually because then that first column is no longer coming out of the right order or it's not guaranteed to i mean you might get lucky but there's no guarantee so it goes ahead and sorts it here upfront the sql like which is which does it prefer does it refer to more complex indexes or a bunch of tiny indexes well as as of this very moment sqlite doesn't do the bitmap indexing uh we are we're putting that in now so it prefers larger indexes it would first larger indices so yeah if you give it an uh a wear term a wear claws with a bunch of with with an ore in it it does a full table scan so um you know this is this is one of the problems of fitting in 256 kilobytes so but we we will by by spring uh that will be remedied and it'll it'll do the full bitmap thing so but right now it prefers the other and it's probably always going to be faster for sqlite because it really is going for the lower end hardware and and the memory really really is a factor uh um in the keynote i was telling brian afterwards he was trying to oh you know we're being uh we're wasting power that's never a problem with sqlite because the cell phone and ipod people are constantly screaming at me they don't care about cpu time they don't care about memory what we care about is running the battery down so anything i can do to save battery power is a plus question yeah sqlite is uh i i haven't played with it much a little bit but it's different mysql obviously has a server how is sqlite has no server it just runs when you use it it talks directly to the disk which is and has that has advantages and disadvantages and in the environment that sqlite is using is a huge win because if there's no server you don't need an administrator to start the server to fix the server when it breaks etc etc the disadvantage is that you can't really do things like mvcc without a server and you just you know because what happens if you get halfway in the middle of a well we'll talk about it later but i'm convinced you could theoretically do it but the overhead would be so spirit of here and multiple vcc i want to see how you do it okay i want to see guys i don't believe it i cannot believe it he was born that way it's the client and the the program itself all one yes this is a sql is a just a pure library it's it's really just a translator from sql and do into accesses the disk you know the the slide that i'll give in my next talk so i'm going to spoil the punch line is that sqlite is not trying to compete with with postgres and oracle and mysql and these others it's competing with f open okay that's that's what we're really trying to replace not these others so it's really a very different mission i've got five minutes left well we're on questions so we're we're fine any other questions okay any other questions or do you have a five minute break cool back to my question then you're going to have to add a much more levels of optimism optimization if you have a choice between complex index and also using good maps yeah we're we're what the situation is is that um the way sqlite is structured the way we make our money is we have the sqli consortium and and that consists of some large companies who use sqlite in their products and they're sort of mission critical for the products and so they just kind of give us a little bit of money every year to make sure that we don't go away um and and and that actually works and and so we've got three guys working on it now and that works really well for what we've recently picked up is one of these companies uh uh bloomberg who was using sqlite on big iron and um they are uh you know with the cell phone people they're they really don't want these bitmap indices because they use memory but uh the big iron people they want it and so we're gonna have to put it in for for them because they're helping to keep the lights on and but we'll probably put it in a way that you can add it as a compile plan option because it is going to make it a little bit bigger a little bit more complex and the cell phone people ipod people they want to keep it small and simple so it'd be like an if-def and you can put this in as an if-def or you can leave it out according to what you want to do in truth though lately the cell phone people have been saying you know what we can go above your 250 kilobyte limit you know go up to 500 kilowatts we don't care because memory is is getting cheaper and so maybe they'll keep it in i'm not sure we'll see question in the back um just a question uh where did yes yeah that you know i had six or seven slides on exactly that question that i cut this morning um in in sqlite well really in most database engines you've got a little you've really got a computer program and there's these variables in there that get initialized and when you bind a new value what you're doing is changing the initializer on that variable so when you bond and so when when you compile the program those initializers are really in sql they're always initialized to a null and so you compile the program you get your program and all these things are where it's a variable x equals null and then you bind a value against that it changes the program slightly to say x is equal to one two three or whatever number you bound to it and so the binding is persistent if you rerun the prepared statement multiple times unless you change that binding again it's still one two three every time you run it until you reset it to something different i that would make a lot more sense with a slide which i deleted i'm sorry does that mean you have to regenerate the code no no the bytecode is not regenerated it actually just goes in and changes the bytecode slightly it doesn't it doesn't have to insert instructions or anything it just has to change a constant in there to the new value that you bound to it that's all it has to do other questions if not we will adjourn two minutes into schedule

Channel: tcation

Views: 64,257

Rating: 4.9063964 out of 5

Keywords: sqlite, databases, richard hipp, opensqlcamp

Id: Z_cX3bzkExE

Length: 43min 59sec (2639 seconds)

Published: Wed Aug 19 2009

Reddit Comments

[u/luckydog5656]

Video is so low quality, I can't read the examples.

[u/Utilities]

Similar lecture but better quality here

[u/[deleted]]

It kind of triggers me that people keep talking while he's trying to give his lecture.

[u/dhaansulonda]

Fun fact, SQLite was made for rockets initially.