Large amount of data like 0.5 Million join with 0.5 Million left join with 0.75 Million, etc.. retrieving data in a second is a dream that will never come true with relational database and data integrity.
In a small companies we are a full stack developers. We are not experts in DB (And we do not want to). We want Entity Framework Code First to handle our database creation and relation. Having a good a knowledge in creating Non clustered indices is a plus (Getting the suggestions form SQL Server Management Studio Execution Plan will be more than enough).
But again, how could we make our search super fast. Yes, the solution was the Elastic search for me. And another library called Hang-fire will notify Elastic search service to reindex some data or to delete it.
Your suggestions and cooperation is appreciated.
My friend Amer Ala’amar added this comment when I shared with him this article.