Slow Query in Aurelius. Where can I optimize?

I have a simple query for use in person search.

I have one edit field and the user can write some letters to search.

It is a XData Function Call : function Search( AFilter: String): TList;

Example: I search "dem"

The SQL-Code is:

select * 
  from t_p_person p, 
       t_p_adresse a, 
       t_p_email e, 
       t_p_telefon t
where p.p_id = a.p_id 
  and p.p_id = e.p_id 
  and p.p_id = t.p_id
  and ( (p.p_nachname like '%dem%') or 
        (p.p_vorname like '%dem%') or 
        (p.p_nummer like '%dem%')
order by p_nachname, p_vorname

My SQL-Server needs 422 ms
Result has 153 datasets
person table has 40.000 datasets

I have 4 tables.
t_p_person with p_id as primary key
t_p_adresse, t_p_email, t_p_telefon
each table 1:n and as eager configured

procedure TdmDatabasePerson.makeFilter(var Criteria: TCriteria<TPerson>; const AFilter: string);
  ... some code deleted. In this case only one criteria is added

  Criteria.Add( (Linq.Contains( 'Nummer', AFilter)) or
    (Linq.Contains( 'Nachname', AFilter)) or
    (Linq.Contains( 'Vorname', AFilter)) );

function TdmDatabasePerson.Search(const AFilter: String): TList<TPerson>;
  Criteria: TCriteria<TPerson>;
  Criteria := TXDataOperationContext.Current.GetManager.Find<TPerson>;
  makeFilter( Criteria, AFilter);  
  Criteria.OrderBy( 'Nachname');
  Criteria.OrderBy( 'Vorname');
  Result := Criteria.List;

Aurelius needs 4352 ms

This is factor 10

Where can I optimize?

Can you please post the mapping of the four classes involved? Can you check the SQL statements executed by Aurelius and post how it looks like here?

Ok, here are the entities and a Microsoft SQL-Profiler Trace File as .trc and as .xml

In the trace is first the call: SearchCount(const AFilter: string): integer;
and after: Search(const AFilter: String): TList;

My programm looks for the count and if it is under 300 then it gets the data.

The Files:
Person.dbEntities.pas // the entities
sng.dbEntities.pas // the entities
Person.dmDatabase.pas // the search-call
the file includes:
SQL-Profiler-Aurelius-PersonList.trc // profiler trace
SQL-Profiler-Aurelius-PersonList.xml // profiler trace as xml

I think, there are too many calls and I must optimize. But where can I do it?

Many thanks

P.S. A little additional question. The sng.entities unit is included in all programs. Can I optimize the "[Model ('xxx')]" definition there?
Person.dmDatabase.pas (6.0 KB) Person.dbEntities.pas (31.9 KB) sng.dbEntities.pas (5.2 KB) (210.5 KB)

The logging with times

Your lists (many-valued association) are all configured as eager-loading. Don't do that. Make them always lazy-loading. Lists are always retrieved with an additional SQL for each record, no matter what, Aurelius can't retrieve all lists for all records in a single statement.

I have some joins as eager and some lazy.
For example: I need the person list with addresses. the addresses are in another table, because some people have 2 addresses.
The user search in person names and in addresses.
I must read the address table for every person.
I think,lazy is wrong for this.

I use many joins and this very often in our data model.
I will try a new way with views, because I have many tables that are joined with 3 or 4 other tables that I need in query arguments, if there is no other way.

Hi ... Lazy is not wrong for this at all.
Even if your criteria uses joined tables columns, you doesn't need to fetch them with eager
Lazy will generate SQL for fetching these rows only when the ManyValued object list is explicitly referenced, and it is good for most situations.
Aurelius first will generate SQL for processing your criteria conditions.
After that, it'll generate SQL for fetching needed data from joined tables, by each main row. If you use eager, it'll affect your response time when searching and maybe you still don't need all this related data.

Maybe you should capture the SQL generated by Aurelius with a ModelEvents object, and see the difference when using Lazy / Eager, and also check if there are DB indexes according to the way it is generated.

1 Like

This is valid for many-valued associations, i.e., when the "needed data from joined tables" are a list - for example, you get an invoice, and then need the invoice details. If you get two invoices, then Aurelius will perform two additional SQL statements to retrieve the invoice details for invoice 1 and invoice 2. In this case, always use lazy-loading. That's why for lists (many-valued association), there is virtually no reason to keep them eager.

On the other hard, for single-valued associations, then Aurelius can use lazy/eager accordingly. For example, the customer of each invoice. For invoice 1, there is customer A. For invoice 2, there is customer B. In this case, if eager, Aurelius will perform a single SQL statement and retrieve all data at once.

Yes, that was about many valued, as all Thomas' eager loading are those many-value associations that retrieve addresses from other tables.
I have eager only on some few child entities associations that I know I'll always use the joined parent table data, otherwise, I've been using just Lazy.
Most of what I've done till now using Aurelius depends on user interactions, and they not always drill down to deeper details, so this as-needed approach have been working very fine for me.

1 Like

Thank you for this info. I will test it today.

1 Like

Thank you, we can close here.
It is faster with lazy definition.
To optimize, I will use (in my big datapool ;-) for some querys independent views.

1 Like

This topic was automatically closed 60 minutes after the last reply. New replies are no longer allowed.