 |
 |
Archives of the TeradataForum
Message Posted: Thu, 06 Jan 2011 @ 15:48:06 GMT
| Subj: | | Re: Questions including Performace tuning |
| |
| From: | | John_Wight |
Swami responded with:
| | Below are few point for performance and tuning | |
| | 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 | |
| | 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.
JK
| |
