In addition to this, when I was with Column we did a lot of testing of these types of queries against different DB's for performance tuning - so although some people do say that it does not make a difference we did prove that the indexes were skipped in certain instances on different DB's.
When I made my logging application, I added a tab that showed where the qualifications are "backwards" in their logic due to the above. Other third party logging tools also do the same where they indicate that the query "should" be reversed for optimal performance. Always a good rule of thumb to stick to the rule of referencing the external (or non local/remote) table field first in the qualification, although this may be now mitigated in the latest versions of the DB's used [good coding practise] J _____ Kind Regards, Carl Wilson From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Rick Westbrock Sent: 17 October 2014 23:17 To: arslist@ARSLIST.ORG Subject: Re: Set Field If syntax when matching on another form ** Whew, glad it wasn’t just me! I was taught by Sydney Dent probably about ten years ago IIRC. I learned a lot from that class that I have used over the years. It does grate on me every time I see the “backwards” syntax even if it doesn’t affect anything except my brain. Have a great weekend all! -Rick From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Lucero, Michelle Sent: Friday, October 17, 2014 3:09 PM To: arslist@ARSLIST.ORG Subject: Re: Set Field If syntax when matching on another form ** Hi, Everyone: I thought I was the only one who feels automatically driven, like some outside force will punish me if I don’t stick to the ‘Field’ = $Value$ syntax. I can’t write it any other way. I just can’t. Let me try, $Valu…. Nope, can’t do it. It is literally a physical reaction in my gut that won’t let me. With that said, I’m with Carl and Rick. I do believe that in the AR System Performance, Tuning and Troubleshooting 3.x class that we learned to start with remote form.field first and current.value second in qualifications. On page 238 of the manual (copyright 1997 Remedy Corporation), although Set fields aren’t referenced specifically, samples to promote index use are written ‘Field’ = “known value” or ‘Field’ >= value. My two cents, Michelle P.S. To Whomever taught this class in Dallas (Richardson), TX in August 1997 – Thank you. This is by far the best Remedy class, I’ve ever attended. I still use the Performance Tuning tips to this day. I still offset escalation interval times to make sure they’re not a factor of 60. I still use >= to give an index a starting point, etc. From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Rick Westbrock Sent: Friday, October 17, 2014 11:11 AM To: arslist@ARSLIST.ORG Subject: Re: Set Field If syntax when matching on another form ** Thanks for all the feedback. I was on MSSQL back when I learned this but am on Oracle now so I won’t spend the time to change these when I run across them for now. If I have time I might ping BMC Support to see if they have a definitive answer. Carl I like that you reference local and remote forms, that’s how I normally refer to them myself but I was afraid that some people might think I meant a form on another server. -Rick From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Carl Wilson Sent: Friday, October 17, 2014 9:04 AM To: arslist@ARSLIST.ORG Subject: Re: Set Field If syntax when matching on another form ** Hi, Depending on the Database used, referencing the local field first (and not the remote table field) will cause the Database to skip the use of the indexes on the remote table and thus cause performance issues with the query. This was mainly present in MS SQL as opposed to Oracle from memory. _____ Kind Regards, Carl Wilson From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Thad Esser Sent: 17 October 2014 16:34 To: arslist@ARSLIST.ORG Subject: Re: Set Field If syntax when matching on another form ** Same here, 'Field' = $Value$ is what I use, but I'm not really sure why. As Sonny from "I, Robot" (the movie) says, "It just feels wrong." :-) If the $Value$ were null, does that mess with things at all? You'd essentially be saying: where null = 'Field' . I'd assume that was handled or there'd be more issues. Just a thought. Thad On Fri, Oct 17, 2014 at 8:11 AM, LJ LongWing <lj.longw...@gmail.com> wrote: ** Rick, This is one of my personal pet peeves. I started learning database and Remedy around the same time, and all of the instruction that I ever received in the database, and what makes most sense to me is field = value This translates in a remedy world to 'Field' = $Value$ So, from this convention, I always use it as you state you prefer. Over the years, I have found TONS of different contractors that come and go use $Value$ = 'Field' and while I don't believe it causes any performance impact in any way to do it that way, it's visually unappealing to me...it took me awhile, but I eventually figured out WHY some developers produce code that looks like that. It has to do how things show up in the drop down in the Dev Studio. When building a setfield action, or push for that matter I think, when selecting fields, 'Current Form' is always the 'default', and current form is the one that produces $Value$, where 'form that it's being selected from' is not the default....so, what you get is the developer selecting the field on the current form first, and the 'other' form second, which builds a $Value$ = 'Field' type of qualification....so, while I don't think it causes any problems, it's certainly not visually appealing to me...and I agree with you that it 'should be' the other way. On Fri, Oct 17, 2014 at 8:34 AM, Rick Westbrock <rwestbr...@24hourfit.com> wrote: ** Hi all- I wanted to get feedback on something I remember learning way back in a Remedy class (pre-BMC) in Pleasanton to make sure that I am remembering correctly. I seem to recall that I was told that the best practice when writing the qualification for a Set Fields action from another form that you should use the other form’s field before the operator and the current form’s field after the operator like this: 'DEPTID' = $DeptID-New$ I thought that this was either more efficient or provided more predictable results (or maybe both). Does anyone else follow this convention? I have run across several instances in some custom workflow where that is reversed and I see $DeptID-New$ = 'DEPTID' which just feels wrong to me. Below is more detail to clarify my examples. Form A: field DeptID-New Form B: field DEPTID There is a filter on form A that does a set fields which reads its value from form B. I believe the proper syntax if I want to match the department ID fields between the forms is this: 'DEPTID' = $DeptID-New$ >From there the set fields action can copy values from the matching record in >form B into the fields of form A. Thanks, Rick _________________________ Rick Westbrock AppOps Engineer | IT Department 24 Hour Fitness USA, Inc. _ARSlist: "Where the Answers Are" and have been for 20 years_ _ARSlist: "Where the Answers Are" and have been for 20 years_ _ARSlist: "Where the Answers Are" and have been for 20 years_ _ARSlist: "Where the Answers Are" and have been for 20 years_ _ARSlist: "Where the Answers Are" and have been for 20 years_ _____ This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer. If you are not the intended recipient, please delete this message. _ARSlist: "Where the Answers Are" and have been for 20 years_ _ARSlist: "Where the Answers Are" and have been for 20 years_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
