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);
begin
... 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)) );
end;
function TdmDatabasePerson.Search(const AFilter: String): TList<TPerson>;
var
Criteria: TCriteria<TPerson>;
begin
Criteria := TXDataOperationContext.Current.GetManager.Find<TPerson>;
makeFilter( Criteria, AFilter);
Criteria.OrderBy( 'Nachname');
Criteria.OrderBy( 'Vorname');
Result := Criteria.List;
end;
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
SQL-Profiler-Aurelius-PersonList.zip
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?
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.
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.
