Sybase 15 performance issue
I am working with Sybase 15 in my application and there is a performance issue with nested joins. I have been storing a procedure that selects 2 columns from 2 tables and compares more than 10 column equalities between those two tables. But when I started this repository. proc. the result takes 40 minutes. I added the command "set merge-join off" to the beginning of my program, then the result will take 22 seconds. but I need another solution without this. I used sybase 12.5 before and there were no similar problems and my result was wasted for 3 minutes.
I was comparing server configurations with sp_configure between 15 and 12.5, and sybase15 server configurations (I / O and memory configuration parameters) are larger than sybase12.5 server.
Info: sybase15 found in PC system resources are really good.
source to share
I just spent 14 hours at work debugging critical performance issues from the weekend Sybase 15 migration.
The query optimizer makes (for us) some very strange decisions.
Let's take an example,
select a, b, c from table1, table2, table3 where ...
against
create table #temp (col1 int, col2 int, ... etc)
insert #temp
select a, b, c from table1, table2, table3 where ...
We had our first launch on time and couldn't get him to make the right decision in the second instance, despite extensive rework. We even split the query into temporary tables, but still got unusual results.
In the end we resorted to SET FORCEPLAN ON
for some queries - this is 10 hours later with our DBAs and Sybase on line. The decision was also obtained from the application developers and not from the recommendations of the Sybase engineers.
So, to save time, take this route - my suggestion.
source to share
Same as others, I have more sympathy than a real answer! We see an issue where the ASE 15 query planner significantly underestimates the cost of a table scan and similarly overestimates the cost of using a clustered index. This makes the merge merge the proposed plan. Disabling merge pooling or setting allrows_oltp optgoal sometimes results in a better data plan. Estimated costs are still dormant, but by taking one from the table, the query planner might find a good solution - albeit with the wrong analysis.
The ASE 15 docs say it has a much cleaner set of algorithms, whereas the ASE 12 scheduler had a bunch of special cases. Perhaps a special case that says "If you have a clustered index column in a join it will be faster than a table scan" would not be such a bad idea ... :(
source to share
Sybase has effectively rewritten the query engine for version 15, which means that queries that ran ultra-fast on 12.x may run much slower on a newer version, and vice versa. The only way to debug this is to compare the 12.x query plan with the 15 query plan and see what is being done differently.
source to share
Anyone interested in this issue should read this document:
http://www.sybase.com/files/White_Papers/ASE15-Optimizer-Best-Practices-v1-051209-wp.pdf
He has an outspoken warning about moving from Sybase 12 to Sybase 15.
Quoteth:
... don't see ASE 15 as "just another release". how to say that you could just update and point your applications to updated servers, the depth and breadth of change in one of the most fundamental areas of the database, query execution, requires a more focused testing mode. This article is intended to provide you with as clear facts and advice as possible.
The following explains the new ASE 15 Query Optimizer, OLTP queries, and DSS (Decision Support System) queries.
However, there is good news : Sybase 15.0.3 introduced compatibility mode in March 2009. See the following document:
http://www.sybase.com/detail?id=1063556
In this mode, you do not need to analyze queries to decide if they are suitable for OLTP or DSS profiles.
source to share