• Replies 5 replies
  • Subscribers 338 subscribers
  • Views 108 views
  • Users 0 members are here
Related

Purging Stale Customers

Sean Pinto
$organization

Hello,

  Has anyone had any success with purging (deleting) old and unused customers from their database?  Using some criteria we are considering deleting upwards of 200,000 customer records that have no ticketing, contribution, or informational history (mostly from our conversion 7 years ago).  I feel like there are a few tables with customer data that don't use have any referential integrity constraints.....  

Thanks for any info!

Sean

  • $organization

    Hi Sean,

    Just wandering what would be a reason to purge customers. If interested: to improve the overall database performance and its size we do purge and archive periodically the following tables:

    T_WEB_SESSION_VARIABLE:
    delete entries older than 5 days (in our environment, the table size would be several hundred million rows without the purge);

     T_WEB_SESSION_SESSION:
    delete and archive entries older than 2 months;

    T_ORDER_SEAT_HIST:
    for performances older than 1.5 year, for each seat preserve the latest transaction and archive/delete the rest; leave performances "younger" than 1.5 year intact.


    To asset tables contribution to DB size, please check out the attached DB procedure.

    If you need details, my direct contact is: SBasyuk@CarnegieHall.com

    Thanks,
    Simon Basyuk
    DBA, Carnegie Hall

  • $organization

    There is a SQL file named "truncate_all.sql" that lives in Conversion Scripts -> Extra that is normally used during the conversion process to unload all constituent data before loading it again.

    Carefully edited, that code could be used as the basis of a sproc that would completely delete selected constituents as well. We've never done that here, but if we did, that's where I'd start.

  • $organization in reply to Chris Jensen

    Thanks for the great responses!

    Simon - FYI I tried responding to your email directly but it bounced back saying the user didn't exists.

     

    Our main motivation for wanting to delete customers rather than inactivate them is both for overall database performance and the plain fact that we don't want to have them converted in our v11 migration since I presume they get converted even if they inactivated.  Do you guys know if that is not the case?  But per Simon's suggestion I think we can get rid of more rows by clearing out the web and order history tables.  

     

    Thanks for the continuing help!

    Sean

  • $organization in reply to Sean Pinto

    Hi Sean,

    Confirming that the v11 upgrade doesn't delete any constituents, inactive or otherwise.

    THANKS

    Andrew