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.

Sybase ASE performance

4 posts in Cluster Last posting was on 2012-08-18 15:25:59.0Z
Arnold Nair Posted on 2010-09-29 10:38:11.0Z
Sender: 6722.4ca31529.1804289383@sybase.com
From: Arnold Nair
Newsgroups: sybase.public.ase.cluster
Subject: Sybase ASE performance
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ca31713.6754.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 29 Sep 2010 03:38:11 -0700
X-Trace: forums-1-dub 1285756691 10.22.241.41 (29 Sep 2010 03:38:11 -0700)
X-Original-Trace: 29 Sep 2010 03:38:11 -0700, 10.22.241.41
Lines: 16
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.cluster:219
Article PK: 48497

Hi

We have a table in our database which has grown very large,
and to matter worse it has been extensively used for all the
DML operations. This is the most important table in our
application and any changes to this table will lead to huge
development effort. Now we have a scenario where the
slowness in the application is quiet evident given the size
of this table.

How do we tackle this problem, what are the different ways
to approach this problem and avoid it being a major show
stopper

Regards,
Arnold.


Rob V [ Sybase ] Posted on 2010-09-29 11:24:34.0Z
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.9) Gecko/20100915 Thunderbird/3.1.4
MIME-Version: 1.0
Newsgroups: sybase.public.ase.cluster
Subject: Re: Sybase ASE performance
References: <4ca31713.6754.1681692777@sybase.com>
In-Reply-To: <4ca31713.6754.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: <4ca321f2@forums-1-dub>
Date: 29 Sep 2010 04:24:34 -0700
X-Trace: forums-1-dub 1285759474 10.22.241.152 (29 Sep 2010 04:24:34 -0700)
X-Original-Trace: 29 Sep 2010 04:24:34 -0700, vip152.sybase.com
Lines: 61
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.cluster:220
Article PK: 48499


On 29-Sep-2010 12:38, Arnold Nair wrote:
> Hi
>
> We have a table in our database which has grown very large,
> and to matter worse it has been extensively used for all the
> DML operations. This is the most important table in our
> application and any changes to this table will lead to huge
> development effort. Now we have a scenario where the
> slowness in the application is quiet evident given the size
> of this table.
>
> How do we tackle this problem, what are the different ways
> to approach this problem and avoid it being a major show
> stopper
>
> Regards,
> Arnold.

There may not be an easy answer or solution (also depending on your
definitoin of 'easy').
When you mention 'slowness', it is crucial to understand what this is
caused by: is it due to lock contention? (perhaps change lock scheme?)
Is it that queries need to scan too many pages? (perhaps add different
indexes) Is it that your data cache hit rates are too low? (increase
their sizes). You'll need this understanding first.

If it's a bit of everything because the table has just gotten too large,
one option is to clean up the table, and remove old rows, thus making it
smaller.
Another option could be to use semantic table partitioning so as to
reduce the size of the table that needs to be accessed for certain
queries. The tricky part here will be to figure out the partitioning
scheme that works best with your application.

In summary, you will need to perform some detailed analysis to
understand precisly what's happening in your ASE server, before you can
decide on possible remedies.

Note that this is unrelated to ASE Cluster Edition, so it would have
bene more appropraitely posted in sybase.public.ase.general .

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------


"Mark A. Parsons" <iron_horse Posted on 2010-09-29 18:23:46.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.cluster
Subject: Re: Sybase ASE performance
References: <4ca31713.6754.1681692777@sybase.com> <4ca321f2@forums-1-dub>
In-Reply-To: <4ca321f2@forums-1-dub>
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: <4ca38432@forums-1-dub>
Date: 29 Sep 2010 11:23:46 -0700
X-Trace: forums-1-dub 1285784626 10.22.241.152 (29 Sep 2010 11:23:46 -0700)
X-Original-Trace: 29 Sep 2010 11:23:46 -0700, vip152.sybase.com
Lines: 79
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.cluster:221
Article PK: 48498

Other considerations ...

If the table uses the allpages locking scheme and has a non-unique clustered index, excessive overflow pages can cause
performance degradation. (The dataserver can spend an inordinate amount of time scanning overflow pages for any DML
operations that hit on the index key values.) Look for clustered index key sets with lots of duplicate values.
Making the index unique, or replacing the clustered index with a different (and more unique) column set can
reduce/eliminate overflow pages.

If the table uses datarows/datapages locking, take a look at the size of the indexes. If any of the indexes are
extremely large, especially in relation to the size of the data pages, then take a look at the optdiag (or systabstats)
data for the indexes in question. What you're looking for is a large number of empty leaf pages (in which case the
dataserver will spend an inordinate amount of time scanning empty leaf pages => performance degradation). For DOL
tables with a high volume of inserts/updates/deletes the dataserver may not do a very good job of garbage collecting
empty leaf pages in an index. Dropping/rebuilding the index will eliminate the empty leaf pages. [I've seen some
client DOL tables with 100 rows and 4GB-8GB of empty index leaf pages, ymmv.]

Rob V [ Sybase ] wrote:
> On 29-Sep-2010 12:38, Arnold Nair wrote:
>> Hi
>>
>> We have a table in our database which has grown very large,
>> and to matter worse it has been extensively used for all the
>> DML operations. This is the most important table in our
>> application and any changes to this table will lead to huge
>> development effort. Now we have a scenario where the
>> slowness in the application is quiet evident given the size
>> of this table.
>>
>> How do we tackle this problem, what are the different ways
>> to approach this problem and avoid it being a major show
>> stopper
>>
>> Regards,
>> Arnold.
>
> There may not be an easy answer or solution (also depending on your
> definitoin of 'easy').
> When you mention 'slowness', it is crucial to understand what this is
> caused by: is it due to lock contention? (perhaps change lock scheme?)
> Is it that queries need to scan too many pages? (perhaps add different
> indexes) Is it that your data cache hit rates are too low? (increase
> their sizes). You'll need this understanding first.
>
> If it's a bit of everything because the table has just gotten too large,
> one option is to clean up the table, and remove old rows, thus making it
> smaller.
> Another option could be to use semantic table partitioning so as to
> reduce the size of the table that needs to be accessed for certain
> queries. The tricky part here will be to figure out the partitioning
> scheme that works best with your application.
>
> In summary, you will need to perform some detailed analysis to
> understand precisly what's happening in your ASE server, before you can
> decide on possible remedies.
>
> Note that this is unrelated to ASE Cluster Edition, so it would have
> bene more appropraitely posted in sybase.public.ase.general .
>
> HTH,
>
> Rob V.
> -----------------------------------------------------------------
> Rob Verschoor
>
> Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
> and Replication Server 15.0.1/12.5 // TeamSybase
>
> Author of Sybase books (order online at www.sypron.nl/shop):
> "Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
> "The Complete Sybase ASE Quick Reference Guide"
> "The Complete Sybase Replication Server Quick Reference Guide"
>
> mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
> http://www.sypron.nl
> Sypron B.V., Amersfoort, The Netherlands
> Chamber of Commerce 27138666
> -----------------------------------------------------------------
>


Ranjit Posted on 2012-08-18 15:25:59.0Z
From: Ranjit <mandal.ranjit@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:12.0) Gecko/20120428 Thunderbird/12.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.cluster
Subject: Re: Sybase ASE performance
References: <4ca31713.6754.1681692777@sybase.com>
In-Reply-To: <4ca31713.6754.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: <502fb407@forums-1-dub>
Date: 18 Aug 2012 08:25:59 -0700
X-Trace: forums-1-dub 1345303559 172.20.134.152 (18 Aug 2012 08:25:59 -0700)
X-Original-Trace: 18 Aug 2012 08:25:59 -0700, vip152.sybase.com
Lines: 29
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.cluster:255
Article PK: 48534

Hi,
1) If you have partitioning license then use hash or other semantic
partition which suits your environment. Try with four partition.

2)If you have hardware RAID then use RAID10 which will improve performance.

Above methods has no impact on development.

Regards,
Ranjit

On 9/29/2010 4:08 PM, Arnold Nair wrote:
> Hi
>
> We have a table in our database which has grown very large,
> and to matter worse it has been extensively used for all the
> DML operations. This is the most important table in our
> application and any changes to this table will lead to huge
> development effort. Now we have a scenario where the
> slowness in the application is quiet evident given the size
> of this table.
>
> How do we tackle this problem, what are the different ways
> to approach this problem and avoid it being a major show
> stopper
>
> Regards,
> Arnold.