On 10 Jun 2013, at 1:56pm, Daniel Hofmann <hofm...@axivion.com> wrote:
> Background is, that I want to implement my paging entirely in sql in order to
> save memory, because the complete result-data consumes a lot of memory.
If you use _prepare(), _step(), _finalize() instead of using _exec() then you
get the behaviour you want. SQLite does not read all rows into memory when you
execute _prepare(). SQLite reads just one row each time you call _step(),
reading the data from your database file.
The only time that _prepare() will use a lot of memory is when it has to
generate a temporary index because you have not created a table index suitable
for the WHERE and ORDER BY clauses.
> Question is: Why isn't there a way like the FOUND_ROWS()-function of MySQL (I
> didn't find any reading the docs and googling), to get the amount of rows a
> query would have returned if it had been executed without specifying a
> limit-clause?
It may be that you are trying to combine the SELECT you are using to get your
data with the SELECT needed to find how many rows will be returned. This isn’t
the most efficient way to do things in SQLite. The fastest way to learn how
many rows a SELECT would return is to do
BEGIN
SELECT count(*) FROM <table> WHERE <your clause here>
... any number of SELECT commands you need to retrieve your data ...
END
The SELECT instruction I gave does the fastest minimum database-retrieval
necessary to return your row count. This tells you your row count first and
you can use this number to decide how your other SELECT commands should work.
The count is valid until you execute the END, at which point other processes or
users can again change the data in the file which might, of course, change the
number of rows you’d find with your SELECT.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
