Sqlite and performances in LiveCode
pete at mollysrevenge.com
Wed Mar 7 10:37:27 CST 2012
I emailed sqlitemanager support re the manage/sql tab performance
difference. Mystery solved by the reply:
"In the Manage panel when you see SELECT * FROM table the real query
executed is SELECT rowid FROM table and then a SELECT * FROM table WHERE
rowid=N is execute for each visible row of the listbox. In the SQL panel
query executed is just what you type"
On Mar 6, 2012 5:47 PM, "Pete" <pete at mollysrevenge.com> wrote:
> I just noticed that the same vendor that makes SQLiteManager also makes an
> SQLite server package (cubeSQL) that does implement MVCC. But you're
> right, it's definitely not there in standard SQLite. An SQLite server
> sounds interesting though.
> On Tue, Mar 6, 2012 at 11:35 AM, Bernard Devlin <bdrunrev at gmail.com>wrote:
>> I notice from things discussed on the Valentina list that they
>> recommend using API methods rather than SQL calls, because the former
>> will be faster due to less overhead. So these different ways of
>> interacting with a sqlite database might well represent an application
>> using the sqlite API.
>> Although when I use the Valentina VStudio SQL interface I will still
>> see results being returned in 25 ms, compared to 500 ms going through
>> sql yoga > revdb > v4rev sql > valentina. This is doing a "select *"
>> from a table with 22k rows (10mb in size). That's not to say that I'm
>> outraged at 500ms. There are other steps one can take to make an
>> interface responsive at these timescales.
>> The Valentina guys are pretty used to comparing Livecode with the
>> other platforms for which they provide Valentina. On 19/12/2010
>> Ruslan had this to say about Livecode:
>> "REALbasic - as I remember -- was very fast, near to C code...so it
>> gives e.g. 5% overhead to pure C engine... Revolution and Director
>> give me x4-x10 overhead I think....Revolution because all params are
>> packed to strings ... Director because quite complex SDK similar to MS
>> COM ..." This conclusion by Ruslan appears to be only in the
>> discussion of calling the Valentina API, therefore one would expect
>> that adding other layers (sql, revdb, etc.) one would see other layers
>> adding their own torpor to the action.
>> Does using explict transation control make any difference with sqlite?
>> It shouldn't matter in a case where the data is only being read. I
>> know on MVCC databases this is still a factor if one has a cursor
>> containing the read data, as such data might still be altered from the
>> cursor and the currency control of the MVCC requires a handle on such
>> data to be able to maintain consistency. I can't see that it would
>> apply to sqlite, and this site confirms that sqlite does not implement
>> MVCC: http://www.sqliteconcepts.org/SI_index.html
>> On Tue, Mar 6, 2012 at 5:15 PM, Pete <pete at mollysrevenge.com> wrote:
>> > I got the same results on the movies database and even more pronounced
>> > differences on a table in one of my own databases with ~48k records in
>> > SQL tab 0.519secs, Manage tab 0.045 secs, more than a 10-fold
>> > Just a straight SELECT * in each case.
>> > Pete
>> use-livecode mailing list
>> use-livecode at lists.runrev.com
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
> Molly's Revenge <http://www.mollysrevenge.com>
More information about the use-livecode