How To Speed Up Your Queries With Materialized Views

Most applications do read data much more often than they write data. SQL databases are actually optimized for writing data. Materialized views, on the other hand, are optimized for reading. You can consider pre-computing materialized views for some expensive queries to speed them up. I'll show you how to create materialized views using domain events in this video.

Join my weekly .NET newsletter:

Read my Blog here:

Subscribe for more:

Chapters
0:00 What is a Materialized View?
2:29 Pre-calculating the Materialized View
6:31 Implementing the Materialized View calculation service
13:34 Configuring EF Core to use JSON columns
15:09 Materialized View calculation flow
15:55 Non-scientific performance comparison

[ColinM]

This isn't a materialized view, it is a projection query.

Additionally, in terms of the response speed, you did only run the summary-raw request once and it actually yielded a faster response than your first and last runs of the projected query request. The reason the subsequent responses were in the ~80ms space is because the DbContext has already cached the result in memory since no AsNoTracking call was specified - although I appreciate it's possible you may have set this on a global level. While I agree that projection is much better in terms of speed and memory allocations, I think the non-scientific method of demonstrating the differences is misleading and, if we're to take the results at face value, the non-projected query looks to be better in performance.

[grumpydeveloper]

So are you going to show how to update these materialized views when th underlying data changes? Easiest way would probably be to delete the record and created it again. At first when i read materialized views I thought you were going to use SQL Server index views, where SQL server stores the data from a view in actual tables rows, but also takes care of updating when the underlying data changes.

[newraze]

Question. In one of your videos you've mentioned that its better to retrieve partial data if some fields are not needed for user. How to solve that proble. I placed IShopRepository in Shop Domain Layer. Shop entity is created in DDD style (private/internal constructor, entity is created by factory). I want to fetch just two, three fields instead of full entity. I cannot then fetch it by Select(x => new Shop{x .ShopId....}) cause constructor is internal/private. I can not use also DTO cause IShopRepository is in domain layer that have no access to Application layer. I would like to optimize it and also keep it encapsulated as much as possible

[joga_bonito_aro]

This feels like it is very useable for recursive reads from a database in which the aggregate roots has Children and Parent properties referencing entries from the same table.

[AmjadKhateeb]

I know that materialized view is a database topic, not a c#/ef topic. I don't know when and where the materialized view is created. Or it may be created internally by ef or by postgres ?
As I see you are just storing the order summary as it is in the database as a table. So, if any update happened to the order you have to recalculate the order summary and replace the old one.

[alexnilev]

Where can I find code an example from this video?

[GGGStalker]

Can you please publish your Git with this example? Or more extended one.

[ikaros]

Wouldnt it have been possible to execute the GroupBy and Select in the database without materializing all line items?

[damianrecinski]

Instead manually deleting all usings after adding new class, you can edit new class schema file, and set which usings should be automatically added by visual studio

[bahmansani]

handing domain events this way seems to be problematic. ( maybe my assumption about your implementation is not true). the problem is, we may lose domain event before processing unless we use outbox messages. is this true? and we mark it after process done. i mean we need guarantee.

[srik]

How to seed data in Order Summary table in following case
1) Order table has millions of data and OrderSummary is created after that.
2) New Column is required and needs data in older records as well and the column is not null. Example Order Date

[antonofka]

You're doing 2 joins on the same level btw. I'm pretty sure EF Core doesn't prevent cartesian explosion by default.

[wadeaashraf]

If there is any way to download this code?

[chrishabgood]

The only issue is if it has a tons of joins and sub queries then if you refresh it it will take a long time.

[weluvmusicz]

Your candle is way to close to the cupboard board!

[antonofka]

Running with the debugger is like 50 times slower than a regular run. At least run it without the debugger.

[sandragredo]

Jesi li s e ovo naucio preko neta? Daj neke smernice, tutorijale, please!