3

I have an SQLite database in an Android app that stores basic transaction info (date, amount, category, comments). I know I can do a query to get all of the transactions of a specific category between two dates easily enough, but I want to add up the cost (stored as a real in SQLite) for each transaction of a particular category between two dates. I know there is a sum function in SQLite (thanks to this question), but the last answer in this other question makes me question the performance cost of doing so.

To my understanding, SQLite is written in native code, which is a win for mobile, where performance is key. So I've been told to do less in the java and let SQLite do what it does best in native code (and by native code I mean C / C++).

So in the case of Android, is it better to get a cursor to all the costs and loop through to get the total (in java code), or is it better to leave that lifting to SQLite? I'm just not sure what SQLite is optimized to do on Android.

Thanks!

Community
  • 1
  • 1
justinrixx
  • 801
  • 1
  • 7
  • 16
  • Try both and time the code. It's certainly not a given that it's faster in SQLite. It's a common misconception that native code will always be faster. Also consider the maintainability and complexity in your decision. – Jeffrey Blattman Jul 21 '15 at 00:32
  • In general, you want to do the manipulations in the database -- databases should be optimized for the operations they implement and you reduce the data being passed back and forth. As with any general rule, though, you should validate on your data on your systems. – Gordon Linoff Jul 21 '15 at 00:33

1 Answers1

4

In general, you should rely on your database. Otherwise, we'd always get all of our data with a select * from table and do all the manipulations ourselves.

You're taking into account a very unpopular answer compared to the accepted one. Check out this question, for instance. And the last answer of this one.

But I believe this would be the mother question.

Community
  • 1
  • 1
Fred Porciúncula
  • 8,533
  • 3
  • 40
  • 57
  • 1
    `we'd always get all of our data with a select * from table` which is **not a healty practice**. You should always get `only the columns you really need`. – Phantômaxx Jul 21 '15 at 06:45