Performane Questions

BIZ TMS Aurelius
1

Hi.

After playing some time with Aurelius there are some questions, that i hope I will find answers to. 
1) I noticed that if you will try loading objects from the database directly, then performance of ORM will decrease rapidly. With simple objects, loading 20000 can take 4 to 6 seconds (depending on object fields), while loading 100000 takes 1 minute 20-30  seconds!
I managed to get around this issue by using Paging. If you load objects in chunks of 1000, then loading those 100000 will take 20-30 seconds. 
Are there any properties that will allow to use something like "autopaging"? Or maybe there are something missing in way I load object? 
Basically I try to use Manager.FindAll<T>  with slow results.
2) Is there bulk insert/update feature on RoadMap for MsSQL? I noticed that inserts is basically one-by-one, thus very slow.  Maybe there is some way for putting those inserts in one transaction, similar to MORMOT framework? 
3)  Are there any way, besides DynamicProperties to limit loading of objects from database? I don'tmean associated fields, I want to limit non-required columns loading for some object. I don't want to create separate object (Like Employee and EmployeeCoreInformation) for those cases when I need only several columns from Employee Table and dont want to load additional fields from the database. I can easily do it Via DynamicProperties, but then I cannot use really nice features of class generation from DataModeller.
Thanks for your support.

2

One other question: Is Aurelius capable to map objects to views? 

3
Hi,
1) You have more options to retrieve objects from database. You can use lists, paged results and cursors. Paged results will bring a subset of the objects and close. Cursors will keep the connection open and will retrieve objects on demand. And the lists is the traditional way which will always bring all objects at once, thus it's slower for many objects. I'm not sure how do you expect to have a list-approach with fetch on demand, since a TList<T> is returned, it just doesn't have a fetch-on-demand mechanism, it's just a plain Delphi list object. More info here: http://www.tmssoftware.com.br/aurelius/doc/web/index.html?retrieving_results.htm

2) You can manually use transactions in Aurelius. You can use IDBConnection.BeginTransaction method to start a transaction. That method will return an IDBTransaction interface which have both methods Commit and Rollback that you can use to finish the transaction. So you already have that option. In addition to that, we plan to add some optimizations to Aurelius in general, one of them is to use prepared queries for bulk inserts to speed up a little more, in addition to using them inside transactions

3) In addition to associated objects that can be loaded in lazy mode, blobs can also be loaded on demand, which makes sense since blobs can have high amount of data. Other than this, no, there is no such option, and I wonder if it makes any different to load or not load fields that are small size like integers, or small varchars. But even if it does, problem is the objects won't be consistent. Say that you have a property of type Null<Integer>. How would you know that the property is null because the data in database is actually null, or just because it was not loaded from the database yet. It would need a complex mechanism similar to blobs and associations, that in the end imho will add too much overhead just to save a few bytes (integer in this case).

4) Aurelius just build SQL statements for the mapped classes. So it generates Select, Insert, Delete, Update statements and those are mapped to tables. If such SQL statements would work in a view as well, then yes, you can just pass the name of a view as the table name, it will behave the same. If your database doesn't support updateable views, then you would only be able to perform select on those objects, of course.
4

Hi,

1) There is slight misunderstanding about this question.  I had run some basic performance tests. If you load 140000 records of objects as a big list, at once, using Manager.Find<TCustomer>.AddOrder(Torder.Asc('CustomerID')).Take(160000).List it will take 5min:12 sec. Loading same amount, however, using
 For i:=0 to 159 do 
 Manager.Find<TTimesheet>.AddOrder(Torder.Asc('CustomerID')).Skip(1000*i).Take(1000).List;
will take only 1min:48 seconds. 
I can safely add Mylist.AddRange in both cases without noticeable changes in performance.  So there should be some kind of a problem with memory allocation, that will significantly slow loading big lists of objects at once.   IF you go to 500000 fairly complex objects, difference in loading times between those two methods will be more then 6 times!

2)  Thank you, it really helped!
3)  Is not about saving few bytes, its again, about the performance. The similar feature is in the Entity Framework, when you can map service layer objects to database entity, reading only specific properties. And if you mapped your service layer to 2 properties out of 12, it will only select those 2 needed properties. 
Again, I did some basic performance testing using Dynamic Properties. 
I checked int, datetime, guid, nvarchar(50), int fields loading.  Normally on 40.000 records each additional field gives 500ms-1000ms loading time.  Loading 40.000 objects that consists of 10 guids instead of 1 more than twice times slower (11216ms vs 4711ms).  Consider having CreatedAt,CreatedBy(uid),ModifiedAt, ModifiedBY(UID),DeletedAt, DeletedBy(UID) fields, that in most cases should not be loaded at all. Or, consider populating some list with only selected object properties... Why should we waste time and resources on loading the data that we don't really need? 
And it should not return Null, it should just return exception if you try to access property that wasn't supposed to be loaded because of your mapping setup. 

4) Thank you, it worked! Is there any way to map Aurelius Objects to Stored procedures and/or Functions?  I noticed that you have Dataset that can be mapped to any ObjectList. Is there any way to map existing ObjectList TO dataset? Basically fill the dataset with properfields and types, and then get Aurelius to try and fetch the objects from this list? 
If we had this functionality it would be easy to get objects from any database objects, even from functions and procedures. 

5) When i tried to create simple test object with ID that should be generated by Aurelius and not mapped to database I always get an error 'Invalid member FUID defined in ID attribute'. 
  [Table('AureliusTest')]
  [id('FUID',TidGenerator.Uuid38)]
 private
 FUID: string;
FProps: TDynamicProperties;

6)I have test class: 
[Table('AureliusTest')]
[Id('Fint', TIdGenerator.IdentityOrSequence)]:
 TAUTestD = class
  private
  TUID: Nullable<TGuid>;
    [Column('int', [])]
    Fint: Nullable<integer>;
    FProps: TDynamicProperties;
If I comment out TUID and try to create dynamic property of Nullable<TGuid> I would get an error that this type is not declared in interface section. However, with uncommented TUID, i could easily create dynamic properties, without any further changes to the code. 

Thank you for your support.

5

Sorry about last question. Nullable is not supported in Dynamic Properties...

Are there any way to change between lazy load and eager load without creating new class? In 90% of cases lazy load would suffice, but in some cases I need eager load. What is the easiest way to achieve that?
6
1) Well, the example you mentioned uses two different classes, so they would of course have different times. If you are using same classes and just typed by mistake, then I would kindly ask if you could send me a project with that test that I can run (with SQLite database with existing data, for example). As I said, we plan to perform a good optimization on Aurelius, but as you know, different scenarios take different times. So the more different scenarios we have to optimize, the better. Sometimes even a different class can cause a bottleneck.

3) Even if it can save time, will it be relevant, compared to the other optimizations that can be done? Nevertheless, I'm not exactly against selecting specific data, I just don't know how it would fit in Aurelius philosophy. For example: a) How do you expect the syntax to do such selection? Are you aware that Aurelius already support projections? How would a query syntax be that are not projections, but a selection of specific properties. b) EF works that way because you have more possibilities in C#. We just can't raise exceptions when reading a pure Delphi property. Either the property will be null or not. Unless of course we "invent" some different types and you manually declare the class like that, so for example instead of "Code: integer" you will declare "Code: Wrapper<integer>". But I think that's overhead and go against our philosophy to use plain Delphi classes as much as possible.

4) I didn't understand your example? Just the same as with views, you can map to any other database object that can be selected and maybe inserted/updated/deleted with regular SQL commands. Other than this, if the database requires a different syntax to perform CRUD operations, than it's not possible.

5) Yes, this is by design, your ID must be persisted in the database

6) Nullable is not supported because it's not needed. You can set null values to dynamic properties and you can define if they will be required or not in database by using the TColumnProp.Required flag.

7) About eager/lazy loading, that's not possible, and is something considered to be implemented, yes.
7

Where can I found samples about this "bulk insert" feature? I have the following entity, and I found with "cached update", inserting 10000 records still takes 15 seconds, which is unacceptable for my realtime application.

  [Entity, Automapping]
  [Id('FId', TIdGenerator.None)]
  TCustomer = class
  strict private
    FId: Integer;
    FName: string;
    FCity: string;
  public
    property Id: Integer read FId write FId;
    property Name: string read FName write FName;
    property City: string read FCity write FCity;
  end;
8

You can have more information in the blog post and in the documentation topic.
I see you cross-posted, so we can continue the conversation from your post below.