Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Tue, 08 Jan 2002 @ 21:04:20 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: Optimizer clues
From:   Anomy Anom

<-- Anonymously Posted: Tuesday, January 08, 2002 15:56 -->

I agree with Richard that the optimiser does not find always the best plan. This is especially true in the BI environment and for ad hoc SQL - for two different reasons (personal believe).

In the BI tool environment the SQL is usually rule driven generated. The rules are usually not based on the complete functionality of Teradata. The number of joined tables is growing constantly. The result can be a huge strait forward simple functionality SQL with a bad performance. Sometime a rewriting can solve to problem but this is not a acceptable solution as the SQL should be run within the BI tool.

In the ad hoc environment the growing SQL functionality combined with lacks in the data model or missing statistics causes problems and sometimes (even if seldom) the optimiser simply does not find the best solution for other reasons. Usually this kind of problems can be fixed with dirty solutions like splitting the SQL in different steps, adding dummy where-conditions or rewriting the SQL. In this cases the space requirements might be huge and not every user has the ability to store big intermediate results. Sometimes only additional statistics (which is an request of NCR but can only be done by an DBA) or additional indices are required (which are needing space and DBAs). In this cases you are loosing the lot of the 'ad hoc'. I spent hours by understanding very large explains to find out why it does not work or what causes the problem. Sometimes is also very hard for NCR to figure out what the problem for a specific query is and the response time can be long as SQL problems are usually difficult and not critical in the meaning of system critical.

But I am not sure to agree with Richards conclusion to add hint functionality into the optimiser. Personally I still believe that the optimiser - especially in the BI-tool environment - should be improved. NCR should work harder together with the vendors to develop the optimiser and the SQL generators. I still believe the NCR optimiser is the best in place but it still needs improvements. In a long term there might be the risk that more and more time is needed to understand what Teradata does as it is getting more and more complex and I see no best solution for this scenario.

  <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