[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

RE: [ISSForum] Manual purging data from a RealSecureDB database



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: issforum-bounces@xxxxxxx [mailto:issforum-bounces@xxxxxxx] 
Sent: 21 June 2004 10:50
To: issforum@xxxxxxx
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
ISSForum@xxxxxxx

TO UNSUBSCRIBE OR CHANGE YOUR SUBSCRIPTION, go to
https://atla-mm1.iss.net/mailman/listinfo/issforum

To contact the ISSForum Moderator, send email to mod-issforum@xxxxxxx

The ISSForum mailing list is hosted and managed by Internet Security
Systems, 6303 Barfield Road, Atlanta, Georgia, USA 30328.

_______________________________________________
ISSForum mailing list
ISSForum@xxxxxxx

TO UNSUBSCRIBE OR CHANGE YOUR SUBSCRIPTION, go to https://atla-mm1.iss.net/mailman/listinfo/issforum

To contact the ISSForum Moderator, send email to mod-issforum@xxxxxxx

The ISSForum mailing list is hosted and managed by Internet Security Systems, 6303 Barfield Road, Atlanta, Georgia, USA 30328.