BuyLog In
Ticket T623550
Visible to All Users

Audit Trail has missing Index

created 7 years ago (modified 9 months ago)

Hi,

Our XAF Audit Trail has hit 1 million records and performance is very bad. See attached ExpensiveSQL.png.

Below is the SQL Statement from 2008 R2 SQL Management Studio Execution Plan:

(@p0 int,@p1 nvarchar(4000))select top 1 N0."Oid",N1."TargetType",N1."TargetKey",N1."OptimisticLockField",N1."GCRecord",N1."ObjectType",N0."GuidId",N0."IntId",N0."DisplayName" from ("dbo"."AuditedObjectWeakReference" N0
 inner join "dbo"."XPWeakReference" N1 on (N0."Oid" = N1."Oid"))
where (N1."GCRecord" is null and (N1."TargetType" = @p0) and (N1."TargetKey" = @p1))

After adding a recommended index as shown below the problem is resolved:

**CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[XPWeakReference] ([TargetType],[TargetKey],[GCRecord]) **

Is this a missing index from XAF Audit Trail ?
If so, will you implement in future version as I m afraid similar index being created after upgrading XAF.
If not, do I need to add manually for other types of DBMS like Oracle ?

With Regards
Edward Tan

Sign in to comment on this post

Answers approved by DevExpress Support

created 4 months ago

Hello,

In v24.2, we optimized indices used for AuditDataItemPersistent and other audit tables in the database. This change will substantially reduce audit-related save operations:

Sample Usage Scenario v24.2 (s) v24.1 (s)
Test #1 (10 runs) Small database (<1000 objects and audit records) 2.94 8.97
Test #2 (10 runs) Large database (1 million objects, 3 million audit records). 4.33 172.37

Best regards,
Herman

    Sign in to comment on this post
    created 7 years ago

    Hello Edward,

    Adding the recommended index manually is the correct solution - XPO will not override this change. It is possible that you will need to apply the same index if you decide to switch to another DBMS, but actually the situation can be different. In fact, you may have a different volume of data in the database, different operations in the regular end-user workflow, different database structure, etc. All above can affect the SQL execution plan and performance, so you will need to thoroughly research the issue to find the most effective solution.

      Comments (2)
      DevExpress Support Team 7 years ago

        Hello Edward,

        Having discussed this issue with our developers, I also suggest that you include only the TargetKey column in the index. This should be sufficient, because the GCRecord and TargetType columns have the bad selectivity, while the TargetType column values are almost unique.

        AN AN
        Andrea Novelli 5 years ago

          Hello
          after I have entered the proposed index,
          the intervention time has improved considerably.
          first an entry every 3 seconds.
          now 20 entries for one second.
          Thank you.
          this is the solution.

          Sign in to comment on this post

          Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

          Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.