MySQL Forums
Forum List  »  Perl

moving data to an archive table
Posted by: Adam McCormack
Date: October 02, 2008 03:32PM

Good afternoon!

Ive got a prickly problem that I KNOW there has to be a better solution for available.

The situation is this:
We have a contract system that has massive piles of old data (8 million + records) and need to move a selection of the historical data to a seperate table (efficiency issues)

There are two supporting tables that I think are likely to be useful in selecting what stays and what goes. The schema is essentially as follows (only listing the relevant fields)


contract_Data_of_rec
Contract_number
UPC
store_id
Effective_Date

contract_upc
Contract_number
UPC

contract_store
Contract_number
Store_id

contract_data_of_rec (Ill call it CDOR to save typing) is where we have the over abundance of records which we will be moving to an identical contract_Data_of_rec_hist table.

contract_upc and contract_store contain the respective upcs and stores that are on the contract, they can be used to identify the upcs and stores that have been removed from the contract via a join.

the criteria of what can be moved to the historical table as spelled out to me by the business is as follows:
1) Future Effective Dates need to be kept in CDOR
2) The current effective records need to be kept in CDOR
3) the records for the 2 prior effective records for each contract_number/upc/store_id need to be kept in CDOR

criteria 1 and 2 are easy, its the third that complicates the issue. and the archive needs to be a repeatable process (so I cant rename the CDOR as hist, and move the future, then the current (removing the "moved" record), then two more times for the "current" to get the historicals.

I am really enjoying beating my head against this problem, and have a bad solution (it took 40 min to run for one contract, and even THAT was only walking through to find the 3rd oldest effective date for each ctr/store/upc, not performing the inserts or deletes to the historical table) I seek a way to avoid having to query specifically for each contract/store/upc.


I am feeling that this could be done as a single complex compound sql statement, but have not yet found a way to "phrase it". The perl script I wrote depends on what amounts to a table traversal where it hits every record found as below:
select distinct ctr_no, upc, store_id
from ecommerce.contract_data_of_rec
order by upc, store_id;

As I said, Im loving the problem, but since I am doing it for work rather than myself, I need to get it solved faster than I am getting it.

This is what I have written so far
I know its not pretty, its a speed written kludge. I can save some time by using a limit and skip count, but I was wanting to see what was being found, and I have not added the "effective_date <= now()" to not count the future dates, but it gives an idea of what I am after.

#!/usr/bin/perl
use DBI;
use strict;

my $scriptname = $0;
my $dbh = DBI->connect ($dsn, $duser, $dpass) || error_out_cl("Could not connect to the database in $scriptname: ".$DBI::errstr);

#for testing, just hit one contract, for live take out the where clause
my $sth_get_ctr_nos = $dbh->prepare("select distinct ctr_no, upc, store_id from ecommerce.contract_data_of_rec where ctr_no = 98001302");

my $sth_get_ctr_dates = $dbh->prepare("select distinct(effective_date) ".
"from ecommerce.contract_data_of_rec ".
"where ctr_no = ? and upc = ? and store_id = ? ".
"order by effective_date desc");
while (($ctr_no, $upc, $store_id) = $sth_get_ctr_nos->fetchrow_array() )
{
#print "blach $ctr_no, $upc, $store_id\n";
$sth_countorig->execute($ctr_no) || die $dbh->errstr;
my ($countorig) = $sth_countorig->fetchrow_array() ;
#print "\ncontract - $ctr_no\n";
#print "\toriginal record count = $countorig\n";

my $date;
$sth_get_ctr_dates->execute($ctr_no, $upc, $store_id) || die $dbh->errstr;
print "\$ctr_no = $ctr_no\n";
if (($date) = $sth_get_ctr_dates->fetchrow_array() )
{
print "\t$date, \t$ctr_no, \t$upc, \t$store_id\n";
if (($date) = $sth_get_ctr_dates->fetchrow_array() )
{
print "\t$date, \t$ctr_no, \t$upc, \t$store_id\n";
if (($date) = $sth_get_ctr_dates->fetchrow_array() )
{
print "\t$date, \t$ctr_no, \t$upc, \t$store_id\n";
#move_CDOR($ctr_no, $date);
}
}
}


$sth_countorig->execute($ctr_no);
my ($countlive) = $sth_countorig->fetchrow_array() || die $dbh->errstr;
}


Thanks in advance for any help advice, or "nope, cant be dones" that follow!



Edited 1 time(s). Last edit at 10/06/2008 07:20AM by Adam McCormack.

Options: ReplyQuote


Subject
Written By
Posted
moving data to an archive table
October 02, 2008 03:32PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.