Does anyone know if it's good solution to use SQLite in multi-thread environment. I want to replace SQL Server with more simple and built-in database as there is no need to feed such big server DB. The supposed max size of DB would be 4 gigabyte after 4-5 years of usage. Is it normal for built-in DB? Could it affect performance?
-
SQL Server, or SQL Server Compact Edition? Compact Edition is embeddable. There's a world of difference between SQL Server non-Compact Edition and SQLite...OMG Ponies– OMG Ponies2010-07-31 18:53:39 +00:00Commented Jul 31, 2010 at 18:53
Add a comment
|
5 Answers
It depends on the type of queries you would use. If the queries are simple selects with plain joins, then SQLite could do fine but I think you would still be better off with e. g. Firebird 2.5 when the stable release gets out (RC3 is available now). You would have somewhat richer SQL to work with. I don't know how much bulk loads are important for you, but neither SQLite nor Firebird are very strong in this area. If you need good bulk insert performance and low cost, then you should look at PostgreSQL or MySQL. There is also a very interesting looking database I happened to stumble upon recently called CUBRID. I have only installed it so far, so I can't tell how good or bad it is but it certainly seems worth a look. You might also want to look at this wikipedia article: http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems
Comments
I don't know which distro you're talking about here. I've only used SQLite.NET and I know it works well on multithreaded applications.
It can also be deployed on client-server systems so you need not worry at all.
Considering Vinko's statement of 'real' databases, you can ignore him. SQLite is really worth it's salt.
If you're working with .NET, you might find this link useful: http://sqlite.phxsoftware.com
1 Comment
Vinko Vrsalovic
The 'real' databases bit wasn't meant to diss SQLite, which is great for what it is, and it is not aimed to be a replacement of the complete RDBMS servers as it lacks, by design!, many of the features the 'real' databases have.
According to the documentation SQLite is thread safe but there are caveats.
Comments
You can use SQLite in a multithreaded environment, but if and only if you build a special version of it (and find out if the library you'll be using it supports it and tweak it if it doesn't.) So, assuming your library supports multithreaded SQLite, if you really need a high level of concurrency to the database you may prefer to use a 'real' database. Be it MSSQL or any other falls out of the scope of the question.
Consider MySQL and SQL Server Express, for example.
If your concurrency level is low, SQLite can cope with it.
Comments
I would also suggest you to take a look at the CUBRID database. It has nice optimizations for Web applications and it is easy to learn.
