on 08-27-2010 9:04 PM
We are looking for a way to default a number of global Universe parameters specifically DISTINCT_VALUES automatically set to GROUPBY for each new Universe generated for a given Teradata DBMS. I reviewed the PRM files for the DBMS in question, but I didn't see the DISTINCT_VALUES listing in the Parameters file.
Any ideas would be greatly appreciated.
You are asking for a way to default this permanently, rather than changing it inside of Designer itself?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Dave,
It will not use Group By in all the cases. If my understanding is correct then Distinct still performs better when the size of table is small. Group By takes over when the table size is huge. This is what the TD magazine states:
GROUP BY and DISTINCT planning enhancements
This feature unifies GROUP BY and DISTINCT, thus removing performance discrepancy between them. In addition, all aggregation operations are incorporated into cost-based join planning, enhancing the Optimizeru2019s ability to pick optimal plans. The innovation significantly improves aggregation performance, which is key to data warehousing applications. It provides the infrastructure for continuing enhancements in the aggregation area.
I will try to find if there is any configuration setting for it or not.
- Noman Jaffery
We only just upgraded to Teradata 12, it could be quite some time before we are upgraded to Teradata 13. Additionally we just upgraded to XIR3SP2, and it does not have Teradata 13 middleware option and so we would need to upgrade again if there is a release avaiable with Teradata 13. We are not likely to do another upgrade this year.
Any other ideas? We are hoping to implement something universal, but not complicated. I am hoping there are some universal parameters list file we can modify to control the defaults from our end.
We do it on each universe. It's not that hard, and it takes only a few minutes. We are working on a "new universe" guide and this is one of several steps we take now for each new Teradata universe.
Even if you found a way to do it via the parameter file, you would still have to update it on every developer system.
Agreed.
Also I did check and can confirm that there is no specific setting for the new Group By/Distinct feature to be enabled in TD 13. It is the default behavior now.
For small tables it will still use Distinct which is more efficient. When the size of the tables is huge only then Group By will be used. I get the same Explain plan for both the options.
- Noman Jaffery
I agree that the parameter change is extremely easy to do, but again its not getting done. We are getting a push by upper level management to provide hard defaults that make sense. GROUPBY is one of several that we are having concerns about. We don't have Teradata 13 and won't have for quite a long time and it won't handle some of the other defaults we need to define as well.
Also, we have always been aware that Distinct performs better on small tables than Group By, but the positive benefit is very minor as the additional processing incurred by Group By method for small tables is minute as the table is small. Distinct on very large tables has been extremely bad for us, hence this inquiry on changing the defaults.
User | Count |
---|---|
81 | |
25 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.