Sqlite and performances in LiveCode

[Jérôme Rosat]

Your "acid test" is very interesting. Using sqlite3 directly is one solution to increase performance of LiveCode. 

And you're right, a response time of less than a second is acceptable from a user's perspective and and limit the number of registration is a way to reach this "performance".

Le 5 mars 2012 à 08:45, Pete a écrit :

> I think the acid test here is to use sqlite3 vs Livecode, since sqlite3 is
> the official command line tool for sqlite and likely to be more efficient
> than any other browsing tool.  I tested a select * from a table with about
> 48,000 records in it, using a datagrid versus an LC screen that invoked
> sqlite3, passing it all the necessary parameters, reading the output and
> displayed it in a scrolling field.  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).
> 
> It's pretty clear that using a datagrid to display large datasets is not a
> good idea if you want to maximize performance unless you really need the
> the functionality that a datagrid provides.
> 
> On the other hand, the original post seemed to indicate that, even though
> the elapsed time using a datagrid was several times longer than the SQLabs
> tool, it was still less than a second.  While it's interesting to figure
> out why that should be, I'd guess that a response time of less than a
> second is acceptable from a user's perspective.
> 
> Pete
> 
> On Sun, Mar 4, 2012 at 10:22 PM, Kay C Lan <lan.kc.macmail at gmail.com> wrote:
> 
>> No, it's not the same one.
>> 
>> Looking at the SQLabs site, SQLabs "developed from scratch a brand new
>> ListBox that is able to display an infinite number of rows and columns in
>> just few milliseconds. This is a fundamental base control for the app."
>> They've been optemising their product since 2003, it would be surprising if
>> you can match them on your first try.
>> 
>> I did a few tests with FireFox's SQLite Manager and for 50,000 records
>> (11MB) it took 504ms to display in a field that could display ALL records.
>> 
>> Testing with LC I could extract the same records in 409ms but to display
>> them all in a field took it out to 917ms. By reducing the number of records
>> displayed to the size of the field, it took 410ms. Clearly LC fields are
>> the limiting factor here.
>> 
>> I then noticed that in FireFox, although all the rows could be scrolled
>> down to the bottom, the columns were all truncated. So I then changed my LC
>> field to a 'Basic table object', set the 'Maximum editable columns' to
>> match exactly the number of columns I had, and then set the tab stops so
>> only a small portion of data of each column was displayed - similar to how
>> FireFox truncated it's columns. In this way I was able to display ALL the
>> rows in 491 ms.
>> 
>> This may not be as fast as SQLabs, but I think they maybe the speed kings.
>> Compared to another SQLite tool I was able to get LC to perform just as
>> fast, if  not a little faster.
>> 
>> HTH
>> 
>> 
>> On Sun, Mar 4, 2012 at 7:12 PM, Jérôme Rosat <jrosat at mac.com> wrote:
>> 
>>> I don't know if SQLiteManager for Firefox is the same one I use (from
>>> SQLabs) but it display all the records in one time (I can scroll all the
>>> list).
>>> 
>>> But thank you for the suggestion, If necessary I'm going to display only
>> a
>>> part of the records with a next button, even if I don't like very much
>> this
>>> approach.
>>> 
>>> 
>>> Le 4 mars 2012 à 03:37, Kay C Lan a écrit :
>>> 
>>> 
>>>> If the 'SQLiteManager' you refer to is the same one I use with FireFox,
>>> in
>>>> my case it only displays 100 records at a time, you have to click the
>>> Next
>>>> button to see more - so this is a performance trick you can do with LC.
>>>> Instead of extracting 150000 records in one go, and displaying it in a
>>>> field in one go( which will take a long time) only extract 100 records
>>>> (which will take significantly less time) and have a Next button, just
>>> like
>>>> SQLIte Manager, to retrieve the next 100 records.
>>>> 
>>>> If I'm right, the the fact that LC only took 4.4 times longer to
>> display
>>>> 1500 times more records would suggest that you'll be able to do a lot
>>>> better with LC
>>>> 
>>>> HTH
>>>> _______________________________________________
>>>> 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
>>> 
>>> 
>>> _______________________________________________
>>> 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
>>> 
>> _______________________________________________
>> 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
>> 
>> 
> 
> 
> -- 
> Pete
> Molly's Revenge <http://www.mollysrevenge.com>
> _______________________________________________
> 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