Materialized View in SQL | Faster SQL Queries using Materialized Views

hey everyone tawfiq here now if you have an sql query that is taking a very long time to execute just try to create a materialized web on that sql query and see the magic now having said that it does not mean you start creating a materialized beep on each and every sql query that is taking time because the way how materialized weave actually works in sql is slightly different from any other database object now in this video i'm going to give you all the information that you need to know about materialized view we shall see how does it actually improve the performance and how does actually materialize weave work i'm also going to tell you what is the difference between a weave and a materialized weave and of course i am going to explain all of these concepts practically that is we are going to create a table i am going to insert millions of records into that table and then we will try to write a query on that table and then we will see how does metallized view actually improve the performance of that query now before i can start i would like to mention two things number one is that metallized wave is a concept that is common across all the other rdbms so no matter which rdbms you are using everything that you will be learning about materialized wave in this video should be applicable to all but of course i am going to just be using one rdbms i am going to be using postgresql in order to explain the concept of materialized wave in this video the second thing is similar to practically learning materialized viv in this video if you also want to practically learn all the other sql concepts right from the scratch that is from the most basic sql concepts until the intermediate sql concepts and then also the advanced sql concepts and you want to practically learn that is not just learn the sql concepts but also learning how to practically use these concepts when solving sql queries then definitely consider joining mysql course i'm going to be starting my sql quotes from 3rd august 2022 it's going to be a live and interactive training session they're going to be 20 sessions each session is going to be of 1.5 hours long it's going to be conducted on lighthall and all the other details about mysql codes the course content and all the other details is mentioned in my website i'll leave the link to my website in the description below so if you are interested in learning sql the right way definitely consider looking at my website and also joining my quotes now before i can create a metallized weave and show you how does it actually improve the performance let's first try to understand what exactly is a materialized wave a materialized wave is a database object it is created over an sql query similar to a wave but when you create a materialized wave it does two things or basically it stores two things the first thing is it's going to store the sql query that is used to create that materialized wave and the second thing is it's also going to store the data that is returned from that sql query and this second reason is why materialized wave improves the performance of your query okay so what happens is every time you execute the materialized wave it's not going to internally execute the query that is associated with the materialized wave but it's only going to return the data that is already stored in the materialized wave okay and this is the reason why the performance of a materialized wave is so damn good now let's try to better understand this by taking some examples so i'm just going to create a table so i'm just going to say create table and maybe i'm just going to name it like random table okay and i'm going to have let's say two columns in this so id it's going to have an integer value and then i'm going to have a value corresponding to each id and let's say this is a decimal value okay so i'm just going to create this table and now i want to insert some records into this table so i want to insert millions of records into this table so that i can show you how materialized view actually improves the performance okay now in postgresql if we wanted to insert a series of numbers then we can use an inbuilt function called as a generate series so i'm just going to do that so i'm just going to say insert into random tab and here i'm just going to write a query which is going to insert this series of numbers so my first column is an id so let's say i'm just going to hard code it to one okay and my second column let's say i'm just going to generate or fetch the random values from this generate series function okay so i'm just going to say generate series and i want to insert let's say 10 million records okay so one two three one two three okay this should hopefully fetch me 10 million records now if you have never used generate series for example let's say if i just say 10 okay it's going to fetch me 10 randomly generated series of numbers okay as you can see here now what i want to do is i want to insert 10 million records so one two three one two three okay so i think yeah it's 10 million records now if i execute this it should insert me 10 million records but i also want to insert one more id okay so with id 1 i'll have 10 million records and then i want to insert let's say id 2. let's say this one also should have 10 million records okay now i'm just going to execute both this insert and this one is going to take a few seconds so i'm just going to skip that okay so it is done now you can see that it has inserted 20 million records now just to confirm that i'm just going to say count of one from random tab okay and you can now see that this is having as you can see 20 million records so this is a pretty big table now so we have created a table and we have inserted 20 million records into this table now what i want to do is i want to do some simple calculations on this table i just want to write a query doing some simple calculations so what i'm just going to do is i'm just going to say select id i'll find the average of these values uh corresponding to each id okay so i'm just going to say average of value and i'm also going to count how many records are present corresponding to each id so i'm just going to say count of one you can use count of one or count of star both are the same and here i'm just going to say group by let's say id okay and now if i just execute this it should fetch me two records because i'm doing a group by id and there are only two ids but you can see it took three seconds 218 milliseconds okay so this query here took over three seconds to execute now this is just some random table that i created just for this demo but when you're working on real projects you might have some complex sql queries which might take a few seconds to execute or a few minutes to execute in that case having a materialized view will really help okay so i'm just going to go with this example so if i execute this table as you can see it takes three seconds to execute right but let's say if i create a materialized weave on this query then you can see how fast the execution will happen so in order to create a materialized wave on this query what i'm going to do is i'm just going to say create materialize weave okay it should be materialized weave and i'm going to give a name for this like let's say mv just a prefix mb underscore just stands for materialized weave and i'm just going to give the same table name okay so mb underscore random tab and the syntax is basically the same i just need to say create materialized beef give a name for my materialized beef and then the as clause and then the sql query here okay so the syntax is exactly similar to what we do in uh when creating a wave okay now i'm going to create this materialized weave and it's going to take maybe a few seconds it took a three seconds uh because it created a metallized weave as i told you it's doing two things it's going to store this query itself and also it's going to store the result from this query okay now in order to execute this materialized wave i can just write it like select star from the materialize view name and if i execute it you can see it's fetching the two records and it just took 63 milliseconds okay this is the difference now when i execute this materialized weave it's taking just 60 around 60 milliseconds but if i had to execute this entire query where we are fetching the data from the base table you can see it's taking more time and it's actually taking over three seconds so over three seconds and 160 milliseconds okay now this is a big difference but let's say if you had a complex query which was taking maybe a few different seconds or a few minutes then if you create a materialized wave you will see big improvement in the performance okay now how did this actually happen so you understood that if i just create a materialized web on this query and then instead of executing this query if i just execute from the materialize weave i will get the same data but the performance is so much better but how did actually sql do that the reason why sql was able to improve the performance using materialized wave is because when i created the materialized wave sql had generated the data from this query and this data was stored in the materialize view so every time i executed this materialized wave it did not go back and execute the query again but what it did was it just went to this materialized view it saw the data that was stored for this materialized wave and it just returned that data okay so it just went to the memory it fetched the data that was already stored corresponding to this materialized wave and that data was just returned and that is why it's faster it's not going back to the database and executing this query okay and that is why the performance is so much better now of course there is a catch and the catch is that this data will not get automatically updated meaning let's say if i change the data in this base table that is in my random tab table then materialize viv is not going to have that data updated automatically we have to manually do that using a refresh okay now what i mean by that is for example now we know that in our random tab table i have two ids one and two now i'm just going to delete one of these ids so let's say i'm just going to say delete from random tab and i'm just going to say where id equal to 1 okay this is having 10 million records so i'm going to delete 10 million records from this table okay so once i delete 10 million records from the base table i'm going to query the base table again and see the data and then we will see if this update has automatically come to materialize viv or not okay so the deletion is successful now if i run my base query that is from my base table where i am doing a group by from the random tab you can see it's only fetching me one record and it's only having the id too but if i do a query from my materialized wave you can see it's still having the old data okay why this happened was when the metallize v was created it had stored the data that it found when the creation happened right and even though after that that table data got changed materialized view is not going to automatically update it okay so if we want to update the data in the materialize view we will need to manually do a refresh okay so for example if i have to do a refresh of the materialized weave i will need to run the command like refresh materialized weave and the materialized view name so mv underscore random tab okay now if i run this refresh command what it's going to internally do is internally sql went and executed the query and whatever the data was returned from this query at this moment it got stored into the materialize view okay so this is basically what a refresh happened okay it's basically telling it's refreshing the data in the materialized wave and now if i go and execute the materialized wave it's going to get the latest data okay now you can see i'm only having the id one now this is exactly the same as we have in our base table so basically this is what a materialized wave is we create metallized weave on a query and when we create a materialized weave the it gets the data from that query and it is stored corresponding to that materialized beef and even though there was a change in the base table whether there is a data getting updated deleted inserted or whatever right materialized web is not going to automatically get these changes okay in order for materialized view to reflect these new changes we will need to manually do a refresh on this materialized wave now some of you might ask then what is the use of materialized view because metallized web is not going to have the latest data right and this is the reason in the beginning of the video i told you that do not create a materialized view for each and every query that is taking time because many times we might be executing a query and we expect the latest data to be showcased from that query but several times when we are working on real-time projects or when we are building some product we would not really bother about the latest data in the base table or we would know that the data in the base table does not get updated or modified frequently and in that case creating a materialized view would be a much better approach on any query now from my personal experience one of the projects where i actually created several materialize we was when we were basically building some reports for one of the projects and this report was something like the report table or the base table that you that we were using would get updated only uh once in a while that is i think it was monthly a table the data would get updated monthly but then this report table itself would be used by multiple business users and stakeholders and clients and vendors etc and they would execute the data in the report several times in a day okay in this case if we had not created a materialized view then what would happen is every time any user or any vendor executed that report it would internally basically execute the query and several times these reports would have very complex queries which would take a few seconds to several minutes to execute and it would be a very bad for the performance right but instead of that what we did was we created a materialized weave and we made sure that we refreshed the materialized weave every end of the day because we knew that the base table would not get updated uh very frequently and even if we just updated the data in a material sv once in a day that would be still good enough and the users and the vendors would just execute from the materialized view which would basically return the data very fast now i also want to talk about what is the difference between a metallized weave and a weave i already told you that metallized weave is basically storing two things the sql query and the data but a weave doesn't store the data so for example what i'm going to do is i'm just going to copy this same syntax and down here i'm going to paste it here and here instead of materialized beef i'm just going to create a weep the syntax is the same i'm just going to give a new name so i'm just going to prefix it with let's say vw okay just stands for weave and i'm just going to execute it okay now you can see that we have created the weave and now if i query the data from this weave so let's say i'm just going to query it here okay so select star from my weave and if i run this you can see it's still taking some time and it took over one second to execute whereas if i execute my materialized wave it's hardly taking few milliseconds okay the reason for that is when i created a weave it just stored the query that was used to create this wave so every time i execute a wave it's actually internally going and executing this query so when i'm executing a weave it's basically the same as executing this entire query itself okay and that is why we've basically does not improve any performance but materialize view will improve the performance because it's not going to execute the query it's going to return the data that is already stored okay so the difference between a weave and a metallized view is weave will only store the select statement when it is created materialize we will store both the select statement as well as the data returned from that select statement okay that's the first difference the second difference is materialize weave every time you execute it's only going to return the data that is already stored corresponding to that metallized wave whereas every time you execute a wave it's going to re-execute the query that is associated with the wave every single time okay so this is basically the two major difference between weave and a materialized vape i hope this is clear and i think that's all i wanted to explain about metallize view now since we are using postgresql the concept of metallized weave is made pretty simple but let's say if you're using oracle then in metallized view we could do a lot more things because there are several different types of refresh and there are few other things as well now if you want me to make a separate video covering materialized wave in oracle then leave a comment below i'll try to cover them if you are interested okay i hope this video was helpful and see you soon in the next one bye

Channel: techTFQ

Views: 90,176

Keywords: SQL, Materialized View in SQL, Materialized View in PostgreSQL, Materialized View, SQL Tutorial, Learn SQL, difference between view and materialized view, view vs materialized view, materialized views, materialized view, difference between views and materialized views, materialised views, indexed views, indexed view

Id: WzkBZ0byoYE

Length: 16min 39sec (999 seconds)

Published: Thu Jul 07 2022