I agree with Mike: you are going after this the wrong way. I have plenty of customers with Millions of history rows.....
Proper RAID hard drives, indexes, full backups with update statistics, reindex indexes nightly, and the history table is fine.
if you need to see 10,000 rows in a grid quickly search the LAN dev forum from a few years ago (or email me).
Archive them, delete the database change records, place them in a second table, etc....
I have plenty of customers with orders emanating from SalesLogix INTO the ERP system.... I don't feel its backwards at all. The ERP system feeds saleslogix shipping information and orders fulfilled... (i.e. actual sales as opposed to quotes or orders).
I'd have to see your system, but long running queries can be fixed very quickly...... and I don't know what the term 'ideal' performance means.....fastest retrievals I've ever experienced were always preceded by TRUNCATE TABLE....now that's 'ideal'.
And this stuff:
"We are looking to purge rows from the History table, but I would like to know if there are any preferred methods for such a task. I have already removed rows that have an OpportunityID that is not in the Opportunity table, but only 33 of these rows existed. My next thought is to remove rows that have an ActivityID that is not in the Activity table. A simple query reveals that this would remove a large portion of the rows in the History table. The thinking is that if the acitivity does not exist in the Activity table, then there is no need for the record in the History table. Is that thought process valid, or is there a better method to approach this with? Also, are records not deleted from the History table when the entity is removed through the purge records process in SalesLogix? "
shows me that you do NOT have clue about SalesLogix from a data perspective. Next you're going to tell us that you use RAID 5 on your hard drives?