I am working with Sybase 15 in my application and there is performance issue related with nested joins. I have stored procedure which selects 2 columns from 2 tables and compares equalities of over 10 columns between this 2 tables. But when I run this stor. proc., the result takes 40 minutes. I added "set merge-join off" statement to top of my proc then the result takes 22 seconds. but I need one more solution without that. I was using sybase 12.5 before and there was no any issue like that and my proc was take 3 mins for the result.
I have compared server configurations with sp_configure between 15 and 12.5 and sybase15 server configurations (I/O and memory configuration settings) are bigger than sybase12.5 server.
Info: sybase15 located pc's system resources are really good.
I have just spent 14 hours at work debugging critical performance issues that arose from a Sybase 15 migration on the weekend.
The query optimiser has been making (for us) some very odd decisions.
Take an example,
select a, b, c from table1, table2, table3 where ...
versus
create table #temp (col1 int, col2 int, ... etc)
insert #temp
select a, b, c from table1, table2, table3 where ...
We had the first run in good time, and could not get it to make the correct decision in the 2nd instance, despite extensive reworking. We even took the query apart into temporary tables, but still got unusual results.
In the end we resorted to SET FORCEPLAN ON for some queries - this is after 10 hours of having our DBAs and Sybase on the line. The solution came from the application developers also rather than any advice from the Sybase engineers.
So to save yourself some time, take this route is my suggestion.