0

I have a .NET e-commerce solution running off a mid-sized SQL Server express database. The system queries the order data which involves many joins (potentially 20 tables) which is quite slow, particularly during periods of heavy use, and I think I have exhausted the options for indexing the tables and optimising the queries.

I now believe the best option going forward is denormalization - see https://msdn.microsoft.com/en-us/library/cc505841.aspx

What I would like to know is:

  1. Would SQL Server columnstore indexes be a better option?
  2. I am considering using in-memory OLTP on the denormalized tables because having the data in memory will undoubtedly make queries faster but it doesn't seem like the intended use, so should I?
  3. Should I use something like ElasticSearch instead, and what would be the benefit over SQL Server in-memory OLTP?
  4. Should I use SQL Server OLAP instead? Seems like overkill...
smartypants
  • 51
  • 1
  • 5
  • It is very board question to answer here in SO.Please be specific and share the code part for the better help. @smartypants – Rohit Poudel Aug 09 '17 at 10:34
  • @RohitPoudel granted it is fairly broad as there is no "correct" answer, but I want to know the best way to tackle this problem. I have specified the options I am considering and hope that others will have had similar experiences and can share their insight. It is an architecture problem rather than programmatic so I don't see how including code will help. – smartypants Aug 09 '17 at 10:41
  • there will be no better solution for broad questions.there will be only best answer for specific case question or problem.@smartypants – Rohit Poudel Aug 09 '17 at 10:50
  • can you show a sample query plan? – Xingzhou Liu Aug 09 '17 at 10:50
  • @Xingzhou Liu As I understood he don't want to optimize his queries, he just looking for a magic button "make it faster!" – sepupic Aug 09 '17 at 11:30

0 Answers0