PY uttered:
Hi All,
I have a problem about LIMIT & OFFSET profermance.
Due to the limitation of memory, I could not get all of the query result at
a time.
In our soluction, we use the LIMIT and OFFSET to avoid the problem of memory
issue.
we observed the performance of LIMIT & OFFSET, it looks like a liner grow of
the response time. In our table, it only has 300~500 records.
Here is the dummy script
Create Table foo(id INTEGER PRIMARY KEY AUTOINCREMENT, x TEXT);
create index idx_foo on foo(x);
insert into foo(x) values('text001');
:
:
insert into foo(x) values('text300');
Select distinct x from foo LIMIT 20 OFFSET 0; Select distinct x from foo
LIMIT 20 OFFSET 20; Select distinct x from foo LIMIT 20 OFFSET 40; Select
distinct x from foo LIMIT 20 OFFSET 60;
:
:
Select distinct x from foo LIMIT 20 OFFSET 280;
Would you please help to tell me how to improve that?
I could only observe the issue on the slowest machine I have (SUN
SPARCclassic). I guess this is an embedded device, based on the fact
you're also limited in memory.
Using your query, the index for x is ignored. To do the "DISTINCT"
processing, SQLite creates a temporary table which is populated with
distinct rows from the base table. This is done using a full table scan,
so the index wouldn't help anyway.
Your best bet, if you always want distinct x, is to add a UNIQUE
constraint to x:
CREATE TABLE foo (id INTEGER PRIMARY KEY, x TEXT UNIQUE);
Now your selects, minus the distinct clause, will run in constant time:
SELECT x FROM foo ORDER BY x LIMIT 20 OFFSET 60;
Note, using "ORDER BY" will make SQLite use the implicit index on x, and
also ensure the ordering of the result set, which is not defined
otherwise.
Thanks for your great help.
Thanks,
VK
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
