Sybase 15 performance issue

Question

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.

Answer 1

Sybase effectively rewrote the query engine for version 15 which means that queries that ran super-fast on 12.x may run much slower on the newer version, and vice versa. The only way to debug this is to compare the 12.x query plan to the 15 query plan and see what's being done differently.

Answer 2

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.

Answer 3

Same as the others, I have commiseration rather than a real answer! We are seeing a problem where the ASE 15 query planner massively underestimates the cost of a table scan and similarly overestimates the cost of using the clustered index. This results in a merge join being the suggested plan. Disabling merge joins or setting the allrows_oltp optgoal sometimes results in a better query plan. The estimated costs are still way off, but by taking one option off the table the query planner may find a good solution - albeit via the wrong analysis.

ASE 15 documents say that it has a much cleaner set of algorithms whereas the ASE 12 planner had a bunch of special cases. Perhaps a special case that says "if you have the clustered index column in the join it's going to be faster than a table scan" wouldn't be such a bad idea ... :(

Answer 4

Everyone concerned with this issue should read this doc:

http://www.sybase.com/files/White_Papers/ASE15-Optimizer-Best-Practices-v1-051209-wp.pdf

It has a candid warning about migrating from Sybase 12 to Sybase 15.

Quoteth:

... don't treat ASE 15 as "just another release". As much as we would like to say that you could simply upgrade and point your applications at the upgraded servers, the depth and breadth of change in one of the most fundamental areas of a database, query execution, necessitates a more focused testing regimen. This paper is meant to provide you with the clear facts and best practices to reduce this effort as much as practically possible.

It goes on to talk about the new ASE 15 Query Optimizer, vis-a-vis OLTP queries and DSS (Decision Support System) queries.

However, there's good news: in March 2009, Sybase 15.0.3 introduced a compatibility mode. See the following doc:

http://www.sybase.com/detail?id=1063556

With this mode, you need not analyze queries to decide if they fit OLTP or DSS profiles.