On Tuesday, 29 January, 2019 16:28, Wout Mertens wrote: >To: SQLite mailing list >Subject: Re: [sqlite] SQLite slow when lots of tables > > I always have to explain to people that there's no magic sauce that > "real databases" add versus SQLite.
> SQLite uses the same techniques all databases use, and thanks to the > absence of a network later, you avoid a lot of latency, so it can > actually be faster. > (I do believe that SQLite optimizes for smaller disk footprint, so > that may be a tradeoff where other databases might gain speed) Well, there is a bit more to it than that. The SQL interface and the capabilities may be similar but paying $$$$$ for a client/server database does get you something that you do not get with SQLite3: - Someone else wrote the networking layer, so if you want one, you do not have to do it yourself - C/S databases are designed to handle REAMS (ie, thousands) of remote clients doing simultaneous access, so the concurrency is much greater - C/S databases are parallelized and multithreaded, meaning that the single server process can use all the cores on your server simultaneously - C/S databases may be parallelized so that a single query uses all available CPU cores simultaneously - C/S databases may be NUMA and/or SYSPLEX enabled so that a single database can be scattered across multiple disparate machines yet queried as if there was only one database on one machine - C/S databases have more complicated data fetching methods (ie, they can use such things as hash tables, intersection sorts, and all sorts of other methods to fetch your data rather than just the nested loop retrieval supported by SQLite) - Some C/S databases may have extremely complex planners designed to take maximum advantage of all the above things simultaneously (at a cost, of course) Of course, other than the big bucks you will spend on the C/S database, you will also have to spend big bucks to give it a big computer to run on. That means lots of fast I/O and gobs of RAM and CPU. Of course, in the grand scheme of things a 48 or 96 core x64 server with a terrabyte of RAM and a couple hundred (or thousand) terrabytes of SSD is not really that expensive. So really, it depends on your needs and what you are willing to pay, In many cases for a single user or even a small number of concurrent users on a single computer will likely achieve better performance (and cost efficiency) by using SQLite3. On the other hand if the application is an line-of-business database for a Fortune 5 multinational corporation, you will probably choose the C/S database (particularly if you are running some <redacted> software like SAP ...). _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
