multiple, not optimized queries

BIZ TMS Aurelius
1

I have created 3 classes (songs, singers, albums)
The albums class has an one-to-many association to singers and a many-to-many association to songs.
In Talbum it exists:

  [Id('Falbumid', TIdGenerator.None)]
  Talbums = class
  private
    [Column('albumid', [TColumnProp.Required])]
    Falbumid: Int64;
    [Association([], CascadeTypeAllButRemove)]
    [JoinColumn('singerid', [])]
    Fsinger: Tsinger;
.....
    [ManyValuedAssociation([TAssociationProp.Required], [TCascadeType.SaveUpdate, TCascadeType.Merge],'falbum')]
    Fsongs:TList<Tsongs>;

In Tsongs it exists:

  [Entity]
  [Table('mysongs')]
  [Id('Falbum', TIdGenerator.None)]
  [Id('Faa', TIdGenerator.None)]
  Tsongs = class
  private
    [Association([TAssociationProp.Required], CascadeTypeAll - [TCascadeType.Remove])]
    [JoinColumn('Falbumid', [TColumnProp.Required], 'Falbumid')]
    Falbum: Talbum;
    [Column('aa', [TColumnProp.Required])]
    Faa: Integer;

When I do a query like:

obj:=Context.GetManager; 
result:=obj.Find<Talbums>
.CreateAlias('Tsongs','songs')
.CreateAlias('Tsingers','singers')
.Where( Linq.GreaterThan('date',a) and Linq.LessThan('date',b) and
       (linq['songs.time']<d)).List;

I can see in the logs that Aurelius does first a query with the 3 tables joined with the "where" expressions.
After that a big number of queries are issued for each song, with the the 3 tables joined for each index value of the table for Tsongs.
To my knowledge only the first query is needed, but in the first query, the table fields for Tsongs are not retrieved.
How to optimize that? Thank you in advance

2

From what I read in the documentation this ManyValuedAssociation is always lazy. So, the "Required" is not taken in account

Lazy: The associated list is not loaded together with the current object. Lazy-
Loading is used. In a SELECT operation, Aurelius will only retrieve the Id of the
parent object. The list will only be loaded when the application effectively needs it
(e.g., when user references property MyObject.AssociatedList). When it happens,
Aurelius will perform another SELECT in the database just to retrieve the associated
object data. Only at this point the object is instantiated and data is filled.
If Lazy is not specified, the default behavior is eager-mode loading. It means that
after the parent is loaded, the associated list will be immediately load, but still with
another SELECT statement. For lists, since eager mode will not improve
performance, it's always recommended to use Lazy mode, unless you have a very
specific reason for not doing so, like for example, you will destroy the object
manager after retrieving objects and lazy-loading the lists will not be further
possible.

Should I request the Tsongs and build manually the Talbums?
This worked

3

What I understand from the above, is that, when using XData, it is better not to use classes with ManyValuedAssociation.
If xdata-expand-level is used, or a Createassociation/subcriteria is in the Find procedure this will result in many sql queries to be run.

4

Many-valued (one-to-many, list) associations are always retrieved with an additional SQL for each record. So better keep them lazy and retrieve them when needed. If you need to retrieve lots of songs for many albums, then search for the songs directly and get the respective album of each song.

5

Hi,
We are using Firebird+FireDAC+Aurelius+Datasnap+Http+Rest/JSON (Server) and FMX+...(Client), and when we get an Invoice from the server, we need all the invoice items (lines), soo, if the invoice has 500 items
we have an additional SQL for each record. If Aurelius implement sometink like "Master-Detail" with just one SQL, the improve on performance should be much better. This is a very nice feature to have..,. ;)
P.S. We love aurelius

6

You can simply to a Find of the detail records ordered by the master id and then group them client side. More or less how you would do with raw SQL.

7

Hi, Wagner,
i think i understood the idea, but incase of the invoice, it's not good, because the master(Header) is not the sum of the detailed (lines), there are a lot o business rules to calculate, like the taxes plus the invoice hash and more stuf...
Consider that you have Master(Invoice)/Detailed(Invoice Items) and Master(Invoice)/Detailed(Invoice tax)?

"...by the master id and then group them client side...", how to do that with Aurelius Dataset?

Thank's

8

I don't know your business logic, but since you mentioned SQL, it's more or less how would you do with it anyway. Bring your data as a whole, and process it somehow, for example adding the invoice itens to the invoice item list, and/or calculating totals/grouping as you process the list, etc..

9

Regarding your question about AureliusDataset, note it works with any Delphi object, not only Aurelius entities. You can simply create "view objects", fill their properties with data retrieved from the database, and then set those objects as the source for the Aurelius dataset.

10

ok, :(

think about that :)