Synopse

To content | To menu | To search

Tag - MSSQL

Entries feed

2014, Tuesday November 18

HTTP remote access for SynDB SQL execution

For mORMot, we developed a fully feature direct access layer to any RDBMS, implemented in the SynDB.pas unit.

You can use those SynDB classes to execute any SQL statement, without any link to the framework ORM.
At reading, the resulting performance is much higher than using the standard TDataSet component, which is in fact a true performance bottleneck.
It has genuine features, like column access via late-binding, an innovative ISQLDBRows interface, and ability to directly access the low-level binary buffers of the database clients.

We just added a nice feature to those classes: the ability to access remotely, via plain HTTP, to any SynDB supported database!

Continue reading...

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 June 16

FireDAC / AnyDAC support for mORMot

Our SynDB classes feature now FireDAC / AnyDAC access, with full speed!

Up to now, only UniDAC, BDE or ZEOS components were available as source, but we just added FireDAC / AnyDAC.

FireDAC is an unique set of Universal Data Access Components for developing cross platform database applications on Delphi. This was in fact a third-party component set, bought by Embarcadero to DA-SOFT Technologies (formerly known as AnyDAC), and included with several editions of Delphi XE3 and up. This is the new official platform for high-speed database development in Delphi, in favor to the now deprecated DBExpress.

Our integration within SynDB.pas units and the mORMot persistence layer has been tuned. For instance, you can have direct access to high-speed FireDAC Array DML feature, via the ORM batch process, via so-called array binding.

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...

2013, Monday January 28

External database speed improvements

Some major speed improvements have been made to our SynDB* units, and how they are used within the mORMot persistence layer.
It results in an amazing speed increase, in some cases.

Here are some of the optimizations how took place in the source code trunk:

Overall, I observed from x2 to x10 performance boost with simple Add() operations, using ODBC, OleDB and direct Oracle access, when compare to previous benchmarks (which were already impressive).
BATCH mode performance is less impacted, since it by-passed some of those limitations, but even in this operation mode, there is some benefits (especially with ODBC and OleDB).

Here are some results, directly generated by the supplied "15 - External DB performance" sample.

Continue reading...

2012, Friday September 14

Updated mORMot benchmarks on another HW configuration

With a refreshed hardware, and the latest code modifications of the framework code, I run again the benchmark sample.

The new PC has an Intel Core i7-2600 CPU, running on Windows Seven 64-bit, with anti-virus (Norton) fully enabled.
Oracle 11g database is remotely accessed over a corporate network, so latency and bandwidth is not optimal.
Still no SSD, but a standard 7200 rpm hard drive of 500 GB.

The results are impressive, when compared to the previous run using a Core 2 Duo CPU - mORMot's optimized code achieves amazing speed on this platform.
And I guess the 8MB of L3 cache of the Core i7 does wonders with our code.

Continue reading...

2012, Sunday September 9

Synopse mORMot framework 1.17

Our Open Source mORMot framework is now available in revision 1.17.

The main new features are the following:

We have some very exciting features on the road-map for the next 1.18 release, like direct Event/CallBacks handling.
Stay tuned!

Continue reading...

2012, Monday September 3

Client-Server allowed back to XE3 pro

The attempt to restrict the XE3 professional license did evolve into an amazing discussion in Embarcadero forums, and Delphi-related blogs.

David I announced the (reverted) EULA for Delphi Pro. Remote database access is again possible, with terms similar to Delphi Xe2.
You can check the Software License and Support Terms (EULA) for RAD Studio XE3 products.

This is good news, but also the opportunity to check the definitive terms.

In short, with the XE3 pro license, you have a deployment restrictive clause:

  • You can use DBExpress components and units only locally;
  • You can use DataSnap features only locally (it means that you can prototype using DataSnap, but are not allowed to deploy or redistribute DataSnap).

If you want to use those two features on Client-Server, you would need to buy a Client/Server Pack license:

If licensee has purchased a Client/Server Pack, the Licensee of RAD Studio, Delphi, or C++Builder XE3 Professional Edition (“Product”) may deploy that portion of the Product identified as "dbExpress" and dbExpress enterprise database drivers, in executable form only, to enable client server database access. Embarcadero may deliver the Product identified as “Enterprise,” however Licensee is licensed to use only the “Professional” edition features plus "dbExpress" and the Enterprise dbExpress database drivers in a client/server configuration. Licensee may evaluate the n-Tier DataSnap functionality included in the Enterprise Product delivered, but may not deploy or redistribute DataSnap.

This is now a real opportunity for our Open Source mORMot framework.
With a XE3 pro license, and even with a XE3 starter license, you are able, via our free units and classes:

That is, everything you need to build from a small Client-Server or stand-alone application up to the most scalable Domain-Driven design.

Nice, isn't it?

2012, Friday August 10

Microsoft states: OleDB out - enjoy ODBC!

For our native connection to any DB, we developed a set of classes and several units.

We implemented at first OleDB, then native Oracle direct access and SQlite3 static engine.

Now, Microsoft is officially deprecating OleDB, and urge all developers to switch to the open and cross-platform ODBC API for native connection.

Continue reading...

2012, Wednesday August 1

Managing unique properties

For real applications, retrieving objects per ID is not enough.
Your project may have the need to retrieve objects by a textual field, e.g. a name or identifier.
In this case, you can specify a published property of the TSQLRecord as stored false, and it will be defined as an unique column in the underlying database.

For instance, in the latest version of our performance benchmark sample code, you can define the UNIK conditional to define both LastName and FirstName properties as unique:

type
  TSQLRecordSample = class(TSQLRecord)
  private
    fFirstName: RawUTF8;
    fLastName: RawUTF8;
    fAmount: currency;
    fBirthDate: TDateTime;
    fLastChange: TModTime;
    fCreatedAt: TCreateTime;
  published
    property FirstName: RawUTF8 index 40 read fFirstName write fFirstName
      {$ifdef UNIK}stored false{$endif};
    property LastName: RawUTF8 index 40 read fLastName write fLastName
      {$ifdef UNIK}stored false{$endif};
    property Amount: currency read fAmount write fAmount;
    property BirthDate: TDateTime read fBirthDate write fBirthDate;
    property LastChange: TModTime read fLastChange;
    property CreatedAt: TCreateTime read fCreatedAt write fCreatedAt;
  end;

During insertion or update of records, the database will have to check for uniqueness of those column values. It will have an additional performance cost, since a search of the new value is to be performed among existing values.
In order to speed-up the process, a so-called index is created at the database level.
As a consequence, further lookup using this property will benefit for this index, and will be much faster than a classic loop throughout all data.

In the mORMot core, we just made some modifications related to this feature:

Let's see how it works on the benchmark side.

Continue reading...

2012, Thursday July 26

ACID and speed

As stated during our recent benchmarks, the default SQlite3 write speed is quite slow, when running on a normal hard drive. By default, the engine will pause after issuing a OS-level write command. This guarantees that the data is written to the disk, and features the ACID properties of the database engine.

ACID is an acronym for "Atomicity Consistency Isolation Durability" properties, which guarantee that database transactions are processed reliably: for instance, in case of a power loss or hardware failure, the data will be saved on disk in a consistent way, with no potential loss of data.

You can overwrite this default behavior by setting the TSQLDataBase.Synchronous property to smOff instead of the default smFull setting. When Synchronous is set to smOff, SQLite continues without syncing as soon as it has handed data off to the operating system. If the application running SQLite crashes, the data will be safe, but the database might become corrupted if the operating system crashes or the computer loses power before that data has been written to the disk surface. On the other hand, some operations are as much as 50 or more times faster with this setting.

When the same tests are performed with Synchronous := smOff, "Write one" speed is enhanced from 8-9 rows per second into about 400 rows per second, on a physical hard drive (SSD or NAS drives may not suffer from this delay). We'll show below the detailed benchmark results.

So depending on your application requirements, you may switch Synchronous setting to off, to enhance server-side responsiveness.

Continue reading...

2012, Wednesday July 25

Synopse mORMot benchmark

After having tested and enhanced the external database speed (including BATCH mode), we are now able to benchmark all database engines available in mORMot.

In fact, the ORM part of our framework has several potential database backends, in addition to the default SQLite3 file-based engine.
Each engine may have its own purpose, according to the application expectation.

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

In these tables:

  • 'internal' means use of the internal SQLite3 engine;
  • 'external' stands for an external access via SynDB;
  • '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;
  • 'trans' stands for Transaction, i.e. when the write process is nested within BeginTransaction / Commit calls;
  • 'batch' mode will be described in this article;
  • 'read one' states that one object is read per call (ORM generates a SELECT * FROM table WHERE ID=?);
  • 'read all' is when all 5000 objects are read in a single call (i.e. running SELECT * FROM table);
  • ACID is an acronym for "Atomicity Consistency Isolation Durability" properties, which guarantee that database transactions are processed reliably: for instance, in case of a power loss or hardware failure, the data will be saved on disk in a consistent way, with no potential loss of data.
In short: depending on the database you can persist up to 150,000 objects per second, or retrieve  240,000 objects per second.
With a high-performance database like Oracle and our direct access classes, you write 53,000 and read 72,000 objects per second.
Difficult to find a faster ORM, I suspect. :)

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...

2012, Thursday July 12

One ORM to rule them all

If you discovered the mORMot framework, you may have found out that its implementation may sound restricted, in comparison to other ORMs, due to its design. It would be easy to answer that "it is not a bug, it is a feature", but I suspect it is worth a dedicated article.

Some common (and founded) criticisms are the following (quoting from our forum - see e.g. this question):
- "One of the things I don't like so much about your approach to the ORM is the mis-use of existing Delphi constructs like "index n" attribute for the maximum length of a string-property. Other ORMs solve this i.e. with official Class-attributes";
- "You have to inherit from TSQLRecord, and can't persist any plain class";
- "There is no way to easily map an existing complex database".

I understand very well those concerns.
Our mORMot framework is not meant to fit any purpose, but it is worth understanding why it has been implemented as such, and why it may be quite unique within the family of ORMs - which almost all are following the Hibernate way of doing.

Continue reading...

2012, Sunday June 24

Use TDataSet in mORMot or SynDB

In our documentation, and in all our code source, we avoid using the VCL DB.pas related units, and all the associated RAD components.

This is by design, since our experiment encouraged us to "think ORM, forget anything about RAD (and even SQL in most cases)" in mORMot.
And it introduced some nice border-side effect to Delphi users, e.g. that even a "Delphi Starter Edition" is able to use mORMot, have access to SQLite3, MS SQL or Oracle or any other DB, add interface-based RESTful JSON services over it, just for free...

But in the real world, you may need to upgrade some existing application, get rid of the BDE, or add a SOA layer over an existing business intelligence.
And mORMot is able to serve you well in those scenarios.
That's why we just added a first attempt to expose SynDB results and mORMOt TSQLTableJSON content into a TDataSet.

Continue reading...

2012, Wednesday April 25

The mORMot attitude

In a discussion with Henrick Hellström, in Embarcadero forums, I wrote some high-level information about mORMot.

It was clear to me that our little mORMot is now far away from a simple Client-Server solution.

The Henrick point was that with Real Thin Client (RTC), you are able to write any Client-Server solution, even a RESTful / JSON based one.

He is of course right, but it made clear to me all the work done in mORMot since its beginning.
From a Client-Server ORM, it is now a complete SOA framework, ready to serve Domain-Driven-Design solutions.

Continue reading...