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.

Sparse Column in ASE 15.x

7 posts in General Discussion Last posting was on 2010-11-11 15:52:15.0Z
VJ Posted on 2010-11-09 15:03:23.0Z
Reply-To: "VJ" <jainv@michigan.gov>
From: "VJ" <jainv@michigan.gov>
Newsgroups: sybase.public.ase.general
Subject: Sparse Column in ASE 15.x
Lines: 10
Organization: VJ
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5994
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4cd962bb$1@forums-1-dub>
Date: 9 Nov 2010 07:03:23 -0800
X-Trace: forums-1-dub 1289315003 10.22.241.152 (9 Nov 2010 07:03:23 -0800)
X-Original-Trace: 9 Nov 2010 07:03:23 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29688
Article PK: 78917

Does any one have any idea of implementing Sparse Columns in Sybase ASE 15.x
databases.

fyi : A sparse column is another tool used to reduce the amount of physical
storage used in a database. They are the ordinary columns that have an
optimized storage for null values. Sparse columns reduce the space
requirements for null values at the cost of more overhead to retrieve
nonnull values


"Mark A. Parsons" <iron_horse Posted on 2010-11-09 20:22:24.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: Sparse Column in ASE 15.x
References: <4cd962bb$1@forums-1-dub>
In-Reply-To: <4cd962bb$1@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: <4cd9ad80@forums-1-dub>
Date: 9 Nov 2010 12:22:24 -0800
X-Trace: forums-1-dub 1289334144 10.22.241.152 (9 Nov 2010 12:22:24 -0800)
X-Original-Trace: 9 Nov 2010 12:22:24 -0800, vip152.sybase.com
Lines: 17
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29690
Article PK: 78922

Ummmm ... storing a NULL in a (nullable) column doesn't get much sparser than that, and Sybase is pretty good at
retrieving NULL and non-NULL columns.

I'm probably missing your question/point ... could you provide more details of what you're talking about in comparison
to Sybase nullable columns ?

VJ wrote:
> Does any one have any idea of implementing Sparse Columns in Sybase ASE 15.x
> databases.
>
> fyi : A sparse column is another tool used to reduce the amount of physical
> storage used in a database. They are the ordinary columns that have an
> optimized storage for null values. Sparse columns reduce the space
> requirements for null values at the cost of more overhead to retrieve
> nonnull values
>
>


VJ Posted on 2010-11-10 18:49:33.0Z
Reply-To: "VJ" <jainv@michigan.gov>
From: "VJ" <jainv@michigan.gov>
Newsgroups: sybase.public.ase.general
References: <4cd962bb$1@forums-1-dub> <4cd9ad80@forums-1-dub>
Subject: Re: Sparse Column in ASE 15.x
Lines: 31
Organization: VJ
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5994
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4cdae93d@forums-1-dub>
Date: 10 Nov 2010 10:49:33 -0800
X-Trace: forums-1-dub 1289414973 10.22.241.152 (10 Nov 2010 10:49:33 -0800)
X-Original-Trace: 10 Nov 2010 10:49:33 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29692
Article PK: 78920

We have a table in the database having more than 100 column of all INT type
but nullable .

Acc. to our DBA , "why we have not left the values as NULL when not needed
because that saves space " was the question.

Does it actually takes more space if they all are nullable columns ?

"Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in message
news:4cd9ad80@forums-1-dub...
> Ummmm ... storing a NULL in a (nullable) column doesn't get much sparser
> than that, and Sybase is pretty good at retrieving NULL and non-NULL
> columns.
>
> I'm probably missing your question/point ... could you provide more
> details of what you're talking about in comparison to Sybase nullable
> columns ?
>
> VJ wrote:
>> Does any one have any idea of implementing Sparse Columns in Sybase ASE
>> 15.x databases.
>>
>> fyi : A sparse column is another tool used to reduce the amount of
>> physical storage used in a database. They are the ordinary columns that
>> have an optimized storage for null values. Sparse columns reduce the
>> space requirements for null values at the cost of more overhead to
>> retrieve nonnull values


"Mark A. Parsons" <iron_horse Posted on 2010-11-10 20:56:57.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: Sparse Column in ASE 15.x
References: <4cd962bb$1@forums-1-dub> <4cd9ad80@forums-1-dub> <4cdae93d@forums-1-dub>
In-Reply-To: <4cdae93d@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: <4cdb0719$1@forums-1-dub>
Date: 10 Nov 2010 12:56:57 -0800
X-Trace: forums-1-dub 1289422617 10.22.241.152 (10 Nov 2010 12:56:57 -0800)
X-Original-Trace: 10 Nov 2010 12:56:57 -0800, vip152.sybase.com
Lines: 110
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29693
Article PK: 78921

Sybase does require a few more bytes for managing nullable columns, even if they contain a non-NULL value.

A simplistic example:

===========================================
use tempdb
go

create table t1 (a int not null, b int not null, c int not null)
create table t2 (a int null, b int null, c int null)
go

insert t1 values (1,2,3)
insert t2 values (1,2,3)
go

select left(object_name(id),2),firstpage
from syspartitions
where id in (object_id('t1'),object_id('t2'))
go

firstpage
-- -----------
t1 104450
t2 104457

(2 rows affected)

set switch on 3604
go

dbcc page(tempdb,104450,1) -- t1
dbcc page(tempdb,104457,1) -- t2
go

-- dbcc page output ...

... snip ... (record in t1)

Offset 32 - row ID=0 row length=14 # varlen cols=0
25105820 ( 0): 00000100 00000200 00000300 0000 ..............
Row-Offset table for variable-length columns:
[<varcol number>, <offset from start of the row>, <varcol length>]


... snip ... (record in t2)

Offset 32 - row ID=0 row length=21 # varlen cols=3
25105820 ( 0): 03001500 01000000 02000000 03000000 ................
25105830 ( 16): 04100c08 04 .....
Row-Offset table for variable-length columns:
[<varcol number>, <offset from start of the row>, <varcol length>]
[1, 4, 4] [2, 8, 4] [3, 12, 4]

... snip ...

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

Notice that the t1 row requires 14 bytes and has 0 variable length columns (varlen cols), while the t2 row requires 21
bytes and has 3 variable length columns. This (obviously) shows up with the t2 row having a longer hex string.

If you now update various t2 columns to be NULL and run the dbcc page() command again, you'll see the number of bytes
fluctuate and the leading/training bytes of the hex string will change in number and value.

------

So, if you don't plan on storing NULL in a column then it would make sense to define the column as non-nullable in order
to save some space. The more columns you convert, and the larger the number of records, the more space you stand to save.

Whether you notice any space savings will depend on how much more narrower you row becomes and whether this frees up
enough room to squeeze an extra row or two onto a data page.

And while I don't have any performance stats, I wouldn't be surprised if the dataserver needs a few additional clock
cycles (microseconds) when processing a record that contains nullable columns.

VJ wrote:
> We have a table in the database having more than 100 column of all INT type
> but nullable .
>
> Acc. to our DBA , "why we have not left the values as NULL when not needed
> because that saves space " was the question.
>
> Does it actually takes more space if they all are nullable columns ?
>
>
>
> "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in message
> news:4cd9ad80@forums-1-dub...
>> Ummmm ... storing a NULL in a (nullable) column doesn't get much sparser
>> than that, and Sybase is pretty good at retrieving NULL and non-NULL
>> columns.
>>
>> I'm probably missing your question/point ... could you provide more
>> details of what you're talking about in comparison to Sybase nullable
>> columns ?
>>
>> VJ wrote:
>>> Does any one have any idea of implementing Sparse Columns in Sybase ASE
>>> 15.x databases.
>>>
>>> fyi : A sparse column is another tool used to reduce the amount of
>>> physical storage used in a database. They are the ordinary columns that
>>> have an optimized storage for null values. Sparse columns reduce the
>>> space requirements for null values at the cost of more overhead to
>>> retrieve nonnull values
>
>


"Mark A. Parsons" <iron_horse Posted on 2010-11-10 21:13:56.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: Sparse Column in ASE 15.x
References: <4cd962bb$1@forums-1-dub> <4cd9ad80@forums-1-dub> <4cdae93d@forums-1-dub> <4cdb0719$1@forums-1-dub>
In-Reply-To: <4cdb0719$1@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: <4cdb0b14$1@forums-1-dub>
Date: 10 Nov 2010 13:13:56 -0800
X-Trace: forums-1-dub 1289423636 10.22.241.152 (10 Nov 2010 13:13:56 -0800)
X-Original-Trace: 10 Nov 2010 13:13:56 -0800, vip152.sybase.com
Lines: 130
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29694
Article PK: 78924

small correction/clarification ...

When changing a t2 column from non-NULL to NULL, the hex string (from dbcc page output) will shrink primarily because
you just removed the need for a 4-byte integer, while the position of the column in the row (middle of row, end of row)
may dictate whether an additional byte disappears from the hex string.

Use some single digit numbers for the column values so that they are easy to pick out of the hex string, then play with
some different updates followed by dbcc page(), and you should be able to figure out what's happening to the hex string.

"Duh, Mark!" ?

Mark A. Parsons wrote:
> Sybase does require a few more bytes for managing nullable columns, even
> if they contain a non-NULL value.
>
> A simplistic example:
>
> ===========================================
> use tempdb
> go
>
> create table t1 (a int not null, b int not null, c int not null)
> create table t2 (a int null, b int null, c int null)
> go
>
> insert t1 values (1,2,3)
> insert t2 values (1,2,3)
> go
>
> select left(object_name(id),2),firstpage
> from syspartitions
> where id in (object_id('t1'),object_id('t2'))
> go
>
> firstpage
> -- -----------
> t1 104450
> t2 104457
>
> (2 rows affected)
>
> set switch on 3604
> go
>
> dbcc page(tempdb,104450,1) -- t1
> dbcc page(tempdb,104457,1) -- t2
> go
>
> -- dbcc page output ...
>
> ... snip ... (record in t1)
>
> Offset 32 - row ID=0 row length=14 # varlen cols=0
> 25105820 ( 0): 00000100 00000200 00000300 0000 ..............
> Row-Offset table for variable-length columns:
> [<varcol number>, <offset from start of the row>, <varcol length>]
>
>
> ... snip ... (record in t2)
>
> Offset 32 - row ID=0 row length=21 # varlen cols=3
> 25105820 ( 0): 03001500 01000000 02000000 03000000 ................
> 25105830 ( 16): 04100c08 04 .....
> Row-Offset table for variable-length columns:
> [<varcol number>, <offset from start of the row>, <varcol length>]
> [1, 4, 4] [2, 8, 4] [3, 12, 4]
>
> ... snip ...
>
> ===========================================
>
> Notice that the t1 row requires 14 bytes and has 0 variable length
> columns (varlen cols), while the t2 row requires 21 bytes and has 3
> variable length columns. This (obviously) shows up with the t2 row
> having a longer hex string.
>
> If you now update various t2 columns to be NULL and run the dbcc page()
> command again, you'll see the number of bytes fluctuate and the
> leading/training bytes of the hex string will change in number and value.
>
> ------
>
> So, if you don't plan on storing NULL in a column then it would make
> sense to define the column as non-nullable in order to save some space.
> The more columns you convert, and the larger the number of records, the
> more space you stand to save.
>
> Whether you notice any space savings will depend on how much more
> narrower you row becomes and whether this frees up enough room to
> squeeze an extra row or two onto a data page.
>
> And while I don't have any performance stats, I wouldn't be surprised if
> the dataserver needs a few additional clock cycles (microseconds) when
> processing a record that contains nullable columns.
>
>
>
>
> VJ wrote:
>> We have a table in the database having more than 100 column of all INT
>> type but nullable .
>>
>> Acc. to our DBA , "why we have not left the values as NULL when not
>> needed because that saves space " was the question.
>>
>> Does it actually takes more space if they all are nullable columns ?
>>
>>
>>
>> "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in
>> message news:4cd9ad80@forums-1-dub...
>>> Ummmm ... storing a NULL in a (nullable) column doesn't get much
>>> sparser than that, and Sybase is pretty good at retrieving NULL and
>>> non-NULL columns.
>>>
>>> I'm probably missing your question/point ... could you provide more
>>> details of what you're talking about in comparison to Sybase nullable
>>> columns ?
>>>
>>> VJ wrote:
>>>> Does any one have any idea of implementing Sparse Columns in Sybase
>>>> ASE 15.x databases.
>>>>
>>>> fyi : A sparse column is another tool used to reduce the amount of
>>>> physical storage used in a database. They are the ordinary columns
>>>> that have an optimized storage for null values. Sparse columns
>>>> reduce the space requirements for null values at the cost of more
>>>> overhead to retrieve nonnull values
>>
>>


Bret Halford Posted on 2010-11-11 15:45:10.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.12) Gecko/20101027 Thunderbird/3.1.6
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Sparse Column in ASE 15.x
References: <4cd962bb$1@forums-1-dub> <4cd9ad80@forums-1-dub> <4cdae93d@forums-1-dub>
In-Reply-To: <4cdae93d@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: <4cdc0f86$1@forums-1-dub>
Date: 11 Nov 2010 07:45:10 -0800
X-Trace: forums-1-dub 1289490310 10.22.241.152 (11 Nov 2010 07:45:10 -0800)
X-Original-Trace: 11 Nov 2010 07:45:10 -0800, vip152.sybase.com
Lines: 24
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29698
Article PK: 78927


On 11/10/2010 11:49 AM, VJ wrote:
> We have a table in the database having more than 100 column of all INT type
> but nullable .
>
> Acc. to our DBA , "why we have not left the values as NULL when not needed
> because that saves space " was the question.
>
> Does it actually takes more space if they all are nullable columns ?

Hi VJ,

The DBA's question doesn't quite make sense to me.

Does he mean that you have the columns declared as NULLABLE but
are using a default value rather than NULL when you could use NULL?

Does he mean that you have declared the columns as NULLABLE but
don't actually have any NULLs in them?

Something else?

-bret


Bret Halford Posted on 2010-11-11 15:52:15.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.12) Gecko/20101027 Thunderbird/3.1.6
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Sparse Column in ASE 15.x
References: <4cd962bb$1@forums-1-dub> <4cd9ad80@forums-1-dub>
In-Reply-To: <4cd9ad80@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: <4cdc112f$1@forums-1-dub>
Date: 11 Nov 2010 07:52:15 -0800
X-Trace: forums-1-dub 1289490735 10.22.241.152 (11 Nov 2010 07:52:15 -0800)
X-Original-Trace: 11 Nov 2010 07:52:15 -0800, vip152.sybase.com
Lines: 41
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29699
Article PK: 78929

The "fyi" paragraph is a quote from an online blog entry regarding
a MS SQL Server 2008 feature.
http://blog.sqlauthority.com/2008/07/10/sql-server-2008-introduction-to-sparse-columns/

There is no direct equivalent in ASE 15.x

It might be possible to write something similar into a database
schema by splitting the table's data into two or more tables.
all the columns that commonly have data in the main table, along
with a unique identifier. The additional tables would contain the
values for columns (or related groups of columns)
that seldom had values, either as a wide table (id,col1,col2,col3)
or narrow (id, colname, value).

My guess is that in general, the added complexity and programming
overhead would not be worth the disk space savings.

-bret

On 11/9/2010 1:22 PM, Mark A. Parsons wrote:
> Ummmm ... storing a NULL in a (nullable) column doesn't get much sparser
> than that, and Sybase is pretty good at retrieving NULL and non-NULL
> columns.
>
> I'm probably missing your question/point ... could you provide more
> details of what you're talking about in comparison to Sybase nullable
> columns ?
>
> VJ wrote:
>> Does any one have any idea of implementing Sparse Columns in Sybase
>> ASE 15.x databases.
>>
>> fyi : A sparse column is another tool used to reduce the amount of
>> physical storage used in a database. They are the ordinary columns
>> that have an optimized storage for null values. Sparse columns reduce
>> the space requirements for null values at the cost of more overhead to
>> retrieve nonnull values
>>