1

I work on a relatively large system where have started to run into performance problems scaling for multiple users.

The system is a .NET application, so query's are written using an ORM (entity framework), and the database is an Azure SQL database.

I'm a developer and not a DBA; Typically when we've hit performance limits, and have optimised our queries to the best of our ability, but if we are still throttling the database, I scale up to a higher tier to increase our DTUs and the problem is solved.

We're now at a point where it would be cheaper to give individual users their own database, rather than scale any further.

I wont go into the details of what we do, but essentially we have a constant stream of data being sent from our users which on average is writing about 100,000 rows of data per user, per day, to the same table. Our users need quick access to this data, which typically involves loading in one month to a year of data at a time.

My question is - In this scenario, what options do I have to maintain our performance.

As far as I can tell, my only options are:

1 - Generate each user their own table within the database (if that's even possible), so I only need to deal with a few billion rows per user when querying (35b per year).

2 - I generate each user their own database (which should help with the performance hit from concurrent queries, but would be a nightmare to manage)

3 - I just keep throwing more money at azure until it becomes technically impossible to scale any further?

Thanks.

Improve this question
5
  • 3
    How about 4 - hire a DBA?
    – mustaccio
    Feb 9, 2022 at 12:18
  • @mustaccio - That will most likely be where this leads...
    – Verno
    Feb 9, 2022 at 12:24
  • Honestly, move to a different data platform. SQL Server is for relational data. You're doing everything in a single table. That's not in any way relational. Sounds like maybe a MongoDB database in Cosmos might be better.
    – Grant Fritchey
    Feb 9, 2022 at 15:33
  • Having some details on your schema such as the create table scripts, some sample (if even obfuscated) data, & types of queries you're running would be helpful here. From a performance perspective it shouldn't really matter which mainstream database system you're using, but from a financial one it may make a difference (but that's almost unadvisable without many details). You probably can architecturally improve your current database for better performance but knowing the aforementioned details would be helpful in determining that. #2 is a possibility and could help with better data statistics.
    – J.D.
    Feb 10, 2022 at 2:58
  • @Verno, I wrote a post about your question. Is interesting how many people get puzzled during the choice: jeeja.biz/2022/03/13/…
    – Francesco Mantovani
    Mar 14, 2022 at 20:32

2 Answers 2

Reset to default
1

I like your question and I will try to find a solution for you.

Your scenario is where Entity Framework and others ORM brings applications like yours. "Just write code, don't think about the DB"... till you have to think about the DB.

As said in the comment hiring a DBA is the next step.

Anyway, let's look at the solutions we might approach:

Multi Tenant Architecture (All customers on the same DB)

  • Azure Hyperscale: is good for Multi Tenant architecture (which is your case, all customers on the same database) but you need to understand if is going to be more or less expensive than Azure SQL Database. Keep in mind that once you scale to Azure Hyperscale you cannot switch back to Azure SQL Database.
  • Cosmos DB: You are already using a ORM, right? The idea was not caring about the DB anyway. Let's see if you can denormalize your database schema and move your Azure SQL Database to Cosmos DB which is tailored for heavy load.

Single Tenant Architecture (One DB per customer)

  • Azure SQL Database: you are already on that option and when you scale up the problem is solved. However DTUs are expensive and the problem is solved by burning money. Try to extract a few customers and simulate the load and see if it's let's expensive.
  • Azure SQL Edge: "[...] writing about 100,000 rows of data per user, per day" that sounds like an IoT to me so why not approaching Azure SQL Edge which is made for data streaming. Here too you have to test it before you move completely.

And now the reply to each of your questions:

  1. Generate each user their own table within the database: Please don't do it. the database will still be under pressure and you will not solve the problem.
  2. I generate each user their own database: That is the solution for me, moving from Multi Tenant to Single Tenant: Divide et Impera. And is not that hard to do if you are already on Azure SQL Database. If you have Customer1, Customer2 and Customer3 you just need to make 3 copies of the database that you already have and if you point the same .NET application to each of the database the application will work. You then need to DELETE all the data related to the other customers from each database and, voilà, you passed from Multi Tenant to Single Tenant architecture.
  3. I just keep throwing more money at azure: This is impractical.

So the solution for me is moving Multi Tenant to Single Tenant architecture and decide between Azure SQL Database or Azure SQL Edge.

But please, hire a DBA.

Improve this answer
4
  • Thank you for your response - You're correct that it is IoT data we're dealing with, but Azure Edge wouldn't fit our use case. I think we need to explore something of a hybrid of the single the tenant architecture you've suggested. The database itself has around 500 tables, all of which are highly relational; So moving the tables storing the IoT data only to individual databases per user, would probably be the best solution for us. I'll test with SQL and Cosmos before we venture into the Hyperscale territory. But as has been made abundantly clear - we'll consult with a DBA. Thanks again.
    – Verno
    Feb 10, 2022 at 11:38
  • I've you're looking at Cosmos and the data rows are not too complex you might consider Azure Table Storage.. this is really really cheap by comparison and can handle large volumes of data. It's a bit limited in terms of features but it may be sufficent.
    – Quango
    Feb 19, 2022 at 15:13
  • " Azure Table Storage", never heard about that @Quango. I'm going to study it a bit. Thank you
    – Francesco Mantovani
    Feb 25, 2022 at 17:04
  • No problem @FrancescoMantovani - check out Troy Hunt's blog post about why he uses it for haveibeenpwned: troyhunt.com/working-with-154-million-records-on
    – Quango
    Feb 26, 2022 at 9:17
0

I was going to make this a comment but I do not have the points.

You might want to try database table partitioning, but it will depend on your database design if this option will work for you. Here is a link for you.

https://www.sqlshack.com/database-table-partitioning-sql-server/

Another great link. https://www.brentozar.com/archive/2012/03/how-decide-if-should-use-table-partitioning/

Good Luck

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.