Sqlite and performances in LiveCode

[Pete]

Hi Trevor,
I'm pretty sure that LC 5.0 included a more recent version of the sqlite
library so there could be some performance improvements there.  I don't
know how to find out what version they are using but the current sqlite
library version is 3.7.10, released in January this year so it's probably
not in LC yet but maybe SQLite Manager ahs it..

I'm not familiar with the sqlite API either but I doubt there's any way of
retrieving data other than using a SELECT command.

Pete

On Mon, Mar 5, 2012 at 8:22 PM, Trevor DeVore <lists at mangomultimedia.com>wrote:

> 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
> 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. 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
> _______________________________________________
> 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>