1

Well, this is not specific to any programming languages. I came across this situation in Android, iOS, PHP and .NET.

The problem is many times I come across a situation while building IoT apps, where I need to call a webservice (each in a separate thread) which does the insert into DB (probably more than 20 - 50 insert in a sec) to log the sensor data continuously.

So I face 2 problems here...

  1. In the app end, it stops responding for creating so many threads.
  2. In the server end, it also hangs due to so many inserts.

Sometimes, in Android and iOS programming I need to do the same in SQLite DB. In that case too it hangs in such cases.

The only option that I'm aware of is using Message Queue (which is specific to .NET I guess)

Are there any other way to solve these type of cases?

Improve this question
4
  • 4
    Doing a single insert per sample is probably not the best approach...
    – Telastyn
    Dec 15, 2014 at 14:59
  • 1
    Batching changes, in particular if they are small changes could look like a good strategy.
    – Oded
    Dec 15, 2014 at 15:01
  • 2
    SQLite normally executes a transaction for each INSERT statement. If you're inserting your records individually without opening a transaction and batching your INSERTS together, that's what your problem is. See stackoverflow.com/questions/3852068/sqlite-insert-very-slow
    – Robert Harvey
    Dec 15, 2014 at 16:56
  • 1
    Well I can't use Transaction, as I'm not trying to batch insert. Each time I get a value from sensor I'm doing the insert. But thanks, that gives me a idea about implementing a write-cache in my DAL with batch insert for multiple values.
    – Krishnandu Sarkar
    Dec 15, 2014 at 17:14

1 Answer 1

Reset to default
1

Solving this problem is one of those situations where using a data abstraction layer (DAL) in your webservice might help you.

In your DAL on the server component, realising that there is a performance issue, you could replace the DAL code which writes directly to the database with one that implements a write cache.

That should allow the system to handle any spikes in inbound traffic, although if the problem is sustained writes or volume then you may need another optimisation - but so long as your webservice uses a DAL then you should be able to rewrite it with minimal fuss.

Improve this answer
3
  • Thanks a lot. I'm already using DAL in Webservice. But it's not helping much. Though I'll try to implement write-cache mechanisms. But what about the client part? Mobile apps are getting hanged for creating multiple threads. Any solution for that?
    – Krishnandu Sarkar
    Dec 15, 2014 at 16:47
  • 1
    For the client side you could look at either 'thread pooling' or a similar caching mechanism. Generally speaking lots of small requests from a mobile device is bad as mobile devices suffer all sorts of latency/bandwidth issues anyway so you want a cache in case the traffic doesn't get through.
    – James Snell
    Dec 15, 2014 at 17:42
  • Thanks :) Any other way you'd like to suggest? As you said it's bad. I need to push the data to the server in real time from the sensors. It's a IoT.
    – Krishnandu Sarkar
    Dec 16, 2014 at 19:57

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.