View, a solution to slow complex queries?

#sql #mongodb #discuss

I have heard about this before in SQL, but never tried it.

Now I have a complex aggregate in MongoDB, and I stored it in a server-side function (not inside the database), and it is ridiculously slow (takes 4-8 sec / query).

So, I have found MongoDB views and $merge, and I hope it make things faster. But,

It takes space, actually, double the space it used to take...
Has to update the view "every time" I make C-UD.
- It is only easy? before I have ODM (mongoose)'s hook. Not a native database's.

I wonder if it is the same in SQL, esp SQLite?

Top comments (3)

Evaldas Buinauskas • Apr 13 '20 • Edited

Views in relational databases consume no space, they're basically just named reusable queries.

What you described above is usually called material (indexed) view in SQL databases. In a nutshell, this creates an actual table behind the scenes and keeps it up to date. You really are trading space and computational resources for faster reads. It depends if it makes sense to do that on your use case. 🙂

Pacharapol Withayasakpunt • Apr 13 '20 • Edited

So, views are equally slow if they are complex?

Evaldas Buinauskas • Apr 13 '20 • Edited

Yes.

This happens behind the scenes.

SELECT *
FROM complex_custom_view;

Is same as

SELECT *
FROM (complex_custom_query) AS T;

DEV Community

View, a solution to slow complex queries?

Top comments (3)

Read next

Sloan's Inbox: How do you ask for help?

Does Stress Lead to Personal Growth and Resilience?

Postman 101 Workshop

The Great Migration from MongoDB to PostgreSQL