On 2017/05/24 3:21 PM, Thomas Flemming wrote:
Hi Ron,
> there is no system in existence that will do
I was working a lot with Valentina-DB and they have a cursor class:
var cursor=database.SqlSelect( "SELECT... WHERE... ORDER...");
then you can just get any the ListView wants, forward and backwards,
very fast:
cursor.Position = rownumber;
I'm quiet new to SQLite and was surprised, that its so difficult to
write this kind of cursor.
SQLite is the database engine, not a data class. The thing you talk
about, Valentina DB and that cursor statement actually invokes a data
class that does a LOT of processing and background information handling
for you - much like the things we've been suggesting in point of fact.
It's a bit like asking an engine manufacturer why the engine you've
purchased doesn't come with a rear-passenger-seat, because the previous
car you drove had a great rear-passenger-seat. SQLite is only the
engine, lots of systems however have wrapping data classes that will do
a lot of processing for you, much like what you are used to.
system.data.sqlite if you are .net MSVS fan or such, DBX for any of the
Delphi, C++, Visual C, C# stuff, JAVA has many wrappers, so do Python,
PHP etc. etc.
Perhaps you are looking for one of these, if you specify your
development environment, lots of people here will be able to suggest the
best database wrappers and data handlers for you.
> Another way is to Query to a temporary table with an automatic
incremented
> This is extremely fast, only the initial query will take some time.
yeah, this might work, but imagine how much time and memory this would
cost for 10mio records...
I have no need to imagine, I use this often, it is very much lighter
than you think - even for hundreds of millions of rows. If you store to
a temporary table, sqlite will manage by itself how much of stuff stay
in memory and how much spills to disk etc. Either way, you querying that
temp table will be extremely fast and your users will have a very speedy
experience. In the words of my favourite dentist: "You won't a feel a
thing..."
Tom
Am 24.05.2017 um 13:20 schrieb R Smith:
You are asking the DB to give you all the 8000...+ results, sort them
and then you opt to only look at some of them, there is no way this
can ever be fast in any system, you need to rethink how you ask for
information.
First things first, you should never be using the sqlite (or any
other database's) STEP to support user scrolling, you should be using
it to load the results you want to see, and then in a different
method show those results to the user. What if the user wants to move
up by one line? You can't un-step in a database.
There are many ways this can be overcome, first with dynamic listviews:
The way to set up a dynamic listview is to get a query of the ID's of
the entire list of possible values, sorted and so on, that you might
want to display into your own list object or array. Then populate the
listview with the ID's only and determine which are visible, for the
visible ones, load the data from a query using only those ID's,
perhaps something like:
SELECT v1, v2.... FROM MyMainTable WHERE ID IN (7001, 7002,
7003...for all IDs visible...);
and set them tot he screen. If the user scrolls loads, you update
only when needed, perhaps using a time difference function or such,
and when the view "settles" load those results that are visible.
Almost all programming systems with visual components like "Listview"
has a function or callback that can tell you the current visible
items AND whether the visible index/count changed or not. It is often
enough to catch this and simply update the visible items when such a
change happens.
Another way is to Query to a temporary table with an automatic
incremented primary key, and simply read from that table the
paginated values, i.e. if your listview scrolls to line 500000013 you
can query the temp table like this:
SELECT * FROM TempResults WHERE ID BETWEEN ?1 AND ?2;
where ?1 = current_idx (such as 500000013) and ?2 = current_idx +
page_items_count as defined in your software;
This is extremely fast, only the initial query will take some time.
What you can't do is query an insane amount of rows EVERY time the
user moves the cursor or scrolls the page, there is no system in
existence that will do that quick, ever.
Good luck!
Ryan
On 2017/05/24 11:53 AM, Thomas Flemming wrote:
Yes, but this would still be slow, because lastValue is lets say
page 50 in the telephone directory, but I need to go to page 800.
So this query would still return all pages from 50 to 800, which I
dont need.
Am 24.05.2017 um 10:45 schrieb Andy Ling:
Then when you detect a jump you'll need to use a new search to
"jump" to the page you want. Something like
SELECT .... WHERE sortedColumn > lastValue ORDER BY sortedColumn
And make sure you have indexes on all the columns that you can sort
by.
Andy Ling
-----Original Message-----
From: sqlite-users
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of
Thomas Flemming
Sent: Wed 24 May 2017 10:37
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] SQLite3.Step fast forward / skipping rows
Almost, but when you compare to a telephone directory, then the use
case of
fast scrolling down in a listbox would be going directly to page
800 and not
going to "Smithson".
And yes, there is a unique key, but this doesn't help, because the
list can
also be sorted to various columns.
Am 24.05.2017 um 10:27 schrieb Keith Medcalf:
You need to write your application like a telephone directory. To
get to
the page with the "Smithson" entry on it, you do not read all the
entries
starting from the begining until you get there -- you turn
directly to the
page you want by doing a search.
Surely you have a unique key for the list?
-- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
-----Original Message----- From: sqlite-users
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf
Of Thomas
Flemming Sent: Wednesday, 24 May, 2017 02:09
To:sqlite-users@mailinglists.sqlite.org Subject: [sqlite]
SQLite3.Step
fast forward / skipping rows
Hi SQLite Users,
I have a SELECT query, which returns some 100000 records and is
displayed in a scrollable ListView.
When the user scrolls down the list, each new row is loaded with
SQLite3.Step().
The problem is, when the user scrolls fast with the
scroll-slider, lots
of rows are skipped, but SQLite still needs to load them all with
SQLite3.Step until it reaches the row which is actually needed.
This is
very slow.
Is there a way to skip all these unnecessary rows? For example going
directly from row 1000 to row 100000 ? I tried SELECT ... OFFSET
100000
but this is also very slow the more down we go.
Thanks Tom
_______________________________________________ sqlite-users
mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sql
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
