Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Get Creative to Squeeze Performance from SQLite

Get Creative to Squeeze Performance from SQLite

Slides from my talk at Droidcon Berlin 2017

The presentation covers several topics related to performance in SQLite and I attempt to provide you with some tools to use when you're trying to optimize data persistence in your app.

575e4a2585aa6b240abf0b995f9fcc72?s=128

Jason Feinstein

September 05, 2017
Tweet

More Decks by Jason Feinstein

Other Decks in Technology

Transcript

  1. Get Creative to squeeze performance from SQLite Jason Feinstein •

    Bandcamp
  2. we should leverage our science and our technology, together with

    our creativity and our curiosity, to solve the world's problems — Jason Silva @jasonwyatt
  3. Get Creative and Curious to squeeze performance from SQLite @jasonwyatt

  4. Why should you worry about data performance? @jasonwyatt

  5. Don't punish your best users. — Leigh Dyer (Bandcamp Systems

    Lead) @jasonwyatt
  6. . @jasonwyatt

  7. None
  8. None
  9. Don't punish your best users. — Leigh Dyer (Bandcamp Systems

    Lead) @jasonwyatt
  10. Demystifying the magic of SQLite lets you get the most

    out of it for your apps. @jasonwyatt
  11. Things we're going to talk about: 1. Situations where your

    app might require that you should put some thought into how queries and statements are exectued. 2. How to actually understand the ways SQLite will execute them. 3. Bonus: Sysadmins hate him! Use this one weird trick to pull your data from the cloud and access it in two steps! @jasonwyatt
  12. Motivation — You may need to pull a large dataset

    down from the cloud. — Storing large amounts of sensor data generated by the device might be important to your app. — You'll likely need to be able to search that data quickly, often in response to user input. @jasonwyatt
  13. SQLite is a heavyweight @jasonwyatt

  14. In order to understand SQLite and get the most from

    it, there are some important things you should learn about: — Prepared Statements and the Virtual Machine — How Indexes Work — Transactions — Databases are kept in a single file @jasonwyatt
  15. The Virtual Machine (VDBE) — Every statement or query is

    compiled into a bytecode program called a prepared statement — Prepared statements can be referenced and reused — Input parameters and values from the database are kept in the VDBE's registers — ? tokens are actually parameters for the prepared statement (like command line arguments for a program) @jasonwyatt
  16. The Virtual Machine (VDBE) To see the bytecode your statements

    compile into, use the EXPLAIN keyword. @jasonwyatt
  17. SELECT 'Hello ' || 'World' AS greeting; > Hello World

    @jasonwyatt
  18. EXPLAIN SELECT 'Hello ' || 'World' AS greeting; addr opcode

    p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 4 0 00 1 Concat 3 2 1 00 2 ResultRow 1 1 0 00 3 Halt 0 0 0 00 4 String8 0 2 0 Hello 00 5 String8 0 3 0 World 00 6 Goto 0 1 0 00 @jasonwyatt
  19. EXPLAIN SELECT 'Hello ' || 'World' AS greeting; addr opcode

    p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 4 0 00 1 Concat 3 2 1 00 2 ResultRow 1 1 0 00 3 Halt 0 0 0 00 4 String8 0 2 0 Hello 00 5 String8 0 3 0 World 00 6 Goto 0 1 0 00 @jasonwyatt
  20. EXPLAIN SELECT 'Hello ' || 'World' AS greeting; addr opcode

    p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 4 0 00 1 Concat 3 2 1 00 2 ResultRow 1 1 0 00 3 Halt 0 0 0 00 4 String8 0 2 0 Hello 00 5 String8 0 3 0 World 00 6 Goto 0 1 0 00 @jasonwyatt
  21. EXPLAIN SELECT 'Hello ' || 'World' AS greeting; addr opcode

    p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 4 0 00 1 Concat 3 2 1 00 2 ResultRow 1 1 0 00 3 Halt 0 0 0 00 4 String8 0 2 0 Hello 00 5 String8 0 3 0 World 00 6 Goto 0 1 0 00 @jasonwyatt
  22. EXPLAIN SELECT 'Hello ' || 'World' AS greeting; addr opcode

    p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 4 0 00 1 Concat 3 2 1 00 2 ResultRow 1 1 0 00 3 Halt 0 0 0 00 4 String8 0 2 0 Hello 00 5 String8 0 3 0 World 00 6 Goto 0 1 0 00 @jasonwyatt
  23. EXPLAIN SELECT 'Hello ' || 'World' AS greeting; addr opcode

    p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 4 0 00 1 Concat 3 2 1 00 2 ResultRow 1 1 0 00 3 Halt 0 0 0 00 4 String8 0 2 0 Hello 00 5 String8 0 3 0 World 00 6 Goto 0 1 0 00 @jasonwyatt
  24. EXPLAIN SELECT 'Hello ' || 'World' AS greeting; addr opcode

    p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 4 0 00 1 Concat 3 2 1 00 2 ResultRow 1 1 0 00 3 Halt 0 0 0 00 4 String8 0 2 0 Hello 00 5 String8 0 3 0 World 00 6 Goto 0 1 0 00 @jasonwyatt
  25. EXPLAIN SELECT 'Hello ' || 'World' AS greeting; addr opcode

    p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 4 0 00 1 Concat 3 2 1 00 2 ResultRow 1 1 0 00 3 Halt 0 0 0 00 4 String8 0 2 0 Hello 00 5 String8 0 3 0 World 00 6 Goto 0 1 0 00 @jasonwyatt
  26. A more realistic example: EXPLAIN SELECT a.id AS album_id, b.name

    AS band_name, a.title AS album_title FROM bands AS b JOIN albums AS a ON a.band_id = b.id WHERE b.location = ? ORDER BY b.name, a.title; @jasonwyatt
  27. A more realistic example: addr opcode p1 p2 p3 p4

    p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 24 0 00 1 SorterOpen 2 6 0 k(2,B,B) 00 2 OpenRead 1 2 0 3 00 3 OpenRead 0 3 0 3 00 4 Rewind 1 15 0 00 5 Column 1 1 1 00 6 SeekRowid 0 14 1 00 7 Column 0 2 2 00 8 Ne 3 14 2 (BINARY) 52 9 Rowid 1 6 0 00 10 Column 0 1 4 00 11 Column 1 2 5 00 12 MakeRecord 4 3 9 00 13 SorterInsert 2 9 4 3 00 14 Next 1 5 0 01 15 OpenPseudo 3 10 6 00 16 SorterSort 2 23 0 00 17 SorterData 2 10 3 00 18 Column 3 2 6 00 19 Column 3 0 7 00 20 Column 3 1 8 00 21 ResultRow 6 3 0 00 22 SorterNext 2 17 0 00 23 Halt 0 0 0 00 24 Transaction 0 0 2 0 01 25 Variable 1 3 0 00 26 Goto 0 1 0 00 @jasonwyatt
  28. Find out more about the VDBE https://sqlite.org/opcode.html @jasonwyatt

  29. More 'splainin to Do Use EXPLAIN QUERY PLAN to see

    how SQLite will go about rounding up your data. — When your queries are slow — When you are suspicious that complex queries could be optimized — Evaluate the need for updating/creating indexes @jasonwyatt
  30. Example CREATE TABLE albums ( `id` INTEGER NOT NULL, `band_id`

    INTEGER NOT NULL, `title` TEXT NOT NULL ); CREATE TABLE bands ( `id` INTEGER NOT NULL, `name` TEXT NOT NULL, `location` TEXT ); @jasonwyatt
  31. Example SELECT a.id AS album_id, b.name AS band_name, a.title AS

    album_title FROM bands AS b JOIN albums AS a ON a.band_id = b.id WHERE b.location = 'Berlin' ORDER BY b.name, a.title; @jasonwyatt
  32. Example EXPLAIN QUERY PLAN SELECT a.id AS album_id, b.name AS

    band_name, a.title AS album_title FROM bands AS b JOIN albums AS a ON a.band_id = b.id WHERE b.location = 'Berlin' ORDER BY b.name, a.title; selectid order from detail ---------- ---------- ---------- ------------------------ 0 0 0 SCAN TABLE bands AS b 0 1 1 SEARCH TABLE albums A... 0 0 0 USE TEMP B-TREE FOR O... @jasonwyatt
  33. Example EXPLAIN QUERY PLAN SELECT a.id AS album_id, b.name AS

    band_name, a.title AS album_title FROM bands AS b JOIN albums AS a ON a.band_id = b.id WHERE b.location = 'Berlin' ORDER BY b.name, a.title; selectid order from detail ---------- ---------- ---------- ------------------------ 0 0 0 SCAN TABLE bands AS b 0 1 1 SEARCH TABLE albums A... 0 0 0 USE TEMP B-TREE FOR O... @jasonwyatt
  34. How Indexes Work When you create an index on a

    table, you're essentially providing a faster way for SQLite to find your data. — PRIMARY KEY is kind of like an index, provides ordering for the table itself. If you don't specify one, you will be provided with rowid. — You can use CREATE INDEX to make more targeted indexes, including indexes on multiple columns. — Indexes allow SQLite to use binary search to find your data when you're looking for records based on something other than the PRIMARY KEY. @jasonwyatt
  35. How Indexes Work CREATE TABLE bands ( `id` INTEGER NOT

    NULL, selectid order from detail `name` TEXT NOT NULL, ---------- ---------- ---------- --------------------- `location` TEXT 0 0 0 SCAN TABLE bands AS b ); 0 1 1 SEARCH TABLE albums A 0 0 0 USE TEMP B-TREE FOR O EXPLAIN QUERY PLAN SELECT a.id AS album_id, b.name AS band_name, a.title AS album_title FROM bands AS b JOIN albums AS a ON a.band_id = b.id WHERE b.location = 'Berlin' ORDER BY b.name, a.title; @jasonwyatt
  36. How Indexes Work CREATE TABLE bands ( `id` INTEGER NOT

    NULL, selectid order from detail `name` TEXT NOT NULL, ---------- ---------- ---------- --------------------- `location` TEXT 0 0 0 SCAN TABLE bands AS b ); 0 1 1 SEARCH TABLE albums A 0 0 0 USE TEMP B-TREE FOR O EXPLAIN QUERY PLAN SELECT a.id AS album_id, b.name AS band_name, a.title AS album_title FROM bands AS b JOIN albums AS a ON a.band_id = b.id WHERE b.location = 'Berlin' ORDER BY b.name, a.title; @jasonwyatt
  37. SELECT * FROM bands WHERE location = 'Berlin'; rowid id

    name location Scan Location → 1 1 The Beatles Liverpool 2 2 Elvis Presley Memphis 3 3 Michael Jackson Los Angeles 4 4 Madonna Los Angeles 5 5 Led Zeppelin London 6 6 AC/DC Sydney 7 7 Rammstein Berlin 8 8 ABBA Stockholm ... ... ... ... @jasonwyatt
  38. SELECT * FROM bands WHERE location = 'Berlin'; rowid id

    name location 1 1 The Beatles Liverpool Scan Location → 2 2 Elvis Presley Memphis 3 3 Michael Jackson Los Angeles 4 4 Madonna Los Angeles 5 5 Led Zeppelin London 6 6 AC/DC Sydney 7 7 Rammstein Berlin 8 8 ABBA Stockholm ... ... ... ... @jasonwyatt
  39. SELECT * FROM bands WHERE location = 'Berlin'; rowid id

    name location 1 1 The Beatles Liverpool 2 2 Elvis Presley Memphis Scan Location → 3 3 Michael Jackson Los Angeles 4 4 Madonna Los Angeles 5 5 Led Zeppelin London 6 6 AC/DC Sydney 7 7 Rammstein Berlin 8 8 ABBA Stockholm ... ... ... ... @jasonwyatt
  40. SELECT * FROM bands WHERE location = 'Berlin'; rowid id

    name location 1 1 The Beatles Liverpool 2 2 Elvis Presley Memphis 3 3 Michael Jackson Los Angeles Scan Location → 4 4 Madonna Los Angeles 5 5 Led Zeppelin London 6 6 AC/DC Sydney 7 7 Rammstein Berlin 8 8 ABBA Stockholm ... ... ... ... @jasonwyatt
  41. SELECT * FROM bands WHERE location = 'Berlin'; rowid id

    name location 1 1 The Beatles Liverpool 2 2 Elvis Presley Memphis 3 3 Michael Jackson Los Angeles 4 4 Madonna Los Angeles Scan Location → 5 5 Led Zeppelin London 6 6 AC/DC Sydney 7 7 Rammstein Berlin 8 8 ABBA Stockholm ... ... ... ... @jasonwyatt
  42. SELECT * FROM bands WHERE location = 'Berlin'; rowid id

    name location 1 1 The Beatles Liverpool 2 2 Elvis Presley Memphis 3 3 Michael Jackson Los Angeles 4 4 Madonna Los Angeles 5 5 Led Zeppelin London Scan Location → 6 6 AC/DC Sydney 7 7 Rammstein Berlin 8 8 ABBA Stockholm ... ... ... ... @jasonwyatt
  43. SELECT * FROM bands WHERE location = 'Berlin'; rowid id

    name location 1 1 The Beatles Liverpool 2 2 Elvis Presley Memphis 3 3 Michael Jackson Los Angeles 4 4 Madonna Los Angeles 5 5 Led Zeppelin London 6 6 AC/DC Sydney Scan Location → 7 7 Rammstein Berlin 8 8 ABBA Stockholm ... ... ... ... @jasonwyatt
  44. SELECT * FROM bands WHERE location = 'Berlin'; rowid id

    name location 1 1 The Beatles Liverpool 2 2 Elvis Presley Memphis 3 3 Michael Jackson Los Angeles 4 4 Madonna Los Angeles 5 5 Led Zeppelin London 6 6 AC/DC Sydney 7 7 Rammstein Berlin Scan Location → 8 8 ABBA Stockholm ... ... ... ... @jasonwyatt
  45. SELECT * FROM bands WHERE location = 'Berlin'; rowid id

    name location 1 1 The Beatles Liverpool 2 2 Elvis Presley Memphis 3 3 Michael Jackson Los Angeles 4 4 Madonna Los Angeles 5 5 Led Zeppelin London 6 6 AC/DC Sydney 7 7 Rammstein Berlin 8 8 ABBA Stockholm Scan Location → ... ... ... ... @jasonwyatt
  46. Linear table scans are slow. @jasonwyatt

  47. How Indexes Work — Indexes allow SQLite to use binary

    search instead of a linear scan by providing an ordered structure to the table's data. — Linear scans are — Binary search is @jasonwyatt
  48. CREATE INDEX band_locname ON bands (`location`, `name` ASC); location name

    rowid Austin Explosions in the Sky 11 Austin Spoon 10 Berlin Rammstein 7 Berlin Real McCoy 9 Chicago Chicago 12 Denver The Lumineers 13 Liverpool The Beatles 1 ... ... ... Stockholm ABBA 8 Sydney AC/DC 6 @jasonwyatt
  49. Before EXPLAIN QUERY PLAN SELECT * FROM bands WHERE location

    = 'Berlin'; selectid order from detail ---------- ---------- ---------- -------------------------------------------------------- 0 0 0 SCAN TABLE bands After CREATE INDEX band_locname ON bands (`location`, `name` ASC); EXPLAIN QUERY PLAN SELECT * FROM bands WHERE location = 'Berlin'; selectid order from detail ---------- ---------- ---------- -------------------------------------------------------- 0 0 0 SEARCH TABLE bands USING INDEX band_locname (location=?) @jasonwyatt
  50. SELECT * FROM bands WHERE location = 'Berlin'; location name

    rowid Austin Explosions in the Sky 11 Austin Spoon 10 Berlin Rammstein 7 Berlin Real McCoy 9 Chicago Chicago 12 Denver The Lumineers 13 Liverpool The Beatles 1 ... ... ... Stockholm ABBA 8 Sydney AC/DC 6 @jasonwyatt
  51. SELECT * FROM bands WHERE location = 'Berlin'; location name

    rowid Austin Explosions in the Sky 11 Austin Spoon 10 Berlin Rammstein 7 Berlin Real McCoy 9 Chicago Chicago 12 Denver The Lumineers 13 Liverpool The Beatles 1 ... ... ... Stockholm ABBA 8 Sydney AC/DC 6 @jasonwyatt
  52. SELECT * FROM bands WHERE location = 'Berlin'; location name

    rowid Austin Explosions in the Sky 11 Austin Spoon 10 Berlin Rammstein 7 Berlin Real McCoy 9 Chicago Chicago 12 Denver The Lumineers 13 Liverpool The Beatles 1 ... ... ... Stockholm ABBA 8 Sydney AC/DC 6 @jasonwyatt
  53. SELECT * FROM bands WHERE location = 'Berlin'; location name

    rowid Austin Explosions in the Sky 11 Austin Spoon 10 Berlin Rammstein 7 Berlin Real McCoy 9 Chicago Chicago 12 Denver The Lumineers 13 Liverpool The Beatles 1 ... ... ... Stockholm ABBA 8 Sydney AC/DC 6 @jasonwyatt
  54. After a record's PRIMARY KEY (e.g. rowid) is located in

    the index, SQLite uses another binary search on the actual table. @jasonwyatt
  55. SELECT * FROM bands WHERE location = 'Berlin'; Found matching

    record using band_locname at rowid 7 rowid id name location 1 1 The Beatles Liverpool 2 2 Elvis Presley Memphis 3 3 Michael Jackson Los Angeles 4 4 Madonna Los Angeles 5 5 Led Zeppelin London 6 6 AC/DC Sydney 7 7 Rammstein Berlin 8 8 ABBA Stockholm Using Index → ... ... ... ... @jasonwyatt
  56. SELECT * FROM bands WHERE location = 'Berlin'; Found matching

    record using band_locname at rowid 7 rowid id name location 1 1 The Beatles Liverpool 2 2 Elvis Presley Memphis 3 3 Michael Jackson Los Angeles 4 4 Madonna Los Angeles Using Index → 5 5 Led Zeppelin London 6 6 AC/DC Sydney 7 7 Rammstein Berlin 8 8 ABBA Stockholm ... ... ... ... @jasonwyatt
  57. SELECT * FROM bands WHERE location = 'Berlin'; Found matching

    record using band_locname at rowid 7 rowid id name location 1 1 The Beatles Liverpool 2 2 Elvis Presley Memphis 3 3 Michael Jackson Los Angeles 4 4 Madonna Los Angeles 5 5 Led Zeppelin London 6 6 AC/DC Sydney Using Index → 7 7 Rammstein Berlin 8 8 ABBA Stockholm ... ... ... ... @jasonwyatt
  58. Binary search is fast. @jasonwyatt

  59. Even two, or three, or more binary searches will be

    way faster than a single linear scan with enough data. @jasonwyatt
  60. In some situations you can even remove the second binary

    search. @jasonwyatt
  61. Covering Indexes — When your index includes all of the

    columns you are selecting, it's called a "covering index". — SQLite is smart enough to skip the binary search of the actual table data and just returns information straight from the index. @jasonwyatt
  62. Downsides of Indexes — Each index on a table is

    a copy of the data it indexes. — Not as useful without high "cardinality". (lots of different values) — INSERT / UPDATE / DELETE will take a little longer. @jasonwyatt
  63. Example CREATE TABLE albums ( `id` INTEGER PRIMARY KEY NOT

    NULL, `band_id` INTEGER NOT NULL, `title` TEXT NOT NULL ); CREATE TABLE bands ( `id` INTEGER PRIMARY KEY NOT NULL, `name` TEXT NOT NULL, `location` TEXT ); CREATE INDEX album_bandid ON albums (`band_id`); CREATE INDEX band_locname ON bands (`location`, `name`); @jasonwyatt
  64. Example CREATE TABLE albums ( `id` INTEGER PRIMARY KEY NOT

    NULL, `band_id` INTEGER NOT NULL, `title` TEXT NOT NULL ); CREATE TABLE bands ( `id` INTEGER PRIMARY KEY NOT NULL, `name` TEXT NOT NULL, `location` TEXT ); CREATE INDEX album_bandid ON albums (`band_id`); CREATE INDEX band_locname ON bands (`location`, `name`); @jasonwyatt
  65. Example EXPLAIN QUERY PLAN SELECT a.id AS album_id, b.name AS

    band_name, a.title AS album_title FROM bands AS b JOIN albums AS a ON a.band_id = b.id WHERE b.location = 'Berlin' ORDER BY b.name, a.title; @jasonwyatt
  66. Example Before selectid order from detail ---------- ---------- ---------- ----------------------------------------------------------------------

    0 0 0 SCAN TABLE bands AS b 0 1 1 SEARCH TABLE albums AS a USING AUTOMATIC COVERING INDEX (band_id=?) 0 0 0 USE TEMP B-TREE FOR ORDER BY After selectid order from detail ---------- ---------- ---------- ---------------------------------------------------------------------- 0 0 0 SEARCH TABLE bands AS b USING COVERING INDEX band_locname (location=?) 0 1 1 SEARCH TABLE albums AS a USING INDEX album_bandid (band_id=?) 0 0 0 USE TEMP B-TREE FOR RIGHT PART OF ORDER BY @jasonwyatt
  67. Example Before selectid order from detail ---------- ---------- ---------- ----------------------------------------------------------------------

    0 0 0 SCAN TABLE bands AS b 0 1 1 SEARCH TABLE albums AS a USING AUTOMATIC COVERING INDEX (band_id=?) 0 0 0 USE TEMP B-TREE FOR ORDER BY After selectid order from detail ---------- ---------- ---------- ---------------------------------------------------------------------- 0 0 0 SEARCH TABLE bands AS b USING COVERING INDEX band_locname (location=?) 0 1 1 SEARCH TABLE albums AS a USING INDEX album_bandid (band_id=?) 0 0 0 USE TEMP B-TREE FOR RIGHT PART OF ORDER BY @jasonwyatt
  68. Example Before selectid order from detail ---------- ---------- ---------- ----------------------------------------------------------------------

    0 0 0 SCAN TABLE bands AS b 0 1 1 SEARCH TABLE albums AS a USING AUTOMATIC COVERING INDEX (band_id=?) 0 0 0 USE TEMP B-TREE FOR ORDER BY After selectid order from detail ---------- ---------- ---------- ---------------------------------------------------------------------- 0 0 0 SEARCH TABLE bands AS b USING COVERING INDEX band_locname (location=?) 0 1 1 SEARCH TABLE albums AS a USING INDEX album_bandid (band_id=?) 0 0 0 USE TEMP B-TREE FOR RIGHT PART OF ORDER BY @jasonwyatt
  69. How Indexes Work You can read more: — EXPLAIN QUERY

    PLAN: https://sqlite.org/eqp.html — Indexes: https://sqlite.org/queryplanner.html — My Medium: https://medium.com/@JasonWyatt/ squeezing-performance-from-sqlite-indexes-indexes- c4e175f3c346 @jasonwyatt
  70. For a short break, let's do something Android-specific. @jasonwyatt

  71. Consider using a database Cursor instead of loading all of

    your data into memory for a RecyclerView adapter.* @jasonwyatt
  72. Cursor-based RecyclerView Adapter — Set up the cursor within a

    method you will call from onStart() — Use cursor.getCount() in getItemCount() — Move the cusor back and forth with cursor.moveToPosition(pos) when fetching data for onBindViewHolder() — Don't forget to tear down the cursor with a method you can call from onStop() @jasonwyatt
  73. Cursor-based RecyclerView Adapter Consider using when: — Your query can

    execute very quickly. — And you have a really large amount of data to display and it doesn't all fit in memory. @jasonwyatt
  74. It depends... @jasonwyatt

  75. @jasonwyatt

  76. It's better to drop a few frames than it is

    to crash because devices can't handle the data in-memory. @jasonwyatt
  77. Insert hundreds or thousands of records as quickly as possible.

    @jasonwyatt
  78. Insertions — Wrap multiple statements in a single transaction. —

    Consider inserting multiple records in each statement. these rules can also apply to UPDATEs and DELETEs @jasonwyatt
  79. Insertions Wrap multiple statements in a single transaction. Object[] values

    = new Object[3]; for (int i = 0; i < bandsFromServer.length; i++) { values[0] = bandsFromServer[i].id; values[1] = bandsFromServer[i].name; values[2] = bandsFromServer[i].location; db.execSQL("INSERT INTO bands (id, name, location) VALUES (?, ?, ?)", values); } @jasonwyatt
  80. Insertions Wrap multiple statements in a single transaction. Object[] values

    = new Object[3]; for (int i = 0; i < bandsFromServer.length; i++) { values[0] = bandsFromServer[i].id; values[1] = bandsFromServer[i].name; values[2] = bandsFromServer[i].location; db.execSQL("INSERT INTO bands (id, name, location) VALUES (?, ?, ?)", values); } This is slow. @jasonwyatt
  81. Insertions Wrap multiple statements in a single transaction. db.beginTransaction(); Object[]

    values = new Object[3]; for (int i = 0; i < bandsFromServer.length; i++) { values[0] = bandsFromServer[i].id; values[1] = bandsFromServer[i].name; values[2] = bandsFromServer[i].location; db.execSQL("INSERT INTO bands (id, name, location) VALUES (?, ?, ?)", values); } db.setTransactionSuccessful(); db.endTransaction(); This is much faster. @jasonwyatt
  82. Insertions Wrap multiple statements in a single transaction. @jasonwyatt

  83. No changes can be made to the database except within

    a transaction. Any command that changes the database will automatically start a transaction if one is not already in effect. Automatically started transactions are committed when the last query finishes. — https://sqlite.org/lang_transaction.html @jasonwyatt
  84. Insertions Consider inserting multiple records in each statement. db.execSQL("INSERT INTO

    bands (id, name, location) VALUES (?, ?, ?), (?, ?, ?), ...", ...); Warning! SQLite limits you to 999 variables per statement. From sqlite3.c: /* ** The maximum value of a ?nnn wildcard that the parser will accept. */ #ifndef SQLITE_MAX_VARIABLE_NUMBER # define SQLITE_MAX_VARIABLE_NUMBER 999 #endif @jasonwyatt
  85. Insertions Consider inserting multiple records in each statement. — Not

    as big of an improvement when compared with going from not-wrapping inserts in a transaction, but still good. — Beware of the max variable number restriction. — Some older versions of Android (and thus: older versions of SQLite) are limited to the number of records that can be inserted at once. @jasonwyatt
  86. Insertions Find more on my Medium: https://medium.com/@JasonWyatt/squeezing- performance-from-sqlite-insertions-971aff98eef2 @jasonwyatt

  87. A SQLite database is just a file. @jasonwyatt

  88. Content-Type: application/x-sqlite3 Because SQLite databases are stored in a single

    file, consider serving up a database from your API to your app. @jasonwyatt
  89. Content-Type: application/x-sqlite3 public interface DatabaseFetchService { @Headers("Accept: application/x-sqlite3") @GET("myapi/3/database_sync") Call<RequestBody>

    fetchDatabase(); } @jasonwyatt
  90. Content-Type: application/x-sqlite3 @WorkerThread public SQLiteDatabase getDatabase() throws IOException { File

    f = new File(app.getFilesDir(), "mydatabase.sqlite3"); if (!f.exists()) { Call<RequestBody> call = myDatabaseFetchService.fetchDatabase(); Response<RequestBody> response = call.execute(); copyToFile(f, response.body()); } return SQLiteDatabase.openOrCreateDatabase(f, null); } @jasonwyatt
  91. Content-Type: application/x-sqlite3 Caveats — Managing migrations can be a bit

    more complex. — Api versioning needs to be in lock-step with the app's versioning. — If you want to allow the user to change the data, consider a second database. — "Joining" data between two separate SQLite databases needs to be done in code. — This method of fetching and opening a database isn't yet possible with Room. @jasonwyatt
  92. Wrapping Up @jasonwyatt

  93. Wrapping Up Always keep your best/biggest users in mind, keep

    them happy and try to make it so new users' experiences don't degrade as they grow into your app. @jasonwyatt
  94. Wrapping Up Always keep your best/biggest users in mind, keep

    them happy and try to make it so new users' experiences don't degrade as they grow into your app. Get curious with EXPLAIN and EXPLAIN QUERY PlAN and profile your code. @jasonwyatt
  95. Thanks! Danke schön! twitter.com/jasonwyatt github.com/jasonwyatt bandcamp.com/jasonwyatt @jasonwyatt