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.
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.
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.