ASE 15 slower than ASE 12.5.3 in testing - any other experiences?

The Sybase press releases (and their sales people) do a good job of talking 
up how much better the new query optimizer is in ASE 15. We had a short term 
need to squeeze more performance out of our existing servers so we decided 
to give it a test.

We installed ASE 12.5.3 and ASE 15.0 (both with the latest EBFs) on the same 
dual-core, dual-processor, Intel Xeon server with Windows 2003 Std Edition 
and 4GB of RAM.

ASE 12.5.3 and ASE 15.0 were installed in separate directories. Only one was 
run at a time (with a clean reboot before switching from one version to 
another).

Into both servers a 15GB database dump was loaded, and all of the stored 
procedures, triggers and indexes were reloaded.

Both editions of ASE had identical configuration (including 2,300MB of named 
cache). Each test (on each version) was run twice, with the second time 
being taken (to ensure the data the queries needed was cached in memory).

In testing 7 of our most complex reporting stored procedures (which perform 
large data manipulations), in all cases ASE 12.5.3 was faster. Sometimes 
only by one or two percent. In one case, ASE 12.5.3 took 4 minutes to run 
the proc, and ASE 15 took 56 minutes (the optimizer adding an extra 2 
million scans into the query plan in ASE 15).

Obviously this is a limited test but we found it a little bit concerning 
because we don't do anything particuarily tricky in these procs, and our 
schema is pretty well optimized.

So, for the time being, we're sticking with ASE 12.5.3.

The reason for this post is to ask if anyone else had performed a controlled 
test of their database on ASE 15 (versus ASE 12.5.x) and whether you've had 
better success with getting improved performance?

- Richard 


0
Richard
8/3/2006 4:18:42 PM
sybase.ase.general 8655 articles. 0 followers. Follow

15 Replies
718 Views

Similar Articles

[PageSpeed] 1

Richard Mundell wrote:
> The Sybase press releases (and their sales people) do a good job of talking 
> up how much better the new query optimizer is in ASE 15. We had a short term 
> need to squeeze more performance out of our existing servers so we decided 
> to give it a test.
> 
> We installed ASE 12.5.3 and ASE 15.0 (both with the latest EBFs) on the same 
> dual-core, dual-processor, Intel Xeon server with Windows 2003 Std Edition 
> and 4GB of RAM.
> 
> ASE 12.5.3 and ASE 15.0 were installed in separate directories. Only one was 
> run at a time (with a clean reboot before switching from one version to 
> another).
> 
> Into both servers a 15GB database dump was loaded, and all of the stored 
> procedures, triggers and indexes were reloaded.
> 
> Both editions of ASE had identical configuration (including 2,300MB of named 
> cache). Each test (on each version) was run twice, with the second time 
> being taken (to ensure the data the queries needed was cached in memory).
> 
> In testing 7 of our most complex reporting stored procedures (which perform 
> large data manipulations), in all cases ASE 12.5.3 was faster. Sometimes 
> only by one or two percent. In one case, ASE 12.5.3 took 4 minutes to run 
> the proc, and ASE 15 took 56 minutes (the optimizer adding an extra 2 
> million scans into the query plan in ASE 15).
> 
> Obviously this is a limited test but we found it a little bit concerning 
> because we don't do anything particuarily tricky in these procs, and our 
> schema is pretty well optimized.
> 
> So, for the time being, we're sticking with ASE 12.5.3.
> 
> The reason for this post is to ask if anyone else had performed a controlled 
> test of their database on ASE 15 (versus ASE 12.5.x) and whether you've had 
> better success with getting improved performance?
> 
> - Richard 
> 
> 
Were you careful to rebuild statistics on both databases before testing? 
  ASE15 is very sensitive to statistic data.
0
John
8/3/2006 4:32:48 PM
"John Griffin" <jgriffin@dstina.com> wrote in message 
news:44d22530@forums-1-dub...
> Were you careful to rebuild statistics on both databases before testing? 
> ASE15 is very sensitive to statistic data.

Nice thought, but we dropped all of the indexes and recreated them from 
scratch. We also dropped and reloaded all of the stored procedures and 
triggers to give ASE15 the opportunity to compile them from scratch.

- Richard 


0
Richard
8/3/2006 4:44:11 PM
Richard Mundell wrote:
> "John Griffin" <jgriffin@dstina.com> wrote in message 
> news:44d22530@forums-1-dub...
>> Were you careful to rebuild statistics on both databases before testing? 
>> ASE15 is very sensitive to statistic data.
> 
> Nice thought, but we dropped all of the indexes and recreated them from 
> scratch. We also dropped and reloaded all of the stored procedures and 
> triggers to give ASE15 the opportunity to compile them from scratch.
> 
> - Richard 
> 
> 
Creating the indexes only create stats on the first column I believe.. 
You should try an 'update index statistics' on all tables next time.
0
John
8/3/2006 4:54:29 PM
"John Griffin" <jgriffin@dstina.com> wrote in message 
news:44d22a45$1@forums-1-dub...
> Creating the indexes only create stats on the first column I believe.. You 
> should try an 'update index statistics' on all tables next time.

Just to be sure I just updated the statistics and reran the tests. ASE15 was 
still slower than ASE12.5.3.

Anyone else had any experience comparing 12.5.x vs 15? 


0
Richard
8/3/2006 7:55:28 PM
Almost inevitably, query plans on some SQL will change when you upgrade 
15 (and with many other upgrades).  Because you will have fixed or 
worked around any "problem" queries in your existing system, any query 
plan changes stand a good chance of making things worse.  Even with all 
the index and statistics updates in the world, you are still going to 
have to go in and tweak some code.

ASE 15 has new capabilities which *should* enable more performance to be 
obtained (with suitable configuration) and *should* make it easier to 
write code and have the optimiser execute it well without further 
tuning.  But you shouldn't expect a big performance gain "straight out 
of the box".  Also some of the advances are in areas like the 
transaction log, to improve throughput with a loaded system.  You might 
find real world performance gains even where you see no improvement with 
an individual benchmark.

It would be interesting to look in more detail at some of the code which 
is one or two percent slower (the extra 2 million scans is just the 
optimiser choosing the wrong plan) and see just why ASE 15 is slower and 
whether there are server configurations which would improve things. 
Possibly this is just a case of a more complex ASE thinking harder 
(1%-2% harder) but ending up doing the exact same thing.

--ian

Richard Mundell wrote:
> The Sybase press releases (and their sales people) do a good job of talking 
> up how much better the new query optimizer is in ASE 15. We had a short term 
> need to squeeze more performance out of our existing servers so we decided 
> to give it a test.
> 
> We installed ASE 12.5.3 and ASE 15.0 (both with the latest EBFs) on the same 
> dual-core, dual-processor, Intel Xeon server with Windows 2003 Std Edition 
> and 4GB of RAM.
> 
> ASE 12.5.3 and ASE 15.0 were installed in separate directories. Only one was 
> run at a time (with a clean reboot before switching from one version to 
> another).
> 
> Into both servers a 15GB database dump was loaded, and all of the stored 
> procedures, triggers and indexes were reloaded.
> 
> Both editions of ASE had identical configuration (including 2,300MB of named 
> cache). Each test (on each version) was run twice, with the second time 
> being taken (to ensure the data the queries needed was cached in memory).
> 
> In testing 7 of our most complex reporting stored procedures (which perform 
> large data manipulations), in all cases ASE 12.5.3 was faster. Sometimes 
> only by one or two percent. In one case, ASE 12.5.3 took 4 minutes to run 
> the proc, and ASE 15 took 56 minutes (the optimizer adding an extra 2 
> million scans into the query plan in ASE 15).
> 
> Obviously this is a limited test but we found it a little bit concerning 
> because we don't do anything particuarily tricky in these procs, and our 
> schema is pretty well optimized.
> 
> So, for the time being, we're sticking with ASE 12.5.3.
> 
> The reason for this post is to ask if anyone else had performed a controlled 
> test of their database on ASE 15 (versus ASE 12.5.x) and whether you've had 
> better success with getting improved performance?
> 
> - Richard 
> 
> 
0
Iann
8/3/2006 8:14:28 PM
I ran into some similar issues with the initial release of ASE 15.0 (I 
posted about some of these issues back in Dec '05).

The gist of our problem was that the ASE15 optimizer seemed to think all of 
our big/complicated queries should be performed as 'merge joins' ... which 
in our case consisted of table scanning a 20+ million row table (~2GB 
data), building a ~1.2GB worktable, sorting said worktable, and then 
performing the merge join against said worktable ... needless to say there 
was mucho time spent building/sorting the worktable, not to mention eating 
up a lot of tempdb space for said worktable.

ASE15 was installed, out of the box, with the configuration parameter 
'optimization goal' set to 'allrows_dss' (or some such thang).  By 
switching this to 'allrows_oltp' we were able to get the ASE15 optimizer to 
run the query as a nested loop join (just like on the ASE12.5.x server).

The difference in run times with ASE15 performing merge joins ... 5-10 
minutes on ASE12 with one version of query running at a time ... 40-50 
minute on ASE15 with one version of query running at a time.  In a 
realistic test of 4-5 of these queries running at the same time ... ASE12 
queries averaged ~15 minutes per query ... ASE15 averaged almost 2 hours 
per query (ie, mucho, Mucho, MUCHO contention for tempdb space and data 
cache while trying to build/sort 4-5 worktables (5-6GB of tempdb space with 
a 2GB data cache), each with 20+ million records).

Once we got the ASE15 optimizer to use nested loop joins we found that the 
queries on ASE15 performed on par with the ASE12 queries ... not much 
noticeable difference for this particular query.

[NOTE:  The documentation, at the time, was next to nil in regards to the 
various 'optimization goal' settings; I ended up getting the info from Rob V.]

Oh, yeah, one other thing ... when running 4-5 copies of the same query in 
ASE15 ... we always blew out one of our 'small' (~4GB) tempdb's ... we 
ended up having to consolidate our 3 tempdb's into one big tempdb to 
support the 4-5 concurrent queries ... at least until we reconfigured to 
use 'allrows_oltp', at which point worktables (and thus tempdb space) were 
no longer needed.

===============================

In ASE15 'set statistics io on'  wasn't reporting on worktable io 
statistics so it's hard to tell just how much logical io work was performed 
on the worktable ... but once we got rid of the merge join our queries on 
ASE15 ran in about the same time as the ASE12 queries ... so we're talking 
about 35-40 minutes (with 1 copy of query running) to perform the worktable 
build/sort.

In ASE12 there were no worktables for this particular query.

Now, without the worktable stats in ASE15, the ASE15 merge join query 
actually showed fewer logical io's on the base tables ... basically because 
ASE15 was table scanning using large IO's while ASE12 was performing 
smaller IO's for the nested loop join.  Soooo, based solely on the base 
table stats ... ASE15 *was* faster than ASE12 ... but them costly 
worktables 'got in the way'!?!?!?! ;-)

It was almost as if the optimizer was ignoring the cost of the worktables 
.... that ... or perhaps 'merge joins' were configured to cost 'less' than a 
nested loop join without much thought given to the overhead (cache, tempdb) 
for worktables.

Anyhoo ... we opted to stay away from ASE15's new merge join logic ... in 
favor of the tried-n-true nested loop join logic.

YMMV ...

Richard Mundell wrote:

> The Sybase press releases (and their sales people) do a good job of talking 
> up how much better the new query optimizer is in ASE 15. We had a short term 
> need to squeeze more performance out of our existing servers so we decided 
> to give it a test.
> 
> We installed ASE 12.5.3 and ASE 15.0 (both with the latest EBFs) on the same 
> dual-core, dual-processor, Intel Xeon server with Windows 2003 Std Edition 
> and 4GB of RAM.
> 
> ASE 12.5.3 and ASE 15.0 were installed in separate directories. Only one was 
> run at a time (with a clean reboot before switching from one version to 
> another).
> 
> Into both servers a 15GB database dump was loaded, and all of the stored 
> procedures, triggers and indexes were reloaded.
> 
> Both editions of ASE had identical configuration (including 2,300MB of named 
> cache). Each test (on each version) was run twice, with the second time 
> being taken (to ensure the data the queries needed was cached in memory).
> 
> In testing 7 of our most complex reporting stored procedures (which perform 
> large data manipulations), in all cases ASE 12.5.3 was faster. Sometimes 
> only by one or two percent. In one case, ASE 12.5.3 took 4 minutes to run 
> the proc, and ASE 15 took 56 minutes (the optimizer adding an extra 2 
> million scans into the query plan in ASE 15).
> 
> Obviously this is a limited test but we found it a little bit concerning 
> because we don't do anything particuarily tricky in these procs, and our 
> schema is pretty well optimized.
> 
> So, for the time being, we're sticking with ASE 12.5.3.
> 
> The reason for this post is to ask if anyone else had performed a controlled 
> test of their database on ASE 15 (versus ASE 12.5.x) and whether you've had 
> better success with getting improved performance?
> 
> - Richard 
> 
> 
0
Mark
8/4/2006 12:40:11 AM
Richard Mundell wrote:
> 
> The Sybase press releases (and their sales people) do a good job of talking
> up how much better the new query optimizer is in ASE 15. We had a short term
> need to squeeze more performance out of our existing servers so we decided
> to give it a test.
> 
> We installed ASE 12.5.3 and ASE 15.0 (both with the latest EBFs) on the same
> dual-core, dual-processor, Intel Xeon server with Windows 2003 Std Edition
> and 4GB of RAM.
> 
> ASE 12.5.3 and ASE 15.0 were installed in separate directories. Only one was
> run at a time (with a clean reboot before switching from one version to
> another).
> 
> Into both servers a 15GB database dump was loaded, and all of the stored
> procedures, triggers and indexes were reloaded.

	That may not have been a very good idea. Rebuilding the indexes
	means you lose all your existing heuristics and histogram steps.
	There's a dbcc call you can make to rebuild sprocs. I'd suggest
	calling that instead.

> Both editions of ASE had identical configuration (including 2,300MB of named
> cache). Each test (on each version) was run twice, with the second time
> being taken (to ensure the data the queries needed was cached in memory).
> 
> In testing 7 of our most complex reporting stored procedures (which perform
> large data manipulations), in all cases ASE 12.5.3 was faster. Sometimes
> only by one or two percent. In one case, ASE 12.5.3 took 4 minutes to run
> the proc, and ASE 15 took 56 minutes (the optimizer adding an extra 2
> million scans into the query plan in ASE 15).

	I gather you took showplans. You may want to post an example
	of each.

	What about configuration settings?

> Obviously this is a limited test but we found it a little bit concerning
> because we don't do anything particuarily tricky in these procs, and our
> schema is pretty well optimized.
> 
> So, for the time being, we're sticking with ASE 12.5.3.
> 
> The reason for this post is to ask if anyone else had performed a controlled
> test of their database on ASE 15 (versus ASE 12.5.x) and whether you've had
> better success with getting improved performance?

	I have a client testing now but its on different hardware.

-am	� MMVI
0
A
8/4/2006 3:40:01 AM
to answer your question, I did perform a similar test.  I dumped an ASE 
12.5.3 database into a ASE 15_0 ESD2 server, and ran some very unscientific 
performance tests on some of our reporting (ie: DSS type) procedures.  This 
was on Solaris Sparc 8 E2900, using EMC Raid5 disk with same memory and 
cache configurations.  However, I did increase the allocation of procedure 
cache a bit (about 10%) as suggested by the literature and previous techwave 
slides.  I used the "allrows_mix" optimization goal setting which is the 
default as well.

My testing showed that most of my queries were within reasonable performance 
comparatively.  I tune mostly using logical IO's rather than response time 
though, and my tests involved multiple connections on a 4 engine ASE server. 
There were no procedures which performed abnormally in 15.x and a few which 
performed quite a bit better with 15.

I don't draw any conclusions from this though, other than the fact that for 
these procedures, and for this data set, 15.x is a viable (and very 
attractive) path for us.  Your findings are unfortunate and hopefully 
unusual, but the good news is that 15 comes with some great built-in tools 
for drilling down to what this issues are so that you can fix them, and then 
leverage 15 and it's new features.

For your case, I would begin by inspecting the configuration file of both 
servers to verify the settings are the same.  For the ASE15 settings that 
aren't available in 15, look into to the possible impact of those as well.

"Richard Mundell" <r Underscore mundell AT hotmail> wrote in message 
news:44d221e2$1@forums-1-dub...
> The Sybase press releases (and their sales people) do a good job of 
> talking up how much better the new query optimizer is in ASE 15. We had a 
> short term need to squeeze more performance out of our existing servers so 
> we decided to give it a test.
>
> We installed ASE 12.5.3 and ASE 15.0 (both with the latest EBFs) on the 
> same dual-core, dual-processor, Intel Xeon server with Windows 2003 Std 
> Edition and 4GB of RAM.
>
> ASE 12.5.3 and ASE 15.0 were installed in separate directories. Only one 
> was run at a time (with a clean reboot before switching from one version 
> to another).
>
> Into both servers a 15GB database dump was loaded, and all of the stored 
> procedures, triggers and indexes were reloaded.
>
> Both editions of ASE had identical configuration (including 2,300MB of 
> named cache). Each test (on each version) was run twice, with the second 
> time being taken (to ensure the data the queries needed was cached in 
> memory).
>
> In testing 7 of our most complex reporting stored procedures (which 
> perform large data manipulations), in all cases ASE 12.5.3 was faster. 
> Sometimes only by one or two percent. In one case, ASE 12.5.3 took 4 
> minutes to run the proc, and ASE 15 took 56 minutes (the optimizer adding 
> an extra 2 million scans into the query plan in ASE 15).
>
> Obviously this is a limited test but we found it a little bit concerning 
> because we don't do anything particuarily tricky in these procs, and our 
> schema is pretty well optimized.
>
> So, for the time being, we're sticking with ASE 12.5.3.
>
> The reason for this post is to ask if anyone else had performed a 
> controlled test of their database on ASE 15 (versus ASE 12.5.x) and 
> whether you've had better success with getting improved performance?
>
> - Richard
> 


0
Sherlock
8/4/2006 3:56:25 AM
Richard;

    This is our experience thus far as well. I wonder if some of this is the
Unicode aspect of ASE 15 vs its older ANSI versions?

    The good news is that on the same test of SS2005 vs SS2000 - v2000
whipped the pants off of 2005! So it looks like MS has some work to do there
as well <lol>!

Regards ... Chris


"Richard Mundell" <r Underscore mundell AT hotmail> wrote in message
news:44d252ad@forums-2-dub...
>
> "John Griffin" <jgriffin@dstina.com> wrote in message
> news:44d22a45$1@forums-1-dub...
> > Creating the indexes only create stats on the first column I believe..
You
> > should try an 'update index statistics' on all tables next time.
>
> Just to be sure I just updated the statistics and reran the tests. ASE15
was
> still slower than ASE12.5.3.
>
> Anyone else had any experience comparing 12.5.x vs 15?
>
>


0
Chris
8/4/2006 11:37:20 AM
Iann;

    I would "highly" recommend Quest Software's Quest Central for ASE. I
agree with you 100% that some Query Plans will have to be updated. For those
sites that use PowerBuilder, InfoMaker, DW.Net, WorkSpace, etc with ASE - I
would recommend also looking at my SQL"X"Tract utility
(http://chrispollach.pbdjmagazine.com/sql_extract_utility.htm) to assist in
updating your QP's!

Regards ... Chris


"Iann" <iann@nospam.com> wrote in message news:44d2571f$1@forums-2-dub...
> Almost inevitably, query plans on some SQL will change when you upgrade
> 15 (and with many other upgrades).  Because you will have fixed or
> worked around any "problem" queries in your existing system, any query
> plan changes stand a good chance of making things worse.  Even with all
> the index and statistics updates in the world, you are still going to
> have to go in and tweak some code.
>
> ASE 15 has new capabilities which *should* enable more performance to be
> obtained (with suitable configuration) and *should* make it easier to
> write code and have the optimiser execute it well without further
> tuning.  But you shouldn't expect a big performance gain "straight out
> of the box".  Also some of the advances are in areas like the
> transaction log, to improve throughput with a loaded system.  You might
> find real world performance gains even where you see no improvement with
> an individual benchmark.
>
> It would be interesting to look in more detail at some of the code which
> is one or two percent slower (the extra 2 million scans is just the
> optimiser choosing the wrong plan) and see just why ASE 15 is slower and
> whether there are server configurations which would improve things.
> Possibly this is just a case of a more complex ASE thinking harder
> (1%-2% harder) but ending up doing the exact same thing.
>
> --ian
>
> Richard Mundell wrote:
> > The Sybase press releases (and their sales people) do a good job of
talking
> > up how much better the new query optimizer is in ASE 15. We had a short
term
> > need to squeeze more performance out of our existing servers so we
decided
> > to give it a test.
> >
> > We installed ASE 12.5.3 and ASE 15.0 (both with the latest EBFs) on the
same
> > dual-core, dual-processor, Intel Xeon server with Windows 2003 Std
Edition
> > and 4GB of RAM.
> >
> > ASE 12.5.3 and ASE 15.0 were installed in separate directories. Only one
was
> > run at a time (with a clean reboot before switching from one version to
> > another).
> >
> > Into both servers a 15GB database dump was loaded, and all of the stored
> > procedures, triggers and indexes were reloaded.
> >
> > Both editions of ASE had identical configuration (including 2,300MB of
named
> > cache). Each test (on each version) was run twice, with the second time
> > being taken (to ensure the data the queries needed was cached in
memory).
> >
> > In testing 7 of our most complex reporting stored procedures (which
perform
> > large data manipulations), in all cases ASE 12.5.3 was faster. Sometimes
> > only by one or two percent. In one case, ASE 12.5.3 took 4 minutes to
run
> > the proc, and ASE 15 took 56 minutes (the optimizer adding an extra 2
> > million scans into the query plan in ASE 15).
> >
> > Obviously this is a limited test but we found it a little bit concerning
> > because we don't do anything particuarily tricky in these procs, and our
> > schema is pretty well optimized.
> >
> > So, for the time being, we're sticking with ASE 12.5.3.
> >
> > The reason for this post is to ask if anyone else had performed a
controlled
> > test of their database on ASE 15 (versus ASE 12.5.x) and whether you've
had
> > better success with getting improved performance?
> >
> > - Richard
> >
> >


0
Chris
8/4/2006 11:40:34 AM
We have had the same experience with ASE 15 ESD#2 on Solaris9.
We have had to modify/rewrite stored procedures to match the performanceto
12.5.3.
We have had poblems with joins, outer joind uning and subrtrings in the
where clauses i.e on query went from 62 io's
to 5000 io's , another went from a few thousand io's to billions

Try joining 2 tables with 2+ milliion rows each the sould return 100,000+
rows.  12.5.3 return in seconds, 15.0 never returns
Yes cases were opened months ago
Ase 15.0 has a long way to go ot match 12,5.3 performance.

"Richard Mundell" <r Underscore mundell AT hotmail> wrote in message
news:44d221e2$1@forums-1-dub...
> The Sybase press releases (and their sales people) do a good job of
talking
> up how much better the new query optimizer is in ASE 15. We had a short
term
> need to squeeze more performance out of our existing servers so we decided
> to give it a test.
>
> We installed ASE 12.5.3 and ASE 15.0 (both with the latest EBFs) on the
same
> dual-core, dual-processor, Intel Xeon server with Windows 2003 Std Edition
> and 4GB of RAM.
>
> ASE 12.5.3 and ASE 15.0 were installed in separate directories. Only one
was
> run at a time (with a clean reboot before switching from one version to
> another).
>
> Into both servers a 15GB database dump was loaded, and all of the stored
> procedures, triggers and indexes were reloaded.
>
> Both editions of ASE had identical configuration (including 2,300MB of
named
> cache). Each test (on each version) was run twice, with the second time
> being taken (to ensure the data the queries needed was cached in memory).
>
> In testing 7 of our most complex reporting stored procedures (which
perform
> large data manipulations), in all cases ASE 12.5.3 was faster. Sometimes
> only by one or two percent. In one case, ASE 12.5.3 took 4 minutes to run
> the proc, and ASE 15 took 56 minutes (the optimizer adding an extra 2
> million scans into the query plan in ASE 15).
>
> Obviously this is a limited test but we found it a little bit concerning
> because we don't do anything particuarily tricky in these procs, and our
> schema is pretty well optimized.
>
> So, for the time being, we're sticking with ASE 12.5.3.
>
> The reason for this post is to ask if anyone else had performed a
controlled
> test of their database on ASE 15 (versus ASE 12.5.x) and whether you've
had
> better success with getting improved performance?
>
> - Richard
>
>


0
A
8/4/2006 8:22:06 PM
Richard,

Could you post the trouble query statements here?
With all indexes on those tables. If you can post optdiag from those table,
that would be easier to narrow down the problem.

Cheers,

dbMethods


On Thu, 03 Aug 2006 12:55:28 -0700, Richard Mundell wrote:

> 
> "John Griffin" <jgriffin@dstina.com> wrote in message 
> news:44d22a45$1@forums-1-dub...
>> Creating the indexes only create stats on the first column I believe.. You 
>> should try an 'update index statistics' on all tables next time.
> 
> Just to be sure I just updated the statistics and reran the tests. ASE15 was 
> still slower than ASE12.5.3.
> 
> Anyone else had any experience comparing 12.5.x vs 15?

0
dbmethods
8/8/2006 12:56:18 AM
> to answer your question, I did perform a similar test.  I
> dumped an ASE  12.5.3 database into a ASE 15_0 ESD2 server
> , and ran some very unscientific  performance tests on
> some of our reporting (ie: DSS type) procedures.  This
> was on Solaris Sparc 8 E2900, using EMC Raid5 disk with
> same memory and  cache configurations.  However, I did
> increase the allocation of procedure  cache a bit (about
> 10%) as suggested by the literature and previous techwave
> slides.  I used the "allrows_mix" optimization goal
> setting which is the  default as well.
>
> My testing showed that most of my queries were within
> reasonable performance  comparatively.  I tune mostly
> using logical IO's rather than response time  though, and
> my tests involved multiple connections on a 4 engine ASE
> server.  There were no procedures which performed
> abnormally in 15.x and a few which  performed quite a bit
> better with 15.
>
> I don't draw any conclusions from this though, other than
> the fact that for  these procedures, and for this data set
> , 15.x is a viable (and very  attractive) path for us.
> Your findings are unfortunate and hopefully  unusual, but
> the good news is that 15 comes with some great built-in
> tools  for drilling down to what this issues are so that
> you can fix them, and then  leverage 15 and it's new
> features.
>

What built-in tools you like most on ASE 15?

> For your case, I would begin by inspecting the
> configuration file of both  servers to verify the settings
> are the same.  For the ASE15 settings that  aren't
> available in 15, look into to the possible impact of those
> as well.

I doubt the config file will make such performance
difference here.


>
> "Richard Mundell" <r Underscore mundell AT hotmail> wrote
> in message  news:44d221e2$1@forums-1-dub...
> > The Sybase press releases (and their sales people) do a
> > good job of  talking up how much better the new query
> > optimizer is in ASE 15. We had a  short term need to
> > squeeze more performance out of our existing servers so
> we decided to give it a test. >
> > We installed ASE 12.5.3 and ASE 15.0 (both with the
> > latest EBFs) on the  same dual-core, dual-processor,
> > Intel Xeon server with Windows 2003 Std  Edition and 4GB
> of RAM. >
> > ASE 12.5.3 and ASE 15.0 were installed in separate
> > directories. Only one  was run at a time (with a clean
> > reboot before switching from one version  to another).
> >
> > Into both servers a 15GB database dump was loaded, and
> > all of the stored  procedures, triggers and indexes were
> reloaded. >
> > Both editions of ASE had identical configuration
> > (including 2,300MB of  named cache). Each test (on each
> > version) was run twice, with the second  time being
> taken (to ensure the data the queries needed was cached in
> > memory).
> >
> > In testing 7 of our most complex reporting stored
> > procedures (which  perform large data manipulations), in
> > all cases ASE 12.5.3 was faster.  Sometimes only by one
> > or two percent. In one case, ASE 12.5.3 took 4  minutes
> to run the proc, and ASE 15 took 56 minutes (the optimizer
> > adding  an extra 2 million scans into the query plan in
> ASE 15). >
> > Obviously this is a limited test but we found it a
> > little bit concerning  because we don't do anything
> > particuarily tricky in these procs, and our  schema is
> pretty well optimized. >
> > So, for the time being, we're sticking with ASE 12.5.3.
> >
> > The reason for this post is to ask if anyone else had
> > performed a  controlled test of their database on ASE 15
> > (versus ASE 12.5.x) and  whether you've had better
> success with getting improved performance? >
> > - Richard
> >
>
>
0
dbMethods
8/8/2006 2:59:59 PM
<dbMethods> wrote in message news:44d8a6ef.44cf.1681692777@sybase.com...
>
> What built-in tools you like most on ASE 15?

Most notably would be the "query metrics capture" capability which comes out of
the box with 15.  No MDA tables, or third party tools to install.  A quick and
simple way to identify poorly performing queries.  Obviously MDA is much more
robust and granular, but can be intimidating for some to setup, capture, and
digest the information.

> > For your case, I would begin by inspecting the
> > configuration file of both  servers to verify the settings
> > are the same.  For the ASE15 settings that  aren't
> > available in 15, look into to the possible impact of those
> > as well.
>
> I doubt the config file will make such performance
> difference here.

Correct, there aren't that many in 15 that  aren't in 12.5.x, but most important
would be the "optimization goal" setting.  Perhaps to most closely resemble the
old 12.5.x optimizer, one could try the "allrows_oltp" setting, rather than the
defualt setting of "allrows_mix".

One also must keep an eye on "number of open partitions" now just as open
objects/indexes settings before.


0
Sherlock
8/14/2006 4:15:19 PM
A Cornell wrote:
> We have had the same experience with ASE 15 ESD#2 on Solaris9.
> We have had to modify/rewrite stored procedures to match the performanceto
> 12.5.3.
> We have had poblems with joins, outer joind uning and subrtrings in the
> where clauses i.e on query went from 62 io's
> to 5000 io's , another went from a few thousand io's to billions
> 
> Try joining 2 tables with 2+ milliion rows each the sould return 100,000+
> rows.  12.5.3 return in seconds, 15.0 never returns
> Yes cases were opened months ago
> Ase 15.0 has a long way to go ot match 12,5.3 performance.

Could you post some detail info on your case, such as ddl on 
tables/indexes and the sql statement, sp_spaceused, optdiag etc?

It might be good idea to start another round of consulting business on 
ASE 15 migration.

Cheers,


> 
> "Richard Mundell" <r Underscore mundell AT hotmail> wrote in message
> news:44d221e2$1@forums-1-dub...
>> The Sybase press releases (and their sales people) do a good job of
> talking
>> up how much better the new query optimizer is in ASE 15. We had a short
> term
>> need to squeeze more performance out of our existing servers so we decided
>> to give it a test.
>>
>> We installed ASE 12.5.3 and ASE 15.0 (both with the latest EBFs) on the
> same
>> dual-core, dual-processor, Intel Xeon server with Windows 2003 Std Edition
>> and 4GB of RAM.
>>
>> ASE 12.5.3 and ASE 15.0 were installed in separate directories. Only one
> was
>> run at a time (with a clean reboot before switching from one version to
>> another).
>>
>> Into both servers a 15GB database dump was loaded, and all of the stored
>> procedures, triggers and indexes were reloaded.
>>
>> Both editions of ASE had identical configuration (including 2,300MB of
> named
>> cache). Each test (on each version) was run twice, with the second time
>> being taken (to ensure the data the queries needed was cached in memory).
>>
>> In testing 7 of our most complex reporting stored procedures (which
> perform
>> large data manipulations), in all cases ASE 12.5.3 was faster. Sometimes
>> only by one or two percent. In one case, ASE 12.5.3 took 4 minutes to run
>> the proc, and ASE 15 took 56 minutes (the optimizer adding an extra 2
>> million scans into the query plan in ASE 15).
>>
>> Obviously this is a limited test but we found it a little bit concerning
>> because we don't do anything particuarily tricky in these procs, and our
>> schema is pretty well optimized.
>>
>> So, for the time being, we're sticking with ASE 12.5.3.
>>
>> The reason for this post is to ask if anyone else had performed a
> controlled
>> test of their database on ASE 15 (versus ASE 12.5.x) and whether you've
> had
>> better success with getting improved performance?
>>
>> - Richard
>>
>>
> 
> 
0
dbMethods
8/14/2006 6:26:27 PM
Reply:

Similar Artilces:

Dump/Load (from ASE 12.5.4 to ASE 15.x) - VS.
Hi All, We are in the process of planning the upgrade of our ASE 12.5.4 dataservers to ASE 15.x. What is the most recommended way to upgrade ASE dataservers from ASE 12.5.4 to ASE 15.x? Potential options: 1) Install new instances of ASE 15.x and load database dumps from our ASE 12.5.4 dataservers to the newly installed ASE 15.x dataservers. - OR - 2) Use the ASE upgrade utility (sqlupgrade) to upgrade our existing ASE 12.5.4 dataservers to ASE 15.x. What are the Pros vs. Cons for loading a 12.5.4 database into a 15.x ASE dataserver versus performing an ASE dataserv...

ASE 12.5 to ASE 12.5.3
One of our client is having 3 no license for ASE 12.5 on Win2K (running 3 production servers) and not in the support/AMC with Sybase.. is client eligible to use ASE 12.5.3 ? Thanks KRV ...

Vulnerability of ASE 12.5.2 vs ASE 12.5.3 ESD#3
Recently, Application Security, Inc. has run a vulnerability test on our ASE12.5.2. The program gave a 'high risk' on the ASE12.5.2 and suggested we install the latest Sybase patch 12.5.3 ESD#3. I have some question on the recommendation. 1. The latest patch is definitely not 12.5.3 ESD#3. Why did the program recommend 12.5.3 ESD#3? Isn't 12.5.4 better than 12.5.3? 2. Is it practical to keep up with the latest releases or patches? Now if we upgrade to 12.5.4 and then rerun the vulnerability test, will the program suggest that newer version is better with respect to s...

Migration from ASE-64 bits 12.5.0.2 to ASE 64 bits 12.5.3 for AIX 5.3
Here there someone to indicate me the road tasks in order to migrate an ASE 12.5.0.2 to ASE 12.5.3 on unix 5.3 ? (64 bits) Thank you kamel wrote: > Here there someone to indicate me the road tasks in order to > migrate an ASE 12.5.0.2 to ASE 12.5.3 on unix 5.3 ? (64 > bits) > > Thank you Apply 12.5.2 IR then 12.5.3 esd 5. -- Jason L. Froebe http://jfroebe.livejournal.com http://www.propsmart.com Team Sybase On Tue, 31 Jan 2006 07:18:22 -0800, kamel wrote: > Here there someone to indicate me the road tasks in order to migrate an > ASE 12.5.0.2 ...

ASE 12.5.2 to ASE 12.5.0.3
Hello, I have to return to ASE 12.5.0.3 from ASE 12.5.2. I created a new server and did a dump/load of a user database and that seems to be fine. Is this a safe way to downgrade ? Is it necessary to create a new server (master database) or should it also be possible to shutdown the 12.5.2 server and start it again with the 12.5.0.3 binary ? Thanks, Luc. ...

Same machine: ASE 12.5.3 Linux vs ASE 12.5.3 Windows
We had a report from one of our clients recently that our Sybase ASE-based app was running slowly on their new Linux box. As we develop internally against ASE on Windows we thought it would be an interesting test to install both the Windows and Linux flavors of ASE 12.5.3 onto the same server and look at the relative performance. We used a Dell PowerEdge 1950 with 2 x Intel Xeon 3.73GHz dual core processors. On one drive we put SuSE Enterprise Linux Server 10 x86-64, and on the other we put Windows Server 2003 Std R2 64-bit edition. Both were fresh-off-the-CD OS installs. ...

Upgrade ASE 12.5.3 to ASE 15.0
Can I upgrade my ASE Server 12.5.3 ESD #3 to ASE15.0 without any problems? According to a ESD #2 notice, if I do this it may regress the bug compatibility?. What exactly does that mean? What should I do instead? Deborah, Brown wrote: > Can I upgrade my ASE Server 12.5.3 ESD #3 to ASE15.0 > without any problems? > > According to a ESD #2 notice, if I do this it may regress > the bug compatibility?. What exactly does that mean? What > should I do instead? It means that most of the bugs fixed in 12.5.3 ESDs haven't yet been forward-ported to 15.0....

Does ASE 12.5.3 IR is a EBF for ASE 12.5.2?
This is a multi-part message in MIME format. --------------090403060603000107000502 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit I noticed that release description of ASE 12.5.3 like the following: *Product Name: * Adaptive Server Enterprise *Platform: * Win NT *Release Version: * 12.5.2 *Release Description: * EBF 12150: 12.5.3 IR *Release Date: * 01 Dec 2004 So it seems that it's a EBF of ASE 12.5.2. But it seems not possible to download it. :( What's the matter? Must pay for that? Best Regards FlyBe...

Dump on ASE 12.5.2 and Load on ASE 12.5.3
Is possible to make one dump database in ASE 12.5.2 and later making one load of this archive in ASE 12.5.3? It is possible to dump a database in 12.5.2 and load it onto 12.5.3. -bret Leandro wrote: > Is possible to make one dump database in ASE 12.5.2 and > later making one load of this archive in ASE 12.5.3? I recently dumped from 12.0 & loaded to 12.5, was pleasantly surprised the load process upgraded the database automatically. <Leandro> wrote in message news:425d7e31.7943.1681692777@sybase.com... > Is possible to make one dump database in ASE 12...

ASE 12.5.2 or ASE 12.5.3 on Solaris 10
Hi there! Have anyone found whitepapers or documentation regarding ASE on Solaris 10? I have tried to search around on sybase.com, but without any luck. I'm particulary looking into zones/containers with ASE and if there is any issues regarding this. Any help is much appreciated. ..prn Hi, As for as i known there aren't whitepapers or docu. I have a couple of boxes with ASE in a zone. So if you have questions mail me. Sjaak Preben Olsen wrote: > Hi there! > > Have anyone found whitepapers or documentation regarding ASE > on Solaris 10? I h...

Upgrade from ASE 12.5.0.3 to ASE 12.5.1
This is a multi-part message in MIME format. ---=_forums-2-dub3ff97039 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Hi, I've just upgraded from ASE 12.5.0.3 to ASE 12.5.1. I tried to start the server, but it won't startup. Here is the message: sybase(ASE1250)328 /app/sybase/ASE-12_5/install --> startserver -f RUN_AMXDB sybase(ASE1250)329 /app/sybase/ASE-12_5/install --> 00:00000:00000:2004/01/05 21:37:44.10 kernel Use license file /app/sybase/SYSAM-1_0/licenses/license.dat. 00:00000:00000:2004/01/05 21:37:44.10 ker...

Where can I get the ASE 12.5.4 or ASE 12.5.3 for Linux?
Hi, all : I want to get the ASE12.5.4(DE) for Linux; but In the sybase.com,just have ASE 15.0.3 for download. Now, Where can I get the ASE 12.5.4(DE or XE) for Linux? Thank you! ----------------- Best Regards! On May 22, 6:10 pm, "Killwind" <killwin...@sina.com> wrote: > Hi, all : > I want to get the ASE12.5.4(DE) for Linux; > but In the sybase.com,just have ASE 15.0.3 for download. > Now, Where can I get the ASE 12.5.4(DE or XE) for Linux? Go to www.sybase.com -- in the upper-right-hand corner is a "Search" box. Search for...

ASE 12.5.0.3 to ASE 12.5.4 upgrade path
I would like to know if the migration from ASE 12.5.0.3 to 12.5.4 a direct migration or is there an intermediate upgrade to be performed in between? Take a look at the release bulletins and installation guides for your platform. These are the first steps to any upgrade. http://sybooks.sybase.com/nav/detail.do?docset=1287 <passerby> wrote in message news:484e9ca8.3f0d.1681692777@sybase.com... >I would like to know if the migration from ASE 12.5.0.3 to > 12.5.4 a direct migration or is there an intermediate > upgrade to be performed in between? ...

Upgrade ASE 12.0/64 bit on AIX 4.3.3 to ASE 12.5.x/64 bit on AIX 5.2
Trying to find what is the supported upgrade path for the above. Going from 12.0/64bit on AIX 4.3.3 to 12.5 (or 12.5.x) 64bit on AIX 5.2. If I put AIX 5.2 , the dataserver binary of 12.0 will not work which is used during the upgrade. If I put 12.5.x on 4.3.3, the dataserver binary will not work beacuse it is new binary for 5.2. Other than backup the databases and load them to the new server, is there a supported how to upgrade? Thanks! ...

Web resources about - ASE 15 slower than ASE 12.5.3 in testing - any other experiences? - sybase.ase.general

Total Experience Records - Wikipedia, the free encyclopedia
... studio, launching his production company, and met the Wilson brothers through a friend, singer D. J. Rogers . After signing Total Experience ...

Telstra mobile customers experience mass outage
Telstra’s mobile services have been largely restored with the telco pointing to “an embarrassing human error” as the main culprit for a nationwide ...

US Republican debate: Marco Rubio wilts amid questions on experience, offering hope to rivals including ...
Republican contender Marco Rubio comes under heavy attack in a presidential debate from rivals who accuse him of being too inexperienced for ...

Marketers Are Changing Their Approach to Measuring Customer Experience
A new study shows how marketers are changing their approach to measuring the customer experience. Now, the top metrics being used to measure ...

Day One 2 brings an even more streamlined journaling experience
Day One 2 Journal + Notes Day One 2 brings an even more streamlined journaling experience is a story by AppAdvice.

Charlie Sheen Returns To "The Dr. Oz Show" For Another Exclusive In Depth Interview On His Experience ...
CBS News 8 and CBS8.com is the local source for San Diego breaking news and top story headlines. Get the latest San Diego TV news, sports, weather ...

Bush jabs Rubio over leadership experience
During the ABC News Republican debate, former Florida Gov. Jeb Bush criticized Sen. Marco Rubio for his lack of executive experience.

Bush jabs Rubio over leadership experience
During the ABC News Republican debate, former Florida Gov. Jeb Bush criticized Sen. Marco Rubio for his lack of executive experience.

Christie, Rubio lock horns: Rubio 'does not have the experience' to be President
... night’s debate was between Chris Christie and Marco Rubio, or rather from Chris Christie towards Marco Rubio. Rubio was asked about his experience, ...

Create VR experiences within VR itself using Unreal Engine
Epic Games has been teasing "the future of VR development" recently, and the team is finally ready to tell everyone what that is: Creating virtual ...

Resources last updated: 2/10/2016 12:52:45 PM