Over a few years, we've ended up with a HUGE number of rows in the Visitor tables from profiling. Is there an efficient way to prune some of this old history (eg anyone with a LastVisit date more than year ago)?
Raw SQL DELETE's cause lock issues that take the site down. I can do with it with DELETEs of smaller batches but it's a tedious process. Does the product not perform it's own housekeeping?
My advice for now would be to copy the recent/relevant data to a new table, truncate the two tables and re-insert the copied data. This skips the sql server logging part that slows the delete down.
Sorry I can't give you anything better at this point. Hope this helps.
Pruning database
Over a few years, we've ended up with a HUGE number of rows in the Visitor tables from profiling. Is there an efficient way to prune some of this old history (eg anyone with a LastVisit date more than year ago)?
Raw SQL DELETE's cause lock issues that take the site down. I can do with it with DELETEs of smaller batches but it's a tedious process. Does the product not perform it's own housekeeping?
Phil
I've finally been able to work out that there are ~23 million rows in Visitors, 3 million in VisitorTriggerRules !
Hi Phil,
I am afraid there's no housekeeping logic built-in, definately a good idea for the roadmap, but that doesn't help you much right now.
I can imagine that lots of these visits are bots, I assume you excluded those using this: http://www.spindoctorhq.com/blog/2013/12/14/ignoring-crawlers-how-to/
My advice for now would be to copy the recent/relevant data to a new table, truncate the two tables and re-insert the copied data. This skips the sql server logging part that slows the delete down.
Sorry I can't give you anything better at this point. Hope this helps.
Thanks,
Wouter
is working on a reply...