[Qt-interest] Huge amount of data in tree or whatever(view)

[cb]

Hi, Marek:

There are all kind of approaches to do this. Here are a few suggestions 
(some of them might have been mentioned by the other posters):

- load a limited number of records and add a toolbar with buttons for paging 
through the records - this technique would be similar with the way data is 
displayed on web pages. The grid would contain a resonable number of records 
(perhaps 1000?) then the user can navigate to the next 1000 records or to 
the last page and so on. - This would be my prefered way. You could also 
provide an export function to export all the data to a text or csv file.
- cache all the data locally in a file with a fixed record size. You can get 
the data using a separate thread to keep the application responsive. This is 
a bit more complicated because you have to define a file structure that 
caters for all the database field types. It is as if you get the data and 
put it in a foxpro dbf file. Then you can develop your model to get the data 
from the local file and it will be fast because you can navigate directly to 
a record due to the fact that the records have a fixed size. I've tried this 
in the past with my own developed grid (similar with the qt one) and with a 
file that had 1,000,000 rows the performance was pretty good. Just a side 
note, some odbc drivers do this already, that is they download the data to a 
local file, especially when you use bidirectional cursors (that is cursors 
that allow you to navigate to any record in the recordset).
- load the records from the database as needed. You run one query that 
returns the number of records and then you get the data as the user 
navigates through the records. Of course you can use a certain level of 
caching, you can cache records and get them from the cache instead of 
running a query if they already have been displayed.
- another variation of the previous approach would be to retrieve the 
primary keys of your data - I assume that your recordset has a primary key - 
and store those in the memory, then you get the records by using the primary 
key values as needed by building queries dynamically.

There are also other questions that one can ask but you didn't mention,. 
such as: do you want to support some kind of filtering and sorting? Then 
things can get more complicated.

It really depends on your requirements.

Just one more thing, 500,000 records are a lot of records and unless you are 
Kim Peek all you can do is skim through a few of them hence filtering and 
sorting functionality would be highly desired.

Costa Basil

"Marek Bronowicki" <marek at cyberdeus.pl> wrote in message 
news:4B3CF7F7.4040007 at cyberdeus.pl...
> Hi
>
> Perhaps this topic was "blended" and "sqashed" milions of times, but I 
> still cannot find a solution.
>
> This is what I need to do:
> I have one and a half milion of records in SQL database and I need to show 
> them all in some view.
>
> What I know already that is that about 40000 records in treeview is a 
> total mass of information for my Athlon X2 2,0 to show and pressing the 
> end key hangs the soft for about 10 seconds to get on the end of the list.
> My friend told me that this has to be model view, but after inserting the 
> same amount of data to model based view the result is the same.
>
> I'm thinking about simple solution that can retrievie only shown rows from 
> the database with scrollbar as huge as the whole table. In example:
> My treeview or tableview or whateverview has height for 50 rows (let's say 
> 10 px for row so 500px without header bar), so:
> my select query: "SELECT * FROM table LIMIT 0,50;" and this is shown live 
> on the treeview
>
> but vertical scrollbar it's as big as "select * from table"
>
> If i will press page down, the select query will change to SELECT * FROM 
> table LIMIT 50,50;
> the same with down arrow SELECT * FROM table LIMIT 1,50;
> etc.
>
> I don't know if i'm describing it right, or as usual only I'm 
> understanding what I'm writing:-)
>
> Best Regards,
> Marek Bronowicki