We have a gigantic table of products that have many prices that are updated by different microservices (say 20 different sources updating 80 columns on the same rows). We also have a website with say 40-100 concurrent users, and the pricing data is refreshed every 5 seconds and generally not cached as it is time sensitive.

Every single source of price is a new column and as you can imagine there are currently occasional deadlocks.

Would splitting them into multiple tables work well enough (e.g. split meta data and prices, or split meta data and prices that each microservice updates)? Would we get even worse performance with all the joins we have to do while reading?

Are there other options other than splitting tables? We only have indexes on the lookup fields (generally an ID and one other piece such as quantity for bundles). Or should we just suck it up and go into a larger instance?

  • What version and edition of sql server are you using ? – Kin Shah Oct 12 '17 at 12:21
  • @Kin We're currently on 2008 R2 Enterprise I believe but intend to go to RDS and upgrade to 2017 Standard – Jun Wei Lee Oct 12 '17 at 22:29
  • since you are on Enterprise edition, you can consider partitioning instead of splitting into diff tables. – Dharmendar Kumar 'DK' Nov 22 '17 at 18:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.