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.

entry in syspartions for none-partioned table

6 posts in General Discussion Last posting was on 2010-03-09 13:10:36.0Z
tartampion Posted on 2010-03-08 15:44:41.0Z
Sender: 5a77.4b9516d5.1804289383@sybase.com
From: tartampion
Newsgroups: sybase.public.ase.general
Subject: entry in syspartions for none-partioned table
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b951b69.5afb.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 8 Mar 2010 07:44:41 -0800
X-Trace: forums-1-dub 1268063081 10.22.241.41 (8 Mar 2010 07:44:41 -0800)
X-Original-Trace: 8 Mar 2010 07:44:41 -0800, 10.22.241.41
Lines: 14
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29034
Article PK: 78271

After upgrading to ase 15.03, we noticed that when an insert
statement is run, there multiple access to syspartions,
which creates some overhead in the execution of the insert
statement.

The content of the syspartitions shows an entry for each
table with one partition only(I guess roundrobin). It is
impossible to unpartition table as the server says the table
has no partition.
My question is simple, why the tables without any partition
has got an entry in syspartitions? In previous version that
was not the case. Am I mistaken that access to syspartitions
slows fown the DMLs?
tartampion


Sherlock, Kevin [TeamSybase] Posted on 2010-03-08 19:57:12.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4b951b69.5afb.1681692777@sybase.com>
Subject: Re: entry in syspartions for none-partioned table
Lines: 20
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b955698@forums-1-dub>
Date: 8 Mar 2010 11:57:12 -0800
X-Trace: forums-1-dub 1268078232 10.22.241.152 (8 Mar 2010 11:57:12 -0800)
X-Original-Trace: 8 Mar 2010 11:57:12 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29035
Article PK: 78272

in ASE 15.x, every table is partitioned. Previously "unpartitioned" tables
are now tables with exactly one partition.

<tartampion> wrote in message news:4b951b69.5afb.1681692777@sybase.com...
> After upgrading to ase 15.03, we noticed that when an insert
> statement is run, there multiple access to syspartions,
> which creates some overhead in the execution of the insert
> statement.
>
> The content of the syspartitions shows an entry for each
> table with one partition only(I guess roundrobin). It is
> impossible to unpartition table as the server says the table
> has no partition.
> My question is simple, why the tables without any partition
> has got an entry in syspartitions? In previous version that
> was not the case. Am I mistaken that access to syspartitions
> slows fown the DMLs?
> tartampion


tartampion Posted on 2010-03-08 21:05:54.0Z
Sender: 5a77.4b9516d5.1804289383@sybase.com
From: tartampion
Newsgroups: sybase.public.ase.general
Subject: Re: entry in syspartions for none-partioned table
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b9566b2.65b7.1681692777@sybase.com>
References: <4b955698@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 8 Mar 2010 13:05:54 -0800
X-Trace: forums-1-dub 1268082354 10.22.241.41 (8 Mar 2010 13:05:54 -0800)
X-Original-Trace: 8 Mar 2010 13:05:54 -0800, 10.22.241.41
Lines: 33
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29036
Article PK: 78273

Thank you very much Kevin,
We have also noticed and read that in ASE 15 all the tables
are partitioned, my question was why such a partitioning? a
one table partition table is a none-partitioned table,
except that any operation on a partitioned(1 partition
table) imposes access to syspartitions, which can be a
bottle neck and in all cases it is an overhead. Let me ask
you such a partitionening does not slow down the inserts? I
do not see the logic behind the one partition table, any
light shed on that aspect is well appreciated.
tartampion

> in ASE 15.x, every table is partitioned. Previously
> "unpartitioned" tables are now tables with exactly one
> partition.
>
> <tartampion> wrote in message
> > news:4b951b69.5afb.1681692777@sybase.com... After
> > upgrading to ase 15.03, we noticed that when an insert
> > statement is run, there multiple access to syspartions,
> > which creates some overhead in the execution of the
> insert statement. >
> > The content of the syspartitions shows an entry for each
> > table with one partition only(I guess roundrobin). It is
> > impossible to unpartition table as the server says the
> > table has no partition.
> > My question is simple, why the tables without any
> > partition has got an entry in syspartitions? In previous
> > version that was not the case. Am I mistaken that access
> > to syspartitions slows fown the DMLs?
> > tartampion
>
>


Sherlock, Kevin [TeamSybase] Posted on 2010-03-08 22:28:15.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4b955698@forums-1-dub> <4b9566b2.65b7.1681692777@sybase.com>
Subject: Re: entry in syspartions for none-partioned table
Lines: 49
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b9579ff$1@forums-1-dub>
Date: 8 Mar 2010 14:28:15 -0800
X-Trace: forums-1-dub 1268087295 10.22.241.152 (8 Mar 2010 14:28:15 -0800)
X-Original-Trace: 8 Mar 2010 14:28:15 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29037
Article PK: 78275

When you say "any operation ... imposes access to syspartitions", why do you
imply this?

Some of the information that used to live in sysindexes is now moved to
syspartitions. There are also "in-memory" structures (descriptors) that
have some table/index/partition information (des, ides, pdes, etc). For
example, periodically changes to in-memory descriptors are flushed to
various system tables, but that's usually done in the background by a
housekeeper task, etc.

How are you observing and measuring "overhead" with regard to ASE15 and
previous versions for inserts against un-partitioned tables?

<tartampion> wrote in message news:4b9566b2.65b7.1681692777@sybase.com...
> Thank you very much Kevin,
> We have also noticed and read that in ASE 15 all the tables
> are partitioned, my question was why such a partitioning? a
> one table partition table is a none-partitioned table,
> except that any operation on a partitioned(1 partition
> table) imposes access to syspartitions, which can be a
> bottle neck and in all cases it is an overhead. Let me ask
> you such a partitionening does not slow down the inserts? I
> do not see the logic behind the one partition table, any
> light shed on that aspect is well appreciated.
> tartampion
>
>> in ASE 15.x, every table is partitioned. Previously
>> "unpartitioned" tables are now tables with exactly one
>> partition.
>>
>> <tartampion> wrote in message
>> > news:4b951b69.5afb.1681692777@sybase.com... After
>> > upgrading to ase 15.03, we noticed that when an insert
>> > statement is run, there multiple access to syspartions,
>> > which creates some overhead in the execution of the
>> insert statement. >
>> > The content of the syspartitions shows an entry for each
>> > table with one partition only(I guess roundrobin). It is
>> > impossible to unpartition table as the server says the
>> > table has no partition.
>> > My question is simple, why the tables without any
>> > partition has got an entry in syspartitions? In previous
>> > version that was not the case. Am I mistaken that access
>> > to syspartitions slows fown the DMLs?
>> > tartampion
>>
>>


Carl Kayser Posted on 2010-03-09 11:06:17.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general
References: <4b955698@forums-1-dub> <4b9566b2.65b7.1681692777@sybase.com>
Subject: Re: entry in syspartions for none-partioned table
Lines: 47
Organization: BLS
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3598
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b962ba9$1@forums-1-dub>
Date: 9 Mar 2010 03:06:17 -0800
X-Trace: forums-1-dub 1268132777 10.22.241.152 (9 Mar 2010 03:06:17 -0800)
X-Original-Trace: 9 Mar 2010 03:06:17 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29038
Article PK: 78276


<tartampion> wrote in message news:4b9566b2.65b7.1681692777@sybase.com...
> Thank you very much Kevin,
> We have also noticed and read that in ASE 15 all the tables
> are partitioned, my question was why such a partitioning? a
> one table partition table is a none-partitioned table,
> except that any operation on a partitioned(1 partition
> table) imposes access to syspartitions, which can be a
> bottle neck and in all cases it is an overhead. Let me ask
> you such a partitionening does not slow down the inserts? I
> do not see the logic behind the one partition table, any
> light shed on that aspect is well appreciated.
> tartampion
>

If non-partitioned tables don't have (singular) partitions then ..., well,
there there would be one version of ASE 15 with syspartitions and another
without syspartitions (and a sysindexes table with more columns). I imagine
that there could be other system table differences if this path were chosen.
And the optimizers would probably have some small (or not so small?)
differences since teh metadata would differ. Sybase would have to maintain
partitioned and unpartitioned versions of ASE. That doesn't seem like a good
path to follow.

>> in ASE 15.x, every table is partitioned. Previously
>> "unpartitioned" tables are now tables with exactly one
>> partition.
>>
>> <tartampion> wrote in message
>> > news:4b951b69.5afb.1681692777@sybase.com... After
>> > upgrading to ase 15.03, we noticed that when an insert
>> > statement is run, there multiple access to syspartions,
>> > which creates some overhead in the execution of the
>> insert statement. >
>> > The content of the syspartitions shows an entry for each
>> > table with one partition only(I guess roundrobin). It is
>> > impossible to unpartition table as the server says the
>> > table has no partition.
>> > My question is simple, why the tables without any
>> > partition has got an entry in syspartitions? In previous
>> > version that was not the case. Am I mistaken that access
>> > to syspartitions slows fown the DMLs?
>> > tartampion
>>
>>


John McVicker Posted on 2010-03-09 13:10:36.0Z
From: "John McVicker" <jmcvicker@inventa.com>
Newsgroups: sybase.public.ase.general
References: <4b955698@forums-1-dub> <4b9566b2.65b7.1681692777@sybase.com>
In-Reply-To: <4b9566b2.65b7.1681692777@sybase.com>
Subject: Re: entry in syspartions for none-partioned table
Lines: 58
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6001.18000
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6001.18000
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b9648cc$1@forums-1-dub>
Date: 9 Mar 2010 05:10:36 -0800
X-Trace: forums-1-dub 1268140236 10.22.241.152 (9 Mar 2010 05:10:36 -0800)
X-Original-Trace: 9 Mar 2010 05:10:36 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29039
Article PK: 78278

You are probably worried about a very minor overhead that is not noticeable
in any resulting job runtime. The system table(s) that you see IO against
will be in cache, will add one or a small bit of Logical Reads to an
operation and will be far overshadowed by disk IO times and network times.

There is a larger issue in ASE 15 over the one you mention - and that is
creation of #temp tables imposes a few hundred more Logical Reads versus
12.5 and prior. This is because the activities done on the system tables in
a tempdb on RowLock scheme system tables now. The more columns, the more
logical reads against system tables when creating and dropping #temp tables
in procs. Not saying avoid using #temp tables but using this as an example
of the differences you may see. Fantastic new features like deferred commit
in tempdb far outshadow an added logical read on syspartitions.

Tune based on business needs in general. You may see a few more IOs against
syspartitions or other system tables - but is the operation any slower to
complete a batch job or run a stored procedure? Probably not noticeable.
Does ASE 15 also allow for better concurrency and higher overall throughput
due to its changes - yes.

- John

"tartampion" wrote in message news:4b9566b2.65b7.1681692777@sybase.com...
> Thank you very much Kevin,
> We have also noticed and read that in ASE 15 all the tables
> are partitioned, my question was why such a partitioning? a
> one table partition table is a none-partitioned table,
> except that any operation on a partitioned(1 partition
> table) imposes access to syspartitions, which can be a
> bottle neck and in all cases it is an overhead. Let me ask
> you such a partitionening does not slow down the inserts? I
> do not see the logic behind the one partition table, any
> light shed on that aspect is well appreciated.
> tartampion
>
>> in ASE 15.x, every table is partitioned. Previously
>> "unpartitioned" tables are now tables with exactly one
>> partition.
>>
>> <tartampion> wrote in message
>> > news:4b951b69.5afb.1681692777@sybase.com... After
>> > upgrading to ase 15.03, we noticed that when an insert
>> > statement is run, there multiple access to syspartions,
>> > which creates some overhead in the execution of the
>> insert statement. >
>> > The content of the syspartitions shows an entry for each
>> > table with one partition only(I guess roundrobin). It is
>> > impossible to unpartition table as the server says the
>> > table has no partition.
>> > My question is simple, why the tables without any
>> > partition has got an entry in syspartitions? In previous
>> > version that was not the case. Am I mistaken that access
>> > to syspartitions slows fown the DMLs?
>> > tartampion
>>
>>