Home | Forums | Contact | Search | Syndication  
 
 [login] [create account]   Tuesday, May 26, 2020 
 
slxdeveloper.com Community Forums  
   
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
 Administration Forums - Database Administration
Forum to discuss SQL Server or Oracle database administration related to SalesLogix databases. View the code of conduct for posting guidelines.
Forums RSS Feed


 Back to Forum List | Back to Database Administration | New ThreadView:  Search:  
 Author  Thread: Purge From History Table
Yup.
Posts: 126
 
Purge From History TableYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 18 Jul 12 10:11 AM
fiogf49gjkf0d

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? 

[Reply][Quote]
Mike Spragg
Posts: 1226
Top 10 forum poster: 1226 posts
 
Re: Purge From History TableYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 18 Jul 12 11:00 AM
fiogf49gjkf0d

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

[Reply][Quote]
Yup.
Posts: 126
 
Re: Purge From History TableYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 18 Jul 12 11:07 AM
fiogf49gjkf0d

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

[Reply][Quote]
Mike Spragg
Posts: 1226
Top 10 forum poster: 1226 posts
 
Re: Purge From History TableYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 18 Jul 12 11:16 AM
fiogf49gjkf0d

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....

[Reply][Quote]
Yup.
Posts: 126
 
Re: Purge From History TableYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 18 Jul 12 11:27 AM
fiogf49gjkf0d

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.

[Reply][Quote]
Mike Spragg
Posts: 1226
Top 10 forum poster: 1226 posts
 
Re: Purge From History TableYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 18 Jul 12 11:40 AM
fiogf49gjkf0d

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].

[Reply][Quote]
Yup.
Posts: 126
 
Re: Purge From History TableYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 18 Jul 12 11:50 AM
fiogf49gjkf0d

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. 

[Reply][Quote]
RJ Samp
Posts: 973
Top 10 forum poster: 973 posts
 
Re: Purge From History TableYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 19 Jul 12 6:32 AM
fiogf49gjkf0d

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?


 


 


 

[Reply][Quote]
Yup.
Posts: 126
 
Re: Purge From History TableYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 09 Nov 12 1:47 PM
fiogf49gjkf0d

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

[Reply][Quote]
 Page 1 of 1 
  You can subscribe to receive a daily forum digest in your user profile. View the site code of conduct for posting guidelines.

   Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
 

 
 slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation.
 This site, and all contents herein, are Copyright © 2020 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.

code of conduct | Subscribe to the slxdeveloper.com Latest Article RSS feed
   
 
page cache (param): 5/26/2020 11:49:25 AM