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…)
Data amount processed by modern databases grows steadily. In this regard, there is an acute problem – database performance. Insert, Update and Delete operations have to be performed as fast as possible. Therefore Devart provides several solutions to speed up processing of huge amounts of data. So, for example, insertion of a large portion of data to a DB is supported in the Loader. Unfortunately, Loader allows to insert data only – it can’t be used for updating and deleting data.
The new version of Devart Delphi Data Access Components introduces the new mechanism for large data processing — Batch Operations. The point is that just one parametrized Modify SQL query is executed. The plurality of changes is due to the fact that parameters of such a query will be not single values, but a full array of values. Such approach increases the speed of data operations dramatically. Moreover, in contrast to using Loader, Batch operations can be used not only for insertion, but for modification and deletion as well.
LiteDAC and UniDAC interact with SQLite in 2 approaches. The first approach is that an application uses functions of the linked SQLite library. For Windows OS it is sqlite3.dll, for Mac OS and iOS – libsqlite3.dylib, for Android – libsqlite.so. The second approach allows to connect to the database from the application directly, using so called Direct Mode. Direct Mode provides interaction with SQLite avoiding any external libraries linking. It is implemented by embedding the code of the SQLite library directly to the application. This approach allows to work with SQLite in cases, when using third-party libraries is impossible due to a number of reasons. For example, when developing applications for iOS or Mac OS, the Apple corporation prohibits deployment of any libraries along with the application.
However, there are situations, when it is preferably to use the first mode. Thus, when developing applications for Android, there is no need to worry about SQLite client library presence, since it is included into this OS distribution. On the other hand, during project implementation, strict requirements may be imposed on the compiled application size. Such requirements are especially relevant for mobile development. Therefore, it is highly desirable to have an opportunity to disable modules, that won’t be used in the application, before compilation. LiteDAC and UniDAC allow to exclude the code of the SQLite client library, that implements Direct Mode, from a ready application. As a result, the size of the developed application may be decreased. (more…)
Since RAD Studio XE7 Update 1 was released, the ETTracker.dll library was added to installation. Its purpose is obscure, but its presence in the installation caused serious problems for Windows XP users when installing RAD Studio XE7 Update 1. (more…)
Using SDAC in Direct Mode for access to SQL Server from iOS and Android platforms doesn’t significantly differ from the one on Mac OS X or Windows platforms. Let’s see how this works in a sample below for RAD Studio XE7. (more…)
Devart Company released SDAC and UniDAC for RAD Studio with a new functionality – Direct Mode for connection to SQL Server. The existing providers for SQL Server by Microsoft use the OLE DB interface, that allows using client applications only on Windows platforms. Due to absence of native solutions by Microsoft for Mac OS X, SDAC Direct Mode was the only opportunity to support SQL Server for Mac OS X.
Devart provides a ready solution for work with SQL Server from Mac OS X – Direct Mode. Let’s consider a simple sample of using Direct Mode. (more…)
Database application development is usually divided into two stages: database design and application writing. Oracle Data Access Components for Delphi (ODAC) allow implementation of work with Oracle server, and dbForge Studio for Oracle – database design and administration. Now, for convenient development, integration with dbForge Studio for Oracle is implemented in ODAC.
To enable integration to dbForge Studio for Oracle, in the Delphi main menu select ODAC → Database Tools → dbForge Studio for Oracle Integration.
The main idea of EntityDAC, as well as any ORM for RAD Studio, is to free the programmer from having to implement a business-logic, and the logic of database interaction separately in the application source code. When writing code, programmer abstracts himself from specifics of a DBMS and operates the database entities using structures of his “native” programming language. (more…)
DAC Team claims its products to be up-to-date and feature-rich solutions for Delphi developers. We are always trying our best to remain at the edge of programming technologies and cross-platform application development. Support for RAD Studiio XE7 was one of those high-priority tasks… And now we are happy to announce the new versions of already beloved products for Delphi programmers with support for the cutting-edge IDE:
Devart is grateful for your choice and won’t short-sell you.
Thank you for staying with us!
In RAD Studio XE6 there appeared an opportunity to develop applications for iOS and Android on C++. Let’s consider the peculiarities of database application development on C++ using UniDAC.
UniDAC is Universal Data Access Components that allow to connect to SQLite, Oracle, MySQL, PostgreSQL, and InterBase (since version XE3) from iOS and Android (you can use UniDAC for connection from Windows to almost all the known databases, but this is beyond the scope of this blog). (more…)