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? 

Anthony

STOP! WRONG ASSUMPTION!

If you delete from history where the activityid doesn't exist in activity - you will lose a LOT of history that's valid. When an activity completes it moves from ACTIVITY to HISTORY. The ActivityID is the old ID - and is OK to be left there (or nulled out). But, removing it because it doesn't exist in Activity will cause you to lose a lot of valid data.

Yes, data is delete from history if the account is deleted (but NOT if the contact is deleted, the ConID is nulled out - so you don't lose the history just because the person leaves).

I'd work out something based on dates instead. And, move it to an archive copy of History. Is there any reason to purge it at all ?

Regards
Mike

There are about 1.8 million reasons why we are looking into cleaning up the History table.

Yes, but that's not exactly huge. We have customers with 10 times that!

In other words, are you seeing delays? Just feel this is "too much", slow operation, any other good reason ? Disk space is cheap - but, put in some decent SAS drives or SSD and the problem will fall away....

We are looking into implementing an annual maintenance routine for purging data out of SalesLogix.  We set a rentention period for opportunities, and have recently purged all opportunities created outside of the rentention period.  I am considering that to be step one, with step two consisting of an investigation of what else might cause performance degratdations.  My focus shifted to the History table after running a query to pull up all of the tables in SLX as well as their associated row counts.

Personally, I'd leave them where they are. Create a new team, assign the old data to the new team (put only Admin in this archive team). That way, it "disappears" from the client/queries/groups etc. but can be brought back (just by changing seccodeid) quickly and instantly in the event of a query/mistake. By deleting, you'd have to resort to backups [if available].

To add a little more information to this, the SLX opportunity section is being used to enter orders, as opposed to holding information for sales leads, that are later migrated into the ERP system.  I always felt this process was ass-backwards, and that the ERP system should be feeding SLX order information, and SLX should be used to attach various notes and attachments pertaining to the orders.  Essentially, I feel SLX has been customized in a manner in which we will never really be able to achieve ideal performance, which is why we have been looking into row counts as well as long running queries and the tables they are utilizing. 

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?

Man, I am glad I did not come back to this thread after that last response.