blocks|key|391703|text|Sybase有效地重写了版本15的查询引擎，这意味着在12.x上运行超快的查询在新版本上可能运行得慢得多，反之亦然。调试它的唯一方法是将12.x查询计划与15查询计划进行比较，看看有什么不同之处。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|391704|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|391719|text|我刚刚在工作中花了14个小时调试周末Sybase+15迁移产生的关键性能问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|391720|查询优化器(对我们来说)做出了一些非常奇怪的决定。|391721|举个例子，|391722|select+a,+b,+c+from+table1,+table2,+table3+where+...|code-block|syntax|javascript|391723|对比|391724|create+table+#temp+(col1+int,+col2+int,+...+etc)

insert+#temp
select+a,+b,+c+from+table1,+table2,+table3+where+...|391725|我们在很好的时间内进行了第一次运行，尽管进行了大量的返工，但在第二次运行时无法做出正确的决定。我们甚至将查询分解为临时表，但仍然得到了不寻常的结果。|391726|最后，我们求助于SET+FORCEPLAN+ON进行一些查询--这是在我们的DBA和Sybase在线运行了10个小时之后。解决方案也来自应用程序开发人员，而不是Sybase工程师的任何建议。|offset|length|style|CODE|391727|所以为了节省你自己的时间，我建议你走这条路。|391728|entityMap^0|0|0|0|0|0|0|0|8|G|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|12|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|13|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|14|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|15|8|@]|9|@]|A|$I|J]]|$1|O|3|P|5|6|7|16|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|17|8|@$S|18|T|19|U|V]]|9|@]|A|$]]|$1|W|3|X|5|6|7|1A|8|@]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]]|Z|$]]

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,

<pre><code>select a, b, c from table1, table2, table3 where ...
</code></pre>

versus

<pre><code>create table #temp (col1 int, col2 int, ... etc)

insert #temp
select a, b, c from table1, table2, table3 where ...
</code></pre>

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 <code>SET FORCEPLAN ON</code> 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.

blocks|key|1691033|text|和其他人一样，我有同情而不是真正的答案！我们看到了一个问题，ASE+15查询规划器严重低估了表扫描的成本，并同样高估了使用聚集索引的成本。这会使合并联接成为建议的计划。禁用合并连接或设置allrows_oltp选项有时会产生更好的查询计划。估计的成本仍然很低，但是通过从表中删除一个选项，查询规划器可能会找到一个很好的解决方案-尽管是通过错误的分析。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1691034|ASE+15的文档说它有一套更干净的算法，而ASE+12的计划者有一堆特殊的情况。也许说“如果在连接中有聚集索引列，它将比表扫描更快”这样的特殊情况可能不是一个坏主意……:(|1691035|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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 ... :(

blocks|key|251864|text|每个关心这个问题的人都应该阅读这个文档：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|251865|http://www.sybase.com/files/White_Papers/ASE15-Optimizer-Best-Practices-v1-051209-wp.pdf|offset|length|251866|它有一个关于从Sybase+12迁移到Sybase+15的坦率警告。|251867|引用：|251868|251869|+...不要把ASE+15当做“另一个版本”。正如我们想说的那样，您可以简单地升级并将您的应用程序指向升级后的服务器，但数据库的最基本领域之一的更改的深度和广度，查询执行，需要更有针对性的测试方案。本文旨在为您提供清晰的事实和最佳实践，以尽可能减少此工作。|blockquote|251870|251871|251872|它继续谈论新的ASE+15查询优化器，与OLTP查询和DSS+(决策支持系统)查询。|251873|然而，也有的好消息：在2009年3月，Sybase+15.0.3引入了兼容模式。请参阅以下文档：|style|BOLD|251874|http://www.sybase.com/detail?id=1063556|251875|在这种模式下，您不需要分析查询来确定它们是否适合OLTP或DSS配置文件。|251876|entityMap|0|LINK|mutability|MUTABLE|url|1^0|0|0|2G|0|0|0|0|0|0|0|0|0|5|4|0|0|13|1|0|0^^$0|@$1|2|3|4|5|6|7|17|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|18|8|@]|9|@$D|19|E|1A|1|1B]]|A|$]]|$1|F|3|G|5|6|7|1C|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|1D|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]|$1|K|3|L|5|M|7|1F|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|1G|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|1H|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|1I|8|@]|9|@]|A|$]]|$1|R|3|S|5|6|7|1J|8|@$D|1K|E|1L|T|U]]|9|@]|A|$]]|$1|V|3|W|5|6|7|1M|8|@]|9|@$D|1N|E|1O|1|1P]]|A|$]]|$1|X|3|Y|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|Z|3|-4|5|6|7|1R|8|@]|9|@]|A|$]]]|10|$11|$5|12|13|14|A|$15|C]]|16|$5|12|13|14|A|$15|W]]]]

Everyone concerned with this issue should read this doc:

<a href="http://www.sybase.com/files/White_Papers/ASE15-Optimizer-Best-Practices-v1-051209-wp.pdf" rel="nofollow">http://www.sybase.com/files/White_Papers/ASE15-Optimizer-Best-Practices-v1-051209-wp.pdf</a>

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

Quoteth:

<blockquote>
 ... 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.
</blockquote>

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:

<a href="http://www.sybase.com/detail?id=1063556" rel="nofollow">http://www.sybase.com/detail?id=1063556</a>

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

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.

Sybase 15 performance issue

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我在我的应用程序中使用Sybase 15，并且存在与嵌套连接相关的性能问题。我有一个存储过程，它从2个表中选择2列，并比较这2个表之间超过10列的相等。但是当我经营这个商店的时候。proc.，结果需要40分钟。我在proc的顶部添加了"set merge-join off“语句，然后结果花了22秒。但我还需要一个没有那...

问Sybase 15性能问题
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Sybase 15性能问题EN