mORMot Open Source

AntonE

Member

Registered: 2012-02-03

Posts: 74

Fastest DB 'CAS' in ORM or direct SQL

Hi all,
still finding my feet in mORMot...

I need to do a type of CAS in the DB.
I'm recording traffic that is like this:

type
  TSQLTrafficItem=class(TSQLRecord)
  private
...
  published
    property Router   : Integer    read FRouter  write FRouter;
    property Year     : Word       read FYear    write FYear;
    property Month    : Word       read FMonth   write FMonth;
    property Day      : Word       read FDay     write FDay;
    property Hour     : Word       read FHour    write FHour;
    property Minute   : Word       read FMinute  write FMinute;
    property FIP      : RawUTF8    read FFIP     write FFIP;
    property TIP      : RawUTF8    read FTIP     write FTIP;
    property FMAC     : RawUTF8    read FFMAC    write FFMAC;
    property TMAC     : RawUTF8    read FTMAC    write FTMAC;
    property Bytes    : Int64      read FBytes   write FBytes;
    property Packets  : Int64      read FPackets write FPackets;
    property FUN      : RawUTF8    read FFUN     write FFUN;
    property TUN      : RawUTF8    read FTUN     write FTUN;
  end;
  DB.CreateSQLMultiIndex(TSQLTrafficItem,['Router','Year','Month','Day','Hour','Minute','FIP','TIP'],True);
  DB.CreateSQLIndex     (TSQLTrafficItem,'FMAC',False);
  DB.CreateSQLIndex     (TSQLTrafficItem,'TMAC',False);

Traffic is grouped by variable time increment, e.g. 5 or 15 minutes, so when receiving (logging) new traffic, I have to :
1)Check if such record/row exist (unique key : 'Router','Year','Month','Day','Hour','Minute','FIP','TIP')
2a)If it does, add new traffic to it (Bytes+Packets)
2b)If it does not, create new record.
So for each new item, I have to do at least 2 SQL statements.

ATM I'm doing like this:

   TI:=TSQLTrafficItem.FillPrepare(ServerForm.DB,'ROUTER = ? AND YEAR = ? AND MONTH = ? AND DAY = ? AND HOUR = ? AND MINUTE = ? AND FIP = ? AND TIP = ?',[],[RouterID,Y,M,D,H,N,IPF,IPT]);
   if TI.FillOne
      then begin
            if (TI.FMAC='')and (MACF<>'')
               then TI.FMAC:=MACF;
            if (TI.TMAC='')and (MACT<>'')
               then TI.TMAC:=MACT;
            TI.Bytes  :=TI.Bytes  +IPB;
            TI.Packets:=TI.Packets+IPP;
            ServerForm.DB.Update(TI);
           end
      else begin
            TI.Router   :=RouterID;
            TI.Year     :=Y;
            TI.Month    :=M;
            TI.Day      :=D;
            TI.Hour     :=H;
            TI.Minute   :=N;
            TI.FIP      :=IPF;
            TI.TIP      :=IPT;
            TI.FMAC     :=MACF;
            TI.TMAC     :=MACT;
            TI.Bytes    :=IPB;
            TI.Packets  :=IPP;
            TI.FUN      :=UNF;
            TI.TUN      :=UNT;
            ServerForm.DB.Add(TI,True);
           end;
   TI.Free;

But I need to get it as fast/efficient as possible. Would it be significantly faster direct to SQL?
I tried to have a look at direct SQL in documentation and other threads, but could not figure out how to get a connection.

Would something like this be possible/optimal where I get 3 statements beforehand?

  SQLF := dbConn.NewStatementPrepared('select * from TrafficItem WHERE ROUTER = ? AND YEAR = ? AND MONTH = ? AND DAY = ? AND HOUR = ? AND MINUTE = ? AND FIP = ? AND TIP = ?',false, true);
  SQLI := dbConn.NewStatementPrepared('insert into TrafficItem (ROUTER,YEAR,MONTH,DAY,HOUR,MINUTE,FIP,TIP,FMAC,TMAC,BYTES,PACKETS,FUN,TUN) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)',false, true);
  SQLU := dbConn.NewStatementPrepared('update TrafficItem (FMAC,TMAC,BYTES,PACKETS,FUN,TUN) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?) WHERE (ROUTER = ? AND YEAR = ? AND MONTH = ? AND DAY = ? AND HOUR = ? AND MINUTE = ? AND FIP = ? AND TIP = ?',false, true);

If so, how do I get/make a direct SQL connection? Can't figure where to get a DB connection from TSQLRestServerDB to call "NewStatementPrepared".

These statements run in a loop, sometimes recording >8000 new items at a time, minutes or even seconds apart. Do I start/stop a transaction before/after loop? It's safe from data integrity point of view but will it block DB during that time?
This 'table' (TSQLTrafficItem) is only updated from 1 thread in the server code itself.

Thanks
Regards
AntonE

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,238

Website

Re: Fastest DB 'CAS' in ORM or direct SQL

Some points:

1. You can add the fields to retrieve in the FillPrepare() method, i.e. here it seems that you need only 'FMAC,TMAC,Bytes,Packets'.
It will save CPU and memory.
Why not just use a Retrieve()? Sounds easier to check for ID=0 for adding.

2. If you are running the process only for one RouterID,Y,M,D,H,N,IPF,IPT combination at a time, a transaction won't make much benefit.
But if you can, try to combine several requests in one list, then compute several Add/Update in the same loop: in this case, ensure you have a transaction around the whole process, and run it on TSQLRestServer side.

3. The SQL Statements generated by the ORM for CRUD operations like Update() or Add() are already pre-computed and cached.
So there won't be any benefit of writing your own individual SQL statements.
It could be even slower to use direct SQL, since our ORM is able to use more advanced techniques like multi-insertion or multi-update.

4. The fastest may be to write one or two dedicated SQL Statement to INSERT all new values or UPDATE all existing values at once.
UPDATE ... FROM  ... WHERE....

5. It is better to stick to CRUD ORM methods, and not write SQL, if it is fast enough for you.
It would help you switch to a NoSQL database, if needed.

6. If you write individual Add/Insert, consider using BatchAdd/BatchInsert which are faster.
BATCH insert/updates are pretty fast with a local SQLite3 database - see numbers in http://blog.synopse.info/post/2014/03/0 … PostgreSQL

7. Remember that is you use SQLite3, the DB engine is in-process, so your statements are directly run in-process.
In fact, TSQLRestServer CRUD methods have exactly the same benefit of stored procedures, without the need to write SQL or PL/SQL, for a given DB.

AntonE

Member

Registered: 2012-02-03

Posts: 74

Re: Fastest DB 'CAS' in ORM or direct SQL

Thanks and WOW.
I just added transaction and kept 1 TSQLRecord instance alive during long loop.
Now it is very very fast compared to my Firebird version.
The code is +- 50% less, exe is much smaller, faster...
And this is from simple, readable pure Delphi ORM code!
What can I say? Extremely impressed...

AntonE

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,238

Website

Re: Fastest DB 'CAS' in ORM or direct SQL

Thanks for the nice feedback!

Happy to help.

AntonE

Member

Registered: 2012-02-03

Posts: 74

Re: Fastest DB 'CAS' in ORM or direct SQL

One more question relating:
How do I get an aggregate query, like I would in SQL e.g.:

select YEAR,MONTH,SUM(BYTES),SUM(PACKETS)
FROM TrafficItem
WHERE FMAC='11:22:33:44:55'
GROUP BY YEAR,MONTH

Or must I just query all records and make sum manually in-memory?

Thanks and regards
AntonE

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,238

Website

Re: Fastest DB 'CAS' in ORM or direct SQL

This is where SQL would be preferred.

You can use TSQLRest.ExecuteList() method to run arbitrary SQL SELECT commands.
For security reasons, it will work on the server side, but may be disabled from clients.

Ensure you inline the parameters with :(...): as stated by the SAD pdf.
Use FormatUTF8() with bound parameters.