Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Mon, 04 Nov 2002 @ 15:31:54 GMT

  <Prev Next>  
Next> Last>>  

Subj:   Secondary Indexes on an Active Data Warehouse
From:   Matthew Winter


At present we are loading data on a 4 hourly cycle, but would like to increase the frequency in the future using TPump to near realtime, allowing for more tactical queries to be performed.

Our biggest issue at present is the performance of Secondary Indexes.

To get the best performance during data loading, we would need to drop the secondary indexes, perform the data load and then rebuild the secondary indexes. This process extends the data load considerably, and means the indexes are not available for nearly 50% of the day, which is clearly unacceptable.

Therefore the only option is to keep the indexes present, while the data load is being performed, which is the same as what you would do when using TPump.

The only issue here, is performance. Inserting 1 million records into the table with no secondary indexes takes 37 seconds, whilst inserting the same number of records into a table with a single secondary index takes 1 hour 20 minutes, and with two secondary indexes takes 3 hours 40 minutes. As you can see on a 4 hourly load cycle this does not provide you with much space to perform the actual load itself.

Are these times indicative of other users of Teradata?

Is there something we could do to improve the performance?

Any ideas would be grateful.


Matthew Winter

Technical Architect
TPG IS Design Authority
Teradata Certified Professional

  <Prev Next>  
Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020