6

I'm using a compiled SQLiteStatement with transactions in optimizing SQLite transactions but I'm reading the documentation for the execute function:

Execute this SQL statement, if it is not a SELECT / INSERT / DELETE / UPDATE, for example CREATE / DROP table, view, trigger, index etc.

This seems to imply that this function should not be used with SELECT / INSERT / DELETE / UPDATE statements, but I have code that uses it with an insert and works.

I'm aware of executeInsert and the other methods, but executeUpdateDelete is not available in my API level, so can I use execute?

Also if I don't need the last insert id or the number of rows affected should I use execute instead of executeInsert and etc., in other words is it more efficient?

Emil Davtyan
  • 13,808
  • 5
  • 44
  • 66

2 Answers2

3

execute is probably not faster than executeInsert, could even be slower (on ICS execute calls executeUpdateDelete and discards the return value). You need to test that but I doubt you will find a real difference here.

AFAIK, It is safe to use just execute if you don't need return values but I would not count on that holding true in future Android versions. The documentation says no, so maybe someone is going to change the behavior to reflect that. Older implementations seem to use execute too (e.g. 2.1 delete() sourcecode). Jelly Bean for example changed a lot behind the scenes of SQLite, but it should still work when using execute

Besides, if you don't use the same SQLiteStatement over and over again while just rebinding the args it's probably not worth using it. Building a new one each time you call the regular insert, update, ... methods is fast compared to the actual database access and the required disk I/O. Transactions on the other hand help a lot, since synchronizing database state on the disk for each statement is really slow.

zapl
  • 63,179
  • 10
  • 123
  • 154
  • So you mean there is no guarantee that `execute()` will work in future released too with `INSERT statement` also the docs are just confusing it say it `Execute this SQL statement, if it is not a SELECT / INSERT /....` then it should not execute the `INSERT` (because this is only statement i have tested) and its working! And thanks for the valuable information :) – Muhammad Babar Nov 20 '14 at 07:15
  • Also `statement.executeUpdateDelete();` this available in API 11 any workaround for 2.2? – Muhammad Babar Nov 20 '14 at 07:16
  • 1
    @MuhammadBabar The documentation tries to tell you that the `execute` method is not intended for statements that have a result. Like a row set for a select, the change count for update / delete or the last row id for an insert. There is so far no code that prevents that those unintended statements work. Maybe a future change to the database architecture will, I don't know. The android framework engineers could easily add such a change without notice because the description of the method warns you that it won't work since years. – zapl Nov 20 '14 at 09:30
  • In 2.2 the only way to get the resulting change count of an update / delete statement is to use [SQLiteDatabase#update](http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#update%28java.lang.String,%20android.content.ContentValues,%20java.lang.String,%20java.lang.String[]%29) or `delete`. Those methods use a fresh `SQLiteStatement` per method call internally. And they use `SQLiteStatement#execute()` but then record the change count via a hidden method of `SQLiteDatabase`. That hidden method is finally exposed in API 11 for `SQLiteStatement` use, but not before. – zapl Nov 20 '14 at 09:33
  • Which one is faster `delete()` or `executeUpdateDelete()`? – Muhammad Babar Feb 06 '15 at 13:21
  • @MuhammadBabar feel free to profile but it shouldn't matter since the bottleneck with SQL is the disk IO which is way slower than the few lines of code that differ. Use the one that produces cleaner code when in doubt. – zapl Feb 08 '15 at 21:35
  • Thanks but i did some test deleting some 18k+ records. The one which i find efficient and quick was to use `delete()` with `transactions`. – Muhammad Babar Feb 08 '15 at 22:05
1

Use SQLiteDatabase instead of SQLiteStatement for intereacting with your database. SQLiteStatements are not Thread safe so I would not use them for SELECT / INSERT / DELETE / UPDATE. Also you should try not to use raw database queries for anything other than Selects. There are built in helper functions that will increase your database performance. On your instance of SQLiteDatabase you have .insert, .update, .delete and I use .rawQuery for Selects.

JustinMorris
  • 7,259
  • 3
  • 30
  • 36
  • but being not `THREAD-SAFE` only implies on *multi-threaded* app. For single thread it's worth using if you want bulk insertion. – Muhammad Babar Nov 20 '14 at 07:19
  • um, every app with a DB should be multi-threaded. Any request to disk, DB or web service should be off the UI thread. If you are blocking the UI Thread to do bulk inserts you are doing it wrong. – JustinMorris Nov 23 '14 at 03:24
  • @JustinMorris: If only a single worker thread accesses the DB, it's fine to call non-threadsafe methods. The fact that the app is multithreaded is not an issue, for the very reason that the main thread does not access the DB. – Dabbler Dec 31 '16 at 10:24