BuyLog In
Ticket T464176
Visible to All Users

Audit Tables Insert Performance

created 8 years ago

While watching the output window I noticed that when my app is writing data to the database, especially when I have auditing enabled, I see several insert statements running. An example is below

I believe that there can be some significant performance benefit if I compress this (to three SQL Statements, 1 for each table in this case) using XML

How can I accomplish this for the AuditTables?

SQL
DECLARE @input XML = '<dataset>  
 <metadata>  
  <item name="NAME_LAST" type="xs:string" length="62" />  
  <item name="NAME_FIRST" type="xs:string" length="62" />  
  <item name="NAME_MIDDLE" type="xs:string" length="32" />  
 </metadata>  
<data>  
<row>  
 <value>SMITH</value>  
 <value>MARY</value>  
 <value>N</value>  
</row>  
<row>  
 <value>SMITH2</value>  
 <value>MARY2</value>  
 <value>N2</value>  
</row>  
</data>  
</dataset>'  

INSERT INTO dbo.YourTable(ColName, ColFirstName, ColOther)  
   SELECT  
      Name = XCol.value('(value)[1]','varchar(25)'),  
      FirstName = XCol.value('(value)[2]','varchar(25)'),  
      OtherValue = XCol.value('(value)[3]','varchar(25)')  
   FROM  
      @input.nodes('/dataset/data/row') AS XTbl(XCol)

19.12.16 07:34:20.066 Executing sql 'insert into "dbo"."XPWeakReference"
19.12.16 07:34:20.099 Executing sql 'insert into "dbo"."AuditedObjectWeakReference"
19.12.16 07:34:20.134 Executing sql 'insert into "dbo"."XPWeakReference"
19.12.16 07:34:20.162 Executing sql 'insert into "dbo"."AuditedObjectWeakReference"
19.12.16 07:34:20.204 Executing sql 'insert into "dbo"."XPWeakReference"
19.12.16 07:34:20.233 Executing sql 'insert into "dbo"."AuditedObjectWeakReference"
19.12.16 07:34:20.254 Executing sql 'insert into "dbo"."XPWeakReference"
19.12.16 07:34:20.288 Executing sql 'insert into "dbo"."XPWeakReference"
19.12.16 07:34:20.314 Executing sql 'insert into "dbo"."XPWeakReference"
19.12.16 07:34:20.352 Executing sql 'insert into "dbo"."XPWeakReference"
19.12.16 07:34:20.373 Executing sql 'insert into "dbo"."XPWeakReference"
19.12.16 07:34:20.406 Executing sql 'insert into "dbo"."XPWeakReference"
19.12.16 07:34:20.446 Executing sql 'insert into "dbo"."XPWeakReference"
19.12.16 07:34:20.472 Executing sql 'insert into "dbo"."XPWeakReference"
19.12.16 07:34:20.496 Executing sql 'insert into "dbo"."AuditDataItemPersistent"
19.12.16 07:34:20.531 Executing sql 'insert into "dbo"."AuditDataItemPersistent"
19.12.16 07:34:20.578 Executing sql 'insert into "dbo"."AuditDataItemPersistent"
19.12.16 07:34:20.608 Executing sql 'insert into "dbo"."AuditDataItemPersistent"
19.12.16 07:34:20.656 Executing sql 'insert into "dbo"."AuditDataItemPersistent"
19.12.16 07:34:20.688 Executing sql 'insert into "dbo"."AuditDataItemPersistent"
19.12.16 07:34:20.728 Executing sql 'insert into "dbo"."AuditDataItemPersistent"
19.12.16 07:34:20.756 Executing sql 'insert into "dbo"."AuditDataItemPersistent"
19.12.16 07:34:20.786 Executing sql 'insert into "dbo"."AuditDataItemPersistent"
19.12.16 07:34:20.814 Executing sql 'insert into "dbo"."AuditDataItemPersistent"
19.12.16 07:34:20.848 Executing sql 'insert into "dbo"."AuditDataItemPersistent"
19.12.16 07:34:20.878 Executing sql 'insert into "dbo"."AuditDataItemPersistent"
19.12.16 07:34:20.936 Executing sql 'insert into "dbo"."AuditDataItemPersistent"
19.12.16 07:34:20.978 Executing sql 'insert into "dbo"."AuditDataItemPersistent"
19.12.16 07:34:21.005 Executing sql 'insert into "dbo"."AuditDataItemPersistent"
19.12.16 07:34:21.038 Executing sql 'insert into "dbo"."AuditDataItemPersistent"
19.12.16 07:34:21.077 Executing sql 'insert into "dbo"."AuditDataItemPersistent"
19.12.16 07:34:21.105 Executing sql 'insert into "dbo"."AuditDataItemPersistent"
19.12.16 07:34:21.133 Executing sql 'insert into "dbo"."AuditDataItemPersistent"
19.12.16 07:34:21.193 Executing sql 'insert into "dbo"."AuditDataItemPersistent"
19.12.16 07:34:21.232 Executing sql 'insert into "dbo"."AuditDataItemPersistent"

Comments (1)
DevExpress Support Team 8 years ago

    Hello Fitzroy,

    We need additional time to research this issue. Please bear with us. We will get back to you as soon as possible.

    Sign in to comment on this post

    Answers approved by DevExpress Support

    created 8 years ago (modified 8 years ago)

    Hello Fitzroy,

    >>I believe that there can be some significant performance benefit if I compress this (to three SQL Statements, 1 for each table in this case) using XML
    Currently, this default behavior of our auditing engine neither can be easily changed nor we prepared extensibility points for this sort of customizations originally. From your log, these audit operations take about 1 second and I can suggest you try one of the two strategies to improve the current behavior:

    1. Implement a fully custom auditing mechanism.
      1.1. In the simplest case, you can disable the default audit processing for required types and handle custom events to write custom audit messages. See the Specify the Objects and Properties to Be Audited and Add Custom Data to the Audit Log  topics for more details.
      1.2. You can handle the SaveAuditTrailData event to customize the default saving mechanism (Customize the Data Storing Mechanism) to skip certain items.
      1.3. You can handle the CustomCreateObjectAuditProcessorsFactory event  and provide your own auditing processor. See the Q457937 ticket for some example code.
      1.4. If you want, you may not use our module at all and come with your own business classes and controllers.

    2. You can tune up the INSERT and other important database operations by one of the following methods:
      2.1. Consider setting the XpoDefault.GuidGenerationMode property to UuidCreateSequential as the default mode can slow the INSERT performance on large databases due to fragmentariness.
      2.2. Consider adding an index for the TargetKey column of the XPWeakReference table to improve overall performance.
      I can provide more specific suggestions if you provide us with a debuggable project and test database replicating the performance problems you experienced.

      Comments (1)
      F F
      Fitzroy F. Wright 8 years ago

        Your suggestions are good and some of them I have since implemented.
        However I do not want to change the auditing mechanism as I would prefer to benefit from the expertise of your engineers.

        I will look at my code some more and the database to see how I can improve things in other ways.

        Thanks

        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.