Re: SQL Humor

On 19 Aug 2005 16:18:03 -0700, Mikito Harakiri wrote:

(snip)
>Hugo,
>
>I was going to perform this test in oracle, but then I came across the
>following passage:

(snip)
>I guess this storage quirk doesn't really affect TPC-C benchmarks:)

Hi Mikito,

I've never worked with Oracle, so I'll just have to take your word for it, I guess.

>It might be interesting, however, to dig down into the insert
>performance difference. As I mentioned, 2 extra bytes shouldn't really
>affect the speed of index unique scan.

I think the culprint for the inserts is the extra index. The size of one entry in the index grows from 11 bytes to 15 bytes (a large percentage). And that will mean that less entries fit into one page - and since the inserts are sequential for the PRIMARY KEY, but non-sequential for the UNIQUE, the supporting index for the UNIQUE constraint will face lots of page splits.

I include these two statements in the main loop for the inserts to have some indication that the system is still busy:
>> IF @c % 100 = 0
>> PRINT @c

If you run the script, you'll see that the speed at which the counter increase will graudually slow down as the number of index pages grows.

>BTW, I always use INTEGERs for id columns.

Yeah, I guess I should have named the key columns ClientCode and ProdCode instead of ClientID and ProdID. :-)

Best, Hugo

-- 

(Remove _NO_ and _SPAM_ to get my e-mail address)