January 1, 2016 at 5:37 pm
Comments posted to this topic are about the item The SQLite alternative to NoSQL
Best wishes,
Phil Factor
January 2, 2016 at 3:30 am
Phil, good article but a bit unbalanced in comparing NoSQL to SQLite. And what do you mean by "eek, JSON"? Sounds like what I say if I see a mouse in the server room. Are you scared of JSON?
Our company is developing a replacement for our 10+ year old remote sales order entry app that ran on Windows Mobile handhelds. This new version uses HTML5/CSS/JS/Cordova to run on Android and Apple smartphones and tablets. We did extensive research into our choice for replacing the single-user SQL DB that we used in the old app. If we had stayed within the world of SQL DBs, then SQLite would have been our choice. But instead, we chose the LokiJS, which is an "In- memory Javascript Datastore with Persistence", to store the customers, products and other data that we download from the back office server. Our app is server- agnostic, so the server DB may be SQL Server, MySQL, Oracle DBMS, Progress DBMS, Access, or some other DB product. We access it in php via ODBC.
The biggest advantage we found in using NoSQL is that it decouples the server from the client in regard to data storage. By eliminating the concept of a "schema", NoSQL removes an entire layer of program code on both the server and the client device. Previously, we had a lot of program code dedicated to maintaining the correct table definitions in the client DB, so that data downloads would work correctly. This also was a big source of support issues.
Now, that all goes away. Our web service downloads customers and products data in JSON format, which can be easily stored and accessed by the client app as a JavaScript object in the LokiJS data store. JSON format is much easier to use and less verbose than XML, plus it works better with Javascript. If a new data field is added to the download object, it doesn't matter if the client is ready to use it or not, it can still be sent and saved without first having to update a table definition on the client. When the client app is updated to start using the new field, it just references it within the stored object and there it is. Simple as you can get.
The LokiJS product handles large data stores, uses indexes, and is fast and reliable. Since our app is running on smartphones and tablets, it is by definition single-user, so file locking is not an issue.
So in conclusion, I urge readers to examine the pros and cons of both sides very carefully before making such a decision. Certainly there are cases where a SQL DB is important, but not nearly as often as you imply, even for serious business apps with large data stores.
In theory, theory and practice are the same. In practice, they are not.
January 2, 2016 at 5:34 am
First of all, best wishes for 2016 to everyone and to your families.
My situation: for several years, I developed a software to track works in the area of agriculture, and to do the invoices then the accounting. This software was developed with Visual Studio 2008 (with CSLA Rockford Lotka) and uses SQL Server 2008 Express with stored procedures.
Now I want to use in addition a mobile app for sending and receiving data to / from workers. I am developing an application using HTML5 / CSS / JS / Cordova to run on Android smartphones using Visual Studio 2015. This application must be in online and offline mode. So I need a small database on the smartphone. I thought about SQLite. I did not know LokiJS that Tom talked.
So my questions:
- Phil, do you have any examples or links to move tables, and their data easily between SQL Server and SQLite, using either ODBC, a library or file NET beautiful, simple command line?
- Tom, is it possible to use LokiJS with Visual Studio 2015? Do you have any examples or links?
Thanks in advance
Best regards from France
January 2, 2016 at 6:17 am
Just a few small corrections: The article states that everything is stored as string. This is not correct, SQLite supports blobs / integers / floats natively. Replication can be done by the application via a backup API, but there's no automatic replication (always on, ...).
SQLite is not exactly a competitor to SQL Server. But it's a very useful database for ad-hoc data analysis, for all kinds of small isolated data storage (revision control: fossil), as a studying tool (no installation, xcopy deployment), or as an intermediate data store when importing / cleaning / restructuring data.
January 2, 2016 at 8:22 am
tom-864693 (1/2/2016)
Phil, good article but a bit unbalanced in comparing NoSQL to SQLite. And what do you mean by "eek, JSON"? Sounds like what I say if I see a mouse in the server room. Are you scared of JSON?
I doubt Phil is scared on JSON. Quite some time ago, he authored a function that would rip JSON strings apart into a table structure. It was an MTVF, long and contained loops...but it worked. Considering what JSON stored, I thought it was pretty impressive that it worked.
tom-864693 (1/2/2016)
JSON format is much easier to use and less verbose than XML, plus it works better with Javascript. If a new data field is added to the download object, it doesn't matter if the client is ready to use it or not, it can still be sent and saved without first having to update a table definition on the client. When the client app is updated to start using the new field, it just references it within the stored object and there it is. Simple as you can get.
Tell me, do you store XML and JSON as well? If so, I'm curious to know how it performs, say, over 1M rows. I understand that these single-user databases aren't competitors to SQL Server, but unless you're storing just a couple of MB of data, performance is going to come into play eventually. In SQL Server, it's been my experience that XML dogs tremendously when compared with a normalized structure. I'm curious to know your experience with the LokiJS, SQLLite and NoSQL.
The reason I'm asking about performance is, quite simply, because I don't know and am curious.
January 2, 2016 at 11:02 am
@Ed
Yes, I've done plenty of work with both XML and JSON, but I'm no expert in XML. I find that using SQLite is generally much faster for the jobs I use it for but an XML expert might, for all I know, be able to squeeze performance of XML, so I'm shy of making general statements about comparative performance.
You can't give much information in an editorial but I use SQLite for automation jobs such as storing and loading static data and configuration information when doing CI, where you might otherwise be tempted to use XML. I've never yet done comparative benchmarks in a scientific way. I like to use the command-line tool which does a lot of the work for you. I've been wondering whether to write up the way I use SQLite. I'd certainly do do so if there is enough interest.
I guess that I prefer SQLite for storing data, logs, histories, performance info and config parameters for doing automation jobs because SQL RDBMS are such familiar ground for me, and I like to keep things neatly in one file.
Best wishes,
Phil Factor
January 2, 2016 at 11:15 am
Phil Factor (1/2/2016)
@EdYes, I've done plenty of work with both XML and JSON, but I'm no expert in XML. I find that using SQLite is generally much faster for the jobs I use it for but an XML expert might, for all I know, be able to squeeze performance of XML, so I'm shy of making general statements about comparative performance.
You can't give much information in an editorial but I use SQLite for automation jobs such as storing and loading static data and configuration information when doing CI, where you might otherwise be tempted to use XML. I've never yet done comparative benchmarks in a scientific way. I like to use the command-line tool which does a lot of the work for you. I've been wondering whether to write up the way I use SQLite. I'd certainly do do so if there is enough interest.
I guess that I prefer SQLite for storing data, logs, histories, performance info and config parameters for doing automation jobs because SQL RDBMS are such familiar ground for me, and I like to keep things neatly in one file.
Thanks, Phil. It's good to know something about the performance of the single-user databases, as I really don't know anything. I've done some work with both XML and JSON myself, but not much. I think they're fine mechanisms for exchanging data, but I don't store them for two reasons: 1) They're bloated. 2) I've not yet been able to get them to even rival a normalized structure. I don't consider myself an expert at working with XML by any stretch, but with what I've seen and been able to produce, I'll opt for the performance of a normalized structure instead. They definitely rock for flexibility, but not for performance.
Most of the work I do (and have done for decades) is within the context of an RDBMS. The desktop applications of the past have certainly had INI files (intentionally not used the registry - just my preference), but the majority of the data-centric applications worked with the bulk of the data in the database. I can certainly see how a local data store that's accessible by using the SQL language could be beneficial for local data, but I just don't do much in that arena any more.
Thanks very much for the info.
January 2, 2016 at 11:33 pm
@domino78: Sorry, I do not know if LokiJS can be used with Visual Studio 2015. Although we used VS to develop our old app (in C++ with an embedded SQL DB), we are using the Intel XDK development platform for the new app, mostly because it provides built-in emulation for dozens of smart phones and tablets, along with a good debugger, which tremendously simplifies the task of testing on multiple device platforms. Otherwise, we wouldn't use the XDK, because it is sluggish and a little buggy, and is far inferior to VS in most other regards. But it is good for developing, testing and building apps for multiple platforms.
@Ed Wagner: LokiJS is designed to work with Javascript objects, so saving the JSON response to a web service call (e.g. get all products) into Loki is just a matter of using the "JSON.parse()" method and assigning the result to a new Loki collection. It just takes a few lines of code, very simple. We don't use XML at all, no reason to, JSON is ideal for our needs. In our php web service, we can convert an associative array to a JSON response with one php command, and parse that JSON response on the client device to a Javascript object with one Javascript command, and save it in Loki with a second command. Compared to our old app, this eliminates an entire layer of complex software and a large number of support headaches (when table definitions don't match between client and server).
As far as performance, I probably would not use it with 1M rows of data, but luckily most smartphone apps like ours don't need that amount of data. We are stress testing our app with 5,000 customers and 7,500 products, which is within the top 10% of data volumes for our historic client base (small and mid-sized distributors in the food, C-store, dollar store, and jewelry industries). It requires about 10MB for customer data and 15MB for product data. Performance is quite acceptable on smart phones built within the last several years. Note, the LokiJS web site claims that it can achieve performance up to 1.1M ops/s when using indexed fields. They don't say on what platform though. (See lokijs.org for details).
Please note that I am not any kind of NoSQL zealot or evangelist, just an oldtimer who believes in the KISS principle and in using the right tool for the right job. So far this looks like the right choice for us, given our project goals.
In theory, theory and practice are the same. In practice, they are not.
January 3, 2016 at 2:55 am
Performance of SQLite is definitely OK for simple queries. It does take database statistics into account, but AFAIK it doesn't do parameter sniffing. Personally I wouldn't use it for a 1 TB database. But it is more than good enough for most ad hoc work.
A while ago, I did some quick tests while preparing some data conversions. The query below took 711 ms in SQLite 3.7.13. In MySQL 5.5.28: 830 ms. In Microsoft Access: > 3 seconds. Databasize size: 5000 rows in NetsComponents, 150 rows in Components. Of course that's not a scientific analysis, but it gives you an idea. In this case I had to do a lot of iterations, each time with different data, stored in different files. For this type of analysis, a single file database (MS Access, SQLite) is a far better choice than a client/server database.
SELECT DISTINCT net, nc1.refdes, pin, cinfo.in3V3, cinfo.description
FROM NetsComponents AS nc1 LEFT OUTER JOIN components AS cinfo ON nc1.refdes = cinfo.refdes
WHERE EXISTS (
SELECT 1
FROM NetsComponents AS nc2 LEFT OUTER JOIN Components AS c ON nc2.refdes = c.refdes
WHERE nc1.net = nc2.net
AND COALESCE(c.in3V3, 0) <> 1
) AND EXISTS (
SELECT 1
FROM netscomponents AS nc3 LEFT OUTER JOIN components AS c ON nc3.refdes = c.refdes
WHERE nc1.net = nc3.net
AND nc3.refdes IN ('U244', 'U245')
) AND net not in ('DGD', '.NC.')
ORDER BY 1,2,3;
January 3, 2016 at 2:35 pm
Phil,
+1 in interest on a write up of SQLLite.
Would you have a 1 to 3 website recommendation for resources?
412-977-3526 call/text
January 3, 2016 at 3:47 pm
I don't think there is a website that does justice to the topic of using SQLite in Windows, though SQLite's own website is excellent for general information on SQLite. I found the book Using SQLite by Jay A. Kreibich (ISBN: 9780596521189) to be very helpful, but there are several ways of accessing SQLite in.net all with their own advantages and disadvantages. I'd start by using the command line utility because that is the easiest way of experimenting
Best wishes,
Phil Factor
January 4, 2016 at 7:30 am
SQLite is a good fit (perhaps the defacto choice) for mobile and shareware apps due to it's small footprint, portability, and widespread adoption. However, it could be reccomended with more confidence if it had native support for encryption. Also, it's handicapped by not supporting a Date datatype, which seems like a glaring ommision considering it's established history and how mature the database is in other ways.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 4, 2016 at 9:02 am
Phil-eek, I'd be interested in reading more about SQLLite if you get around to writing it. I think the key here is the zero footprint - why don't we have a better story for that in the SQL Server space?
January 4, 2016 at 2:06 pm
Good answer Phil, don't feed the trolls! 😀
January 4, 2016 at 2:50 pm
Andy Warren (1/4/2016)
Phil-eek, I'd be interested in reading more about SQLLite if you get around to writing it. I think the key here is the zero footprint - why don't we have a better story for that in the SQL Server space?
SQL Server Compact Edition would be Microsoft's alternative to SQLite. I use SSCE on my work PC to contain my table of server logins, simply because it supports encryption natively. However, outside of Windows, there is no API support for it. Actually it seems that Microsoft is deprecating SSCE. You actually must resort to earlier versions of SSCE and client tools to work with it in some cases.
For example, to use the SSCE connector in SSIS, you have to run the package in 32 bit mode.
SSMS only supports SSCE 3.5 connections, not the latest version 4.0.
SSMS support for SSCE connections was dropped entirely starting with v2012.
LINQ supports SSCE 3.5 but not 4.0.
SSCE 4.0 is not supported in Windows mobile (CE/Mobile/Phone) environment.
https://technet.microsoft.com/en-us/library/gg592946(v=sql.110).aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply