Sergey,
I'm not sure how you got this impression. I've found iss_PurgeSD is very
well-behaved, and if it does anything wrong at all, it's using way too
_many_ small transactions. The code does not use explicit transactions,
it uses implicit transactions. To quote from SQL Server's definition:
"Alternatively, the application can set options to run in
implicit transaction ode; the first Transact-SQL statement executed
after the completion of a prior
transaction starts a new transaction automatically. No record is
written to
the log when the transaction starts; the first record is written
to the log
when the application generates the first log record for a data
modification."
At any rate, at the top of the iss_PurgeSD definition is
DECLARE @ChunkSize int
SELECT @ChunkSize = 5000
That Chunksize is is the number of records processed during each loop,
in each phase (Insert to temporary table, delete from Responses, AVP,
SensorData (+rejects)).
Personally, I find it way to small. The trsansaction overhead for a 5000
records DELETE is huge compared to the work itself on a reasonably fast
machine. When I recently deleting approx. 40 million records this was
_quite_ noticeable! After some experimentation I ended up using
chunksizes between 100 000 and 300 000. Remember to clone the original
stored procedure to your own before you make any changes.
Another option is to not use transactions at all by using the SET
IMPLICIT_TRANSACTIONS OFF option. It's not generally advisable to do so,
but since these are straight deletes from a single table, then - as long
as the last step (DELETE from SensorData) is not performed - if a
statement is aborted for any reason, you can simply run the entire
process again from the start.
Cheers,
Robert
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 21 June 2004 10:50
To: [EMAIL PROTECTED]
Subject: [ISSForum] Manual purging data from a RealSecureDB database
While using iss_PurgeSD procedure I found that all rows from SensorData
table are deleted within single transaction. Am I wrong? If it is, how I
can affect the number of rows (amount of data) deteled in one
transaction to reduce the time needed for iss_Purge procedure
performance?
Thanks.
---
Best regards, Sergey V. Soldatov.
tel/fax +7 095 745 89 50 (2663)
_______________________________________________
ISSForum mailing list
[EMAIL PROTECTED]
TO UNSUBSCRIBE OR CHANGE YOUR SUBSCRIPTION, go to
https://atla-mm1.iss.net/mailman/listinfo/issforum
To contact the ISSForum Moderator, send email to [EMAIL PROTECTED]
The ISSForum mailing list is hosted and managed by Internet Security
Systems, 6303 Barfield Road, Atlanta, Georgia, USA 30328.
_______________________________________________
ISSForum mailing list
[EMAIL PROTECTED]
TO UNSUBSCRIBE OR CHANGE YOUR SUBSCRIPTION, go to
https://atla-mm1.iss.net/mailman/listinfo/issforum
To contact the ISSForum Moderator, send email to [EMAIL PROTECTED]
The ISSForum mailing list is hosted and managed by Internet Security Systems, 6303
Barfield Road, Atlanta, Georgia, USA 30328.
