[munish.narula]

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.

[trvishi]

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.