Re: re creating a primary key on a table makes requests faster?

On Dec 17, 2:26 am, "Wilfrid" <grill..._at_yahoo.com> wrote:
> Hello,
>
> Would their be any reason why removing a primary key and recreating it on a
> reasonibly large table (~160000 records and increasing at steady rate, with
> int, nvarchar, bit and datetime columns), would have an impact on select and
> insert performance?
> Is there some kind of fragmentation on a table that could explain this?
> thanks in advance for your insight

After looking through the latest Foote paper http://richardfoote.wordpress.com/2007/12/11/index-internals-rebuilding-the-truth/ Robert referrred to, I think there is a yes answer to this. The case would be like many enterprise software tables are designed - the primary key has a concatenation of company and division codes prepended to whatever the real key is. So if your steady increasing rate means lots of variance among company and division codes while for each of those the rest of the key is increasing, you could get into a situation of lots of 50/50 index block splits as time goes by.

Something like:

company 3 division 4 order 1000001
company 1 division 2 order 1000001
company 3 division 4 order 1000002
company 1 division 3 order 1000001
company 3 division 4 order 1000003
company 3 division 2 order 1000001
company 1 division 2 order 1000002

and so on for ~160000 orders.

Then a thrashed SGA might make it noticeable, and recreating the index could reduce thrashing as more appropriate parts of the index could be kept in memory. Also, a sufficent select and insert load may add performance issues for read-consistency, latching, and even transaction lists reservation, any little thing could explode into a large effect.

Well, it's a nice thought-experiment, anyways. Richard's paper provides methods of showing the split block part of it.

Is there some real-world issue that instigated this question? Have you looked at waits?

jg

--
@home.com is bogus.
http://www.iht.com/articles/2007/12/17/technology/netsuite.php