Synopse

To content | To menu | To search

Tag - NexusDB

Entries feed

2014, Friday April 18

Introducing mORMot's architecture and design principles

We have just released a set of slides introducing 

  • ORM, SOA, REST, JSON, MVC, MVVM, SOLID, Mocks/Stubs, Domain-Driven Design concepts with Delphi, 
  • and showing some sample code using our Open Source mORMot framework.

You can follow the public link on Google Drive!

This is a great opportunity to discovers some patterns you may not be familiar with, and find out how mORMot try to implement them.
This set of slides may be less intimidating than our huge documentation - do not be terrified by our Online Documentation!
The first set of pages (presenting architecture and design principles) is worth reading.

Feedback is welcome on our forum, as usual.

2014, Friday March 7

Support of MySQL, DB2 and PostgreSQL

We just tested, benchmarked and validated Oracle MySQL, IBM DB2 and PostgreSQL support for our SynDB database classes and the mORMot's ORM core.
This article will also show all updated results, including our newly introduced multi-value INSERT statement generations, which speed up a lot BATCH insertion.

Stay tuned!

Purpose here is not to say that one library or database is better or faster than another, but publish a snapshot of mORMot persistence layer abilities, depending on each access library.

In this timing, we do not benchmark only the "pure" SQL/DB layer access (SynDB units), but the whole Client-Server ORM of our framework.

Process below includes all aspects of our ORM:

  • Access via high level CRUD methods (Add/Update/Delete/Retrieve, either per-object or in BATCH mode);
  • Read and write access of TSQLRecord instances, via optimized RTTI;
  • JSON marshaling of all values (ready to be transmitted over a network);
  • REST routing, with security, logging and statistic;
  • Virtual cross-database layer using its SQLite3 kernel;
  • SQL on-the-fly generation and translation (in virtual mode);
  • Access to the database engines via several libraries or providers.

In those tests, we just bypassed the communication layer, since TSQLRestClient and TSQLRestServer are run in-process, in the same thread - as a TSQLRestServerDB instance. So you have here some raw performance testimony of our framework's ORM and RESTful core, and may expect good scaling abilities when running on high-end hardware, over a network.

On a recent notebook computer (Core i7 and SSD drive), depending on the back-end database interfaced, mORMot excels in speed, as will show the following benchmark:

  • You can persist up to 570,000 objects per second, or retrieve 870,000 objects per second (for our pure Delphi in-memory engine);
  • When data is retrieved from server or client 38, you can read more than 900,000 objects per second, whatever the database back-end is;
  • With a high-performance database like Oracle, and our direct access classes, you can write 70,000 (via array binding) and read 160,000 objects per second, over a 100 MB network;
  • When using alternate database access libraries (e.g. Zeos, or DB.pas based classes), speed is lower (even if comparable for DB2, MS SQL, PostgreSQL, MySQL) but still enough for most work, due to some optimizations in the mORMot code (e.g. caching of prepared statements, SQL multi-values insertion, direct export to/from JSON, SQlite3 virtual mode design, avoid most temporary memory allocation...).

Difficult to find a faster ORM, I suspect.

Continue reading...

2014, Monday March 3

ORM enhanced for BATCH insert

We just committed some nice features to the ORM kernel, and SynDB* classes of our mORMot framework.

During BATCH insertion, the ORM is able to generate some optimized SQL statements, depending on the target database, to send several rows of data at once.
It induces a noticeable speed increase when saving several objects into an external database.

This feature is available for SQlite3 (3.7.11 and later), MySQL, PostgreSQL, MS SQL Server (2008 and up), Oracle, Firebird and NexusDB.
Since it is working at SQL level, it is available for all supported access libraries, e.g. ODBC, OleDB, Zeos/ZDBC, UniDAC, FireDAC.
It means that even properties not implementing array binding (like OleDB, Zeos or UniDAC) are able to have a huge boost at data insertion, ready to compete with the (until now) more optimized libraries.

Continue reading...

2013, Monday November 4

Updated mORMot database benchmark - including MS SQL and PostgreSQL

On an recent notebook computer (Core i7 and SSD drive), depending on the back-end database interfaced, mORMot excels in speed:

  • You can persist up to 570,000 objects per second, or retrieve more than 900,000 objects per second (for our pure Delphi in-memory engine);
  • When data is retrieved from server or client cache, you can read more than 900,000 objects per second, whatever the database back-end is;
  • With a high-performance database like Oracle and our direct access classes, you can write 65,000 (via array binding) and read 160,000 objects per second, over a 100 MB network;
  • When using alternate database access libraries (e.g. Zeos, or DB.pas based classes), speed is lower, but still enough for most work.

Difficult to find a faster ORM, I suspect.

The following tables try to sum up all available possibilities, and give some benchmark (average objects/second for writing or read).

In these tables:

  • 'SQLite3 (file full/off/exc)' indicates use of the internal SQLite3 engine, with or without Synchronous := smOff and/or DB.LockingMode := lmExclusive;
  • 'SQLite3 (mem)' stands for the internal SQLite3 engine running in memory;
  • 'SQLite3 (ext ...)' is about access to a SQLite3 engine as external database - either as file or memory;
  • 'TObjectList' indicates a TSQLRestServerStaticInMemory instance, either static (with no SQL support) or virtual (i.e. SQL featured via SQLite3 virtual table mechanism) which may persist the data on disk as JSON or compressed binary;
  • 'Oracle' shows the results of our direct OCI access layer (SynDBOracle.pas);
  • 'NexusDB' is the free embedded edition, available from official site;
  • 'Zeos *' indicates that the database was accessed directly via the ZDBC layer;
  • 'FireDAC *' stands for FireDAC library;
  • 'UniDAC *' stands for UniDAC library;
  • 'BDE *' when using a BDE connection;
  • 'ODBC *' for a direct access to ODBC;
  • 'Jet' stands for a MSAccess database engine, accessed via OleDB;
  • 'MSSQL local' for a local connection to a MS SQL Express 2008 R2 running instance (this was the version installed with Visual Studio 2010), accessed via OleDB.

This list of database providers is to be extended in the future. Any feedback is welcome!

Numbers are expressed in rows/second (or objects/second). This benchmark was compiled with Delphi 7, so newer compilers may give even better results, with in-lining and advanced optimizations.

Note that these tests are not about the relative speed of each database engine, but reflect the current status of the integration of several DB libraries within the mORMot database access.

Purpose here is not to say that one library or database is better or faster than another, but publish a snapshot of current mORMot persistence layer abilities.

In this timing, we do not benchmark only the "pure" SQL/DB layer access (SynDB units), but the whole Client-Server ORM of our framework: process below includes read and write RTTI access of a TSQLRecord, JSON marshaling, CRUD/REST routing, virtual cross-database layer, SQL on-the-fly translation. We just bypass the communication layer, since TSQLRestClient and TSQLRestServer are run in-process, in the same thread - as a TSQLRestServerDB instance. So you have here some raw performance testimony of our framework's ORM and RESTful core.

You can compile the "15 - External DB performance" supplied sample code, and run the very same benchmark on your own configuration.

Continue reading...

2013, Sunday February 17

Interface-based service sample: remote SQL access

You will find in the SQLite3\Sample\16 - Execute SQL via services folder of mORMot source code a Client-Server sample able to access any external database via JSON and HTTP.
It is a good demonstration of how to use an interface-based service between a client and a server.
It will also show how our SynDB classes have a quite abstract design, and are easy to work with, whatever database provider you need to use.

The corresponding service contract has been defined:

  TRemoteSQLEngine = (rseOleDB, rseODBC, rseOracle, rseSQlite3, rseJet, rseMSSQL);

IRemoteSQL = interface(IInvokable) ['{9A60C8ED-CEB2-4E09-87D4-4A16F496E5FE}'] procedure Connect(aEngine: TRemoteSQLEngine; const aServerName, aDatabaseName, aUserID, aPassWord: RawUTF8); function GetTableNames: TRawUTF8DynArray; function Execute(const aSQL: RawUTF8; aExpectResults, aExpanded: Boolean): RawJSON; end;

Purpose of this service is:
- To Connect() to external databases, given the parameters of a standard TSQLDBConnectionProperties. Create() constructor;
- Retrieve all table names of this external database as a list;
- Execute any SQL statement, returning the content as JSON array, ready to be consumed by AJAX applications (if aExpanded is true), or a Delphi client (e.g. via a TSQLTableJSON and the mORMotUI unit).

Of course, this service will be define as sicClientDriven mode, that is, the framework will be able to manage a client-driven TSQLDBProperties instance life time.

Benefit of this service is that no database connection is required on the client side: a regular HTTP connection is enough.
No need to install nor configure any database provider, and full SQL access to the remote databases.

Due to our optimized JSON serialization, it will probably be faster to work with such plain HTTP / JSON services, instead of a database connection through a VPN. In fact, database connections are made to work on a local network, and do not like high-latency connections, which are typical on the Internet.

Continue reading...

2013, Tuesday February 12

Introducing ZEOS, UniDAC, NexusDB, BDE, any TDataset to SynDB and mORMot's ORM

Up to now, our SynDB database classes were handling ODBC, OleDB providers and direct Oracle or SQLite3 connection.

We have added a DB.pas based layer, ready to be used with UniDAC, NexusDB, or the BDE.
Any other TDataset based component is ready to be interfaced, including UIB, AnyDAC or DBExpress.

The ZEOS library (in its latest 7.0.3 stable version, which works from Delphi 7 up to XE3) has also been interfaced, but without the TDataset/DB.pas layer: our SynDBZEOS.pas unit calls the ZDBC layer, which is not tied to DB.pas nor its RAD components, and is therefore faster. By the way, it will work also with the Starter edition of Delphi (which does not include the DB components) - just like the other "regular" SynDB classes.

This is a work in progress, any testing and feedback is welcome!
We had to circumvent some particularities of the libraries, but I guess we have something interesting.

A dedicated "SynDBDataset" sub-folder has been created in the repository, to contain all SynDBDataset.pas-based database providers.
SynDBNexusDB.pas unit has been moved within this sub-folder, as SynDBUniDAC.pas + SynDBBDE.pas units have been added.
SynDBZeos.pas has a direct access to the ZDBC layer, so is not part of the "SynDBDataset" sub-folder.

Here is some benchmark, mainly about Oracle and SQlite3 database access.
Of course, our direct SynDBOracle / SynDBSQLite3 layers are the fastest around, and we can see that ZDBC layer is sometimes more efficient than the TDataset components.

Continue reading...

2012, Tuesday July 24

SQLite3-powered, not SQLite3-limited

Our downloadable documentation has been enhanced, and contains now a description about the main feature of 1.15 version, i.e. "database agnosticism".

The core database of our mORMot framework uses the SQLite3 library, which is a Free, Secure, Zero-Configuration, Server-less, Single Stable Cross-Platform Database File database engine.

As stated below, you can use any other database access layer, if you wish.
A fast in-memory engine (TObjectList-based) is included, and can be used instead or together with the SQLite3 engine.
Or you may be able to access any remote database, and use one or more OleDB, ODBC, ZDBCTDataSet, (or direct Oracle) connections to store your precious ORM objects.
The SQlite3 will be used as the main SQL engine, able to JOIN all those tables, thanks to its Virtual Table unique feature.

(article updated after removal of the TSQLRecordExternal class type for revision 1.17 - note also that BATCH process is now directly supported by the framework and converted to bound array parameters if available)

Continue reading...