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.

Increasing column size to 1938 give performance issue

2 posts in General Discussion Last posting was on 2009-10-29 00:49:41.0Z
senthil Posted on 2009-10-29 00:18:05.0Z
Sender: 3410.4ae8dc9c.1804289383@sybase.com
From: Senthil
Newsgroups: sybase.public.ase.general
Subject: Increasing column size to 1938 give performance issue
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ae8df3d.3494.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 28 Oct 2009 16:18:05 -0800
X-Trace: forums-1-dub 1256775485 10.22.241.41 (28 Oct 2009 16:18:05 -0800)
X-Original-Trace: 28 Oct 2009 16:18:05 -0800, 10.22.241.41
Lines: 3
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28564
Article PK: 77808

I have table that has 45 columns and have 8 million records.
I have requirement to increase one of the column size upto
1938 in that table. Is that will give any performance issue?


"Mark A. Parsons" <iron_horse Posted on 2009-10-29 00:49:41.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: Increasing column size to 1938 give performance issue
References: <4ae8df3d.3494.1681692777@sybase.com>
In-Reply-To: <4ae8df3d.3494.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 091014-0, 10/14/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ae8e6a5$1@forums-1-dub>
Date: 28 Oct 2009 16:49:41 -0800
X-Trace: forums-1-dub 1256777381 10.22.241.152 (28 Oct 2009 16:49:41 -0800)
X-Original-Trace: 28 Oct 2009 16:49:41 -0800, vip152.sybase.com
Lines: 34
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28565
Article PK: 77806

Yes. No. Maybe. It depends ... a few off-the-cuff comments ...

You'll (obviously ?) need to have your dataserver configured with a 4KB, 8KB or 16KB page size. The issue here is that
you won't be able to fit 45 columns, with one of those columns being 1938 bytes in length, on a 2KB data page.

If your columns is 1938 characters in length, but is based on a 2-byte character set, then you'll need at least an 8KB
dataserver page size. Again, the issue is that the 45 columns plus the 1938-character column won't fit on a 2KB or 4KB
data page.

If this new column will be part of an index then (I believe) you'll need to use an 8KB or 16KB dataserver page size.
The issue here is the minimum page size requirement for an index greater than 1938-characters in size.

NOTE: For minimum page size considerations have a look at the results of running "dbcc traceon(3604), dbcc serverlimits".

If you plan on performing updates of this column which could cause the row to grow considerably (eg, insert with 0/1
characters, but update with 1900+ characters), you may want to consider defining the column as "char(1938) not null", or
using one of the space configuration settings (eg, max row size, expected row size). The issue here is to minimize the
overhead of page splits due to newly updated records that will no longer fit on the current page.

If this column will be part of an index, and the column will routinely contain a large volume of text, your index(es)
will use up a large amount of space and be several levels deep. A couple issues here ... amount of space used up in
data cache to house the index pages ... number of IOs to find/insert/update records in the index.

Obviously (?) if this column will routinely be near its max size then your table size will grow. This in could cause
performance issues in terms of having to read more pages from disk, as well as using up more space in your data cache.
This performance hit would occur with normal SQL as well as maintenance operations (eg, update stats, dbcc's, etc).

I'm sure I'm missing a few (potential) issues/concerns ...

Senthil wrote:
> I have table that has 45 columns and have 8 million records.
> I have requirement to increase one of the column size upto
> 1938 in that table. Is that will give any performance issue?