Speed problem with DB Closure Table (small data)

BIZ TMS XData
1

Hello Wagner,

I have the problem that a REST call takes 43 seconds.
I try to explain it.

I have 2 tables. It is a closure table structure that is supposed to represent a tree.

Screenshot wg-table
Screenshot wg-table469×568 54.9 KB

"Warengruppe" has 25 entries and "WarengruppeCT" has 44 entries.
The tree has 6 entries on level 0.
On level 1 are 2 to 7 entries. No more levels.
The two relationships are lazy.

The Aurelius DB call only takes a few milliseconds.
The entire call takes 43 seconds.
( I have tested with Postman)

2024-07-04 10:43:30.935 ×    20420 × sngWare × Enter: WarengruppeList_v1 {"Function":"","Version":"801","Data":{}}
2024-07-04 10:43:30.951 ×    20420 × sngWare × Leave: WarengruppeList_v1 ResultCount=6
2024-07-04 10:44:14.334 ×    20420 × sngWare × POST /sopha/w/func/WarengruppeList 200 - 43410,21 ms

The service interface:

  IWareFunc = interface(IInvokable)
    ['{6DEEED43-7611-4E90-A57C-A4FBE7C26CE4}']

    function WarengruppeList             ( const Param: TJSONObject) : TList<TWarengruppe>;     // Para: WarengruppeID oder Filter

The service implementation (part 1, only the call to an other unit (###)):

function TWareFunc.WarengruppeList( const Param: TJSONObject): TList<TWarengruppe>;
var
  LObjManager : TObjectManager;
  LsngPara    : TsngParameter;
  LJson       : TJSONObject;
begin
  Result := nil;
  LsngPara := nil;
  if TsngParameter.CheckCall( Param, local_location_prefix, cIWare_WarengruppeList,
                              LJson, LsngPara,
                              vcWare_OldestVersion, vcWare_CurrentVersion) then begin

    LObjManager := TXDataOperationContext.Current.GetManager;

    if (LsngPara.VersionCode > 0) and (LsngPara.VersionCode <= vcWare_v1) then
      Result := (###) WarengruppeList( LObjManager, LsngPara); (###)
  end;

  if LJson <> nil then
    LJson.Free;
  if LsngPara <> nil then
    LsngPara.Free;
end;

The service implementation (part 2)):

class function TWareFuncHelper.WarengruppeList( const ObjManager: TObjectManager; const AsngParam: TsngParameter): TList<TWarengruppe>;
var
  LWarengruppeID: TGUID;
begin
  local_func_name := cIWare_WarengruppeList+'_v1';
  DoLog( loaTraceAll, 'Enter: '+local_func_name+' '+AsngParam.toJSONString);

  try
    LWarengruppeID := DirtyStringToGuid( AsngParam.JSONData.Values[ DicWare.Warengruppe.WarengruppeID.PropName].AsType<String>);
  except
    LWarengruppeID := TGUID.Empty;
  end;

  if LWarengruppeID.IsEmpty then
    Result := ObjManager.Find<TWarengruppe>
                        .Where( Linq.Sql('wg_id in (select wgct_child from t_w_warengruppect '+
                                                   'group by wgct_child having count(wgct_child)=1)'))
                        .List
  else
    Result := ObjManager.Find<TWarengruppe>
                        .Where( Linq.Sql('wg_id in (select wgct_child from t_w_warengruppect '+
                                                   'where wgct_parent='''+GetGuidString( LWarengruppeID, guid36)+''' and wgct_deep=1)'))
                        .List;

  DoLog( loaTraceAll, 'Leave: '+local_func_name+' ResultCount='+Result.Count.toString);
end;

Parameter ID is empty.

I get this result:

{
    "value": [
        {
            "$id": 1,
            "WarengruppeID": "3201466D-BF3B-484D-BAB1-18221DFB190E",
            "Bezeichnung": "Edelmetall",
            "Kuerzel": "",
            "Nummer": "0",
            "WareList@xdata.proxy": "Warengruppe(3201466D-BF3B-484D-BAB1-18221DFB190E)/WareList",
            "Ware@xdata.ref": "Ware(1C50385C-BF5F-4BC7-96C5-B38A44FFF836)"
        },
        {
            "$id": 2,
            "WarengruppeID": "FCE7CF27-D911-4F48-9F80-1A0DFFCEE2C9",
            "Bezeichnung": "Uhren",
            "Kuerzel": "",
            "Nummer": "0",
            "WareList@xdata.proxy": "Warengruppe(FCE7CF27-D911-4F48-9F80-1A0DFFCEE2C9)/WareList",
            "Ware": null
        },
        {
            "$id": 3,
            "WarengruppeID": "CCF593A6-D497-4D58-948E-648327D0FFDE",
            "Bezeichnung": "Technik",
            "Kuerzel": "",
            "Nummer": "0",
            "WareList@xdata.proxy": "Warengruppe(CCF593A6-D497-4D58-948E-648327D0FFDE)/WareList",
            "Ware": null
        },
        {
            "$id": 4,
            "WarengruppeID": "09FF8220-85FA-40B0-9391-683AA35DB91B",
            "Bezeichnung": "Fahrzeuge",
            "Kuerzel": "",
            "Nummer": "0",
            "WareList@xdata.proxy": "Warengruppe(09FF8220-85FA-40B0-9391-683AA35DB91B)/WareList",
            "Ware": null
        },
        {
            "$id": 5,
            "WarengruppeID": "865C6A90-8C1F-44E3-9860-D744BFCFBA7B",
            "Bezeichnung": "Schmuck",
            "Kuerzel": "",
            "Nummer": "0",
            "WareList@xdata.proxy": "Warengruppe(865C6A90-8C1F-44E3-9860-D744BFCFBA7B)/WareList",
            "Ware": null
        },
        {
            "$id": 6,
            "WarengruppeID": "C6572296-4CE9-4AA3-887C-F9DA0A796680",
            "Bezeichnung": "Sonstiges",
            "Kuerzel": "",
            "Nummer": "0",
            "WareList@xdata.proxy": "Warengruppe(C6572296-4CE9-4AA3-887C-F9DA0A796680)/WareList",
            "Ware@xdata.ref": "Ware(00000001-0000-0000-0000-000000673739)"
        }
    ]
}

Where is the problem?

Thomas

2

Here are the entities

  [Entity]
  [Table('t_w_warengruppe')]
  {$I '..\sngModellistWare.inc'}
  [Id('FWarengruppeID', TIdGenerator.None)]
  TWarengruppe = class(TsngEntity)
  private
    [Column('wg_id', [TColumnProp.Required])]
    FWarengruppeID: TGuid;
    
    [Column('wg_bezeichnung', [TColumnProp.Required], 200)]
    FBezeichnung: string;
    
    [Column('wg_kuerzel', [], 40)]
    FKuerzel: Nullable<string>;
    
    [Column('wg_nummer', [], 40)]
    FNummer: Nullable<string>;
    
    [ManyValuedAssociation([TAssociationProp.Lazy, TAssociationProp.Required], [TCascadeType.SaveUpdate, TCascadeType.Merge], 'FWarengruppe')]
    FWareList: Proxy<TList<TWare>>;
    function GetWareList: TList<TWare>;
    function GetWare: TWare;
    procedure SetWare(Value: TWare);
  public
    constructor Create;
    destructor Destroy; override;
    class function WareDic: String;
    class function Entityname: String;
    property WarengruppeID: TGuid read FWarengruppeID write FWarengruppeID;
    property Bezeichnung: string read FBezeichnung write FBezeichnung;
    property Kuerzel: Nullable<string> read FKuerzel write FKuerzel;
    property Nummer: Nullable<string> read FNummer write FNummer;
    property WareList: TList<TWare> read GetWareList;
    
    [XDataProperty]
    property Ware: TWare read GetWare write SetWare;    
  end;
  
  [Entity]
  [Table('t_w_warengruppect')]
  {$I '..\sngModellistWare.inc'}
  [Id('FWarengruppeCTID', TIdGenerator.None)]
  TWarengruppeCT = class(TsngEntity)
  private
    [Column('wgct_id', [TColumnProp.Required])]
    FWarengruppeCTID: TGuid;
    
    [Column('wgct_deep', [TColumnProp.Required])]
    FDeep: Integer;
    
    [Association([TAssociationProp.Lazy, TAssociationProp.Required], CascadeTypeAll - [TCascadeType.Remove])]
    [JoinColumn('wgct_parent', [TColumnProp.Required], 'wg_id')]
    FParent: Proxy<TWarengruppe>;
    
    [Association([TAssociationProp.Lazy, TAssociationProp.Required], CascadeTypeAll - [TCascadeType.Remove])]
    [JoinColumn('wgct_child', [TColumnProp.Required], 'wg_id')]
    FChild: Proxy<TWarengruppe>;
    function GetParent: TWarengruppe;
    procedure SetParent(const Value: TWarengruppe);
    function GetChild: TWarengruppe;
    procedure SetChild(const Value: TWarengruppe);
  public
    class function Entityname: String;
    property WarengruppeCTID: TGuid read FWarengruppeCTID write FWarengruppeCTID;
    property Deep: Integer read FDeep write FDeep;
    property Parent: TWarengruppe read GetParent write SetParent;
    property Child: TWarengruppe read GetChild write SetChild;   
  end;

function TWarengruppe.GetWareList: TList<TWare>;
begin
  result := FWareList.Value;
end;

function TWarengruppe.GetWare: TWare;
begin
  if WareList.Count > 0 then Result := WareList[0] else Result := nil;
end;

procedure TWarengruppe.SetWare(Value: TWare);
begin
  { Dummy }
end;
3

Unfortunately, I can't tell. You would need to dig deeper and check with more details which exact operations are taking time, which lines of code, etc. Maybe with a profiler.

If you are able to create a minimal project reproducing the issue that can be easily compiled and run at our side here, I can try to take a look for you.

4

Sorry for the late reply.

This function (a lazy link to another table) was to blame.

function TWarengruppe.GetWare: TWare;
begin
  if WareList.Count > 0 then Result := WareList[0] else Result := nil;
end;

    [ManyValuedAssociation([TAssociationProp.Lazy, TAssociationProp.Required], [TCascadeType.SaveUpdate, TCascadeType.Merge], 'FWarengruppe')]
    FWareList: Proxy<TList<TWare>>;
//    function GetWare: TWare;
//    procedure SetWare(Value: TWare);

//    [XDataProperty]
//    property Ware: TWare read GetWare write SetWare;

I only need this data sometimes.

Is there an elegant solution that I can use to specify whether I want this data transferred?

5

After commenting it out, it runs quickly.

6

This is the problem:

    [XDataProperty]
    property Ware: TWare read GetWare write SetWare;

You explicitly specified the property to be serialize, since the reader of the property always load the list, it will execute an SQL for each object to retrieve the list for each of them.

Why did you add such attribute in the first place? If you remove it, it will work faster.