Home Page for the TeradataForum
 

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>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016