Sqlite and performances in LiveCode

[Bernard Devlin]

The difference might be explained due to Livecode setting a smaller
cache size than other applications.

http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html
http://www.sqlite.org/pragma.html#pragma_cache_size

I don't know if the cache size is under one's control with sqlite.  I
know it is with Valentina.  I can't see that the call to
revOpenDatabase allows a cache size to be set for sqlite.  Perhaps
those options can be set in a sqlite configuration file and picked up
when LC makes a sqlite connection?  I guess that the sqlite command
line tool would enable the cache size to be set, so one could test if
cache size is significant.

Selecting largish result sets on Valentina, I haven't noticed any
significant difference by going beyond the default cache sizes (but
then since Valentian is a column-based database that might be
significant).  But then I don't think that 50k or even 150k is such a
large result set either (of course, it would be different on a
database with a huge number of columns or very wide columns).

Bernard

On Tue, Mar 6, 2012 at 4:22 AM, Trevor DeVore <lists at mangomultimedia.com> wrote:
> I did a test with SQLiteManager 2 and when viewing all movies in the manage
> tab the query takes anywhere from 685 milliseconds to over 1 second
> (looking at the Query time at the bottom of the window). It usually stays
> right around 700 milliseconds, however. Results are the same if I use the
> SQL tab and execute the query "SELECT * FROM movies".
>
> In LiveCode 4.6.4 on the same machine the same query averages around 850
> milliseconds.
>
> Next I downloaded the demo version of SQLiteManager 3 and ran the same
> query from the Manage tab. The query took ~240 milliseconds on average. I
> then switched to the SQL tab and typed in "SELECT * FROM movies" (the query
> from your stack) and the query took ~450 milliseconds on average.
>
> This makes me wonder if SQLiteManager 3 is fetching data in the Manage tab
> using an approach other than generating a query string and passing that off
> to SQLite. I haven't studied the SQLite API in depth so I don't know what
> sorts of options are available in the C API. The equivalent of what RevDB
> is doing is the SQL tab in SQLite Manager.