ASE 15 Cursor Performance Degradation

[ahood]

Is there a change between ASE 12.5 and 15.0.3 that
significantly increases cursor processing overhead?

Are there any server configuration parameters which increase
or decrease this overhead?

Background

We have a Cobol batch job, which makes painful use of
cursors, running in about 4hrs in ASE 12.5. This same job
is taking 8-12 hours in ASE 15.0.3.

Watching the job in ASE 15 I see excessive CPU utilization
and very little logical reads. Every 1.5 seconds of
execution time uses 1 second of CPU time. Every 1 second of
CPU time results in 5,000 logical reads. There are
virtually no physical reads. (from monProcessActivity).

Using sp_showplan I can never catch this process executing a
query.

The cursor select statements are very simple two and three
table joins returning less than 10 records each. Running
them via isql shows no issues with query plan performance.

It appears that the cursor creation, fetch, and deallocation
overhead is killing us.

Any ideas on why the performance is so different between ASE
12.5 and 15.0.3?

[Cory Sane [TeamSybase]]

I did a ASE 15 in March and we struggled for a while until we did a reorg rebuild on every table to eliminate the problem. My
problem was related to a million queries that jumped from 1 sec to 2 secs.

Your Background is a good explanation of the effects of the problem. But we need to find the deltas.
Did you change hardware? Did you switch the client drivers? have you run reorg rebuild on every table or recreated every APL
clustered index?

What does CMDTEXT audit show?

--
Cory Sane
[TeamSybase]
Certified Sybase Associate DBA for ASE 15.0
"ahood" wrote in message news:4a7a17df.181...@sybase.com...

[Mark A. Parsons]

Without more details I'd guesstimate that your batch process generates a large volume of queries (similar to Cory's 1
miilion query batch). If this is the case then one possible issue you could be running into is the (much) slower
optimizer in ASE 15.

The ASE 15 optimizer has a lot more work to do (eg, more types of joins to consider) which can lead to noticeable
degradation in performance for processes with large volumes of queries (eg, Cory's 1 million query batch process; large
volume transactions processed over DSI connections into a replicate database in an ASE 15 dataserver). In a situation
like this you'll see the dataserver's cpu utilization jump up while cache hits remains (relatively) low (ie, the
dataserver is burning cpu cycles while compiling queries).

Some options for improving performance for large batches of SQL queries:

- enable statement cache and literal autoparam; while each query will still need to be parsed, hashed and compared to
other queries in the statement cache ... this is a *LOT* better/faster than having to compile each query; Note however
that these settings may also make matters worse if there are instances where similar-looking queries could be optimized
differently based on different static values (the issue here is with the re-use of 'bad' query plans, akin to the same
issue with a stored proc's query that can be optimized differently based on different input parameters)

- modify the calling application to use prepared statements (in essence the application creates temporary stored procs
for processing look-alike queries)

- replace common, high-volume queries with equivalent stored procs ... then modify the client application to call the
stored procs; obviously (?) you'll want/need to design the stored procs so that you do not end up with the issue of
re-using 'bad' query plans (ie, different input parameters require stored proc queries to be compiled differently)

- if the same query (or set of queries) is being executed over and over again for a large set of input values ...
consider loading the set of input parameters into a staging table (eg, in tempdb) and then replacing the cursor-like
processing (ie, repetitive calls to the same query(s) over and over) with set-based processing (ie, joining the staging
table to a single set of queries); obviously this will require a re-write of the client application

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

Another situation [which may or may not apply in your situation] I've seen that leads to degraded batch performance is
an application that performs a large volume of connect/disconnect activity with the dataserver.

The 'problem' is an application which performs a login/connect to the dataserver, runs a small batch of queries, logs
out of the dataserver, and then goes through the same connect/run/disconnect steps for each set of input data to be
processed. The act of connecting/disconnecting requires a good bit of [extra] processing by the underlying OS as well
as the dataserver engine.

Obviously (?) a heavy volume of connect/disconnect activity is 'bad' regardless of the ASE version ... but I've seen a
noticeable degradation in such activities when switching from ASE 12.x to ASE 15.x.

The (obvious ?) solution in this case is to recode the application so that it re-uses it's connection(s) into the
dataserver ... ideally using 1 connection for the entire batch process.

I've seen batch processes shed 20-40% of their total processing time by eliminating excessive connect/disconnect activity.

NOTE: Excessive connect/disconnect activity can be noticed several ways ... sustained high (> 1 per second) new user
connections in sp_sysmon output ... high volume of audited logins (via auditing and the sybsecurity database) ...
constantly changing spid #'s in sysprocesses (or monProcess%) for a single job ... low BatchID numbers in monSysSQLText
('low' because BatchID is reset to 1 each time a process logs in; 'high' BatchID numbers are indicative of a process
which re-uses it's connection).

[lmarie]

Hi ! Do you find solution to your performance issue with
cursor ? without rewriting application ?

Thanks for your response
Lionel M

[Vickey]

I suspect Sybase did not test ASE thoroughly and now they are just trying to
blame it on users.
This approach may backfire.

Guys fix your product or leave the market.

PS: The reason I am angry is that Sybase have forced us into this mess and
now are just standing by to watch us struggle with the mess.

"lmarie" wrote in message news:4ab39afe.700...@sybase.com...

[Carl Kayser]

Not trying to be an apologist for Sybase but ... comments and questions
embedded below

"Vickey" <contact...@yahoo.com> wrote in message
news:4b035490$1@forums-1-dub...

>I suspect Sybase did not test ASE thoroughly

This could be true for any new product. We have a perf problem on Oracle 11
that didn't exist on 10 (with no code change involved). No resolution from
Oracle yet. OTOH, you could be right.

> and now they are just trying to blame it on users.

I don't see anything in this thread where a Sybase rep "blames it on the
users" (or vendor). Is there something that is missing (e.g., a Sybase
response on a case involving this issue) from this thread?

> This approach may backfire.
>
> Guys fix your product or leave the market.
>
> PS: The reason I am angry is that Sybase have forced us into this mess and
> now are just standing by to watch us struggle with the mess.
>

I assume you mean that the force is per EOL support at the end of this year.
Correct? And the "just standing by" is some form of non-response on a case
concerning this issue?

[Vickey]

What I meant for 'Sybase did not test ASE thoroughly' is: No one should
expect clients to change existing application code to upgrade. That's just
insane. The best approach should have been to make ASE work for existing
application code (which BTW is already tested and deployed) and use of new
features should not be dependent on NOT being in compatibility mode. How can
Sybase make these two factors to be mutually exclusive??

Now, 'blame it on users' means when we call Sybase for support they say we
did not test our applications rather than suggesting solutions. This relates
to point 1 above. Why should we need to test and change an existing(already
tested) application(s).

And, yes, by forcing I mean EOL.
Our company is paying huge amount for support. But, all we hear back is we
need to fix our application code.

"Carl Kayser" <kays...@bls.gov> wrote in message
news:4b0417c8$1@forums-1-dub...

[Vickey]

This document does not have all that create problems.
I conclude. No more comments.

"Carl Kayser" <kays...@bls.gov> wrote in message

news:4b055560@forums-1-dub...

>
> "Vickey" <contact...@yahoo.com> wrote in message

> news:4b04bc08@forums-1-dub...

>> What I meant for 'Sybase did not test ASE thoroughly' is: No one should
>> expect clients to change existing application code to upgrade. That's
>> just insane. The best approach should have been to make ASE work for
>> existing application code (which BTW is already tested and deployed) and
>> use of new features should not be dependent on NOT being in compatibility
>> mode. How can Sybase make these two factors to be mutually exclusive??
>>
>

> (SNIP)
>
> I'm a DBA and just putting in my viewpoints. I'm guessing that most of
> the issues are in
> http://www.sybase.com/files/White_Papers/SY-Required-SQL-Changes-for-ASE15-v.1-073009-WP.pdf,
> which does not seem to have been advertised well.
>
> I think that the "group by without order by" change is legit. I've seen
> this with both Sybase and Oracle. It's the fault of the developers when
> they expect an index to do the ordering for them.
>
> The 255-byte identifier issue isn't much of an issue. Although a "real
> change" I think that most customers prefer the change to using vdevno on
> sysusages.
>
> If you write your own procs that use system tables (like I do) then
> syspartitions (and the transfer of some info from sysobjects or sysindexes
> to syspartitions) is a nuisance if you don't use partitioning. But I
> don't think this belongs in applications.
>
> The cursor and OAM function changes ... well, I can see that the latter
> could be a headache and there should be a traceflag that can turn off the
> irritating messages (and you can live with the possibility of some
> misleading results). The cursor change seems to be one of those "we
> designed something wrong way back when and now we have to fix it" issues.
> It's ugly and I am glad that we can change our code to resolve the issue.
> (The changes are easy. It's all of the testing that is a headache.) Most
> of the DBMS vendors have historically painted themselves into corners and
> the fixes/enhancements provided agony for the customer. (We will have to
> do unload/loads when we convert SQL Anywhere 9 to 11 as an example.) I'm
> glad that we develop most of our apps and can control the changes.
> There's not much else that I can say.
>