I'm working on an archive utility I've inherited.
At the beginning of the archive, two temp tables are built:
1) List of Shop_ID's to archive (from Shop table), where CompleteDate < ArchiveDateSelected
2) List of Time_ID's to archive (from timeMain & timeDetail). List is based on pay periods. The most recent payperiod with 0 outstanding transactions (meaning all records have been zeroed out, nobody owes, or is owed, money).
For example, if I set the archive date to 3/1/2010, I archive all shop items completed before 3/1/2010. I get all Time_ID's before 8/27/2009.
This results in orphaned detail records (because detail has both time_ID & shop_ID as FK's), because this Join (in form of where statement) is used on the accounting timesheet form:
Now, one solution would be:
Don't archive shop id's that still have payroll items outstanding. I already have a list of time ID's, and my tblTime_Detail associates time_ID & Shop_ID, so I could filter my "shop ID's to be archived" table to remove those jobs fairly easily.
However, I'm trying to make the production DB as small as possible (currently 400 meg), so removing as many records as I can is optimal. With these records removed from production, reports that used to take 40 seconds to run are now taking 18. I'd like to keep that.
Can I just say:
I would just have to include the archive_tblWF_Shop during my 'attach tables' function I run at app load.
Functionally, I think that would work (and could test rather easily), but I'm wondering if that's not just an elegant solution, but one that doesn't take a large performance hit.
Thanks a ton!
Scott
At the beginning of the archive, two temp tables are built:
1) List of Shop_ID's to archive (from Shop table), where CompleteDate < ArchiveDateSelected
2) List of Time_ID's to archive (from timeMain & timeDetail). List is based on pay periods. The most recent payperiod with 0 outstanding transactions (meaning all records have been zeroed out, nobody owes, or is owed, money).
For example, if I set the archive date to 3/1/2010, I archive all shop items completed before 3/1/2010. I get all Time_ID's before 8/27/2009.
This results in orphaned detail records (because detail has both time_ID & shop_ID as FK's), because this Join (in form of where statement) is used on the accounting timesheet form:
Code:
(tblWF_Shop.WF_Shop_Key = tblTime_Detail.WF_Shop_ID)Don't archive shop id's that still have payroll items outstanding. I already have a list of time ID's, and my tblTime_Detail associates time_ID & Shop_ID, so I could filter my "shop ID's to be archived" table to remove those jobs fairly easily.
However, I'm trying to make the production DB as small as possible (currently 400 meg), so removing as many records as I can is optimal. With these records removed from production, reports that used to take 40 seconds to run are now taking 18. I'd like to keep that.
Can I just say:
Code:
WHERE (tblWF_Shop.WF_Shop_Key = tblTime_Detail.WF_Shop ID
OR archive_tblWF_Shop.WF_Shop_Key = tblTime_Detail.WF_Shop_ID)Functionally, I think that would work (and could test rather easily), but I'm wondering if that's not just an elegant solution, but one that doesn't take a large performance hit.
Thanks a ton!
Scott
