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.

Primary key : value pools vs identity ?

11 posts in Performance and Tuning Last posting was on 2012-03-15 11:10:09.0Z
Didier Blanchard Posted on 2012-03-13 10:37:35.0Z
Sender: 3100.4f5f2115.846930886@sybase.com
From: Didier Blanchard
Newsgroups: sybase.public.ase.performance+tuning
Subject: Primary key : value pools vs identity ?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f5f236f.3172.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 13 Mar 2012 02:37:35 -0800
X-Trace: forums-1-dub 1331635055 172.20.134.41 (13 Mar 2012 02:37:35 -0800)
X-Original-Trace: 13 Mar 2012 02:37:35 -0800, 172.20.134.41
Lines: 10
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13102
Article PK: 91624

We use a table (TABLE_SYS) that holds a counter of primary
keys for each table.
Each time we insert a row, we request this table and then
increment the counter. This seems to be quite old school, no
?
We plan to use the identity property of the primary key to
avoid locks on TABLE_SYS.
The identity columns seems to have only good aspects, but
can we surely fully migrate our "PK management" with
identity ?


Rob V Posted on 2012-03-13 13:11:43.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:10.0.2) Gecko/20120216 Thunderbird/10.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Primary key : value pools vs identity ?
References: <4f5f236f.3172.1681692777@sybase.com>
In-Reply-To: <4f5f236f.3172.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: <4f5f478f@forums-1-dub>
Date: 13 Mar 2012 05:11:43 -0800
X-Trace: forums-1-dub 1331644303 10.22.241.152 (13 Mar 2012 05:11:43 -0800)
X-Original-Trace: 13 Mar 2012 05:11:43 -0800, vip152.sybase.com
Lines: 38
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13103
Article PK: 91612


On 13-Mar-2012 11:37, Didier Blanchard wrote:
> We use a table (TABLE_SYS) that holds a counter of primary
> keys for each table.
> Each time we insert a row, we request this table and then
> increment the counter. This seems to be quite old school, no
> ?
> We plan to use the identity property of the primary key to
> avoid locks on TABLE_SYS.
> The identity columns seems to have only good aspects, but
> can we surely fully migrate our "PK management" with
> identity ?

What you can do (and what I would recommend) is replace your current
table holding the key counter column with a table holding just the
identity column. By doing an empty insert, or by using the
reserve_identity() function, you can still generate a key value but with
much better performance and concurrency than with the old keycounter column.
For some examples, see http://www.sypron.nl/quiz2008a.html#dec08 (note
that identity columns can also be of the 'int' or 'bigint' datatype
these days, it does not have to be numeric).

HTH,

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

Certified Professional DBA for Sybase ASE, IQ, Replication Server

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

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


Didier Blanchard Posted on 2012-03-14 10:50:47.0Z
Sender: 6d20.4f60770f.1804289383@sybase.com
From: Didier Blanchard
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Primary key : value pools vs identity ?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f607807.6d47.1681692777@sybase.com>
References: <4f5f478f@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 14 Mar 2012 02:50:47 -0800
X-Trace: forums-1-dub 1331722247 172.20.134.41 (14 Mar 2012 02:50:47 -0800)
X-Original-Trace: 14 Mar 2012 02:50:47 -0800, 172.20.134.41
Lines: 3
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13104
Article PK: 91626

OK.
But can we simply have identity columns on the PK of each
table and let ASE supply the values ?


Rob V Posted on 2012-03-14 11:33:13.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:10.0.2) Gecko/20120216 Thunderbird/10.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Primary key : value pools vs identity ?
References: <4f5f478f@forums-1-dub> <4f607807.6d47.1681692777@sybase.com>
In-Reply-To: <4f607807.6d47.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: <4f6081f9@forums-1-dub>
Date: 14 Mar 2012 03:33:13 -0800
X-Trace: forums-1-dub 1331724793 10.22.241.152 (14 Mar 2012 03:33:13 -0800)
X-Original-Trace: 14 Mar 2012 03:33:13 -0800, vip152.sybase.com
Lines: 29
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13105
Article PK: 155140


On 14-Mar-2012 11:50, Didier Blanchard wrote:
> OK.
> But can we simply have identity columns on the PK of each
> table and let ASE supply the values ?

Sure, you can do that. But you have more flexibility when you keep the
identity column in a separate table. For example, when you have foreign
key constraints on the PK column and you want to insert multiple rows as
well. That sort of flexibility is worht a lot in my experience since you
don't necessarily know all future requirements,.

--
HTH,

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

Certified Professional DBA for Sybase ASE, IQ, Replication Server

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

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


Rob V Posted on 2012-03-14 14:03:32.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:10.0.2) Gecko/20120216 Thunderbird/10.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Primary key : value pools vs identity ?
References: <4f5f478f@forums-1-dub> <4f607807.6d47.1681692777@sybase.com> <4f6081f9@forums-1-dub> <4f6087c8@forums-1-dub> <4f609795@forums-1-dub> <4f60a3a4@forums-1-dub>
In-Reply-To: <4f60a3a4@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: <4f60a534$1@forums-1-dub>
Date: 14 Mar 2012 06:03:32 -0800
X-Trace: forums-1-dub 1331733812 10.22.241.152 (14 Mar 2012 06:03:32 -0800)
X-Original-Trace: 14 Mar 2012 06:03:32 -0800, vip152.sybase.com
Lines: 55
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13110
Article PK: 155135


On 14-Mar-2012 14:56, peta62 wrote:
> On 3/14/2012 2:05 PM, Rob V wrote:
>> On 14-Mar-2012 12:58, peta62 wrote:
>>> Duplicate values of PK ???
>>>
>>>
>>> On 3/14/2012 12:33 PM, Rob V wrote:
>>>> On 14-Mar-2012 11:50, Didier Blanchard wrote:
>>>>> OK.
>>>>> But can we simply have identity columns on the PK of each
>>>>> table and let ASE supply the values ?
>>>>
>>>> Sure, you can do that. But you have more flexibility when you keep the
>>>> identity column in a separate table. For example, when you have foreign
>>>> key constraints on the PK column and you want to insert multiple
>>>> rows as
>>>> well. That sort of flexibility is worht a lot in my experience since
>>>> you
>>>> don't necessarily know all future requirements,.
>>>>
>>>
>>
>> Duplicate PK values are not a problem when using an identity column,
>> since they are guaranteed to be unique. When you insert multiple rows,
>> the problem is more that it can get hard to know which PK values were
>> used. That can especially be a problem when you need those values for
>> example because you need to use them for foreign keys in other tables.
>>
>
> So I would say if you have to insert multiple rows into parent and child
> tables, both having more identity values, you have to separate rows in
> both scenarios. Where single parent row inserts in table including
> identity is simplier or do I miss something ?
>

That's a pretty good summary....

--
HTH,

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

Certified Professional DBA for Sybase ASE, IQ, Replication Server

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

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------