0

I'm currently developing a real-time application using .NET Core 2.2. Many IOT devices (could be up to 100) are concurrently sending messages via MQTT. These messages are parsed, elaborated to extract and validate the current position of each device based on some business rules and then persisted to a SQL Server DB using Entity Framework, so they can be displayed in real-time by clients connected to the server via SignalR.

What I'm experiencing even with few devices is that the actual elaboration of each message is nearly immediate (1ms long) and the persistence layer is taking up to 300ms at the point that the system is not able to catch up with the current flow of messages and positions are always delayed in the map display.

What can I do to improve this design?

What I tried:

  • using a memory representation of the whole system and then saving it every few seconds. EF's DbContext is not thread safe so it doesn't like this approach. Using locks is even slower.
  • caching data only mitigates the problem because most of the data is constantly changing and can't be cached.

Other ideas:

  • using a message queue?
  • using MongoDb?
Improve this question
3
  • why is the DB so slow? There's no general reason why a query should take more than, say, 5ms (assuming reasonable queries using a reasonably administered database (software + hardware) that is nearby (same city)). Please investigate this absurd slowdown rather than trying to hide it beneath extra layers. – amon May 17 '19 at 13:57
  • Because EF is tracking what rows where added, updated or deleted. Doing it by hand would require a lot of work to load each item, see if it exists, issuing other queries and repeating the entire process for every child collection and related object. – Alessandro May 17 '19 at 14:19
  • 1
    If you want to use EF because it is convenient and if EF is so slow, then there is no easy fix. Maybe you could use multiple threads to parallelize the effort, but that will only improve throughput, not latency. Your point about “child collection” indicates that your data might not fit a relational data model very well. A different kind of DB might help, but that would also mean giving up on EF. The real solution might be to design a better data model. – amon May 17 '19 at 14:29

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.