Nuno Lucas wrote:
On 10/25/06, Dennis Cote <[EMAIL PROTECTED]> wrote:
Nuno Lucas wrote:
>
> There is another alternative if you don't mind to have the overhead of
> having an automatic row count (which sqlite avoids by design). It's by
> having a trigger that will update the table row count on each
> insert/delete.
>
Nuno,
This technique only works if you never use conditions on your queries.
If your query returns a subset of the rows in a table this carefully
maintained count of all the rows in the table is useless.
Sure, but I wasn't trying to solve the general issue. The only
solution for that is to run the query to full length, whatever the SQL
engine is (even if they hide it from the user/programmer).
The trigger(s) could be elaborated to specific queries, off course,
and that would solve the GUI issue for fixed queries, but the generic
case will never have a solution other than the full scan.
Anyway, I never used this solution, just trying to show it can be
solved for the specific cases of most GUI's, if the trigger overhead
is not a problem.
In my modest opinion, if one really needs to have an accurate
scrollbar, one should show the rows by pages, with buttons to go to
the next/prev page (and the scrollbar will be correct per page). No
need to know the total rows in the view (unless we know the table
doesn't grow that much that a "select count(*)", or a full select into
memory, doesn't add much to the window rendering, which is most
times).
Regards,
~Nuno Lucas
Dennis Cote.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
Some possible solutions to the problem of defining a result set size
without using much extra memory, disk space or machine overhead.
If you want to know the size of your result set in advance and then
select pages from that set an efficient way you could execute the query
and build some form of index to the returned rows, using the rowid as
the unique ID. Then you can traverse that index at leisure, reading
data columns as required. You could organize your index so that a
pre-order traversal gives you the sequence you want later. That avoids
the need for an ORDER BY.
The effect would be like having a CURSOR. It involves a little
programming, but then nothing is free.
A somewhat heavier duty, but simpler, alternative is just to write the
result set to a temporary table, index it on the access key then use it
for output.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
