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.

New clust index for heap table

10 posts in Performance and Tuning Last posting was on 2012-08-21 06:29:12.0Z
rick_906 Posted on 2012-08-06 18:51:24.0Z
Sender: 3c42.502010ac.1804289383@sybase.com
From: rick_906
Newsgroups: sybase.public.ase.performance+tuning
Subject: New clust index for heap table
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <5020122c.3c6b.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 6 Aug 2012 11:51:24 -0700
X-Trace: forums-1-dub 1344279084 172.20.134.41 (6 Aug 2012 11:51:24 -0700)
X-Original-Trace: 6 Aug 2012 11:51:24 -0700, 172.20.134.41
Lines: 12
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13206
Article PK: 91714

I have a heap table with 2.5M rows with 50 columns about
600K per row on a 2K page server. I am considering putting a
clustered index on the table. My insert & update rates are
about equal, 3-4 per second. There is no primary key on the
table. If I use the main id column in the table I make a
hotspot for inserts, but spread out the updates. Is it
better to use a column that spreads out inserts or updates
when they occur at equal rates or choose a column that
spreads out the two?

thx,
rick_806


Sherlock, Kevin [TeamSybase] Posted on 2012-08-06 20:28:51.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <5020122c.3c6b.1681692777@sybase.com>
Subject: Re: New clust index for heap table
Lines: 27
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.6157
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50202903@forums-1-dub>
Date: 6 Aug 2012 13:28:51 -0700
X-Trace: forums-1-dub 1344284931 172.20.134.152 (6 Aug 2012 13:28:51 -0700)
X-Original-Trace: 6 Aug 2012 13:28:51 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13207
Article PK: 91716

What locking scheme are you using? Keep in mind, with a heap on an
un-partitioned table, you already have a hotspot. What is your motivation
to create a clustered index? If there is no primary key (which really
paints you into a corner), then I assume you are only interested in making
your reads faster via an index.

The rest of your question depends a lot on the structure, locking scheme,
and perhaps a few other physical attributes of the table, and the nature of
the updates. When you say 600K per row, I assume you mean 600 bytes per row
(600K wouldn't fit on a 2K page :).

<rick_906> wrote in message news:5020122c.3c6b.1681692777@sybase.com...
>I have a heap table with 2.5M rows with 50 columns about
> 600K per row on a 2K page server. I am considering putting a
> clustered index on the table. My insert & update rates are
> about equal, 3-4 per second. There is no primary key on the
> table. If I use the main id column in the table I make a
> hotspot for inserts, but spread out the updates. Is it
> better to use a column that spreads out inserts or updates
> when they occur at equal rates or choose a column that
> spreads out the two?
>
> thx,
> rick_806


rick_806 Posted on 2012-08-06 23:45:34.0Z
Sender: 3c42.502010ac.1804289383@sybase.com
From: rick_806
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: New clust index for heap table
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <5020571e.43b5.1681692777@sybase.com>
References: <50202903@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 6 Aug 2012 16:45:34 -0700
X-Trace: forums-1-dub 1344296734 172.20.134.41 (6 Aug 2012 16:45:34 -0700)
X-Original-Trace: 6 Aug 2012 16:45:34 -0700, 172.20.134.41
Lines: 38
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13208
Article PK: 91721

This is a datarow locked table non-partitioned and I
included the record size (600bytes) because if the last page
is a hotspot it can only support about 3 records. I do want
to get rid of the hotspot. The table has a unique index on
the column that should be the PK as well as 11 other
indices. I was planning to replace a non-clustered index on
the same column.

> What locking scheme are you using? Keep in mind, with a
> heap on an un-partitioned table, you already have a
> hotspot. What is your motivation to create a clustered
> index? If there is no primary key (which really paints
> you into a corner), then I assume you are only interested
> in making your reads faster via an index.
>
> The rest of your question depends a lot on the structure,
> locking scheme, and perhaps a few other physical
> attributes of the table, and the nature of the updates.
> When you say 600K per row, I assume you mean 600 bytes per
> row (600K wouldn't fit on a 2K page :).
>
>
> <rick_906> wrote in message
> news:5020122c.3c6b.1681692777@sybase.com... >I have a heap
> > table with 2.5M rows with 50 columns about 600K per row
> > on a 2K page server. I am considering putting a
> > clustered index on the table. My insert & update rates
> are about equal, 3-4 per second. There is no primary key
> > on the table. If I use the main id column in the table I
> > make a hotspot for inserts, but spread out the updates.
> > Is it better to use a column that spreads out inserts or
> > updates when they occur at equal rates or choose a
> > column that spreads out the two?
> >
> > thx,
> > rick_806
>
>