Interesting idea...
Haven't tried that.. Didn't think of that as this happens to be an OTB criteria so we went along the lines that the type of join that it creates OTB was the limitation and that a UNION had to be used instead...
I'll try that and see if that makes a difference..
Joe D'Souza
Remedy Developer / Consultant,
Shyle Networks,
New Jersey.
----- Original Message ----
From: Sabyson Fernandes <[EMAIL PROTECTED]>
To: [email protected]
Sent: Tuesday, October 17, 2006 2:11:25 PM
Subject: Re: Remedy and UNION joins..
From: Sabyson Fernandes <[EMAIL PROTECTED]>
To: [email protected]
Sent: Tuesday, October 17, 2006 2:11:25 PM
Subject: Re: Remedy and UNION joins..
Joe,
Try swapping the query on the join criteria so that
the value is on the right hand side of the equal to
operand and see if the indexes are used.
Your query would go from
($instanceId1$ = 'instanceId') OR ($instanceId2$ =
'instanceId')
TO
('instanceId' = $instanceId1$) OR ('instanceId' =
$instanceId2$)
Saby
--- 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.
>
>
>
>
_______________________________________________________________________________
> UNSUBSCRIBE or access ARSlist Archives at
http://www.wwrug.org
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam
protection around
http://mail.yahoo.com
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
Try swapping the query on the join criteria so that
the value is on the right hand side of the equal to
operand and see if the indexes are used.
Your query would go from
($instanceId1$ = 'instanceId') OR ($instanceId2$ =
'instanceId')
TO
('instanceId' = $instanceId1$) OR ('instanceId' =
$instanceId2$)
Saby
--- 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.
>
>
>
>
_______________________________________________________________________________
> UNSUBSCRIBE or access ARSlist Archives at
http://www.wwrug.org
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam
protection around
http://mail.yahoo.com
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
__20060125_______________________This posting was submitted with HTML in it___
