Tag - Oracle - Synopse Open Source

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-07-12

One ORM to rule them all

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

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.

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-04-25

The mORMot attitude

By AB4327-GANDI, 2012-04-25. Open Source › mORMot Framework

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.

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-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-08-10

Framework documentation updated for revision 1.15

By AB4327-GANDI, 2011-08-10. Open Source › mORMot Framework

The framework documentation was just updated.

The general organization of the SAD document (which is the one to be read in all cases) has been refreshed, and is now separated in smaller chapters.

The new official name has been changed into "Synopse SQLite3/mORMot framework"...

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.