Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2008
    Posts
    1

    Unanswered: Performance issue with sysaudit table

    Hi,
    I have configured 8 audit tables on sybsecurity database and switched on auditing. Each table can have upto 15 million records.

    I am fetching records from these tables in a batch of 10000 using a simple query.
    select top 10000 from sysaudits_01 where eventtime >= t1 and eventtime < t2

    The time taken for each iteration keeps on increasing as we move deeper into the table. Below are some stats.

    Time taken for iteration 1: 31
    Time taken for iteration 2: 0
    Time taken for iteration 3: 0
    Time taken for iteration 4: 235
    Time taken for iteration 5: 406
    ....
    ....
    ....
    Time taken for iteration 50: 5203
    Time taken for iteration 51: 5610
    Time taken for iteration 52: 6453
    Time taken for iteration 53: 5937
    Time taken for iteration 54: 5922
    Time taken for iteration 55: 6312
    ....
    ....
    ....
    Time taken for iteration 87: 10906
    Time taken for iteration 88: 10281
    Time taken for iteration 89: 10703
    Time taken for iteration 90: 10711
    Time taken for iteration 91: 10360
    Time taken for iteration 92: 11188

    Can you suggest me a way by which i can improve on the timings for the query. I tried creating index on the table but sybase does not allow me to do so as it is a system table. Is there any other way of tuning the query.

  2. #2
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    You probably need a mechanism to archive your audit records periodically to a different dba database and flush out the audit database. If you do that you have the option of creating indexes etc.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •