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.

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
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