William, if you haven't already, investigate the pre-caching of Entry-IDs. The sweet spot seems to be around 100 at a time, and it really does help. Also, large data imports are sped up by temporarily disabling the indexes on the import form. Make sure that your data doesn't violate any unique ones, or you will have a nasty surprise when you add them back in. Rick _____
From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of William Rentfrow Sent: Wednesday, November 07, 2007 7:06 AM To: arslist@ARSLIST.ORG Subject: Degrading performance on large updates and inserts ** We are pushing about 800,000 records into the CTM:People form. This is an initial data load - updates will be smaller. To do this we've imported the records into a staging form to format the data properly. Then an escalation kicks off and marks every record's status to "Processed" from "New". A filter fires on the change of status and pushes data to the CTM:People form. This filter does a match on our staging form EID = CTM:People.Corporate ID which is an indexed field in the CTM:People form. With the base product I had exceedingly bad import times - about 1.5 seconds per record (and this is on a pretty decent Sun box with a separate Oracle server also on a Sun box). So I added a filter on CTM:People that said if $USER$="AR_ESCALATOR" then Goto 1000. Essentially I skipped all the OOB validation (which I'd already done in the staging form out of necessity). This got the record import speed up to about 15 records/second (which is STILL far too slow IMHO). As the 800,000 records imported the speed decreased. At the end we averages 6.92 records/second pushed to CTM:People and it took ~31.5 hours. Also, we initially left one piece of data out of the staging form - so we added it back in and ran an update push to CTM:People. This was faster but had the same symptom - I started out getting 15/second updates and now (nearly 24 hours later) I'm averaging 9.5 updates/second. My questions.... 1.) Why would performance degrade for an update? I can see it for the insert because the tables/indexes/paging/etc are growing - but on an update that should be fairly static. 2.) Any ideas why this SO slow? These are big enterprise boxes. I have appropriate ports/queues configured, etc. The arserverd process is not chewing up all the clock time so obviously it's not taxed. And barely anyone is on this box at all. Also.... 3.) If this is still running and I turn on server side SQL logging why don't I see any of the transactions? Especially when server filter logging shows them still processing..... William Rentfrow, Principal Consultant [EMAIL PROTECTED] C 701-306-6157 O 952-432-0227 __20060125_______________________This posting was submitted with HTML in it___ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
