PY wrote:
In fact, I use sqlite in a embedded device. So the memroy problem is really
critical to us.
For profermance improving, all of the database running in a
in-memory database.
Hmm, I am not 100% sure, but I think queries like
select distinct x from foo
or
select distinct x from foo order by x
have to create internally a temp table with
the results anyway to find duplicates or to
sort the table.
Furthermore, I need to sort the result, which following sql command has
better performance?
- Create Table tmp as select distinct x from foo;
Select x from tmp where OID>0 and OID<=20 order by x;
This one will only sort the columns 1 to 20, but not the
entire table, so it does not solve your problem...
- Create Table tmp as select distinct x from foo order by x; Select x
from
tmp where OID>0 and OID<=20;
This will put the table sorted into the temp table. That's
actually how I use it. In fact, this does not work with
arbitrary SQL databases. But it works well with SQLite, because
SQLite optimizer does not remove the ORDER BY clause (it could,
because there is no guarantee in SQL in which order tables are
stored) and it inserts into the tmp table in order.
However, I don't know what the memory overhead is for this
kind of query. Your original approach, with the LIMIT has
create an internal result table anyway, but putting it into
a temp table might double the amount of memory needed.
Well, the best way to find out is to try it ;-)
Michael
--
http://MichaelScharf.blogspot.com/
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
