Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Thu, 06 Jan 2011 @ 15:48:06 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: Questions including Performace tuning
From:   John_Wight

Swami responded with:

  Below are few point for performance and tuning  

  1. collecting statistics  

  2. Explain Statements  

  3. Avoid Product Joins when possible  

  4. select appropriate primary index to avoid skewness in storage  

  5. Avoid Redistribution when possible  

  6. Use sub-selects instead of big "IN" lists  

  7. Use derived tables  

  8. Use GROUP BY instead of DISTINCT ( GROUP BY sorts the data locally on the VPROC. DISTINCT sorts the data after it is redistributed)  

  9. Use Compression on large tables  

All of these are good and should be considered for 'tuning'.

HOWEVER, I would like to suggest that these are NOT done in production to 'tune' - BUT - all these are to be considered/done in the DEVELOPMENT phase!!! It's always been my goal/practice to do my tuning when/as I develop using the items Swami mentioned above. By the time the code gest to System Testing/QA then Prod - you should have no big surprises! One thing you might want to add is 'data volume' - i.e. have data volumes available during your development/unit testing so that stats, etc. will be 'relevant'.

When I was with TD, I had a customer that did all their development on a small system with very little/no data. we practiced all these but when we place the code into systems test with 'near production' data volumes, we had to re-write/tune/test the code AGAIN with about 30-30% of the code - VERY wasteful.

Also, have good standards for coding/formatting, (readability), etc. as well as a scoring process. The Scoring process should utilize DBQL stats collected and be able to show you things like parallelism of CPU. I/O and Spool, your total CPU, I/O and Spool for an execution test. AS you can see, if you have only very little/no data, these metrics will be quite meaningless and won't show up skewing, parallelism %, etc.

Hope this helps.


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