Performance Tuning

Database
Enthusiast

Performance Tuning

Hi Teradata Champs,

 

Request your advise on how to improve query performance with a huge table with 50 odd columns and 6 Billions of Data.

What all starndards to be followed with the table design to efficiently retreive data.

 

Currently the table having UPI on a Key and Key_Seq_Num columns, and simple select * from it is spooling out for users with 1TB of spool space.

 

Please advise what all options we have/should look into, to redesign such table.

 

Thanks & Regards.

3 REPLIES
Teradata Employee

Re: Performance Tuning

A query generates an answer set.  The answer set is stored in spool and sits there until it is all fetched or discarded by the user.  6,000,000,000*50 = 300,000,000,000 values.  For that many values to fit in 1 TB, they would have to average 3.3 bytes each.  However, I would guess the average uncompressed size is at least 10 bytes, which would require at least 3 TB of spool space to store.

As a design suggestion, a table of this size is usually partitioned by row and/or column.  That improves performance for queries that only operate on a subset of rows and/or columns.  Most queries against such a table should probably be like that.

 

However, any query that operates on all the rows and columns (such as an unqualified select *) will still require enough spool space to hold the entire table uncompressed and also the data from any tables that are joined to it.  I am not trying to suggest you shouldn't do that; just know that such queries will probably need to be limited to user IDs that have a lot of spool space allocated.

Enthusiast

Re: Performance Tuning

Thanks a lot Coleman, for your response, and the explanation. Appreciate it. 

New Member

Re: Performance Tuning

I have been looking for tuning the performance of the system. So I have installed a 16 GB RAM and a 4 GB graphics card and an i7 processor so to improve my performance. I have also asked iCloud Support they also suggested the same thing.