Hi,
I am facing issue with SQLite performance when having more records in the table
and fetching from higher offsets, though I have proper index in place. Using
SQLite version 3.7.10 running on ARM Cortex A5 processor.
Here is my schema (relevant table and index) and query
CREATE TABLE FileTable(
FileID
INTEGER PRIMARY KEY,
FileName TEXT
DEFAULT NULL,
FileType INTEGER
DEFAULT 0,
GenreID INTEGER
DEFAULT 0,
ArtistID
INTEGER DEFAULT 0,
ComposerID INTEGER
DEFAULT 0,
AlbumID INTEGER
DEFAULT 0,
TrackID INTEGER
DEFAULT 0,
TrackName TEXT DEFAULT NULL,
TrackDuration INTEGER DEFAULT
0,
InValidFlag INTEGER
DEFAULT 0
)
CREATE INDEX Idx_TrackName_OnFileTable ON FileTable(FileType, TrackName COLLATE
NOCASE ASC);
SELECT FileID , TrackName, FileType, InValidFlag FROM FileTable WHERE
FileType = 1 AND TrackName <>"" ORDER BY TrackName COLLATE NOCASE LIMIT 10
OFFSET 19950
database connection opened with flags (SQLITE_OPEN_READWRITE |
SQLITE_OPEN_FULLMUTEX)
PRAGMA journal_mode = MEMORY
PRAGMA synchronous = FULL
PRAGMA temp_store = 2
PRAGMA cache_size = 10000LL
When I have 10000 records in the table, it takes 20 ms to fetch 10 items from
offset 0, and it increases to 220 ms to fetch 10 items from offset 9900.
While I have 20000 records in the table, it takes 20 ms to fetch 10 items from
offset 0, 440 ms for 10 items from offset 9600 and 720 ms for 10 items from
offset 19950.
My understanding is, since index table is created in the sorted order, time to
fetch from any offset should be the same. Why is the time to fetch increasing
when fetching from higher offset? Why is fetching time increasing for the same
offset when more records in the table? Is this expected behavior from SQLite or
is there something wrong with schema/index/query?
Best regards,
Sabeel
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
