Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

ASE 15 too slow

3 posts in General Discussion Last posting was on 2010-01-11 20:50:13.0Z
RGS Posted on 2010-01-09 22:02:22.0Z
Sender: 48f2.4b48fb38.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: ASE 15 too slow
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b48fcee.4937.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 9 Jan 2010 14:02:22 -0800
X-Trace: forums-1-dub 1263074542 10.22.241.41 (9 Jan 2010 14:02:22 -0800)
X-Original-Trace: 9 Jan 2010 14:02:22 -0800, 10.22.241.41
Lines: 28
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28858
Article PK: 78100

Hi!!

Recently we migrate from ASE 12.5.4 to ASE 15 and now we
have problems with the perfomance of our system

We have two situations:

1) FLAG 450

We turn on flag 450 for simulate ORDER BY like 12.5.4 This
flag affect the perfomance in general? We have to change
thousands of programs and we don't know if this effort is
really necesary

2) SET MERGEJOIN OFF

We have some stored procedures slows, we review the
sentences and we find a specific "freeze" sentence. Our dbo
said us that we have to put this sentence before of the
"freeze" sentence:

set mergejoin off

We test the stored procedure and the program is faster. Why
happens that? Exactly, what do this set option? Is right to
put this kind of sentences in our programs?

Thanks a lot


"Mark A. Parsons" <iron_horse Posted on 2010-01-09 22:17:19.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: ASE 15 too slow
References: <4b48fcee.4937.1681692777@sybase.com>
In-Reply-To: <4b48fcee.4937.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 091223-0, 12/23/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b49006f@forums-1-dub>
Date: 9 Jan 2010 14:17:19 -0800
X-Trace: forums-1-dub 1263075439 10.22.241.152 (9 Jan 2010 14:17:19 -0800)
X-Original-Trace: 9 Jan 2010 14:17:19 -0800, vip152.sybase.com
Lines: 53
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28860
Article PK: 78104


RGS wrote:
> Hi!!
>
> Recently we migrate from ASE 12.5.4 to ASE 15 and now we
> have problems with the perfomance of our system
>
> We have two situations:
>
> 1) FLAG 450
>
> We turn on flag 450 for simulate ORDER BY like 12.5.4 This
> flag affect the perfomance in general? We have to change
> thousands of programs and we don't know if this effort is
> really necesary

I don't use TF 450 so I'm not sure what 12.5.4 behaviour you're trying to emulate in 15.

Does TF 450 improve or degrade your performance in 15?

If you're going to have to change a lot of code to use TF 450 ... why not take the opportunity to change the code to
eliminate the need for TF 450?


> 2) SET MERGEJOIN OFF
>
> We have some stored procedures slows, we review the
> sentences and we find a specific "freeze" sentence. Our dbo
> said us that we have to put this sentence before of the
> "freeze" sentence:
>
> set mergejoin off
>
> We test the stored procedure and the program is faster. Why
> happens that? Exactly, what do this set option? Is right to
> put this kind of sentences in our programs?

Can't comment on why the procs are faster without knowing more about the queries involved and the query plans the
optimizer is coming up with. [P&T work on individual queries, especially for a 12-to-15 upgrade can be quite involved
... after 4+ years of ASE 15 upgrades I still come across some doozies that require a good bit of work to 'fix'.]

Right off the bat I'd suggest you change the dataserver optimization goal to 'allrows_oltp' to see if that helps. On
most of the dataservers I've upgraded we have set the server-level optimization goal to 'allrows_oltp' and found that
most instances of run-away merge joins disappear ... ymmv.

----------------------

ASE 15 upgrades, contrary to the bs generated by Sybase marketing, has been a major pain in the keister for a lot of
folks ... so much so that Sybase has had to publish multiple documents over the years covering a wide range of 'best
practices' for upgrading to ASE 15.

Have you looked through any of the ASE 15 P&T guides available at sybase.com?


Bret Halford Posted on 2010-01-11 20:50:13.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.5) Gecko/20091204 Thunderbird/3.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: ASE 15 too slow
References: <4b48fcee.4937.1681692777@sybase.com>
In-Reply-To: <4b48fcee.4937.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b4b8f05$1@forums-1-dub>
Date: 11 Jan 2010 12:50:13 -0800
X-Trace: forums-1-dub 1263243013 10.22.241.152 (11 Jan 2010 12:50:13 -0800)
X-Original-Trace: 11 Jan 2010 12:50:13 -0800, vip152.sybase.com
Lines: 62
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28869
Article PK: 78111


On 1/9/2010 3:02 PM, RGS wrote:
> Hi!!
>
> Recently we migrate from ASE 12.5.4 to ASE 15 and now we
> have problems with the perfomance of our system
>
> We have two situations:
>
> 1) FLAG 450
>
> We turn on flag 450 for simulate ORDER BY like 12.5.4 This
> flag affect the perfomance in general? We have to change
> thousands of programs and we don't know if this effort is
> really necesary

Prior to 15.0, the GROUP BY operator had a side effect of
returning the results in the same order as the GROUP BY
columns, i.e. GROUP BY x,y,z gave the same output as
GROUP BY x,y,z ORDER BY x,y,z.

15.0 introduced a different strategy for handling GROUP BYs,
but the output of it is no longer necessarily ordered in the
same way. ANSI does not require the ordering.

TRACEFLAG 450 reverts to the 12.5.x behavior, i.e. it performs
an implicit ORDER BY for GROUP BY.

I wouldn't expect it to have any effect on general performance,
however performance for GROUP BY statements that don't require ordering
could be worse than it would be without the traceflag on.


> 2) SET MERGEJOIN OFF
>
> We have some stored procedures slows, we review the
> sentences and we find a specific "freeze" sentence. Our dbo
> said us that we have to put this sentence before of the
> "freeze" sentence:
>
> set mergejoin off
>
> We test the stored procedure and the program is faster. Why
> happens that? Exactly, what do this set option? Is right to
> put this kind of sentences in our programs?

I'm guessing that what you are using "sentence" where I would normally
use "statement", and "freeze" where I would normally use
"poorly performing" or even "seemingly hung".

MERGE-JOIN is one of the newer strategies that the optimizer can
pick to perform joins. The optimizer is sometimes overly optimistic
about the performance of the MERGE-JOIN strategy. Setting
this option on prevents that strategy from being selected within
the current context of the current session, until the option is
turned back on. It is reasonable to use SET operations
this way, it is what they are for. However, it is also a good idea
to go back every now and then and see if improvements in the
optimizer have made the use of the option no longer necessary.

For some general non-ASE-specific information about the join methods
(nested, hash, merge), see
http://en.wikipedia.org/wiki/Category:Join_algorithms