[sqlite] Performance comparison between SQLite and SQL Server?
The quality and helpfulness of this mailing list makes me wish for a SQHeavy...
[sqlite] Performance comparison between SQLite and SQL Server?
IME when SQLite is applicable (mostly the one writer limit), it usually runs circles around to server DBs, provided: - you have comparable CPU - you have enough cache memory, or free RAM for OS disk caching, to be in a comparable memory scenario - you do not have a virtualisation layer, especially compared against a server DB running on bare-metal, as that can make a huge difference in effective IOPS Also the ability of SQLite to smoothly work with temporary tables can make a world of difference when faced with complex queries. That is not something that will show in 1:1 benchmarks, but when a complex query can be broken down into several simpler queries + temporary tables, well, you can save lots on both execution and debugging times. On Tue, Feb 16, 2016 at 3:02 PM, olivier vidal wrote: > > obviously it will never be perfect . We already know the benefits of each > database , including those of SQLITE . This is not because there is a > benchmark that people are not going to look at all the other features. But > in equal hardware, it gives a small indication of current performance, as > sqlite.org had done a long time ago. > > Simon Slavin a ?crit : > >> On what hardware ? Should the SQLite test be on a laptop because SQLite >> will run on a laptop ? Or should you test both on identical hardware even >> though they're unlikely to run on identical hardware ? Should you include >> the time taken to install and configure PostgreSQL in the times quoted >> because it takes no time to install or configure SQLite ? >> > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Performance comparison between SQLite and SQL Server?
obviously it will never be perfect . We already know the benefits of each database , including those of SQLITE . This is not because there is a benchmark that people are not going to look at all the other features. But in equal hardware, it gives a small indication of current performance, as sqlite.org had done a long time ago. Simon Slavin a ?crit : > On what hardware ? Should the SQLite test be on a laptop because SQLite will > run on a laptop ? Or should you test both on identical hardware even though > they're unlikely to run on identical hardware ? Should you include the time > taken to install and configure PostgreSQL in the times quoted because it > takes no time to install or configure SQLite ?
[sqlite] Performance comparison between SQLite and SQL Server?
On 16 Feb 2016, at 6:30am, olivier vidal wrote: > It would be great if someone could redo these tests with the current versions > of the databases. On what hardware ? Should the SQLite test be on a laptop because SQLite will run on a laptop ? Or should you test both on identical hardware even though they're unlikely to run on identical hardware ? Should you include the time taken to install and configure PostgreSQL in the times quoted because it takes no time to install or configure SQLite ? Simon.
[sqlite] Performance comparison between SQLite and SQL Server?
Good thread, which absolutely nails the point 'dev decisions for app cases' make a developers world go round. I personally couldn't think of a greater waste of time than a benchmark comparison between client server rdbms's and sqlite. Do what benefits your case most. The above from Jim pretty much encapsulates my thoughts: "SQLite is not directly comparable to client/server SQL database engines > such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to > solve a different problem. Client/server SQL database engines strive to > implement a shared repository of enterprise data. ...SQLite strives to > provide local data storage for individual applications and devices." > I could bang on about my own preferences and decisions I've made but they'd only be reiterating the points made above. They were based on system requirement specs and where local storage was involved it was a blindingly obvious decision to go with sqlite. Rob above made another excellent point often overlooked (usually an afterthought for many dev's): 4. The support is top notch. I have brought and paid for govt scale > databases for governments and to be honest the support for SQLite is just > as good, and to be honest I would say better than Big Red or Big Blue (and > I used to work for Big Blue). > It is another unique property of a great product. Support is not just sqlite specific either (a cop out on many a tech forum) and particularly on this list the topics can be rather broad. There is plenty of good quality feedback and many a good general SQL solution which just adds to the sqlite package as a whole. On 16 February 2016 at 09:42, Jim Callahan wrote: > SQLite would be most comparable to *SQL Server Express LocalDB* edition > which is introduced in this July 2011 blog post > > https://blogs.msdn.microsoft.com/sqlexpress/2011/07/12/introducing-localdb-an-improved-sql-express/ > > More uptodate information about *SQL Server Express LocalDB* edition > is in this 2016 Microsoft Developer's Network (MSDN) article > https://msdn.microsoft.com/en-us/library/hh510202.aspx > > This page "*Appropriate Uses for SQLite*" (whentouse.html) describes BOTH > "*Situations Where SQLite Works Well*" > > and > > "*Situations Where A Client/Server RDBMS May Work Better*" > http://sqlite.org/whentouse.html > > > Opening lines of whentouse.html: > > "SQLite is not directly comparable to client/server SQL database engines > such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to > solve a different problem. Client/server SQL database engines strive to > implement a shared repository of enterprise data. ...SQLite strives to > provide local data storage for individual applications and devices." > > Even Microsoft has adopted SQLite for some limited tasks (such as storing > state) within every shipping copy of Windows 10. > "SQLite is a unique case: it is an open source, externally developed > software that is used by core system components, and our flagship apps like > Cortana and Skype. ...After shipping SQLite as a system component in July, > we wanted to include it in our SDK for November. With more than 20,000 > Windows Apps and more than half of our top apps using SQLite, it made sense > to just make expose the system SQLite to app developers." > http://engineering.microsoft.com/2015/10/29/sqlite-in-windows-10/ > > > There is a historical and unfair (specially compiled version of SQLite > against default settings of PostgreSQL) benchmark > available on this page, but now that you understand the use cases, this > particular benchmark is not that useful in addition > to being out of date and unfair. > https://www.sqlite.org/speed.html > > Jim Callahan > Data Scientist > https://www.linkedin.com/in/jamesbcallahan > Orlando, FL > > On Mon, Feb 15, 2016 at 4:54 PM, Simon Slavin > wrote: > > > > > On 15 Feb 2016, at 9:41pm, James K. Lowden > > wrote: > > > > > SQL Server has none of those restrictions, and probably keeps pace with > > > SQLite even on its home turf. But the administration of SQL Server is > > > nontrivial. For that reason alone, I would never use it in situations > > > where SQLite would do. > > > > That's the fella. Major advantage of SQLite: zero admin. Not even a > > background task. > > > > Second advantage: you know exactly where you data is. Better still, it's > > simple: one database == one file, and the file has the same name as the > > database. I remember trying to reconstruct a MySQL database from a dead > > server. One folder with a confusing mass of files in. Your database is > > part of some of those files, but the files may be huge even if the one > > database you care about is tiny. That was not a fun time. > > > > Simon. > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > >
[sqlite] Performance comparison between SQLite and SQL Server?
It would be great if someone could redo these tests with the current versions of the databases. Jim Callahan a ?crit : > There is a historical and unfair (specially compiled version of SQLite > against default settings of PostgreSQL) benchmark > available on this page, but now that you understand the use cases, this > particular benchmark is not that useful in addition > to being out of date and unfair. > https://www.sqlite.org/speed.html
[sqlite] Performance comparison between SQLite and SQL Server?
On 15 Feb 2016, at 9:41pm, James K. Lowden wrote: > SQL Server has none of those restrictions, and probably keeps pace with > SQLite even on its home turf. But the administration of SQL Server is > nontrivial. For that reason alone, I would never use it in situations > where SQLite would do. That's the fella. Major advantage of SQLite: zero admin. Not even a background task. Second advantage: you know exactly where you data is. Better still, it's simple: one database == one file, and the file has the same name as the database. I remember trying to reconstruct a MySQL database from a dead server. One folder with a confusing mass of files in. Your database is part of some of those files, but the files may be huge even if the one database you care about is tiny. That was not a fun time. Simon.
[sqlite] Performance comparison between SQLite and SQL Server?
Just for fun: I know a friend who has a Ferrari. It is faster than my Ford F-150. Unless we are racing with both vehicles pulling a 7,000 pound trailer uphill. Then I would probably win. Thousand-mile trip? Take a sports car. Moving a couch a thousand miles? Use a pickup truck. SQLite is kinda like a sports car. SQL Server is kinda like a pickup truck. And this car metaphor of mine is kinda like a motorcycle -- if you lean on it too hard, it'll probably fall over. -- E On Mon, Feb 15, 2016 at 5:24 PM, Michael Falconer < michael.j.falconer at gmail.com> wrote: > Good thread, > > which absolutely nails the point 'dev decisions for app cases' make a > developers world go round. I personally couldn't think of a greater waste > of time than a benchmark comparison between client server rdbms's and > sqlite. Do what benefits your case most. The above from Jim pretty much > encapsulates my thoughts: > > "SQLite is not directly comparable to client/server SQL database engines > > such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying > to > > solve a different problem. Client/server SQL database engines strive to > > implement a shared repository of enterprise data. ...SQLite strives to > > provide local data storage for individual applications and devices." > > > > I could bang on about my own preferences and decisions I've made but they'd > only be reiterating the points made above. They were based on system > requirement specs and where local storage was involved it was a blindingly > obvious decision to go with sqlite. Rob above made another excellent point > often overlooked (usually an afterthought for many dev's): > > 4. The support is top notch. I have brought and paid for govt scale > > databases for governments and to be honest the support for SQLite is just > > as good, and to be honest I would say better than Big Red or Big Blue > (and > > I used to work for Big Blue). > > > > It is another unique property of a great product. Support is not just > sqlite specific either (a cop out on many a tech forum) and particularly on > this list the topics can be rather broad. There is plenty of good quality > feedback and many a good general SQL solution which just adds to the sqlite > package as a whole. > > > On 16 February 2016 at 09:42, Jim Callahan > > wrote: > > > SQLite would be most comparable to *SQL Server Express LocalDB* edition > > which is introduced in this July 2011 blog post > > > > > https://blogs.msdn.microsoft.com/sqlexpress/2011/07/12/introducing-localdb-an-improved-sql-express/ > > > > More uptodate information about *SQL Server Express LocalDB* edition > > is in this 2016 Microsoft Developer's Network (MSDN) article > > https://msdn.microsoft.com/en-us/library/hh510202.aspx > > > > This page "*Appropriate Uses for SQLite*" (whentouse.html) describes BOTH > > "*Situations Where SQLite Works Well*" > > > > and > > > > "*Situations Where A Client/Server RDBMS May Work Better*" > > http://sqlite.org/whentouse.html > > > > > > Opening lines of whentouse.html: > > > > "SQLite is not directly comparable to client/server SQL database engines > > such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying > to > > solve a different problem. Client/server SQL database engines strive to > > implement a shared repository of enterprise data. ...SQLite strives to > > provide local data storage for individual applications and devices." > > > > Even Microsoft has adopted SQLite for some limited tasks (such as storing > > state) within every shipping copy of Windows 10. > > "SQLite is a unique case: it is an open source, externally developed > > software that is used by core system components, and our flagship apps > like > > Cortana and Skype. ...After shipping SQLite as a system component in > July, > > we wanted to include it in our SDK for November. With more than 20,000 > > Windows Apps and more than half of our top apps using SQLite, it made > sense > > to just make expose the system SQLite to app developers." > > http://engineering.microsoft.com/2015/10/29/sqlite-in-windows-10/ > > > > > > There is a historical and unfair (specially compiled version of SQLite > > against default settings of PostgreSQL) benchmark > > available on this page, but now that you understand the use cases, this > > particular benchmark is not that useful in addition > > to being out of date and unfair. > > https://www.sqlite.org/speed.html > > > > Jim Callahan > > Data Scientist > > https://www.linkedin.com/in/jamesbcallahan > > Orlando, FL > > > > On Mon, Feb 15, 2016 at 4:54 PM, Simon Slavin > > wrote: > > > > > > > > On 15 Feb 2016, at 9:41pm, James K. Lowden > > > wrote: > > > > > > > SQL Server has none of those restrictions, and probably keeps pace > with > > > > SQLite even on its home turf. But the administration of SQL Server > is > > > > nontrivial. For that reason alone, I would never use it in > situations > > > > where SQLite would do. > > > > > > That's the
[sqlite] Performance comparison between SQLite and SQL Server?
On Mon, Feb 15, 2016 at 9:07 PM, Keith Medcalf wrote: > > On the other hand if you drive either on a road with a speed limit of 30 > miles per hour (and go the speed limit), they both go the same distance in > the same time. > > In other words, inquiring "which gets from one side of town to the other" > the fastest, a Ferrari or an F-150, is not dependent on either the Ferrari > or the F-150, but the infrastructure on which they are travelling. A > similar question would be "which weighs more, a ton of feathers or a ton of > depleted uranium?". > > So the answer is that both SQL Server and SQLite will "travel" at the > "speed limit" imposed by the hardware on which they are run. > On the third hand, how many system resources will SQL Server use to achieve a given level of performance vs SQLite. You don't want a Ferrari or an F-150. You want a Prius. :)
[sqlite] Performance comparison between SQLite and SQL Server?
On the other hand if you drive either on a road with a speed limit of 30 miles per hour (and go the speed limit), they both go the same distance in the same time. In other words, inquiring "which gets from one side of town to the other" the fastest, a Ferrari or an F-150, is not dependent on either the Ferrari or the F-150, but the infrastructure on which they are travelling. A similar question would be "which weighs more, a ton of feathers or a ton of depleted uranium?". So the answer is that both SQL Server and SQLite will "travel" at the "speed limit" imposed by the hardware on which they are run. > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Eric Sink > Sent: Monday, 15 February, 2016 20:52 > To: SQLite mailing list > Subject: Re: [sqlite] Performance comparison between SQLite and SQL > Server? > > Just for fun: > > I know a friend who has a Ferrari. It is faster than my Ford F-150. > > Unless we are racing with both vehicles pulling a 7,000 pound trailer > uphill. Then I would probably win. > > Thousand-mile trip? Take a sports car. > > Moving a couch a thousand miles? Use a pickup truck. > > SQLite is kinda like a sports car. > > SQL Server is kinda like a pickup truck. > > And this car metaphor of mine is kinda like a motorcycle -- if you lean on > it too hard, it'll probably fall over. > > -- > E > > > On Mon, Feb 15, 2016 at 5:24 PM, Michael Falconer < > michael.j.falconer at gmail.com> wrote: > > > Good thread, > > > > which absolutely nails the point 'dev decisions for app cases' make a > > developers world go round. I personally couldn't think of a greater > waste > > of time than a benchmark comparison between client server rdbms's and > > sqlite. Do what benefits your case most. The above from Jim pretty much > > encapsulates my thoughts: > > > > "SQLite is not directly comparable to client/server SQL database engines > > > such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is > trying > > to > > > solve a different problem. Client/server SQL database engines strive > to > > > implement a shared repository of enterprise data. ...SQLite strives to > > > provide local data storage for individual applications and devices." > > > > > > > I could bang on about my own preferences and decisions I've made but > they'd > > only be reiterating the points made above. They were based on system > > requirement specs and where local storage was involved it was a > blindingly > > obvious decision to go with sqlite. Rob above made another excellent > point > > often overlooked (usually an afterthought for many dev's): > > > > 4. The support is top notch. I have brought and paid for govt scale > > > databases for governments and to be honest the support for SQLite is > just > > > as good, and to be honest I would say better than Big Red or Big Blue > > (and > > > I used to work for Big Blue). > > > > > > > It is another unique property of a great product. Support is not just > > sqlite specific either (a cop out on many a tech forum) and particularly > on > > this list the topics can be rather broad. There is plenty of good > quality > > feedback and many a good general SQL solution which just adds to the > sqlite > > package as a whole. > > > > > > On 16 February 2016 at 09:42, Jim Callahan > > > > > wrote: > > > > > SQLite would be most comparable to *SQL Server Express LocalDB* > edition > > > which is introduced in this July 2011 blog post > > > > > > > > https://blogs.msdn.microsoft.com/sqlexpress/2011/07/12/introducing- > localdb-an-improved-sql-express/ > > > > > > More uptodate information about *SQL Server Express LocalDB* edition > > > is in this 2016 Microsoft Developer's Network (MSDN) article > > > https://msdn.microsoft.com/en-us/library/hh510202.aspx > > > > > > This page "*Appropriate Uses for SQLite*" (whentouse.html) describes > BOTH > > > "*Situations Where SQLite Works Well*" > > > > > > and > > > > > > "*Situations Where A Client/Server RDBMS May Work Better*" > > > http://sqlite.org/whentouse.html > > > > > > > > > Opening lines of whentouse.html: > > > > > > "SQLite is not directly comparable to client/server SQL database > engines > > &g
[sqlite] Performance comparison between SQLite and SQL Server?
SQLite would be most comparable to *SQL Server Express LocalDB* edition which is introduced in this July 2011 blog post https://blogs.msdn.microsoft.com/sqlexpress/2011/07/12/introducing-localdb-an-improved-sql-express/ More uptodate information about *SQL Server Express LocalDB* edition is in this 2016 Microsoft Developer's Network (MSDN) article https://msdn.microsoft.com/en-us/library/hh510202.aspx This page "*Appropriate Uses for SQLite*" (whentouse.html) describes BOTH "*Situations Where SQLite Works Well*" and "*Situations Where A Client/Server RDBMS May Work Better*" http://sqlite.org/whentouse.html Opening lines of whentouse.html: "SQLite is not directly comparable to client/server SQL database engines such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to solve a different problem. Client/server SQL database engines strive to implement a shared repository of enterprise data. ...SQLite strives to provide local data storage for individual applications and devices." Even Microsoft has adopted SQLite for some limited tasks (such as storing state) within every shipping copy of Windows 10. "SQLite is a unique case: it is an open source, externally developed software that is used by core system components, and our flagship apps like Cortana and Skype. ...After shipping SQLite as a system component in July, we wanted to include it in our SDK for November. With more than 20,000 Windows Apps and more than half of our top apps using SQLite, it made sense to just make expose the system SQLite to app developers." http://engineering.microsoft.com/2015/10/29/sqlite-in-windows-10/ There is a historical and unfair (specially compiled version of SQLite against default settings of PostgreSQL) benchmark available on this page, but now that you understand the use cases, this particular benchmark is not that useful in addition to being out of date and unfair. https://www.sqlite.org/speed.html Jim Callahan Data Scientist https://www.linkedin.com/in/jamesbcallahan Orlando, FL On Mon, Feb 15, 2016 at 4:54 PM, Simon Slavin wrote: > > On 15 Feb 2016, at 9:41pm, James K. Lowden > wrote: > > > SQL Server has none of those restrictions, and probably keeps pace with > > SQLite even on its home turf. But the administration of SQL Server is > > nontrivial. For that reason alone, I would never use it in situations > > where SQLite would do. > > That's the fella. Major advantage of SQLite: zero admin. Not even a > background task. > > Second advantage: you know exactly where you data is. Better still, it's > simple: one database == one file, and the file has the same name as the > database. I remember trying to reconstruct a MySQL database from a dead > server. One folder with a confusing mass of files in. Your database is > part of some of those files, but the files may be huge even if the one > database you care about is tiny. That was not a fun time. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Performance comparison between SQLite and SQL Server?
On Mon, 15 Feb 2016 11:21:06 +0800 wrote: > I am just curious whether there is a performance comparison between > SQLite and SQL Server? Odds are you will never see a such a comparison published. If you read your SQL Server EULA, you'll see it specifically prohibits publishing benchmark results. The restriction is not purely evil. It could be argued that the flaws embedded in any testing regimen render the conclusion meaningless. Performance of any kind is notoriously difficult to measure. That's especially true of DBMSs because of their complexity. Optimal DBMS performance is a function of hardware, OS, and DBMS configuration. That's a lot of expertise to bring to bear on making *one* DBMS perform at its best; doing it for N is at least N times harder, especially if you're being scrupulous. Back when SQL was young, people still read magazines about new technology, and benchmarks were a popular feature. I remember poring over them to learn whether Oracle was faster than Sybase, etc. But the more I learned, the better I understood the futility of the question. There were too many moving parts, and the journalists had nowhere near the resources needed to do the job properly. Any decision they rendered was flawed, and in any case became obsolete as the technology evolved. I think it was in answer to such testing that the vendors began including restrictions on published performance measurements in their EULAs. In answer to your question, I would say SQLite performs very well provided you live with its restrictions. In particular: 1. Very little write contention. 2. All processes running on the same machine, using a local filesystem. 3. Queries of limited complexity, or data of limited size. That is, you're lijmited to one writer at a time, you can't use a network filesystem, and query optimization will hurt with large tables if, for example, subqueries can't be flattened. SQL Server has none of those restrictions, and probably keeps pace with SQLite even on its home turf. But the administration of SQL Server is nontrivial. For that reason alone, I would never use it in situations where SQLite would do. --jkl
[sqlite] Performance comparison between SQLite and SQL Server?
Hi, I am just curious whether there is a performance comparison between SQLite and SQL Server? Surely SQL Server will perform better on huge database with thousands of tables(more than 10GB size). But whether SQLite will perform better on smaller database such as one database with one table that is less than 1GB? Thanks
[sqlite] Performance comparison between SQLite and SQL Server?
On 2016/02/15 5:21 AM, admin at shuling.net wrote: > Hi, > > I am just curious whether there is a performance comparison between SQLite > and SQL Server? Surely SQL Server will perform better on huge database with > thousands of tables(more than 10GB size). But whether SQLite will perform > better on smaller database such as one database with one table that is less > than 1GB? > > Thanks Some other replies have hinted at this already, but to expand: The one isn't faster than the other in all cases. There are ways in which SQLite is much faster even on a 100GB database - the "Lite" in SQLite doesn't mean database size - it pertains more to the available function-set. It can handle the very large DB's just as well as any other. Where SQLite lacks (due to the "Lite"-ness) is in not having user-access control, not having programmability (stored procedures and functions, but then you can add custom functions to SQLite in C even, which you can't easily do with the others, especially not with MSSQL). The largest difference however, is that an SQLite connection operates on (talks-to) a file, and the others usually talk to a server. The main advantage of SQLite is that it can be used for an application file format to your program, complete as if it was a database in itself (which, actually, it is), and moreover, you can embed the entire DB in your application and on your hardware etc - like the billions of handheld devices, phones, tablets, etc. that uses SQLite daily. For more information, see: http://www.sqlite.org/whentouse.html To answer your speed question - it depends on data shape, size, IO access speeds, Memory on the generation machine etc. Import big datasets in both MSSQL and SQlite, run some queries, chances are some queries be slightly faster in SQLite, and some are slightly faster in MSSQL. Single query performance is not really the driver of that decision. Cheers, Ryan
[sqlite] Performance comparison between SQLite and SQL Server?
admin at shuling.net wrote: > I am just curious whether there is a performance comparison between SQLite > and SQL Server? That depends on the data, and the software, and the hardware. In other way: you have to measure yourself. > Surely SQL Server will perform better on huge database with thousands > of tables(more than 10GB size). If you write a query that needs to read all the data, both databases are limited by the I/O speed. Otherwise, the two databases will have similar performance, unless you're using a query that can use some optimization that is implemented only in one of them. SQLite uses a much simpler locking scheme that can be faster if there aren't concurrent accesses. SQL Server has higher concurrency. This is not necessarily dependent on the size of the database. Regards, Clemens
[sqlite] Performance comparison between SQLite and SQL Server?
To add to the responses, we use SQLite for our main database which is currently around 60GB in size, so size isn?t an issue for SQLite. I am aware of other people with far, far larger SQLite databases. We did a quick test and ran up to a couple of hundred DB?s of data in SQLIte and it seemed fine. We chose SQLite over other databases as 1. We didn?t need concurrency. All our data is written and read by a single process. 2. Its very lightweight. We can move the database around by simply copying the file which is fantastic for our use. 3. The speed is very, very fast. We haven?t found any significant speed differences FOR OUR USE CASE between Sqlite and other ?bigger? databases. YMMV. 4. The support is top notch. I have brought and paid for govt scale databases for governments and to be honest the support for SQLite is just as good, and to be honest I would say better than Big Red or Big Blue (and I used to work for Big Blue). Thats not to say that SQLite is perfect, its not, however no database is perfect as everyones usage is different. I certainly wouldn?t state that SQLite is the answer to everyones problems and I can state with absolute certainty that DB2 is also not the answer for every use. We are moving into a different phase of our development and we are investigating PostGIS for some of our work now, as that *MAY* be a better tool for some of our more exotic spatial queries and analysis. No reflection on SQLite but its a case of finding the right tool for the right job. After saying that we *may* end up using SQLite for this area as well. Rob On 15 Feb 2016, at 8:20, R Smith wrote: > On 2016/02/15 5:21 AM, admin at shuling.net wrote: >> Hi, >> >> I am just curious whether there is a performance comparison between >> SQLite >> and SQL Server? Surely SQL Server will perform better on huge >> database with >> thousands of tables(more than 10GB size). But whether SQLite will >> perform >> better on smaller database such as one database with one table that >> is less >> than 1GB? >> >> Thanks > > Some other replies have hinted at this already, but to expand: The one > isn't faster than the other in all cases. There are ways in which > SQLite is much faster even on a 100GB database - the "Lite" in SQLite > doesn't mean database size - it pertains more to the available > function-set. It can handle the very large DB's just as well as any > other. > > Where SQLite lacks (due to the "Lite"-ness) is in not having > user-access control, not having programmability (stored procedures and > functions, but then you can add custom functions to SQLite in C even, > which you can't easily do with the others, especially not with MSSQL). > The largest difference however, is that an SQLite connection operates > on (talks-to) a file, and the others usually talk to a server. > > The main advantage of SQLite is that it can be used for an application > file format to your program, complete as if it was a database in > itself (which, actually, it is), and moreover, you can embed the > entire DB in your application and on your hardware etc - like the > billions of handheld devices, phones, tablets, etc. that uses SQLite > daily. > > For more information, see: > http://www.sqlite.org/whentouse.html > > To answer your speed question - it depends on data shape, size, IO > access speeds, Memory on the generation machine etc. Import big > datasets in both MSSQL and SQlite, run some queries, chances are some > queries be slightly faster in SQLite, and some are slightly faster in > MSSQL. Single query performance is not really the driver of that > decision. > > Cheers, > Ryan > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Performance comparison between SQLite and SQL Server?
They're intended for fundamentally different uses. It's like asking what's more energy efficient for cooking dinner - a wok or an oven. On Mon, 15 Feb 2016 at 14:21 wrote: > Hi, > > I am just curious whether there is a performance comparison between SQLite > and SQL Server? Surely SQL Server will perform better on huge database with > thousands of tables(more than 10GB size). But whether SQLite will perform > better on smaller database such as one database with one table that is less > than 1GB? > > Thanks > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Performance comparison between SQLite and SQL Server?
On Feb 15, 2016, at 1:02 AM, Clemens Ladisch wrote: > > SQLite uses a much simpler locking scheme that can be faster if there > aren't concurrent accesses. SQL Server has higher concurrency. SQLite also doesn?t have the IPC overhead of a client/server DBMS, so if you don?t need concurrency or remote access, SQLite can be faster, since all data is moved around inside a single process.
