Meta tags:
description= Optimize your Android app's performance by following best practices for SQLite databases, including configuration, schema design, query optimization, and troubleshooting.;
keywords= Android, SQLite, performance, database, optimization, best practices, schema, queries, indexing, WAL;
Headings (most frequently used words):
use, for, sqlite, the, in, performance, and, you, queries, android, with, content, query, logging, consider, indexes, data, as, read, only, need, sql, code, instead, of, best, practices, stay, organized, collections, save, categorize, based, on, your, preferences, configure, database, define, efficient, table, schemas, improve, troubleshooting, tools, additional, resources, recommended, enable, write, ahead, relax, synchronization, mode, integer, primary, key, accelerate, create, multi, column, without, rowid, store, small, blob, large, file, rows, columns, parameterize, iterate, not, distinct, unique, values, aggregate, functions, whenever, possible, count, cursor, getcount, nest, check, uniqueness, batch, multiple, insertions, single, transaction, interactive, prompt, explain, plan, analyzer, browser, perfetto, tracing, dumpsys, meminfo, views, more, discover, devices, releases, documentation, downloads, support,
Text of the page (most frequently used words):
the (179), and (104), #android (104), for (70), data (63), you (50), sqlite (50), use (50), play (48), app (48), #performance (46), google (43), your (43), customers (40), cursor (39), query (36), from (35), user (34), name (33), this (32), databases (32), city (32), gms (31), can (31), index (30), with (29), com (28), table (28), that (27), queries (24), select (24), column (24), database (23), more (21), cache (21), memory (21), example (21), all (19), create (19), get (16), code (16), are (16), count (16), following (16), using (16), only (16), overview (16), null (15), libraries (15), best (14), where (14), customer (14), rawquery (14), not (13), tools (13), trimindent (13), rowid (13), build (13), studio (12), efficient (12), profiles (11), practices (11), size (11), sql (11), about (11), plan (11), optimize (11), quality (11), wear (10), number (10), one (10), device (10), paris (10), values (10), unique (10), row (10), design (10), note (9), when (9), value (9), columns (9), see (9), how (9), learn (9), return (9), games (9), experiences (9), privacy (8), cars (8), thumb (8), down (8), baseline (8), without (8), text (8), phenotype (8), rows (8), write (8), insert (8), integer (8), primary (8), key (8), order (8), latest (8), core (8), startup (8), need (7), page (7), run (7), hits (7), misses (7), than (7), adb (7), time (7), read (7), keep (7), help (7), into (7), improve (7), accelerate (7), faster (7), indexes (7), username (7), instead (7), movetofirst (7), better (7), most (7), apps (7), optimization (7), services (7), updates (7), compose (7), profile (7), capture (7), profiling (7), support (6), documentation (6), api (6), releases (6), security (6), follow (6), samples (6), measure (6), find (6), file (6), these (6), attached (6), temp (6), usage (6), shell (6), enable (6), logging (6), also (6), explain (6), different (6), arrayof (6), multiple (6), which (6), define (6), now (6), doing (6), process (6), movetonext (6), while (6), val (6), results (6), distinct (6), store (6), england (6), case (6), library (6), technical (6), console (6), background (6), experience (6), battery (6), trace (6), platform (5), developers (5), guides (5), chromeos (5), devices (5), large (5), health (5), blog (5), other (5), information (5), content (5), resources (5), some (5), stats (5), have (5), connection (5), same (5), developer (5), add (5), test (5), scan (5), troubleshooting (5), kotlin (5), then (5), storage (5), approach (5), way (5), getstring (5), however (5), string (5), power (5), jetpack (5), areas (5), work (5), events (5), programs (5), explore (5), phones (5), tablets (5), foldables (5), excessive (5), rendering (5), users (5), news (4), guidelines (4), check (4), many (4), additional (4), statements (4), them (4), our (4), another (4), pgsz (4), 9280 (4), 123 (4), details (4), tracing (4), install (4), command (4), line (4), troubleshoot (4), requires (4), real (4), engine (4), powered (4), tostring (4), execsql (4), single (4), constraint (4), let (4), might (4), limit (4), has (4), types (4), aggregate (4), handle (4), found (4), else (4), such (4), slow (4), because (4), consider (4), files (4), filtering (4), multi (4), cost (4), sevier (4), county (4), liverpool (4), gary (4), fast (4), synchronous (4), wal (4), set (4), mode (4), issues (4), sdk (4), delivery (4), monetization (4), googlebook (4), debug (4), excellent (4), glasses (4), form (4), world (4), started (4), analyze (4), rules (4), microbenchmark (4), benchmark (4), brand (3), studies (3), report (3), download (3), reference (3), downloads (3), camera (3), media (3), fitness (3), enterprise (3), community (3), date (3), macrobenchmark (3), does (3), statement (3), their (3), there (3), per (3), dbname (3), pages (3), lookaside (3), small (3), dbsz (3), gaia (3), discovery (3), user_de (3), context_feature_default (3), gservices (3), notifications (3), android_pay (3), dumpsys (3), meminfo (3), include (3), log (3), times (3), target (3), ask (3), plans (3), search (3), previous (3), fix (3), linear (3), transaction (3), but (3), less (3), getint (3), exists (3), group (3), result (3), returns (3), getcount (3), functions (3), least (3), loop (3), between (3), getnamebyid (3), long (3), processing (3), avoid (3), control (3), examples (3), system (3), cases (3), blob (3), tables (3), ordering (3), since (3), given (3), based (3), dolly (3), parton (3), 789 (3), john (3), lennon (3), 456 (3), michael (3), jackson (3), follows (3), schema (3), setting (3), disk (3), sqlitedatabase (3), configure (3), stay (3), authors (3), apis (3), feature (3), level (3), vitals (3), together (3), gradle (3), projects (3), connectivity (3), gemini (3), testing (3), introduction (3), preview (3), ide (3), adaptive (3), anrs (3), optimizations (3), monitor (3), analyzing (3), custom (3), develop (3), development (3), 한국어 (2), 日本語 (2), ภาษาไทย (2), বাংলা (2), हिंदी (2), فارسی (2), العربيّة (2), עברית (2), русский (2), türkçe (2), tiếng (2), việt (2), português (2), brasil (2), polski (2), italiano (2), indonesia (2), français (2), español (2), américa (2), latina (2), deutsch (2), english (2), tips (2), manage (2), license (2), join (2), bug (2), guide (2), machine (2), discover (2), connect (2), linkedin (2), out (2), youtube (2), understand (2), too (2), steps (2), last (2), updated (2), 2026 (2), utc (2), java (2), trademarks (2), views (2), prepared (2), versions (2), sum (2), save (2), under (2), pool (2), path (2), underlying (2), multiply (2), very (2), entire (2), 252 (2), 233 (2), 105 (2), 107 (2), 156 (2), mobstore_gc_db_v0 (2), 300 (2), 120 (2), googlesettings (2), related (2), output (2), individual (2), perfetto (2), setprop (2), tag (2), sqlitetime (2), error (2), browser (2), pull (2), analysis (2), offers (2), interface (2), used (2), analyzer (2), interactive (2), always (2), idx1 (2), full (2), called (2), needs (2), match (2), provides (2), try (2), commits (2), operations (2), improves (2), efficiency (2), batch (2), insertions (2), constraints (2), practice (2), supports (2), doesn (2), extra (2), addcustomerexception (2), throw (2), uses (2), inserted (2), unless (2), uniqueness (2), desc (2), innercursor (2), topcity (2), numerical (2), works (2), first (2), matching (2), iterate (2), reducing (2), amount (2), want (2), programmatic (2), argument (2), constrained (2), compiled (2), fun (2), every (2), constructs (2), benefit (2), each (2), before (2), parameter (2), runtime (2), further (2), phone (2), limits (2), default (2), outside (2), still (2), accelerated (2), inside (2), specified (2), london (2), don (2), implemented (2), sorted (2), sorting (2), insertion (2), looks (2), filter (2), minimize (2), retrieval (2), section (2), schemas (2), crashes (2), reaches (2), commit (2), normal (2), paramsbuilder (2), builder (2), openparams (2), ensure (2), ahead (2), retrieve (2), achieve (2), reduce (2), tos (2), product (2), categories (2), marketing (2), bundles (2), referrer (2), reviews (2), asset (2), dev (2), center (2), policies (2), integrity (2), fundamentals (2), tech (2), bench (2), plugin (2), workflow (2), interfaces (2), multidevice (2), fraud (2), prevention (2), identity (2), permissions (2), accessibility (2), multiplatform (2), modularization (2), navigation (2), architecture (2), widgets (2), headsets (2), desktop (2), mobile (2), sandbox (2), experimental (2), productivity (2), social (2), messaging (2), category (2), factor (2), training (2), verification (2), hello (2), wake (2), locks (2), swap (2), common (2), monitoring (2), management (2), historian (2), state (2), standby (2), gpu (2), problems (2), study (2), rule (2), choose (2), benchmarking (2), instrumentation (2), arguments (2), native (2), profilingmanager (2), inspecting (2), threads (2), quick (2), processes (2), essentials (2), designed (2), grow (2), step (2), features (2), browse (2), secure (2), factors (2), subscribe, email, cookies, products, cloud, firebase, chrome, research, ndk, screens, learning, gaming, podcasts, source, androiddev, easy, easytounderstand, solved, problem, solvedmyproblem, otherup, missing, missingtheinformationineed, complicated, toocomplicatedtoomanysteps, outofdate, issue, samplescodeissue, otherdown, subject, licenses, described, openjdk, registered, oracle, its, affiliates, frozen, frames, benchmarks, continuous, integration, link, displayed, javascript, off, recommended, starting, lists, total, earlier, equivalent, listed, two, represent, caches, attempts, reuse, running, effort, compiling, dbs, appended, indicate, tracked, maintains, allocated, buffer, bytes, typically, 124, 103169, 69805, 108, 13877, 6394, 12548, 5519, 18328, 7886, 2111, 143, 163, 145, 378, 147921, 89616, 237537, 186, 2114, 2157, 229, 197, 426, will, print, including, was, taken, persistent, package, atrace_categories, ftrace_config, linux, ftrace, config, data_sources, may, tracks, configuring, verbose, disable, logs, gui, tool, app_package_name, db_name, workstation, cli, dump, visit, sqlite3_analyzer, planning, eqp, complexity, intends, answer, timer, sys, revisions, sqlite3, prompt, occur, serialize, writes, moreexecutors, newsequentialexecutor, executor, endtransaction, finally, customervalue, foreach, begintransaction, correctness, consistency, validates, overhead, rather, column2, column1, unique_table, applies, sqliteconstraintexception, catch, created, querying, customersusername, checking, validate, actually, must, particular, enforce, half, nested, inner, composable, subqueries, joins, foreign, going, through, reduces, copy, lets, nest, function, reads, concatenates, strings, optional, separator, group_concat, finds, average, avg, determines, lowest, highest, numeric, max, min, adds, counts, fetch, exist, checks, whether, whenever, possible, over, names, keyword, processed, targeted, iterating, 1000, slower, input, object, just, concatenation, lead, injection, vulnerability, parameters, variables, untrusted, caution, once, cached, reused, invocations, preceding, thus, call, compile, execute, replace, bind, selectionargs, known, parameterize, selecting, unneeded, waste, filters, narrow, specifying, certain, criteria, range, location, clause, good, could, implications, returning, sets, exception, implicitly, dataset, searching, field, defined, zero, minimizing, response, maximizing, any, several, multiples, generally, rounded, increments, rounding, significant, minimizes, calls, filesystem, associate, thumbnail, image, photo, contact, either, incur, penalties, keys, warning, composite, creates, implicit, already, becomes, alias, autoincrement, prefix, inquiries, accelerates, grouping, ordered, city_name_index, combine, fully, done, double, present, both, original, added, worth, maintain, paying, gain, mapped, city_index, lot, those, scanning, aggregating, lookups, storing, overall, preserves, tree, populate, consumption, leading, creating, everywhere, dependencies, yields, improvements, material, stored, shutdown, occurs, loss, kernel, panic, committed, lost, isn, corrupted, pragma, after, having, opened, sync_mode_normal, journalmode, opening, option, durability, slows, fsync, relax, synchronization, attach, implements, mutations, appending, occasionally, compacts, optimal, recommend, room, additionally, available, identify, require, construct, representations, properly, structures, enhance, modify, perform, computations, within, significantly, push, necessary, excess, impact, fewer, principles, ensuring, remains, predictably, grows, possibility, encountering, difficult, reproduce, built, categorize, preferences, organized, collections, healthy, jankstats, dex, permission, denials, network, scans, wakeups, stuck, partial, bitmap, anonymous, rss, low, killers, lmks, sessions, address, hardware, acceleration, resource, batterystats, determining, docking, type, status, metering, charging, doze, layout, view, hierarchies, overdraw, unresponsive, thread, diagnose, responsive, solving, verify, behavior, art, hibernation, buckets, declare, class, environment, gmail, difference, confirm, calendar, manually, generation, customize, variant, advanced, configurations, global, options, adopt, incrementally, wisely, configuration, packages, optimizer, improving, building, hilt, adding, metrics, writing, inspect, navigate, commands, local, limitations, practical, debugging, anr, bulk, worker, uploading, trigger, driven, right, method, profilers, apa, helpful, kswapd, lmkd, interaction, reclaim, eviction, wide, service, bindings, states, locality, webview, bitmaps, assessment, fundamental, concepts, understanding, budgets, allocation, among, score, sign, access, detailed, manuals, references, specifications, integrate, confidence, upcoming, webinars, workshops, meetups, special, initiatives, teams, goals, deep, dive, tutorials, smarter, stories, spotlight, collaborative, bring, behind, scenes, evolving, publishing, promoting, managing, deliver, engage, monitize, publish, game, business, share, own, pipeline, docs, companion, safeguard, against, threats, align, robust, testable, maintainable, logic, beautiful, touch, throughout, year, give, feedback, prescriptive, opinionated, guidance, skip, main,
Text of the page (random words):
tore the rowid column id queries are fast note that the table is now sorted based on id instead of insertion order accelerate queries with indexes sqlite uses indexes to accelerate queries when filtering where sorting order by or aggregating group by a column if the table has an index for the column the query is accelerated in the previous example filtering by city requires scanning the entire table select id name where city london england for an app with a lot of city queries you can accelerate those queries with an index create index city_index on customers city an index is implemented as an additional table sorted by the index column and mapped to rowid city rowid gary in 2 liverpool england 1 sevier county tn 3 note that the storage cost of the city column is now double because it s now present in both the original table and the index since you are using the index the cost of added storage is worth the benefit of faster queries however don t maintain an index that you re not using to avoid paying the storage cost for no query performance gain create multi column indexes if your queries combine multiple columns you can create multi column indexes to fully accelerate the query you can also use an index on an outside column and let the inside search be done as a linear scan for example given the following query select id name where city london england order by city name you can accelerate the query with a multi column index in the same order as specified in the query create index city_name_index on customers city name however if you only have an index on city the outside ordering is still accelerated while the inside ordering requires a linear scan this also works with prefix inquiries for example an index on customers city name also accelerates filtering ordering and grouping by city since the index table for a multi column index is ordered by the given indexes in the given order consider without rowid by default sqlite creates a rowid column for your table where rowid is an implicit integer primary key autoincrement if you already have a column that is integer primary key then this column becomes an alias of rowid for tables that have a primary key other than integer or a composite of columns consider without rowid warning tables using without rowid can incur performance penalties if their primary keys are large for more information see the use cases store small data as a blob and large data as a file if you want to associate large data with a row such as a thumbnail of an image or a photo for a contact you can store the data either in a blob column or in a file and then store the path in the column files are generally rounded up to 4 kb increments for very small files where the rounding error is significant it s more efficient to store them in the database as a blob sqlite minimizes file system calls and is faster than the underlying filesystem in some cases note on android consider using a file for any data that is several multiples of 4 kb improve query performance follow these best practices to improve query performance in sqlite by minimizing response times and maximizing processing efficiency note many of the examples on this page include a limit clause this is good practice because queries that could return many rows have performance implications when returning large data sets the exception to this is for queries that implicitly return a size constrained dataset for example searching on a field defined as unique can only return zero or one row read only the rows you need filters let you narrow down your results by specifying certain criteria such as date range location or name limits let you control the number of results you see db rawquery select name from customers limit 10 trimindent null use cursor while cursor movetonext process cursor data read only the columns you need avoid selecting unneeded columns which can slow down your queries and waste resources instead only select columns that are used in the following example you select id name and phone this is not the most efficient way of doing this see the following example for a better approach db rawquery select id name phone from customers trimindent null use cursor while cursor movetonext val name cursor getstring 1 further processing however you only need the name column db rawquery select name from customers trimindent null use cursor while cursor movetonext val name cursor getstring 0 further processing parameterize queries your query string might include a parameter that is only known at runtime such as the following fun getnamebyid id long string db rawquery select name from customers where id id null use cursor return if cursor movetofirst cursor getstring 0 else null in the preceding code every query constructs a different string and thus doesn t benefit from the statement cache each call requires sqlite to compile it before it can execute instead you can replace the id argument with a parameter and bind the value with selectionargs fun getnamebyid id long string db rawquery select name from customers where id trimindent arrayof id tostring use cursor return if cursor movetofirst cursor getstring 0 else null now the query can be compiled once and cached the compiled query is reused between different invocations of getnamebyid long caution if the input argument is some other object less constrained than just a number string concatenation might lead to a sql injection security vulnerability always use parameters for variables or untrusted data iterate in sql not in code use a single query that returns all targeted results instead of a programmatic loop iterating on sql queries to return individual results the programmatic loop is about 1000 times slower than a single sql query use distinct for unique values using the distinct keyword can improve the performance of your queries by reducing the amount of data that needs to be processed for example if you want to return only the unique values from a column use distinct db rawquery select distinct name from customers trimindent null use cursor while cursor movetonext only iterate over distinct names in kotlin process distinct name use aggregate functions whenever possible use aggregate functions for aggregate results without row data for example the following code checks whether there is at least one matching row this is not the most efficient way of doing this see the following example for a better approach db rawquery select id name from customers where city paris trimindent null use cursor if cursor movetofirst at least one customer from paris handle found else no customers from paris handle not found to only fetch the first row you can use exists to return 0 if a matching row does not exist and 1 if one or more rows match db rawquery select exists select null from customers where city paris trimindent null use cursor if cursor movetofirst cursor getint 0 1 at least one customer from paris handle found else no customers from paris handle not found use sqlite aggregate functions in your app code count counts how many rows are in a column sum adds all numerical values in a column min or max determines the lowest or highest value works for numeric columns date types and text types avg finds the average numerical value group_concat concatenates strings with an optional separator use count instead of cursor getcount in the following example the cursor getcount function reads all the rows from the database and returns all the row values this is not the most efficient way of doing this see the following example for a better approach db rawquery select id from customers trimindent null use cursor val count cursor getcount use count however by using count the database returns only the count db rawquery select count from customers trimindent null use cursor cursor movetofirst val count cursor getint 0 use count nest queries instead of code sql is composable and supports subqueries joins and foreign key constraints you can use the result of one query in another query without going through app code this reduces the need to copy data from sqlite and lets the database engine optimize your query in the following example you can run a query to find which city has the most customers then use the result in another query to find all the customers from that city this is not the most efficient way of doing this see the following example for a better approach db rawquery select city from customers group by city order by count desc limit 1 trimindent null use cursor if cursor movetofirst val topcity cursor getstring 0 db rawquery select name city from customers where city trimindent arrayof topcity use innercursor while innercursor movetonext process inner cursor data to get the result in half the time of the previous example use a single sql query with nested statements db rawquery select name city from customers where city in select city from customers group by city order by count desc limit 1 trimindent null use cursor if cursor movetonext process cursor data check uniqueness in sql if a row must not be inserted unless a particular column value is unique in the table then it might be more efficient to enforce that uniqueness as a column constraint in the following example one query is run to validate the row to be inserted and another to actually insert this is not the most efficient way of doing this see the following example for a better approach db rawquery select exists select null from customers where username trimindent arrayof customer username use cursor if cursor movetofirst cursor getint 0 1 throw addcustomerexception customer db execsql insert into customers values arrayof customer id tostring customer name customer username instead of checking the unique constraint in kotlin you can check it in sql when you define the table create table customers id integer primary key name text username text unique sqlite does the same as the following create table customers create unique index customersusername on customers username note an index table is created for username which uses extra storage for more information about querying an index table see accelerate queries with indexes now you can insert a row and let sqlite check the constraint try db execsql insert into customers values arrayof customer id tostring customer name customer username catch e sqliteconstraintexception throw addcustomerexception customer e note if you define integer primary key then a unique constraint applies to that column and doesn t use an extra index table sqlite supports unique indexes with multiple columns create table table create unique index unique_table on table column1 column2 sqlite validates constraints faster and with less overhead than kotlin code it is a best practice to use sqlite rather than app code batch multiple insertions in a single transaction a transaction commits multiple operations which improves not only efficiency but also correctness to improve data consistency and accelerate performance you can batch insertions db begintransaction try customers foreach customer db execsql insert into customers values arrayof customer id tostring customer name customervalue finally db endtransaction note only one write transaction can occur at a time use moreexecutors newsequentialexecutor executor to serialize writes use troubleshooting tools sqlite provides the following troubleshooting tools to help measure performance use sqlite s interactive prompt run sqlite on your machine to run queries and learn different android platform versions use different revisions of sqlite to use the same engine that s on an android powered device use adb shell and run sqlite3 on your target device you can ask sqlite to time queries sqlite timer on sqlite select run time real user sys explain query plan you can ask sqlite to explain how it intends to answer a query by using explain query plan sqlite explain query plan select id name from customers where city paris query plan scan customers the previous example requires a full table scan without an index to find all customers from paris this is called linear complexity sqlite needs to read all the rows and only keep the rows that match customers from paris to fix this you can add an index sqlite create index idx1 on customers city sqlite explain query plan select id name from customers where city paris query plan search test using index idx1 city if you re using the interactive shell you can ask sqlite to always explain query plans sqlite eqp on for more information see query planning sqlite analyzer sqlite offers the sqlite3_analyzer command line interface cli to dump additional information that can be used to troubleshoot performance to install visit the sqlite download page you can use adb pull to download a database file from a target device to your workstation for analysis adb pull data data app_package_name databases db_name db sqlite browser you can also install the gui tool sqlite browser on the sqlite downloads page android logging android times sqlite queries and logs them for you enable query time logging adb shell setprop log tag sqlitetime verbose disable query time logging adb shell setprop log tag sqlitetime error perfetto tracing when configuring perfetto you may add the following to include tracks for individual queries data_sources config name linux ftrace ftrace_config atrace_categories database dumpsys meminfo adb shell dumpsys meminfo package name will print stats related to the app s memory usage including some details about sqlite memory for example this was taken from the output of adb shell dumpsys meminfo com google android gms persistent on a developer s device databases pgsz dbsz lookaside b cache hits cache misses cache size dbname per connection stats 4 52 45 8 41 6 data user 10 com google android gms databases gaia discovery 4 8 0 0 0 attached temp 4 52 56 5 23 6 data user 10 com google android gms databases gaia discovery 1 4 252 95 233 124 12 data user_de 10 com google android gms databases phenotype db 4 8 0 0 0 attached temp 4 252 17 0 17 1 data user_de 10 com google android gms databases phenotype db 1 4 9280 105 103169 69805 25 data user 10 com google android gms databases phenotype db 4 20 0 0 0 attached temp 4 9280 108 13877 6394 25 data user 10 com google android gms databases phenotype db 2 4 8 0 0 0 attached temp 4 9280 105 12548 5519 25 data user 10 com google android gms databases phenotype db 3 4 8 0 0 0 attached temp 4 9280 107 18328 7886 25 data user 10 com google android gms databases phenotype db 1 4 8 0 0 0 attached temp 4 36 51 156 29 5 data user 10 com google android gms databases mobstore_gc_db_v0 4 36 97 47 27 10 data user 10 com google android gms databases context_feature_default db 4 36 56 3 16 4 data user 10 com google android gms databases context_feature_default db 2 4 300 40 2111 24 5 data user 10 com google android gms databases gservices db 4 300 39 3 17 4 data user 10 com google android gms databases gservices db 1 4 20 17 ...
|
