Thanks Misi, you are a founatin of knowledge and I guess that makes sense. It would be one database transaction and everything would need to be rolled back if it failed. I've used it to delete several hundred temporary rows at a time no problem and it is faster than running several hundred updates. I think if, as you say, you need to update maybe thousands of rows it might become a bit impractical. Might be safer in most instances to go the scalable approach and use the individual delete entry.
Of course there are times when it is best to do all the deletes as one transaction as in my situation where a bunch of temporary rows were created and then deleted soon after. Makes sense not to leave any mess behind if there is a problem and by skipping the modify actions you're saving a bit of time and reducing the risk of some unintended cascading modify. (I've seen a bug caused by this in a recent version of well known Remedy app). Most of the time the individual delete is probably the right way to go and have it tiggered by a simple escalation and (whilst still using one thread) split each delete into a separate transaction. Each deleted row should have a delete filter to delete children. Would you then think deleting children by a query delete or push fields update would be the way to go? Either way the delete children would be combined into one transaction. Rod Harris On 12 December 2011 21:42, Misi Mladoniczky <m...@rrr.se> wrote: > Hi, > > I have doe quite some testing with deleting entries. > > The problem with Application-Query-Delete-Entry is that it is treated as > ONE database transaction. That means that the database needs to be able to > roll back the delet, and copies everything to a transaction-space or > something like that. This all makes it choke, if it is too many records. > > Even deleting 100 records at a time may be a problem if you have big > attachments. > > I suggest that you use Application-Delete-Entry directly from your > escalation. > > If you want to delete child records, create a filter executing on Delete, > that calls Application-Query-Delete-Entry. This is the place where I have > found that "function" useful. > > Best Regards - Misi, RRR AB, http://www.rrr.se (ARSList MVP 2011) > > Products from RRR Scandinavia (Best R.O.I. Award at WWRUG10/11): > * RRR|License - Not enough Remedy licenses? Save money by optimizing. > * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. > Find these products, and many free tools and utilities, at http://rrr.se. > > > I doubt too many instructors would be taking you on in argument about > > Remedy these days Misi. They'd have to be game!! > > > > One thing I'd like to add is that all other things being equal I think > the > > query version of delete entry should be a fair bit faster for bulk > > deletes. > > I think if you do have a bunch of records tagged to delete that the first > > match could delete all the other ones in the one go. I doubt this would > be > > as fast as a database level delete as you still have the app server > > overhead and the potential to run "Delete" filters on each delete but I > > think it would be faster than doing a modify and delete on each match. > > > > I still prefer the control that putting all the logic in the one place - > > filters gives. If you use escalations for just identifying data that > needs > > attention then it makes it easier to develop accurate maintainable bug > > free > > code. > > > > Rod Harris > > > > On 12 December 2011 16:23, Misi Mladoniczky <m...@rrr.se> wrote: > > > >> Hi, > >> > >> I did my PTT (Performance Tuning & Troubleshooting class) in 1998, and > >> had > >> an argument with the teacher. > >> > >> I produced some log files to prove my case during class, but she refused > >> to accept that as proof, and I gave up ;-) > >> > >> Best Regards - Misi, RRR AB, http://www.rrr.se (ARSList MVP > 2011) > >> > >> Products from RRR Scandinavia (Best R.O.I. Award at WWRUG10/11): > >> * RRR|License - Not enough Remedy licenses? Save money by optimizing. > >> * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy > >> logs. > >> Find these products, and many free tools and utilities, at > >> http://rrr.se. > >> > >> > That shatters a long standing understanding I had about data driven > >> > escalations. I had received this info at a Remedy training facility in > >> > Bracknell in UK years ago, that you ought to help Escalations with > >> Filters > >> > triggered off modifications by the Escalation User in processing 2nd > >> and > >> > 3rd > >> > stage actions. I guess they were wrong when they instructed us so > >> then.. > >> > > >> > That is one of the benefit that they explained of having Filters > >> running > >> > with a Run If of $USER$ = "AR_ESCALATOR" AND whatever else the rest of > >> the > >> > qualification may be.. The other benefit I'm guessing (I wasn’t told > >> this > >> > but it makes sense) is if you need to override filter phasing during > >> the > >> > run > >> > of an escalation.. > >> > > >> > Joe > >> > > >> > -----Original Message----- > >> > From: Misi Mladoniczky > >> > Sent: Saturday, December 10, 2011 4:04 AM Newsgroups: > >> > public.remedy.arsystem.general > >> > To: arslist@ARSLIST.ORG > >> > Subject: Re: Application-Query-Delete-Entry > >> > > >> > Hi, > >> > > >> > Filters triggered by an Escalation, either via Set-Fields or > >> Push-Fields, > >> > will ALWAYS be run in the same thread. > >> > > >> > This has ALWAYS been the case. > >> > > >> > Best Regards - Misi, RRR AB, http://www.rrr.se (ARSList MVP > >> 2011) > >> > > >> > Products from RRR Scandinavia (Best R.O.I. Award at WWRUG10/11): > >> > * RRR|License - Not enough Remedy licenses? Save money by optimizing. > >> > * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy > >> logs. > >> > Find these products, and many free tools and utilities, at > >> http://rrr.se > >> . > >> > > >> >> Now that you remind me, I actually remember discussing this once with > >> >> you.. > >> >> > >> >> I'll really need to log the workflow to see what thread processes the > >> >> filter > >> >> workflow when filters are executed triggered by the AR_ESCALATOR > >> user. > >> >> > >> >> I was told this in a performance tuning class years ago (version 4.0 > >> - > >> >> 4.5 > >> >> days so probably 11 or 12 years ago) that you let escalations perform > >> >> first > >> >> stage actions, and leave any 2nd and 3rd stage actions (deletes, push > >> >> fields, notifications) to be performed by filters that are run using > >> the > >> >> admin thread. Maybe the design was different back then? So this is > >> >> obsolete > >> >> now? > >> >> > >> >> I wish I had a server to verify this :-) > >> >> > >> >> Joe > >> >> > >> >> -----Original Message----- > >> >> From: LJ LongWing > >> >> Sent: Thursday, December 08, 2011 2:18 PM Newsgroups: > >> >> public.remedy.arsystem.general > >> >> To: arslist@ARSLIST.ORG > >> >> Subject: Re: Application-Query-Delete-Entry > >> >> > >> >> Joe, > >> >> I know this discussion comes up every once in awhile....but you and I > >> >> seem > >> >> to differ in our opinions of how it works. > >> >> > >> >> So...based on your statement below, having the escalation set a > >> field, > >> >> and > >> >> having a filter fire on that field being set, then performing the > >> delete > >> >> will be 'faster' because of a 'fire and forget' type of mechanism? > >> >> > >> >> I would argue that an action of delete within the escalation, and a > >> >> setfield > >> >> causing a filter to fire that causes the delete are 'the same', in > >> that > >> >> the > >> >> escalation thread does not 'go onto the next record' till after the > >> >> filters > >> >> on the current record are done...so, in essence, the performance of > >> >> either > >> >> action would be the same and the escalation thread would still be > >> tied > >> >> up > >> >> for exactly the same amount of time regardless > >> >> > >> >> At least, that's my understanding :) > >> >> > >> >> -----Original Message----- > >> >> From: Action Request System discussion list(ARSList) > >> >> [mailto:arslist@ARSLIST.ORG] On Behalf Of Joe Martin D'Souza > >> >> Sent: Thursday, December 08, 2011 11:33 AM > >> >> To: arslist@ARSLIST.ORG > >> >> Subject: Re: Application-Query-Delete-Entry > >> >> > >> >> End Date as Linda pointed out should be a field on that form you are > >> >> searching for, represented by 'End Date' in the qualification and not > >> >> $End > >> >> Date$.. > >> >> > >> >> That being said, LJ's suggestion is right.. > >> >> > >> >> The qualification should be in the Run If of the Escalation and the > >> run > >> >> process should be > >> >> > >> >> Application-Delete-Entry $SCHEMA$ $Request ID$ > >> >> > >> >> Having an Escalation with no Run If instructs it to be run over the > >> >> entire > >> >> data table. You do not want to do that in case you have like a > >> million > >> >> or > >> >> more records in it.. It may probably hang the escalation thread > >> waiting > >> >> for > >> >> it to complete.. > >> >> > >> >> Also a faster way to do it would be to 'mark that entry for deletion' > >> >> using > >> >> a tag on a field created for that. This would mean that the > >> Escalation > >> >> would > >> >> do a single update to that table which is a faster operation that > >> >> multiple > >> >> deletes and be done with it.. Create a filter that runs if the $USER$ > >> is > >> >> AR_ESCALATOR and the flag for delete is set, to delete that entry. So > >> on > >> >> a > >> >> fairly large set of data, although the deletes are still potentially > >> >> happening triggered by that filter, the escalation thread has already > >> >> finished processing the escalation and is ready to take on a new > >> job.. > >> >> > >> >> Joe > >> >> > >> >> -----Original Message----- > >> >> From: LJ LongWing > >> >> Sent: Thursday, December 08, 2011 12:54 PM Newsgroups: > >> >> public.remedy.arsystem.general > >> >> To: arslist@ARSLIST.ORG > >> >> Subject: Re: Application-Query-Delete-Entry > >> >> > >> >> Larry, > >> >> Your approach is a bit ‘off’. An escalation performs a search that > >> >> matches > >> >> your qualification, and then performs your action on ALL records that > >> >> match > >> >> that qualification. So in this case, I would expect your run-if > >> >> qualification to be > >> >> > >> >> ('Status' = "Past") and ($End Date$ < ($TIMESTAMP$ - (86400 * 180))) > >> >> > >> >> Or, whatever qual you want to identify your specific records, > >> >> > >> >> Then, from there, you will be modifying ‘that’ record…so you wouldn’t > >> >> want > >> >> to then perform an Application-Query-Delete-Entry, you could simply > >> >> perform > >> >> an > >> >> > >> >> Application-Delete-Entry $SCHEMA$ $Request ID$ > >> >> > >> >> > >> >> > >> >> From: Action Request System discussion list(ARSList) > >> >> [mailto:arslist@ARSLIST.ORG] On Behalf Of Larry Barnes > >> >> Sent: Thursday, December 08, 2011 10:23 AM > >> >> To: arslist@ARSLIST.ORG > >> >> Subject: Application-Query-Delete-Entry > >> >> > >> >> ** > >> >> Hello Listers, > >> >> > >> >> I'm trying to learn how to delete records that are past and the End > >> Date > >> >> is > >> >> more than 6 months prior to todays date. I built the escalation and > >> >> when > >> >> I > >> >> run it nothing happens. Can someone point in the right directions > >> with > >> >> the > >> >> Run Process syntax. > >> >> > >> >> I'm using SQL 2008 and Windows 2008. ITSM is 7.5 > >> >> > >> >> The form I'm deleting from is: AP:Alternate > >> >> > >> >> Run IF Qualification is: 'Status' = "Past" (also tried without > >> >> setting > >> >> a Run If Qualification) > >> >> > >> >> Run Process is: Application-Query-Delete-Entry "AP:Alternate" > >> >> ('Status' > >> >> = > >> >> "Past") and ($End Date$ < ($TIMESTAMP$ - (86400 * 180))) > >> >> > >> >> I have also tried: Application-Query-Delete-Entry > >> "AP:Alternate" > >> >> ('Status' = "Past") and ($End Date$ < ($DATE$ - (86400 * 180))) > >> >> > >> >> > >> >> Thanks in advance for your time, > >> >> > >> >> Larry B > >> >> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ > >> >> > >> >> > >> > _______________________________________________________________________________ > >> >> UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > >> >> attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are" > >> >> > >> >> > >> > _______________________________________________________________________________ > >> >> UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > >> >> attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are" > >> >> > >> >> > >> > _______________________________________________________________________________ > >> >> UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > >> >> attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are" > >> >> > >> >> > >> > _______________________________________________________________________________ > >> >> UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > >> >> attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are" > >> >> > >> > > >> > > >> > _______________________________________________________________________________ > >> > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > >> > attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are" > >> > > >> > > >> > _______________________________________________________________________________ > >> > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > >> > attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are" > >> > > >> > >> > >> > _______________________________________________________________________________ > >> UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > >> attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are" > >> > > > > > _______________________________________________________________________________ > > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > > attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are" > > > > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are" > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
