MySQL Forums

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.