Increasing SQLite Performance

One the major issues a developer encounters when using the SQLite DBMS in his applications is its performance issue.

Perhaps, a classic case everyone gets into when using SQLite for the first time is very slow execution of multiple INSERT/UPDATE/DELETE operations. Indeed, sequential executions of not even thousands, but hundreds of INSERTs into a table may take too long.
The origin of the issue lies in the specificity of using transactions in SQLite. SQLite starts a transaction automatically every time before any DML statement execution and commits it after execution. Accordingly, when executing multiple consequent statements, a new transaction will be started and committed for each statement.

The solution of this problem is quite simple — the block of DML statements may be enclosed into BEGIN … END operators block ( http://www.sqlite.org/lang_transaction.html ). In this case, each DML statement won’t be executed in a separate transaction, but a single transaction will be started before the whole block execution and committed after all modifications.

Such an approach increases SQLite data modification performance by times. See more details about it in the SQLite documentation ( http://www.sqlite.org/faq.html#q19 ).

However, this approach is not the only way to increase performance in SQLite. Parameters of the DBMS may also be configured using so-called PRAGMA ( http://www.sqlite.org/pragma.html ). The fact is that SQLite parameters are oriented not to high performance by default, but to maximum data safety and integrity. Modification of these parameters may increase performance, however, note, that the data corruption risks increase too.

Let’s analyze the impact to inserts performance by different PRAGMAs using LiteDAC. (more…)

Using SQLite Full-Text Search in Entity Framework


SQLite database supports an advanced functionality of full-text search (FTS) and full-text indexing described comprehensively in the SQLite documentation: SQLite FTS3 and FTS4 Extensions.
We decided to meet the needs of our users willing to take advantage of the full-text search in Entity Framework and implemented the full-text search functionality in our Devart dotConnect for SQLite ADO.NET Entity Framework provider.
This article deals with the following:


Dynamic Database Creation in Entity Framework

Entity Framework 4 RC allows you to create and drop databases in run-time using SSDL for DDL generation. Now ObjectContext has CreateDatabase(), DropDatabase(), and CreateDatabaseScript() methods. They appeared in Entity Framework v4 CTP for Code Only and only for SQLClient initially but later they became available for other EF-providers.

In this article we describe implementation of these methods in Devart data providers. We are using dotConnect for Oracle as a data provider in the following examples. Northwind is used as a sample database.


Entity Framework 4 Release Candidate supported!

We have supported new functionality of Entity Framework 4 including Entity Framework v4 Release Candidate for dotConnect for Oracle, dotConnect for MySQL, dotConnect for PostgreSQL, and dotConnect for SQLite. In this article we consider basic new supported possibilities in comparison with Entity Framework v1. Take into account that the new features of Entity Framework v4 like Persistence Ignorance (POCO), Self-tracking entities, Code Only etc. which don’t require support of the provider writers aren’t described here.


Entity Framework Canonical Functions

Article was updated on 3/4/2010

This article can be useful for programmers who want to develop cross-database applications and use the canonical functions.

Entity Framework Canonical Functions are a set of functions, which are supported by all Entity Framework providers. These canonical functions are translated to the corresponding data source functionality for the provider.

The tables below contain information about these functions supported by the Devart products.
Functions supported by the following DBMS: MySQL, PostgreSQL, Oracle, SQLite, SQL Server 2005, and SQL
Server 2008 are marked in green.


SQLite UDFs : Adding Sqrt and CToF functions

According to the list of core functions in SQLite, there is no native support for the most mathematics functions (sqrt, log, etc). Furthermore, user may need non-standard functions for his own task (e.g. conversion Celsius to Fahrenheit). This issue can be resolved with user-defined functions (UDFs).

Support for UDFs is added to dotConnect for SQLite starting with the 2.50 version. UDF provides a mechanism for extending the functionality of the SQLite engine by adding a function that can be evaluated in the SQL statements of SQLiteCommand. For more information, please refer to our documentation to the SQLiteAggregateFunction, SQLiteCollationFunction, SQLiteScalarFunction classes (there are samples of using them). Full support for user-defined functions and collating sequences means that in many cases if SQLite doesn’t have a feature, you can write it yourself in your favorite .NET language. Writing UDF’s and collating sequences has never been easier.