Search - syndb - Synopse Open Source

2012-07-24

SQLite3-powered, not SQLite3-limited

By AB4327-GANDI, 2012-07-24. Open Source › mORMot Framework

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, ZDBC, TDataSet, (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)

2012-07-19

Oracle Array Binding and BATCH performance

By AB4327-GANDI, 2012-07-19. Open Source › mORMot Framework

A common issue with Client-Server databases is the latency introduced for each query.

For example, suppose you have a requirement to first collect some information from your application’s users and then insert that information into a table in Oracle Database.

The first obvious option is to insert these multiple rows into the table through a loop in your program. This loop iterates over the data to be inserted and does what is known as a single-row insert , because the application sends one single row of data to the database at a time. Due to the network latency (typically around 1 ms over a corporate network), it would achieve not more than 500-600 requests per second to let the work done, since for each INSERT, a so-called round-trip occurs: a message is sent to Oracle, then a response is sent back to the client.

You have another option for inserting multiple rows of data into the table that reduces the number of round-trips and improves application performance, database performance, and network resource use. Rather than having the application send a single row of data to the database at a time, it can use array binding to send the data in batches of rows. Therefore, you reduce a lot the number of round-trips to be processed, and enhance performance by a factor of about 100.

Our SynDB unit has been enhanced to introduce new TSQLDBStatement.BindArray() methods, introducing array binding for faster database batch modifications (only implemented in SynDBOracle by now - but MS SQL has a similar feature called OleDB bulk insert).
It is available from the ORM side or mORMot, when working with external tables, in BATCH mode.

Thanks to this enhancement, inserting records within Oracle comes from 400-500 rows per second to more than 50000 rows per second!
It was also a good opportunity to speed up the BATCH process globally, and to benchmark our Oracle back-end against existing external databases, i.e. SQLite3 (as file or in-memory), and Jet / MS Access / .mdb engine.

Note that this article scope is only about virtual tables linked to external databases (i.e. TSQLRecordExternal). Plain TSQLRecord classes will access directly to the SQLite3 engine or in-memory TList, so speed will be even higher than the below values.

Featuring benchmark source code and nice performance charts.

2012-06-24

Use TDataSet in mORMot or SynDB

By AB4327-GANDI, 2012-06-24. Open Source › Open Source libraries

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.

2012-06-23

SynDBExplorer enhancements

By AB4327-GANDI, 2012-06-23. Open Source › Open Source libraries

Our SynDBExplorer free tool has been enhanced. A SQL request history has been added to the software. It is now able to handle directly Jet / MSAccess .mdb files. It has also several fixes included (including Oracle direct link), and the internal SQLite3 engine has been updated to its latest […]

2012-05-28

Synopse mORMot Framework 1.16

By AB4327-GANDI, 2012-05-28. Open Source › mORMot Framework

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

The main new features are the following:

Interface-based services, i.e. comparable to WCF, but with Delphi strengths;
ORM cache which purpose is to enhance server scaling and client responsiveness;
Automatic JOIN query to unleash the underneath DB power;
SQLite3 engine updated to latest revision 3.7.12.1;
Major update of the associated documentation (now more than 800 pages);
A lot of bug fixes and enhancements, mainly from users requests - thanks you all for your feedback, patches and ideas!

Thanks to its features, mORMot is now able to provide a stand-alone Domain-Driven Design framework for Delphi.

Quite a long and nice road for a little mORMot, and more to come!

2012-01-17

SynDBExplorer fast direct export

By AB4327-GANDI, 2012-01-17. Open Source › Open Source libraries

The Open Source SynDBExplorer tool has been enhanced these days.

Main new features are:

Execution of the current selected text (if any) instead of the whole memo content;
"Exec & Export" new button, for direct export to file.

I really like the selection execution feature - this speed up SQL process a lot, and allow to switch from one statement to another.
And the new exporting features are opening new possibilities.

2011-11-27

SOLID design principles

By AB4327-GANDI, 2011-11-27. Pascal Programming

Delphi is sometimes assimilated to a RAD product - and this is a marketing label - but IMHO Delphi is much more than RAD.
With Delphi, you can make very serious and clean programming.

Including SOLID style of coding.

The acronym SOLID is derived from the following OOP principles (quoted from the corresponding Wikipedia article):

Single responsibility principle: the notion that an object should have only a single responsibility;
Open/closed principle: the notion that “software entities ... should be open for extension, but closed for modification”;
Liskov substitution principle: the notion that “objects in a program should be replaceable with instances of their subtypes without altering the correctness of that program” - also named as "design by contract";
Interface segregation principle: the notion that “many client specific interfaces are better than one general purpose interface.”;
Dependency inversion principle: the notion that one should “Depend upon Abstractions. Do not depend upon concretions.”. Dependency injection is one method of following this principle.

If you have some programming skills, those principles are general statements you may already found out by yourself. If you start doing serious object-oriented coding, those principles are best-practice guidelines you would gain following.

They certainly help to fight the three main code weaknesses:

Rigidity – Hard to change something because every change affects too many other parts of the system;
Fragility – When you make a change, unexpected parts of the system break;
Immobility – Hard to reuse in another application because it cannot be disentangled from the current application.

2011-11-23

Does speed matters?

By AB4327-GANDI, 2011-11-23. Pascal Programming

Luigi Sandon wrote on Embarcadero's forum:

And then you ask yourself: "why use a native compiler if its code may be even slower than jitted one?". Hope the new developers will also develop better and faster code - and not viceversa.

Embarcadero is just following the Wirth's law slower than others:

"Software is getting slower more rapidly than hardware becomes faster"

Speed is only a matter of compiler for mathematical computing intensive tasks.
Most of the time, in real apps (like business apps), the main speed issue is more the framework size (and the number of dll invoked), memory consumption, and general design (e.g. how caching and SQL are written).

Delphi, Java or .Net can do slow apps.
Delphi, Java or .Net can do fast apps.

You can do small and fast stand-alone apps with Delphi, running from Windows 2000 to Windows 8.
It is not possible with Java nor .Net.

This is the main difference IMHO with native code and JIT - about memory use, ease of distribution and no need of an external runtime framework.

2011-09-25

Synopse SQLite3/mORMot framework 1.15

By AB4327-GANDI, 2011-09-25. Open Source › mORMot Framework

Our Client-Server ORM framework is now available in revision 1.15.

This is a major upgrade of the framework:

It is now called mORMot - so please update your T-Shirts or coffee cups
It is able to use any Database engine back-end - in fact, it is SQLite3 powered, not SQLite3 limited;
In particular, direct OleDB and native Oracle have been implemented;
It makes use of the genuine SQlite3 Virtual Table mechanism everywhere to allow mixed access to any database engine;
New TModTime / TCreateTime kind of fields;
Enhanced stability, speed and multi-thread implementation;
Methods and functions have been enhanced, according to user feedback (thanks you all for your interest and forum posts!);
Extended documentation (more than 700 pdf pages), with new diagrams and a lot of new content;
New associated tools, like LogViewer or SynDBExplorer;
The SQLite3 core can now be used without our ORM - it has been updated to the latest 3.7.8 version;
Open Source (under GPL/LGPL/MPL license), running from Delphi 6 up to XE2.

2011-07-22

SynDBSQLite3: SQLite3 direct access

By AB4327-GANDI, 2011-07-22. Open Source › Open Source libraries

For our ORM framework, we implemented an efficient SQLite3 wrapper, joining statically (i.e. without any external dll) the SQLite3 engine to the executable. SQLite3 is in fact used as the DB kernel of the framework. For instance, thanks to its unique virtual table mechanism, even tables in other databases (like Oracle or MSSQL) are available as if they were SQLite3 tables.

We just made this wrapper independent from our ORM, in a new dedicated unit, named SynSQLite3.pas.

It was an easy task to let this unit be called from our SynDB database abstract classes.