Ah, you're right. If I do not modify the field in question, I see the select with 'Assigned To' != $NULL$. I suppose this kind of defeats the performance purposes of using TR, unless you use it for everything.
+SE ARSetEntry -- schema zBug_TableDrag-SampleData entryId 00 SELECT C1,C2,C3,C4,C5,C6,C7,C8,0 FROM T1160 WHERE C1 = '000000000 OK SELECT entryId,T0,U0,T1,U1,T2,U2,T3,U3,T4,U4 FROM H1160 WHERE ent OK UPDATE T1160 SET C8='6',C5='AGADMIN',C6=1191527349 WHERE C1 = '00 OK COMMIT WORK -SE OK +GE ARGetEntry -- schema zBug_TableDrag-SampleData entryId 00 SELECT C1,C2,C3,C4,C5,C6,C7,C8,0 FROM T1160 WHERE C1 = '000000000 OK SELECT entryId,T0,U0,T1,U1,T2,U2,T3,U3,T4,U4 FROM H1160 WHERE ent OK -GE OK On 10/4/07, Misi Mladoniczky <[EMAIL PROTECTED]> wrote: > Hi Axton, > > The difference you did compared to me, was that you changed the value of > the field in question. This, I think, made the server skip the fetch. > > The DB-value, of course, allways needs to be fetched from the database. > > Try again, but change some other field. > > I did the same thing, but modified data of another field, and got the > following result with a filter run-of of ('Assigned To' = $NULL$): > http://www.rrr.se/tmp/TestAssignedToMostCurrent.html > > Here is the version where I actually changed the Assigned To field in the > client before saving. It is now in the transaction, and the ('Assigned To' > = $NULL$) has no need to go to the database: > http://www.rrr.se/tmp/TestAssignedToMostCurrent2.html > > It makes sence. > > You learn something new every day ;-) > > Best Regards - Misi, RRR AB, http://www.rrr.se > > Products from RRR Scandinavia: > * RRR|License - Not enough Remedy licenses? Save money by optimizing. > * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. > * RRR|Translator - Manage and automate your language translations. > Find these products, and many free tools and utilities, at http://rrr.se. > > > I only see the db call if using DB. I chopped the log lines so they > > are readable in most mail readers. > > > > ** On modify with qual: 'Assigned To' != $NULL$ > > > > +SE ARSetEntry -- schema zBug_TableDrag-SampleData entryId 00 > > UPDATE T1160 SET C4='aasdfaaaaaaa',C5='AGADMIN',C6=1191521727 WHE > > OK > > COMMIT WORK > > -SE OK > > +GE ARGetEntry -- schema zBug_TableDrag-SampleData entryId 00 > > SELECT C1,C2,C3,C4,C5,C6,C7,C8,0 FROM T1160 WHERE C1 = '000000000 > > OK > > SELECT entryId,T0,U0,T1,U1,T2,U2,T3,U3,T4,U4 FROM H1160 WHERE ent > > OK > > -GE OK > > > > > > ** On modify with qual: 'DB.Assigned To' != $NULL$ > > > > +SE ARSetEntry -- schema zBug_TableDrag-SampleData entryId 00 > > SELECT C1,C2,C3,C4,C5,C6,C7,C8,0 FROM T1160 WHERE C1 = '000000000 > > OK > > SELECT entryId,T0,U0,T1,U1,T2,U2,T3,U3,T4,U4 FROM H1160 WHERE ent > > OK > > UPDATE T1160 SET C4='aaasdfaaaaaaa',C5='AGADMIN',C6=1191521782 WH > > OK > > COMMIT WORK > > -SE OK > > +GE ARGetEntry -- schema zBug_TableDrag-SampleData entryId 00 > > SELECT C1,C2,C3,C4,C5,C6,C7,C8,0 FROM T1160 WHERE C1 = '000000000 > > OK > > SELECT entryId,T0,U0,T1,U1,T2,U2,T3,U3,T4,U4 FROM H1160 WHERE ent > > OK > > -GE OK > > > > > > ** On modify with qual: 'TR.Assigned To' != $NULL$ > > > > +SE ARSetEntry -- schema zBug_TableDrag-SampleData entryId 00 > > UPDATE T1160 SET C4='aaasdfaaaaaaaa',C5='AGADMIN',C6=1191521833 W > > OK > > COMMIT WORK > > -SE OK > > +GE ARGetEntry -- schema zBug_TableDrag-SampleData entryId 00 > > SELECT C1,C2,C3,C4,C5,C6,C7,C8,0 FROM T1160 WHERE C1 = '000000000 > > OK > > SELECT entryId,T0,U0,T1,U1,T2,U2,T3,U3,T4,U4 FROM H1160 WHERE ent > > OK > > -GE OK > > > > Axton Grams > > > > On 10/4/07, Misi Mladoniczky <[EMAIL PROTECTED]> wrote: > >> Hi, > >> > >> As the logs show in my 7.0.1 examples, this is usually true. > >> > >> The only time it is not true is when no filter at all uses > >> 'DB.Field'-syntax or 'Field'-syntax. If this is the case, no fetch is > >> made, as it is not neccessary. > >> > >> You could argue that it the code should not retrieve more columns than > >> neccessary, but they apparently decided to simplify things. It is better > >> to get all fields from the database, even if only one is needed, as it > >> is > >> likely that more fields are required later. > >> > >> The other time, where I think the system refrains from the fetch, is > >> when > >> you have an early filter triggering on ('TR.Goto1000' = "Yes"), and then > >> makes a GOTO 1000 action that skips the rest of the filters. > >> > >> The fetch appears to be done when the first filter is run where the > >> DB-data is needed: http://www.rrr.se/tmp/TestAssignedToMostCurrent.html > >> > >> Best Regards - Misi, RRR AB, http://www.rrr.se > >> > >> Products from RRR Scandinavia: > >> * RRR|License - Not enough Remedy licenses? Save money by optimizing. > >> * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy > >> logs. > >> * RRR|Translator - Manage and automate your language translations. > >> Find these products, and many free tools and utilities, at > >> http://rrr.se. > >> > >> > As I recall from Lenny's Performance Tuning class this summer that > >> > nothing on the Run If of a Filter causes an additional query of the > >> > database. The database record is retrieved at the start of the > >> > processing and used for comparison throughout the process. > >> > > >> > This is just for the Run If...the action items inside the filters can > >> > and do perform additional queries of the database. > >> > > >> > Stephen > >> > Remedy Skilled Professional > >> > > >> > -----Original Message----- > >> > From: Action Request System discussion list(ARSList) > >> > [mailto:[EMAIL PROTECTED] On Behalf Of Axton > >> > Sent: Thursday, October 04, 2007 12:43 PM > >> > To: arslist@ARSLIST.ORG > >> > Subject: Re: Filter Problem > >> > > >> > A flaw, maybe. The solutions I see to address this would be some type > >> > of record locking to prevent updates based on stale data. I guess > >> > there could be a couple of approaches to this: > >> > - return an error if you attempt to update and the data you are using > >> > for the update is stale > >> > - disallow the retrieval of records for update if someone else has a > >> > lock > >> > - return a note that the record was updated by another user > >> > > >> > One has to weigh the consequences of implementing something to address > >> > this behavior against the consequences of not implementing something > >> > to address it. The third, obviously, is what is currently > >> > implemented. > >> > > >> > I have to correct myself on one item. It appears Misi was right that > >> > the entire record is fetched during each ARSetEntry call. Ah well, > >> > something changed when I wasn't looking. > >> > > >> > Axton Grams > >> > > >> > On 10/4/07, Joe D'Souza <[EMAIL PROTECTED]> wrote: > >> >> ** > >> >> > >> >> That's what I thought that it should perform a fetch for DB values > >> > too. The > >> >> DB value could be changed by another user after the initial fetch of > >> > the > >> >> record, hence what displays on the client tool after the initial > >> fetch > >> > of > >> >> the record, may not represent the true DB value. > >> >> > >> >> I wonder though what would happen if lets say the value of a field is > >> > "XYZ". > >> >> User 1 opens the record.. > >> >> > >> >> At about the same time User 2 opens the same record, and changes the > >> > value > >> >> of "XYZ" to "abc" and performs the save. > >> >> > >> >> User 1 then changes what he sees on his screen "XYZ" to "abc". > >> > Considering > >> >> that the DB value now is already "abc" due to the change done earlier > >> > by > >> >> User 2, would the TR value now be NULL? Or would it be "abc"? > >> >> > >> >> From your conclusion based on the SQL logs that TR doesn't perform a > >> > fetch, > >> >> the system would not know that the value has changed. Isn't that a > >> > flaw in > >> >> the design? > >> >> > >> >> > >> >> Joe D'Souza > >> >> > >> >> > >> >> -----Original Message----- > >> >> From: Action Request System discussion list(ARSList) > >> >> [mailto:[EMAIL PROTECTED] Behalf Of Misi Mladoniczky > >> >> Sent: Thursday, October 04, 2007 6:31 AM > >> >> To: arslist@ARSLIST.ORG > >> >> Subject: Re: Filter Problem > >> >> > >> >> > >> >> Hi, > >> >> > >> >> You are correct in the assumption that TR-values can save a little > >> bit > >> > on > >> >> performance. > >> >> > >> >> The server looks through the run-if-qualification of all filters. > >> >> > >> >> If anywhere there is a 'Field' or 'DB.Field', the server will need > >> > fetch > >> >> these values from the database. And the values of all other fields of > >> > the > >> >> form as well! > >> >> > >> >> If you only have 'TR.Field', static values or keywords. This fetch is > >> > not > >> >> needed. > >> >> > >> >> Note that my test form has a lot of fields, and only two filters. One > >> > is > >> >> disabled and one is enabled in the samples below: > >> >> > >> >> ('TR.Assigned To' != $NULL$) > >> >> http://www.rrr.se/tmp/TestAssignedToTR.html > >> >> > >> >> ('Assigned To' != 'DB.Assigned To' AND 'Assigned To' != $NULL$) > >> >> http://www.rrr.se/tmp/TestAssignedToDB.html > >> >> > >> >> All fields will be fetched as soon as you have one 'Field' or > >> > 'DB.Field' in > >> >> your filters, so adding fields will do nothing to performance. > >> >> > >> >> I have noticed this behaviour in previous versions. The verion I am > >> > testing > >> >> is version 7.0.1 patch 003. > >> >> > >> >> I think that the TR-values can have a legitimate use sometimes. > >> Either > >> > for > >> >> the reason of performance, or the reason of simplifying your run-ifs: > >> >> > >> >> Example 1: ('TR.CustID' != $NULL$) > >> >> Your filter makes sure that the CustName is in sync with your CustID. > >> > It > >> >> does not matter much if this filter sometimes run when it is not > >> > really > >> >> neccessary. > >> >> > >> >> Example 2: ('TR.Assigned To' != $NULL$ AND 'TR.Assigned To' != > >> $USER$) > >> >> Your filter sends a notification to the assigned person. If the field > >> > has > >> >> not really been changed, an extra notification is sent, but does no > >> > real > >> >> damage. > >> >> > >> >> Example 3: ('TR.Status' != $NULL$) > >> >> Your filter controls something or other connected with the status. > >> > Extra > >> >> controls may happen, but does not affect things much. > >> >> > >> >> Best Regards - Misi, RRR AB, http://www.rrr.se > >> >> > >> >> Products from RRR Scandinavia: > >> >> * RRR|License - Not enough Remedy licenses? Save money by optimizing. > >> >> * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy > >> > logs. > >> >> * RRR|Translator - Manage and automate your language translations. > >> >> Find these products, and many free tools and utilities, at > >> > http://rrr.se. > >> >> > >> >> > While the TR is confusing isn't there a performance benefit? If I > >> > remember > >> >> > correctly a TR check will not run a query against the database (I > >> > haven't > >> >> > actually logged it to verify). > >> >> > > >> >> > Say you run a daily import of 500k employees (the requirement is to > >> > do it > >> >> > during the business hours for whatever reason) and are looking to > >> > see if > >> >> > their employment status has changed, if so create a request to take > >> >> > action. > >> >> > > >> >> > The import will perform an initial query the DB to find the > >> existing > >> >> > record > >> >> > to update but then you have a filter that runs if 'EMP_Status' != > >> >> > 'DB.EMP_Status' (a required field). Wouldn't that cause an > >> > additional > >> >> > query > >> >> > on the database to retrieve the record you just retrieved? Using > >> >> > 'EMP_Status' != 'TR.EMP_Status' would check the server's memory if > >> > the > >> >> > value > >> >> > has changed and eliminate a redundant trip to the DB. In this case > >> > using a > >> >> > TR would eliminate 500k less queries to the database. During > >> > business > >> >> > hours > >> >> > that may be a huge savings (so I added the business hours part to > >> > help my > >> >> > example). > >> >> > > >> >> > Is my scenario correct? > >> >> > > >> >> > Jason > >> >> > > >> >> > -----Original Message----- > >> >> > From: Action Request System discussion list(ARSList) > >> >> > [mailto:[EMAIL PROTECTED] On Behalf Of Rabi Tripathi > >> >> > Sent: Wednesday, October 03, 2007 6:09 PM > >> >> > To: arslist@ARSLIST.ORG > >> >> > Subject: Re: Filter Problem > >> >> > > >> >> > Thad, I'm glad there's at least one person in the > >> >> > world who has settled on the same rule regarding use > >> >> > of TR values. Don't! > >> >> > > >> >> > Cause it's not necessary. There's no qualification you > >> >> > can't build without using TR. It's nothing but > >> >> > trouble. > >> >> > > >> >> > TR values tend to confuse people, as is evident from > >> >> > this thread. Even if you completely understand the > >> >> > nuances, there are many instances (many ways data can > >> >> > change) in which TR value will not be what you think. > >> >> > > >> >> > TR values give you what you expect only if the update > >> >> > is being done in Remedy user from a record that is "on > >> >> > display". If the update is being done from a dialog > >> >> > (common in ITSM 6), or if workflow is doing a query to > >> >> > get values and then pushing it back, use of TR values > >> >> > will drive you crazy. Why do you want to be driven > >> >> > crazy, I ask. There are enough opportunities to be > >> >> > driven crazy other ways. > >> >> > > >> >> > It has been around 5 years since I stopped using TR > >> >> > values. > >> >> > > >> >> > In with 'Field Name' != 'DB.Field Name' > >> >> > > >> >> > In with 'Field Name' != 'DB.Field Name' AND 'Field > >> >> > Name'!= $NULL$ > >> >> > > >> >> > Out with 'TR.Field Name' = $NULL$ (which misses > >> >> > blanking out of data anyway) > >> >> > > >> >> > --- Thad K Esser <[EMAIL PROTECTED]> wrote: > >> >> > > >> >> >> Also, it could have a value if the record update is > >> >> >> the result of a push > >> >> >> fields from somewhere else. One way to get a handle > >> >> >> on this is to run a > >> >> >> short SQL log and find the update statements. If a > >> >> >> field is included in > >> >> >> the SET clause, the "TR" concept applies, whatever > >> >> >> the actual value. > >> >> >> > >> >> >> My two rules for TR and DB values: > >> >> >> 1. Don't use TR values (DB values are > >> >> >> useful & good). > >> >> >> 2. If you need to use a TR value, see rule > >> >> >> #1. > >> >> >> > >> >> >> Thad Esser > >> >> >> Remedy Developer > >> >> >> "Argue for your limitations, and sure enough, > >> >> >> they're yours."-- Richard > >> >> >> Bach > >> >> >> > >> >> >> > >> >> >> > >> >> >> "Mayfield, Andy L." <[EMAIL PROTECTED]> > >> >> >> Sent by: "Action Request System discussion > >> >> >> list(ARSList)" > >> >> >> <arslist@ARSLIST.ORG> > >> >> >> 10/03/2007 07:55 AM > >> >> >> Please respond to > >> >> >> arslist@ARSLIST.ORG > >> >> >> > >> >> >> > >> >> >> To > >> >> >> arslist@ARSLIST.ORG > >> >> >> cc > >> >> >> > >> >> >> Subject > >> >> >> Re: Filter Problem > >> >> >> > >> >> >> > >> >> >> > >> >> >> > >> >> >> > >> >> >> > >> >> >> OK, I think after a few cups of coffee I am now able > >> >> >> to comprehend this. > >> >> >> > >> >> >> TR.Field != $NULL$ should fire anytime the field is > >> >> >> changed, unless it > >> >> >> is actually set to NULL. > >> >> >> > >> >> >> That is rather confusing to simple minded folk such > >> >> >> as myself (-: > >> >> >> > >> >> >> Andy L. Mayfield > >> >> >> Sr. System Operation Specialist > >> >> >> Alabama Power Company > >> >> >> Office: 8-226-1805 > >> >> >> > >> >> >> > >> >> >> -----Original Message----- > >> >> >> From: Action Request System discussion list(ARSList) > >> >> >> [mailto:[EMAIL PROTECTED] On Behalf Of Shellman, > >> >> >> David > >> >> >> Sent: Wednesday, October 03, 2007 9:16 AM > >> >> >> To: arslist@ARSLIST.ORG > >> >> >> Subject: Re: Filter Problem > >> >> >> > >> >> >> Andy, > >> >> >> > >> >> >> As Axton said TR.field really is the value of the > >> >> >> transaction. > >> >> >> > >> >> >> If a field doesn't change then the TR.field is null. > >> >> >> There isn't a > >> >> >> transaction because the DB.field and field are the > >> >> >> same. > >> >> >> > >> >> >> If a field changes TR.field evaluates to what was > >> >> >> just entered. So if > >> >> >> you change a field from Hello World to Good Morning, > >> >> >> TR.field will > >> >> >> evaluate to Good Morning, DB.field evaluates to > >> >> >> Hello World, and the > >> >> >> field evaluates to Good Morning. > >> >> >> > >> >> >> The tricky one is where the field was Hello World > >> >> >> but is now set to > >> >> >> NULL. The DB.field is Hello World and the field > >> >> >> value is NULL. Because > >> >> >> there is a change there is a transaction but the > >> >> >> TR.field evaluates to > >> >> >> NULL because the field is actually set to NULL. > >> >> >> > >> >> >> Dave > >> >> >> > >> >> >> -----Original Message----- > >> >> >> From: Action Request System discussion list(ARSList) > >> >> >> [mailto:[EMAIL PROTECTED] On Behalf Of Mayfield, > >> >> >> Andy L. > >> >> >> Sent: Wednesday, October 03, 2007 10:06 AM > >> >> >> To: arslist@ARSLIST.ORG > >> >> >> Subject: Re: Filter Problem > >> >> >> > >> >> >> I thought everyone was saying that if I set a field > >> >> >> to NULL (that > >> >> >> previously had a value), the TR value would NOT be > >> >> >> NULL? > >> >> >> > >> >> >> I am getting confused. I may need to take a break > >> >> >> for coffee and a > >> >> >> cigarette to clear my head (-: > >> >> >> > >> >> >> >Andy L. Mayfield > >> >> >> Sr. System Operation Specialist > >> >> >> >Alabama Power Company > >> >> >> Office: 8-226-1805 > >> >> >> > >> >> >> > >> >> >> -----Original Message----- > >> >> >> From: Action Request System discussion list(ARSList) > >> >> >> [mailto:[EMAIL PROTECTED] On Behalf Of Axton > >> >> >> Sent: Wednesday, October 03, 2007 8:41 AM > >> >> >> To: arslist@ARSLIST.ORG > >> >> >> Subject: Re: Filter Problem > >> >> >> > >> >> >> This one that probably often trips people up; if you > >> >> >> set the field to > >> >> >> null, the TR value will be null. > >> >> >> > >> >> >> The TR value represent the current transactions > >> >> >> value, if no change > >> >> >> occurred in the transaction, then the TR value is > >> >> >> null > >> >> >> > >> >> >> The DB value represents what was last committed to > >> >> >> the database; prior > >> >> >> to the current transaction > >> >> >> > >> >> >> The value represents the current value, regardless > >> >> >> of the TR or DB > >> >> >> values > >> >> >> > >> >> >> Axton Grams > >> >> >> > >> >> >> On 10/3/07, Mayfield, Andy L. > >> >> >> <[EMAIL PROTECTED]> wrote: > >> >> >> > ** > >> >> >> > > >> >> >> > > >> >> >> > > >> >> >> > OK, I thought I had this figured. So if while > >> >> >> modifying a ticket, you > >> >> >> > change a field that previously had a value, to > >> >> >> NULL (blank) then the > >> >> >> TR > >> >> >> > value is NOT NULL. > >> >> >> > > >> >> >> > > >> >> >> > > >> >> >> > I need more coffee. > >> >> >> > > >> >> >> > > >> >> >> > > >> >> >> > > >> >> >> > Andy L. Mayfield > >> >> >> > Sr. System Operation Specialist > >> >> >> > Alabama Power Company > >> >> >> > Office: 8-226-1805 > >> >> >> > > >> >> >> > ________________________________ > >> >> >> > > >> >> >> > > >> >> >> > From: Action Request System discussion > >> >> >> list(ARSList) > >> >> >> > [mailto:[EMAIL PROTECTED] On Behalf Of Joe > >> >> >> D'Souza > >> >> >> > Sent: Tuesday, October 02, 2007 10:57 PM > >> >> >> > > >> >> >> > To: arslist@ARSLIST.ORG > >> >> >> > Subject: Re: Filter Problem > >> >> >> > > >> >> >> > > >> >> >> > > >> >> >> > > >> >> >> > > >> >> >> > Yes the TR value is NULL if there is no > >> >> >> modification even if there is > >> >> >> a DB > >> >> >> > value to that field.. > >> >> >> > > >> >> >> > > >> >> >> > > >> >> >> > > >> >> >> > > >> >> >> > This is how it works.. when you are submitting a > >> >> >> new record, and if > >> >> >> there is > >> >> >> > a value in the field, then the TR value is not > >> >> >> NULL (obviously). The > >> >> >> DB > >> >> >> > >> >> > === message truncated === > >> >> __20060125_______________________This posting was > >> >> submitted with HTML in it___ > >> > > >> > ________________________________________________________________________ > >> > _______ > >> > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > >> ARSlist:"Where > >> > the Answers Are" > >> > > >> > _______________________________________________________________________________ > >> > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > >> ARSlist:"Where > >> > the Answers Are" > >> > > >> > >> _______________________________________________________________________________ > >> UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where > >> the Answers Are" > >> > > > > _______________________________________________________________________________ > > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where > > the Answers Are" > > > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the > Answers Are" > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"