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___
