Sqlite and performances in LiveCode

Trevor DeVore lists at mangomultimedia.com
Mon Mar 5 23:22:46 EST 2012

On Mon, Mar 5, 2012 at 6:09 PM, Jérôme Rosat <jrosat at mac.com> wrote:

> I use the stack provided here:
> http://www.bluemangolearning.com/download/revolution/tools/datagrid_databases.zip
> I don't know if I understand your question, but the code is:
>    put the milliseconds into theStart
>    put revQueryDatabase(sConnID,"SELECT * FROM movies") into sCursorID
>    put the milliseconds - theStart into theDuration
>    Log "Opening movies cursor took" && theDuration && "milliseconds"
> There is only one call "revQueryDatabase".

Yes, that is what I was interested in knowing. I just wanted to get a
handle on where the bottleneck is exactly.

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

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. SQLiteManager 3 is roughly twice
as fast at running this query as RevDB. I wonder if there are updated API
calls that SQLiteManager 3 is using that RevDB could take advantage of. It
might be worth pinging RunRev, sharing your test with them and seeing if
revQueryDatabase can't be optimized a bit.

Your data grid has been set up correctly. I get rendering times of 120
milliseconds on average so it isn't the bottle neck you need to be
concerned with at the moment. Looking at your original post it doesn't look
like you were trying to troubleshoot the data grid, though.

Trevor DeVore
Blue Mango Learning Systems
www.clarify-it.com    -    www.screensteps.com

More information about the Use-livecode mailing list