SQLite vs SQL Server

Question

Will anyone dictate what is best to use SQLite or SQL Server? I used the XML file as an ADD data store, delete, update .. Someone suggested using SQLite for quick work, but I'm not familiar with SQLite, I know SQL Server.

Answer 1

SQLite is a great built-in database that you deploy with your application. If you are writing a distributed application that clients will install, then SQLite has the great advantage of not having a separate installer or service — this is just one DLL that will be deployed along with the rest of your application.

SQLite also starts in the process and reduces the amount of overhead that the database brings - all data is cached and queried in the process.

SQLite integrates better with your .NET application than SQL Server. You can write a custom function in any .NET language that runs inside the SQLite engine, but is still within your application, invoking the process and space, and thus can call your application to integrate additional data or perform actions during query execution. This very unusual ability greatly facilitates certain actions.

SQLite is usually much faster than SQL Server.

However, SQLite only supports one writer at a time (which means executing a single transaction). SQLite locks the entire database when it needs a lock (read or write), and only one writer can hold a write lock at a time. Because of its speed, this is actually not a problem for applications with low and medium size, but if you have a higher recording volume (hundreds per second), then this can become a bottleneck. There are a number of possible solutions, such as splitting database data into different databases and caching records in a queue and creating them asynchronously. However, if your application is likely to encounter these usage requirements and is not yet written for SQLite, then it is best to use something like a SQL Server with a thinner lock.

UPDATE: SQLite 3.7.0 has added a new log mode called Write Ahead Locking, which supports simultaneous reading while writing. In our internal multi-criteria competitive test, the time ranged from 110 seconds to 8 seconds for the same sequence of controversial reads / records.

Answer 2

Both are in different leagues. One of them is designed to manage data at the enterprise level, and the other for mobile devices (embedded or server environment). Although SQLite deployments can store data in many hundreds of GBs, it’s not what it is built for.

Updated: to reflect an updated question: Please read this SQLite blog post . I hope this helps you and allows you to access it from a redirect to resources for programmatically accessing SQLite from .net.