Archive CLOB data

  • VastSQL

    SSC-Dedicated

    Points: 35906

    Dear All,

    How to archive a table having 10million records with one CLOB column?

    To select of 1 day records (approx~400) it takes around 5 minutes and without the CLOB column it completes under a second.

     

  • frederico_fonseca

    SSChampion

    Points: 14743

    so you are telling us that doing a

    insert into archive_table
    select *

    from main_table where filtering_column = date_to_archive 

    takes 5 minutes?

    or that it takes that amount of time retrieving the records onto your SSMS window?

    if the second then try the first one - onto a temp table and see how long it takes. and if it still takes more than 1 second give us the actual explain plan of the execution alongside table DDL (table and indexes)

     

    Regarding archive process you need to tell us where you wish to archive to - same db, same instance, different server or filesystem

    and also if people would actively and daily query that archive table or if it can really be archived (e.g.  column compressed) with minimal impact

  • VastSQL

    SSC-Dedicated

    Points: 35906

    frederico_fonseca wrote:

    so you are telling us that doing a

    insert into archive_table
    select *

    from main_table where filtering_column = date_to_archive 

    takes 5 minutes?

    or that it takes that amount of time retrieving the records onto your SSMS window?

    if the second then try the first one - onto a temp table and see how long it takes. and if it still takes more than 1 second give us the actual explain plan of the execution alongside table DDL (table and indexes)

    Regarding archive process you need to tell us where you wish to archive to - same db, same instance, different server or filesystem

    and also if people would actively and daily query that archive table or if it can really be archived (e.g.  column compressed) with minimal impact

     

    Thanks for the reply. Both same almost the same time.

    WRT to archiving we are now planning to move to a different database on same server. The archive database will be queries once in a day or as per report requirement. We are planning to have partition on the archival db based on year. File for each year till 2025.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply