In my last post, we talked about sql views. Yes, I did convince you that they are really cool – but lets face it, is there any improvment in performance? Well, there is none, but even if – it's too small. IMHO views are just facitilation, they makes our life easier and they look prettier – we like pretty things, dont we :smile: ? At the end of my last post I mentioned about materialized views, and thats the topic of this one !

I want you to consider materialized views as a file, a file in which our tables are saved, with the records we've chosen. Database fetches those records directly from a file, with just one query, so we it does its job in just one query. That comparison to file, is just parallel, in real it is alot more advanced !! In our case, which was to implement advanced search, through 5 000 000 records, we had about 200% performance boost, and I am pretty sure that number can become alot higher.

 

continue reading