Hi,
I had a similar problem at a larger scale. One trick is to create
a temporary table from where you can fetch the rows quickly:
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');
Now create a temp table on your query:
Create Table tmp as select distinct x from foo;
This table has the nice property that the (implicitely
created) OID is in order. OID starts with 1!
But now you can say:
Select x from tmp where OID>0 and OID<=20;
Select x from tmp where OID>20 and OID<=30;
...
Also the calculation of the size of the table is
also very fast:
Select max(OID) from t;
This is *much* faster for huge tables than
Select count(*) from t;
Because max simply jumps to the last record, but
count has to touch every record.
Don't modify the temp table: drop it and create it
new. Else the trick with the order of the OID
won't work.
BTW: how can 500 records be a memory problem?
Michael
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?
Thanks for your great help.
Thanks,
VK
--
http://MichaelScharf.blogspot.com/
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
