Sqlite and performances in LiveCode

[Pete]

Scott:

I agree with you - datagrids are powerful, complex structures and come with
a cost.  They're a great tool for sure and I use them a lot when I need
their functionality, especially if I need to include option menus,
checkboxes, or any other column customizations.  But I'm increasingly
starting to use scrolling lists and table fields when all I need is to
present a simple list of data for the user to select from and display more
information or carry out some action on the selected line.

It feels like there's room for another type type of control that goes a bit
further than basic tables but not as complex as datagrids, kind of a
datagrid-lite.

Guglielmo:

Actually, the datagrid only fills the visible rows at any given time
whether you are using GetDataForLine or not.

You can verify that by setting the selectGroupedControls of a datagird to
true, then looking at its structure in the Application Browser (you'll
probably need to to refresh the right pane of the app browser to see it.)
 You will see that it includes only enough controls for however many rows
are visible in the datagrid.  So if you've loaded 100k rows of data into
your datagrid but there are only 20 visible lines, you will see that the
datagrid contains only 20 sets of controls, not 100k sets.

Remember to set the selectGroupedControls of the datagrid back to false
again or your datagrid will stop working.

I think the difference when using GetDataForLine is that the datagrid does
not keep internal copies of the data you load into it and makes a call to
GetDataForLine whenever it needs more data.  It's up to you to keep your
own copies of the data, format it for display, and take care of any of the
other functions normally handled by the datagrid.

That means that you are in complete control of how and when to retrieve the
data from your datasource.  Instead of reading 100k rows from your
database, you can read just enough to fill the datagrid and read more when
needed.  Or you can read all 100k rows and keep them in your own data
storage, supplying them to the datagrid only when GetDataForLine is called.
It's definitely more complex doing it that way but it can improve
performance a lot.

Pete

On Mon, Mar 5, 2012 at 9:23 AM, Guglielmo Braguglia
<guglielmo at braguglia.ch>wrote:

> Hi Pete,
>
> what you believe "... /I believe the datagrid loads only enough database
> rows to fill the number of rows that are visible in the datagrid .../",
> unfortunately, is incorrect ...
> ... to have this behavior you have to manually code a procedure, named
> "GetDataForLine", which fill in the data that the data grid needs.
>
> Please see here : http://lessons.runrev.com/s/**lessons/m/datagrid/l/7341-
> **displaying-large-amounts-of-**data<http://lessons.runrev.com/s/lessons/m/datagrid/l/7341-displaying-large-amounts-of-data>
>
> Guglielmo
>
>
> On 05.03.2012 08:45, Pete wrote:
>
>> ... omissis ...  I did not do any coded timings but
>>
>> retrieving and displaying the records in an LC datagrid took around 3-4
>> times longer than in sqlite3.  The datagrid was using the
>> dgNumberOfRecords/**GetDataForLine techniques outlined in the datagrid
>> manual
>> and there was no data formatting involved.
>>
>> The crazy thing about this is that, using those techniques,*I believe the
>>
>> datagrid loads only enough database rows to fill the number of rows that
>> are visible in the datagrid*, adding more rows as the user scrolls.  So
>> the
>>
>> comparison is really between selecting and displaying all 48,000 rows in
>> sqlite3 vs selecting 48,000 rows and displaying only 11 rows (the number
>> of
>> rows in my datagrid).
>> ... omissis ...
>> Pete
>>
>>  ______________________________**_________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/**mailman/listinfo/use-livecode<http://lists.runrev.com/mailman/listinfo/use-livecode>
>
>


-- 
Pete
Molly's Revenge <http://www.mollysrevenge.com>