mORMot Open Source

mrbar2000

Member

From: Brazil

Registered: 2016-10-26

Posts: 88

Possible Problem with RetrieveDocFromVariant

I execute have this function:

function TSelect.Retrieve: Variant;
begin
    Result := FRestOrm.Orm.RetrieveDocVariantArray(TAmostra, '',
      FWhere,
      TDocVariantData(FWhereParamValues).ToArrayOfConst,
      FCustomFieldsCsv)
...
end;

FWhere =  'inner join Solicitacao s on (s.RowID = Amostra.solicitacaoID)  where (amostra.empresaID = ?) and (s.numero = ?)'
FWhereParams = [1, '4']

On first call this RetrieveDocVariantArray make many selects on database. why? look the log
it show a where clause that i dont put: "where EmpresaID=?"

00000000102FB745 DB     mormot.db.sql.postgres.TSqlDBPostgresConnection(03575620) Connected to 191.101.78.31:5001 DB_SOROTRACK using...
000000001037BA78 DB     mormot.db.sql.postgres.TSqlDBPostgresStatement(0352bea0) Prepare t=1.85s c=00 q=select DataCriacao,UltimaAlteracao,EmpresaID,Numero,SolicitacaoID,Exames,DataEtiqueta,RackID,Linha,Coluna,Coletado,Cancelada,ID from public.Amostra where EmpresaID=?  <<<<< this not is my sql
00000000103BBE57 SQL    mormot.db.sql.postgres.TSqlDBPostgresStatement(0352bea0) Execute t=2.11s c=00 r=25 q=select DataCriacao,UltimaAlteracao,EmpresaID,Numero,SolicitacaoID,Exames,DataEtiqueta,RackID,Linha,Coluna,Coletado,Cancelada,ID from public.Amostra where EmpresaID=1 <<<< neither this
00000000103BD6F4 SQL    mormot.orm.sql.TOrmVirtualTableCursorExternal(03e3b1b0) Search select DataCriacao,UltimaAlteracao,EmpresaID,Numero,SolicitacaoID,Exames,DataEtiqueta,RackID,Linha,Coluna,Coletado,Cancelada,ID from public.Amostra where EmpresaID=?
000000001043C4A4 DB     mormot.db.sql.postgres.TSqlDBPostgresStatement(03529e20) Prepare t=516.47ms c=01 q=select DataCriacao,UltimaAlteracao,EmpresaID,Numero,DataSolicitacao,Paciente,PostoID,AtendenteID,ID from public.Solicitacao where Numero=? and ID=?
000000001047C973 SQL    mormot.db.sql.postgres.TSqlDBPostgresStatement(03529e20) Execute t=774.69ms c=01 r=0 q=select DataCriacao,UltimaAlteracao,EmpresaID,Numero,DataSolicitacao,Paciente,PostoID,AtendenteID,ID from public.Solicitacao where Numero='4' and ID=11
00000000104BBB97 SQL    mormot.db.sql.postgres.TSqlDBPostgresStatement(03529e20) Execute t=256.64ms c=01 r=0 q=select DataCriacao,UltimaAlteracao,EmpresaID,Numero,DataSolicitacao,Paciente,PostoID,AtendenteID,ID from public.Solicitacao where Numero='4' and ID=12
0000000014BCD30A SQL    mormot.orm.sql.TOrmVirtualTableCursorExternal(03e3b170) Search select DataCriacao,UltimaAlteracao,EmpresaID,Numero,DataSolicitacao,Paciente,PostoID,AtendenteID,ID from public.Solicitacao where Numero=? and ID=?
0000000015AC76CC SQL    mormot.db.sql.postgres.TSqlDBPostgresStatement(03529e20) Execute t=256.49ms c=01 r=1 q=select DataCriacao,UltimaAlteracao,EmpresaID,Numero,DataSolicitacao,Paciente,PostoID,AtendenteID,ID from public.Solicitacao where Numero='4' and ID=10
0000000015AC7F3C SQL    mormot.orm.sql.TOrmVirtualTableCursorExternal(03e3b170) Search select ...

and many other lines like above. with where clause that i dont send to retrieve.
then finally my return

0000000018F01559 res    mormot.db.raw.sqlite3.TSqlDatabase(035750a0) [{"rowid":1,"DataCriacao":"2024-10-10T14:21:43","UltimaAlteracao":"2024-10-24T10:04:18","EmpresaID":1,"Numero":"123","SolicitacaoID":10,"Exames":"he","DataEtiqueta":"2024-10-
...
10T18:20:00","RackID":1,"Linha":1,"Coluna":2,"Coletado":1,"Cancelada":0,"numerosolicitacao":"4","Paciente":"ana","DataSolicitacao":"2024-10-10T08:39:00"},
{"ID":9,"DataCriacao":"2024-10-10T14:21:43","Ultim... (truncated) length=7692

then I call my service again...and now it get from cache. showing that correct where  "where (amostra.empresaID = :(1):) and (s.numero = '4'):)"

0000000018F35FCB  -    99.053.647
0000000019A38E4D  +    mormot.rest.memserver.TRestServerFullMemory(0349b260).URI POST v1/amostraService/buscar in=232 B
0000000019C92853 SQL    mormot.db.raw.sqlite3.TSqlDatabase(035750a0) from cache  SELECT Amostra.RowID, Amostra.*, s.numero numerosolicitacao, s.paciente, s.datasolicitacao FROM Amostra  inner join Solicitacao s on (s.RowID = Amostra.solicitacaoID)  where (amostra.empresaID = :(1):) and (s.numero = :('4'):)
0000000019F70618 ret    Infra.Authentication.JWT.TRestServerURIContext_JWT(036e9b40) [{"ID":1,"DataCriacao":"2024-10-10T14:21:43","UltimaAlteracao":"2024-10-24T10:04:18","EmpresaID":1,"Numero":"123","SolicitacaoID":10,"Exames":"he","DataEtiqueta":"2024-10-10T18:20:00","RackID":1,"Linha":1,"Coluna":2,"Coletado":true,"Cancelada":false,"numerosolicitacao":"4","Paciente":"ana","DataSolicitacao":"2024-10-10T08:39:00"},{"ID":5,"DataCriacao":"2024-10-10T14:21:43","UltimaAlteracao":"2024-10-24T10:06:58","EmpresaID":1,"Numero":"125","SolicitacaoID":10,"Exames":"ad","DataEtiqueta":"2024-10-10T18:20:33","RackID":1,"Linha":1,"Coluna":1,"Coletado":true,"Cancelada":false,"numerosolicitacao":"4","Paciente":"ana","DataSolicitacao":"2024-10-10T08:39:00"},...
{"ID":8,"DataCriacao":"2024-10-10T14:21:43","UltimaAlteracao":"2024-10-31T08:18:58","EmpresaID":1,"Numero":"128","SolicitacaoID":10,"Exames":"ijiji","DataEtiqueta":"2024-10-10T18:20:00","RackID":0,"Linha":0,"Coluna":0,"Coletado":true,"Cancelada":false,"numerosolicitacao":"4","Paciente":"ana","DataSolicitacao":"2024-10-10T08:39:00"},{"ID":9,"DataCriacao":"2024-10-10T14:21:43","Ultim... (truncated) length=7692
0000000019F71039  -    05.472.748

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: Possible Problem with RetrieveDocFromVariant

Please follow the forum rules and use external links -like gists- to post such content.

This is how virtual tables work. They are likely to make sub-optimal queries.
For such queries, the best way is to call directly PostgreSQL, and not use the ORM.
As documented.

mrbar2000

Member

From: Brazil

Registered: 2016-10-26

Posts: 88

Re: Possible Problem with RetrieveDocFromVariant

Good morning Arnold, I have read and re-read the documentation several times but as there are several ways to connect to the database, several ways to extract the data and also to store it, this leaves any beginner in the framework very confused. Could you tell me which path I should follow to achieve this?

1) I have complex master detail queries and
I need to deoil this in the json format with objects and subobjects. I was trying to use RetrieveDocVariantArray but this method does not meet the criteria passed to prepare and execute the query.

2) I need to use REST/SOA and work with DTO. When the dto arrives I need to transform it into TORM to save it in the database (or fill automaticaly params of some sql inser/delete/update). If I access the database directly instead of using orm, I would have a huge job of assembling each insert/update/delete, things that orm already does. Furthermore, there is the issue of batch update and transactions.

3) if it is possible to mix ORM with direct access to the bank, what precautions should I take when doing this.
Please, I need more detailed answers. even if it's notes to documentation locations or test routines or examples where I will find a solution to these doubts.

Sorry Arnold if I'm bothering you. I see mormot as a viable solution for my system and I don't want to stop using it because I don't know how to use it properly. what is the best way to work. I use a SQL database and do not intend to work with NoSQL databases. I think this is the big reality for most Delphi/Lazarus programmers.

I can also share my code with you to tell me how I should proceed in order to obtain the best possible performance when using mormot to meet my needs.

Last edited by mrbar2000 (2024-11-02 14:55:39)