Archive CLOB data

Question

Archive CLOB data

VastSQL

SSC-Dedicated

Points: 36097
More actions
January 20, 2020 at 11:25 am

#3716736

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.

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

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

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

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

This reply was modified 4 years, 10 months ago by frederico_fonseca.
This reply was modified 4 years, 10 months ago by frederico_fonseca.

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 2

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.