Archives of the TeradataForum

Message Posted: Wed, 19 May 2004 @ 11:24:02 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Are Numeric Keys and Indices more efficient?

From:		Dieter Noeth

Geoffrey Rommel wrote:

To compare the speed of decimal(13) vs. char(15), I copied the A and B tables to a work area twice. One copy of each table had a decimal(13) primary index; the other copy had char(15). Otherwise, they were identical.

Did you consider that the char(15) needs 15 bytes/row vs. 8 bytes for decimal(13)?

All tables had the same primary index. I then joined them. The results were as follows:

                 Elapsed time Total CPU  Total I/O's
     decimal(13) 5:28         1,931.18   1,950,115
     char(15)    5:21         4,043.43   8,818,803

Do you have any idea why the IOs are more than 4 times higher? Cache hits? I tried a similar approach and had the same IOs.

These results came as a surprise to me. I guess numerics are a lot better after all!

IMHO an [huge?/small?] part of the difference in CPU time is due to Rowhash duplicates. A merge join first compares RowHashes and if they're the same then compares the data.

I just did a count(distinct rowhash) on 3.000.000 distinct numbers:

     3.000.000 decimal
       788.749 trim(trailing from decimal)

So joining the table to itself would result in:

     sel sum(cnt * cnt)
     from
      (sel hashrow(i) as h, count(*) as cnt
       from pitest1
       group by 1
      ) dt

18.050.002 RowHash/Data comparisons vs. 3.000.000

Dieter


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference