DevExpress XAF is a framework for .NET and Visual Studio developers who wants to create Office-like LOB apps for desktop, web and mobile faster and without hiring a large team.
Thursday, March 21, 2013
Avoiding data redundancy for the 'last modified' date when using the Audit Trail module
Recently, our old (he is actually quite young - just has been using XAF for many years;-)) XAF customer, Nate, posted an interesting question at http://www.devexpress.com/issue=Q482829:
"Well, on my BO I want to replace four persistent fields UpdatedOn, UpdatedBy, CreatedOn, CreatedBy with non-persistent fields that pull this information from AuditTrail since it's already there and available. These values are to be shown on detail views so at a glance the user can get this info without going into the audit trail tab. I'm duplicating data by storing these values myself. However the concern is that audittrail is so large that querying this data will be too taxing on performance".
Indeed, how to avoid that duplication? While there is no unambiguous answer on whether this change will be worth from the performance point of view (it is not possible to predict whether the performance difference will be noticeable or not, because everything depends on the amount of history data, existence of indices and other factors), here is a solution to his requirement:
[PersistentAlias("[<AuditDataItemPersistent>][^.Oid=AuditedObject.GuidId].Max(ModifiedOn)")]
public DateTime ModifiedOn {
get {
return Convert.ToDateTime(EvaluateAlias("ModifiedOn"));
}
}
For testing purpuses I put this code into the Contact class in our MainDemo app.I find this solution interesting, because the criterion I used is quite tricky. Here I am using a cool XPO feature called Free Joins, because my Contact class has no direct relation to the audit data (yes, there is a ChangeHistory property in it, but it is not association).
Take special note that I am using ^. operator to refer to the Oid property of my Contact class (see here for more details on this syntax).
The AuditedObject is a property of the built-in AuditDataItemPersistent class used to store audit data. This property is of the AuditedObjectWeakReference type (a descendant of the XPO's XPWeakReference class) and has a few properties: GuidId, IntId and a string TargetKey one, inheriting from the base class. Take special note that my criterion assumes that the Contact's Oid key property is of the Guid type. So, if your business object has an integer key, then you would use the IntId property instead of the GuidId one in the criterion.
There is also a way to avoid this dependency on the key's type:
[PersistentAlias("[<AuditDataItemPersistent>][Contains(AuditedObject.TargetKey, ToStr(^.Oid))].Max(ModifiedOn)")]
public DateTime ModifiedOn {
get {
return Convert.ToDateTime(EvaluateAlias("ModifiedOn"));
}
}
Note that I had to use the Contais criteria function because the TargetKey is a string in a special format:
UPDATE:
I have just found that our another customer, Kim already tried using this approach in his application and found out that the performance is not that good with lots of records:
http://www.devexpress.com/issue=Q416995
I personally think that this is exactly the case when "code beauty" and avoiding de-normalization are not really worth it. Hopefully, these observations will be helpful for other users.
"Well, on my BO I want to replace four persistent fields UpdatedOn, UpdatedBy, CreatedOn, CreatedBy with non-persistent fields that pull this information from AuditTrail since it's already there and available. These values are to be shown on detail views so at a glance the user can get this info without going into the audit trail tab. I'm duplicating data by storing these values myself. However the concern is that audittrail is so large that querying this data will be too taxing on performance".
Indeed, how to avoid that duplication? While there is no unambiguous answer on whether this change will be worth from the performance point of view (it is not possible to predict whether the performance difference will be noticeable or not, because everything depends on the amount of history data, existence of indices and other factors), here is a solution to his requirement:
[PersistentAlias("[<AuditDataItemPersistent>][^.Oid=AuditedObject.GuidId].Max(ModifiedOn)")]
public DateTime ModifiedOn {
get {
return Convert.ToDateTime(EvaluateAlias("ModifiedOn"));
}
}
For testing purpuses I put this code into the Contact class in our MainDemo app.I find this solution interesting, because the criterion I used is quite tricky. Here I am using a cool XPO feature called Free Joins, because my Contact class has no direct relation to the audit data (yes, there is a ChangeHistory property in it, but it is not association).
Take special note that I am using ^. operator to refer to the Oid property of my Contact class (see here for more details on this syntax).
The AuditedObject is a property of the built-in AuditDataItemPersistent class used to store audit data. This property is of the AuditedObjectWeakReference type (a descendant of the XPO's XPWeakReference class) and has a few properties: GuidId, IntId and a string TargetKey one, inheriting from the base class. Take special note that my criterion assumes that the Contact's Oid key property is of the Guid type. So, if your business object has an integer key, then you would use the IntId property instead of the GuidId one in the criterion.
There is also a way to avoid this dependency on the key's type:
[PersistentAlias("[<AuditDataItemPersistent>][Contains(AuditedObject.TargetKey, ToStr(^.Oid))].Max(ModifiedOn)")]
public DateTime ModifiedOn {
get {
return Convert.ToDateTime(EvaluateAlias("ModifiedOn"));
}
}
Note that I had to use the Contais criteria function because the TargetKey is a string in a special format:
UPDATE:
I have just found that our another customer, Kim already tried using this approach in his application and found out that the performance is not that good with lots of records:
http://www.devexpress.com/issue=Q416995
I personally think that this is exactly the case when "code beauty" and avoiding de-normalization are not really worth it. Hopefully, these observations will be helpful for other users.
Subscribe to:
Post Comments (Atom)
Hi Dennis,
ReplyDeleteI've been a Dev Express customer for years but I've never made the leap to XAF or XPO but I'm about to start revising some existing applications that I have built and am giving it a much closer examination this go round - mainly for the Domain Objects, Application Server and Security features.
I've tried to research this topic but can't seem to find anything documented (I'm probably looking in the wrong place) but here goes:
I've always developed my database model first and put most of my logic coding and hard business rules within SQL Server and stored procedures. I've done this for 2 reasons, to make the front ends lighter and to gain data performance as the data set gets larger.
Sometimes, in the applications that I work with, there are many procedures that are performed within the database itself using sets of data.
How does XAF address processes like that and how bad of a performance hit am I taking if my application is doing all the heavy lifting?
To give you an example, let's just say you've built an accounting system and at the end of the month, you do a closing and need to update registers, customer balances, etc. - how do you approach that aspect of your application build?
For argument sake, I'm not worried about implementing on anything but SQL Server. Maybe its the way I'm approaching things but I am unsure about taking the plunge to XAF and am looking for more concrete information/advice on data performance and general application principles.
Thanks in advance and these are all great posts btw.
First of all thanks for reading and please accept my apologies for the delay since I somehow missed your comment.
ReplyDelete>>
How does XAF address processes like that and how bad of a performance hit am I taking if my application is doing all the heavy lifting?
...
how do you approach that aspect of your application build?
<<
There are quite many means in our framework that allow you to achieve a good performance while working with large amounts of data. These are delayed loading (http://documentation.devexpress.com/#XPO/CustomDocument2024), server mode (http://documentation.devexpress.com/#WindowsForms/CustomDocument8398), XPPageSelector, XPCursor (http://documentation.devexpress.com/#XPO/CustomDocument2075) and XPView (http://documentation.devexpress.com/#XPO/CustomDocument2068).
In short, all of these features allow you to work with small portions of data or load only necessary data. However, even with these built-in means it is sometimes possible to hit performance problems, if the design of your persistent classes has mistakes that will lead to pulling unnecessary data. To avoid such problems, there are certain best practices (http://www.devexpress.com/issue=A2944, http://www.devexpress.com/issue=AQ11942, etc.) and also a specialized tool (http://documentation.devexpress.com/#XpoProfiler/CustomDocument10660).
While it is also possible in our framework to get data directly from stored procedures (http://documentation.devexpress.com/#XPO/CustomDocument8919), I think it is still worth quoting myself from the http://www.devexpress.com/issue=Q476093 thread:
"...let me first describe the basics of our framework.
eXpressApp Framework (XAF) framework is based on XPO and Entity Framework, which are ORM. The ORM concept is very important here as in our framework you should not normally think of database tables, stored procedures, SQL statements, but rather operate data in an object-oriented manner.
I suggest you check out the Business Classes vs Database Tables help article to get more inspirations. Some links about Domain Driven Design posted in the http://community.devexpress.com/blogs/eaf/archive/2008/05/20/xaf-1-best-practices-for-domain-objects.aspx blog post may be helpful as well.
As you might distill from this, the core of our framework is a business or domain object, and not a database table or SP. Thus, most of XAF scenarios are implemented considering that you are dealing with business objects. Yes, there is certainly support for executing a raw SQL via the Session.ExecuteXXX method, stored procedures, mapping to database views at the XPO level, but there is no specialized support in XAF for this, as such cases are considered to be exceptions rather than rules due to above.
So, I agree with you that the solutions I suggested above are not straightforward, but I could not help mentioning all the possible ways to accomplish your task.
If in your project you have to heavily work with stored procedures or read data from database views, I think that you would better avoid using XAF for your project and rather implement it the 'old way', just using our visual components and the standard means for accessing SQL data.
Alternatively, you can still use XAF, but try to start thinking its way and operating your requirements by business objects and not database tables. Take special note that you can easily establish relationships between your objects and XPO will manage all the data retrieval tasks for you (like joining multiple tables). I suggest you check out the Object Relationships help article to get started with this. Most likely, it will be easier for you if XPO creates all the associated objects using the ORM Data Model Wizard.
As for filtering data, the object-oriented criteria language or LINQ is always at your service with our frameworks: Querying a Data Store"