Advantages of Memory based sqLite databases

[Bob Sneidar]

So I tested this when I got in the shop this morning. The memory database is roughly 450:1 over an sqLite database on the LAN. That is incredibly significant, if the goal is to start with a certain dataset, then query it over and over. 

Bob S


> On Apr 21, 2022, at 19:52 , Bob Sneidar via use-livecode <use-livecode at lists.runrev.com> wrote:
> 
> Hi all. 
> 
> Just an exercise in semi-futility here. I was curious about the actual difference in performance between querying a memory based sqLite database, as opposed to querying a mySQL database over a slow (iPhone hotspot) connection. I expected it to be significant, but HOW significant I had no idea. 
> 
> Querying for one record produced a performance ratio of 2600 to 1. Some will say, "Of course! What did you expect?" That is just the point. Many people assume repeated queries of an SQL database over a network to be tolerable, even if the connection is remote. But what if your application needs to make repeated queries? At some point, the overhead of repeated queries will exceed that of querying for all or a subset of the data once, storing it in a memory database, then performing your repeated queries on the memory database instead. 
> 
> I will test this over a LAN connection when I get into work tomorrow, but I expect it to be, if not AS significant, certainly more that one would expect. 
> 
> Just food for thought. 
> 
> Bob S
> 
> 
> _______________________________________________
> 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