On 2015-09-17 02:22 PM, Richard Hipp wrote: > On 9/17/15, John McKown <john.archie.mckown at gmail.com> wrote: >> Well, this may be a heretical answer. > Yes, it is. > > There are a number of reasons to still use SQLite even if you are only > doing key/value lookups: > > (1) SQLite is the most widely deployed database engine in the world, > so it is more likely to already be on the system you are using than > any other database. > > (2) Those PRIMARY KEY lookups that the OP is doing are surprisingly > competitive in performance. They may even be faster than other > hash-based systems. Have you measured? Do you *know* that a hashing > system is faster, or are you just assuming? > > (3) Does that alternative key/value database engine offer > transactions? What happens if you lose power in the middle of a > write? > > (4) The next poor soul who has to maintain the program will be better > able to understand the the code if she can see the CREATE TABLE > schema, containing the obvious hints to the meanings of the various > columns inherent in their names. Data lives longer than code. It is > good to keep it in an easy-to-reuse and portable format. > > (5) If you already have a relational database at hand, the application > will be much easier to enhance when you move to version 2. > > (6) Is that hash-based key/value database you want to use > cross-platform? Can you move the database files to a big-endian > system and have it still work? You could if it were SQLite. > > I now open the floor for additional suggestions from readers for why > it is still better to use SQLite even if you currently only think you > need a key/value store....
(7) Let's not forget the most important bit: SQL, so today you only need a key-lookup.. but tomorrow your system gets adopted everywhere, and users start asking for more functions... maybe you need to find the 10 closest points? or you want to add a counter for how many times a point is looked up to provide stats or plots.... SQL will just do that with simple queries or schema additions at lightning speed. Or perhaps you just want to check how many / if any duplicate points are in the system. 1 small SQL statement for man, one giant leap for don't-have-to-program-that-stuff-ever-again. (8) 3rd Party DB admin tools - need to make changes to your DB only once? No need to add programmy bits to do it, just use a DB tool, of which there are a myriad out there, especially in SQLite's case. Perhaps grow and go really big later and upgrade to full client-server system? The same 3rd party tools can make transfer very simple from an existing DB. (9) Get the automatic advantage of future improvements in efficiency and upgrades without any need to redo any code. (10) Later perhaps port your system code to another PHP, Java, whatever... much easier to port 1 SQL instruction to the new system's DB interface than to rewrite all the lines of code that maintains your proprietary hash-value-store. (11) This forum. Need help or suggestions with how to optimize your internal value-store? Not so easy. Need help optimizing an SQLite DB or query? We've all seen the very clever people here always eager to assist. It's an amazing and very valuable resource. (This one is also true for other community-supported DB systems in general) Cheers, Ryan
