When ARS performs data interaction with a Join form, it simply issues the appropriate SQL against the SQL View it created to represent the Join (named T<schema id of the join>). In situations like yours, that SQL View itself references other SQL Views representing the lower level joins, and so on. So, what's really happening is that the DB optimizer must string all this stuff together and compile the result before execution. Fortunately, this process is O(N), where N is the total number of joins, so itself is not very bad at all.
The resulting SQL is similar to what you might create yourself if you had to write the SQL to join all those forms (tables) together, though perhaps not as well optimized as it could be. Obviously, the more tables involved in a query, the more complex it is and the longer it takes the database to compute. However, if this level of complexity is what is needed to get the result you need, then it's what you need. So, the point of all this is that all the complexity of pulling data from such a Join is completely managed by the database itself, and not by ARS (if you're pushing data into this thing...ugh...that's a different story). I'm not sure why BMC mentions that practical limit, though I would suspect it has to do with the amount of work YOU have to do to maintain all those Joins (more on this below). I agree with Shawn, though: if you're concerned it's well worth your time to understand the execution path being used by your queries to this thing, and whether or not your indexes are being used correctly. I've long since moved away from using ARS Join forms, and instead exclusively use (and recommend) manually created SQL View on top of which I create ARS View forms. One immediate advantage for this particular example is that instead of dealing with 9 ARS Join forms (for your 10 base forms), you only need to deal with one View type form. Another advantage is that you can structure the SQL to make maximum use of available indexes (in Oracle, for example, you absolutely CAN include HINTs in the SQL definition of the view to direct the optimizer to appropriately leverage the available indexes). A third advantage is the reduced maintenance. For example, let me assume that in your example you first joined Form1 to Form 2, then joined that to Form 3, and so on. Now, with the ARS Join based solution, if you want to add a field from Form 1 you have to separately modify each of the 9 Joins (this may be why BMC suggests that practical limit of 6). With the SQL View-Remedy View solution, you first modify the SQL view (to include the column), then modify the ARS View form to add the field on top of the column. A fourth advantage is the ability to insert computed columns. For example, ARS Table fields of course only allow you to select one Selection type field on which to base row coloring. However, it may be the case that the coloring scheme you wish to apply is more complex, and is based on factors from some combination of fields (or even from foreign fields). If so, your only pure ARS based solution is to create a real (i.e. Optional or Required) Selection field, and maintain its value through workflow. This can get VERY complicated. In the SQL View/ARS View, the solution is very simple: just create a CASE statement in the SQL View and in the ARS View, create a Selection field on top of it. In this paradigm, the ColorStatus (which is what I always call the column & field) can be arbitrarily complex, yet simple to implement and maintain. Of course, one trade off is that this type of solution is a bit more obscure than the Join based solution (because the SQL View is not visible from within Dev Studio). It also of course requires SQL knowledge, though personally I feel this is an essential skill in Remedy application development. And finally, it requires some understanding of of the ARS data dictionary (at least enough to know how to identify and interact with the SQL Views ARS creates for such use (NOT the T/B/SH tables and views, but the views it creates using your form and field names). But I've been doing this now for many years and find this solution far more manageable. Anyway, my 2 cents. Hope something in here helps. -charlie On Fri, Jan 3, 2014 at 11:30 AM, Pierson, Shawn < shawn.pier...@energytransfer.com> wrote: > ** > > I’d work with the DBAs to run sql profiler or something while running > queries against that table so they could make the argument for you with > actual statistics rather than hypotheticals. > > > > Thanks, > > > > *Shawn Pierson * > > Remedy Developer | Energy Transfer > > > > *From:* Action Request System discussion list(ARSList) [mailto: > arslist@ARSLIST.ORG] *On Behalf Of *Rick Cook > *Sent:* Friday, January 03, 2014 11:58 AM > *To:* arslist@ARSLIST.ORG > *Subject:* Multiple level join forms > > > > ** > > We have a custom application that has a TEN layers deep join form. > Something like > Company:XXXX:YYY:Form1-Form2-Form3-Form4-Form5-Form6-Form7-Form8-Form9-Form10_join. > > BMC documentation (for 7.5) says that six is the practical limit for join > levels. > > There are a large number of rows in this form, and the form is about > average size (80~ columns). I didn't build it, but I'm trying to get the > people who did to understand why this is a bad idea, beyond "It's a > performance impacting design". > > Can someone share their experiences with what happens beyond the > BMC-specified "event horizon"? I'm guessing that the searches increase > exponentially (or at least logarithmically) in time as the complexity > increases. The two base forms are pretty well-indexed - about six each, I > think. > > Rick Cook > > _ARSlist: "Where the Answers Are" and have been for 20 years_ > Private and confidential as detailed > here<http://www.energytransfer.com/mail_disclaimer.aspx>. > If you cannot access hyperlink, please e-mail sender. > _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"
