Axton,
Thank you for your response...
Yes I was aware of that, in fact I have proposed to keep the original join lets say T360 as T360_ORIG, keep a copy of the modified as T360_MOD and then recreate T360 with the UNION. And whenever any modifications are done to any of the primary form or the secondary form, drop T360 and recreate it by copying T360_MOD as T360..
What I had also proposed is whenever they need to upgrade the arsystem server, to replace the T360 that had been modified by the T360_ORIG just in case the upgrade script bombs out for any reason if in case it does a check for certain contents of the join definition.
None of the fields that are included in the join would be modified or deleted so we would not really need to modify the sql to create the join view..
Thats our game plan.. just wondering if we can pull it off...
Rgds
Joe D'Souza
Remedy Developer / Consultant,
BearingPoint,
Virginia.
----- Original Message ----
From: Axton <[EMAIL PROTECTED]>
To: arslist@ARSLIST.ORG
Sent: Tuesday, October 17, 2006 12:26:17 PM
Subject: Re: Remedy and UNION joins..
From: Axton <[EMAIL PROTECTED]>
To: arslist@ARSLIST.ORG
Sent: Tuesday, October 17, 2006 12:26:17 PM
Subject: Re: Remedy and UNION joins..
If you want to use union joins, create a db view of your own and define the join. Point a view form that that db view. I would strongly recommend against modifying the Txxx view to accomodate your requirements becuase the first time someone touches the base forms or join form, your changes will be lost.
Axton Grams
On 10/17/06, Joe DeSouza <[EMAIL PROTECTED]> wrote:
> **
>
> Hello Listers,
>
> I am attempting to do something (which I know is unsupported) and I wonder
> if anyone of you have done that before..
>
> Setup:
> My customer here is using Remedy Customer Support 5.x.
>
> They are on ARS 6.3 patch 16 on Sun OS version 5.9 and using Sybase as the
> backend database version 12.5.0.3/EBF 11331 ESD#3 and Char set ISO_1.
>
> Problem and Proposed Workaround:
> There is a OTB join between two forms SHARE:Association and
> SHARE:Attachment, that has two indexed fields instanceId1 and instanceId2.
> These forms are the base forms of a inner join SHARE:AssocAttachment_join
> where SHARE:Association is the Primary form and SHARE:Attachment is the
> secondary form. The join criteria is:
> ($instanceId1$ = 'instanceId') OR ($instanceId2$ = 'instanceId')
>
> Both instanceId1 and instanceId2 are indexed on two seperate indexes and
> exist on the SHARE:Association form AND instanceId is indexed on the
> secondary form SHARE:Attachment.
>
> A search on this join uses a table scan on both the tables instead of using
> the indexes and using a index scan because an OR is used in the criteria to
> create that join.
>
> If this join were a UNION (OR) join, we noticed that it would use the
> indexes instead of doing a table scan and the results are returned much
> faster than the default OTB join - we tested this at DB level - not through
> the application...
>
> So what if we were to modify the join definition internally in the database
> on the T table that belongs to this join? And this modified join was a UNION
> join.?? Would it work after restarting the AR Server to re-read that
> definition?
>
> I'm wondering if anyone of you have attempted this? What would the results
> be? Would it work? If it does work, anything I got to be careful of? Maybe
> restore the original join during an ARSystem upgrade???
>
> The reason we are doing this is that these kind of joins are causing the CSS
> application to run extremely slow on tables where there is a row count of
> more than 100K to perform queries on these tables. On one of the table where
> there is a row count of 100K it takes upto 11 minutes at times to return the
> results, while if we used a UNION join the same result is returned in a
> second or 2...
>
> It would be nice to get a feeler on this if anyone has attempted this
> before...
>
> Cheers
>
> Joe D'Souza
> Remedy Developer / Consultant,
> Shyle Networks,
> New Jersey.
Axton Grams
On 10/17/06, Joe DeSouza <[EMAIL PROTECTED]> wrote:
> **
>
> Hello Listers,
>
> I am attempting to do something (which I know is unsupported) and I wonder
> if anyone of you have done that before..
>
> Setup:
> My customer here is using Remedy Customer Support 5.x.
>
> They are on ARS 6.3 patch 16 on Sun OS version 5.9 and using Sybase as the
> backend database version 12.5.0.3/EBF 11331 ESD#3 and Char set ISO_1.
>
> Problem and Proposed Workaround:
> There is a OTB join between two forms SHARE:Association and
> SHARE:Attachment, that has two indexed fields instanceId1 and instanceId2.
> These forms are the base forms of a inner join SHARE:AssocAttachment_join
> where SHARE:Association is the Primary form and SHARE:Attachment is the
> secondary form. The join criteria is:
> ($instanceId1$ = 'instanceId') OR ($instanceId2$ = 'instanceId')
>
> Both instanceId1 and instanceId2 are indexed on two seperate indexes and
> exist on the SHARE:Association form AND instanceId is indexed on the
> secondary form SHARE:Attachment.
>
> A search on this join uses a table scan on both the tables instead of using
> the indexes and using a index scan because an OR is used in the criteria to
> create that join.
>
> If this join were a UNION (OR) join, we noticed that it would use the
> indexes instead of doing a table scan and the results are returned much
> faster than the default OTB join - we tested this at DB level - not through
> the application...
>
> So what if we were to modify the join definition internally in the database
> on the T table that belongs to this join? And this modified join was a UNION
> join.?? Would it work after restarting the AR Server to re-read that
> definition?
>
> I'm wondering if anyone of you have attempted this? What would the results
> be? Would it work? If it does work, anything I got to be careful of? Maybe
> restore the original join during an ARSystem upgrade???
>
> The reason we are doing this is that these kind of joins are causing the CSS
> application to run extremely slow on tables where there is a row count of
> more than 100K to perform queries on these tables. On one of the table where
> there is a row count of 100K it takes upto 11 minutes at times to return the
> results, while if we used a UNION join the same result is returned in a
> second or 2...
>
> It would be nice to get a feeler on this if anyone has attempted this
> before...
>
> Cheers
>
> Joe D'Souza
> Remedy Developer / Consultant,
> Shyle Networks,
> New Jersey.
__20060125_______________________This posting was submitted with HTML in it___
