title: Teradata Performance issue and example
body: <p>I am facing an issue in our Teradata QA environment where a simple query that ran in under 1 minute is now taking 12 minutes to complete. This select is pulling 5 fields based on a simple inner join</p>
  
  <pre><code>select a.material
      , b.season
      , b.theme
      , b.collection
  from SalesOrders_view.Allocation_Deliveries_cur a
  inner join SalesOrders_view.Material_Attributes_cur b
      on a.material = b.material;
  </code></pre>
  
  <p>I can run this same query in our Prod environment and it returns in less than a minute while running on approx 200k more records than QA.</p>
  
  <p>Total volume is under 1.1 M records in SalesOrders.Allocation_Deliveries and 129 k records in SalesOrders.Material_Attributes. These are small datasets.</p>
  
  <p>I compared the Explain plans on both environments and there is a stark difference in the estimated spool volume in the first Join step. The estimate in Production is on the money while the Estimate in QA is an order of magnitude off. However the data and table/views are identical in both systems and we have collected stats in every conceivable manner and we can see the particular table demographics in both systems as identical.</p>
  
  <p>Lastly, this query has always returned in under a minute in all environments including QA as it is still doing in Production. This latent behavior is recent in the last week or so. I discussed this with our DBA and we have had no changes to software or configuration. He is new, but seems to know what he's doing but still getting caught up with a new environment.</p>
  
  <p>I am looking for some pointers on what to check next. I have compared the relavant table / view definitions across QA and Prod and they are identical. The Table demographics in each system are also the same (I went through these with our DBA to make sure)</p>
  
  <p>Any help is appreciated. Thanks in advance.
  Pat</p>
  
  <p>This is the Explain plan from QA. Note the very Low estimate in Step 5 (144 Rows). In Prod, the same Explain shows > 1 M rows which would be close to what I know.</p>
  
  <pre><code>Explain select a.material
      , b.season
      , b.theme
      , b.collection
  from SalesOrders_view.Allocation_Deliveries a
  inner join SalesOrders_view.Material_Attributes_cur b
      on a.material = b.material;
  
    1) First, we lock SalesOrders.Allocation_Deliveries in view
       SalesOrders_view.Allocation_Deliveries for access, and we lock
       SalesOrders.Material_Attributes in view SalesOrders_view.Material_Attributes_cur for
       access. 
    2) Next, we do an all-AMPs SUM step to aggregate from
       SalesOrders.Material_Attributes in view SalesOrders_view.Material_Attributes_cur by way
       of an all-rows scan with no residual conditions
       , grouping by field1 ( SalesOrders.Material_Attributes.material
       ,SalesOrders.Material_Attributes.season ,SalesOrders.Material_Attributes.theme
       ,SalesOrders.Material_Attributes.theme ,SalesOrders.Material_Attributes.af_grdval
       ,SalesOrders.Material_Attributes.af_stcat
       ,SalesOrders.Material_Attributes.Material_Attributes_SRC_SYS_NM).  Aggregate
       Intermediate Results are computed locally, then placed in Spool 4. 
       The size of Spool 4 is estimated with high confidence to be
       129,144 rows (41,713,512 bytes).  The estimated time for this step
       is 0.06 seconds. 
    3) We execute the following steps in parallel. 
         1) We do an all-AMPs RETRIEVE step from Spool 4 (Last Use) by
            way of an all-rows scan into Spool 2 (all_amps), which is
            redistributed by the hash code of (
            SalesOrders.Material_Attributes.Field_9,
            SalesOrders.Material_Attributes.Material_Attributes_SRC_SYS_NM,
            SalesOrders.Material_Attributes.Field_7, SalesOrders.Material_Attributes.Field_6,
            SalesOrders.Material_Attributes.theme, SalesOrders.Material_Attributes.theme,
            SalesOrders.Material_Attributes.season, SalesOrders.Material_Attributes.material)
            to all AMPs.  Then we do a SORT to order Spool 2 by row hash
            and the sort key in spool field1 eliminating duplicate rows. 
            The size of Spool 2 is estimated with low confidence to be
            129,144 rows (23,504,208 bytes).  The estimated time for this
            step is 0.11 seconds. 
         2) We do an all-AMPs RETRIEVE step from SalesOrders.Material_Attributes in
            view SalesOrders_view.Material_Attributes_cur by way of an all-rows scan
            with no residual conditions locking for access into Spool 6
            (all_amps), which is redistributed by the hash code of (
            SalesOrders.Material_Attributes.material, SalesOrders.Material_Attributes.season,
            SalesOrders.Material_Attributes.theme, SalesOrders.Material_Attributes.theme,
            SalesOrders.Material_Attributes.Material_Attributes_SRC_SYS_NM,
            SalesOrders.Material_Attributes.Material_Attributes_UPD_TS, (CASE WHEN (NOT
            (SalesOrders.Material_Attributes.af_stcat IS NULL )) THEN
            (SalesOrders.Material_Attributes.af_stcat) ELSE ('') END )(VARCHAR(16),
            CHARACTER SET UNICODE, NOT CASESPECIFIC), (CASE WHEN (NOT
            (SalesOrders.Material_Attributes.af_grdval IS NULL )) THEN
            (SalesOrders.Material_Attributes.af_grdval) ELSE ('') END )(VARCHAR(8),
            CHARACTER SET UNICODE, NOT CASESPECIFIC)) to all AMPs.  Then
            we do a SORT to order Spool 6 by row hash.  The size of Spool
            6 is estimated with high confidence to be 129,144 rows (
            13,430,976 bytes).  The estimated time for this step is 0.08
            seconds. 
    4) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of
       an all-rows scan into Spool 7 (all_amps), which is built locally
       on the AMPs.  Then we do a SORT to order Spool 7 by the hash code
       of (SalesOrders.Material_Attributes.material, SalesOrders.Material_Attributes.season,
       SalesOrders.Material_Attributes.theme, SalesOrders.Material_Attributes.theme,
       SalesOrders.Material_Attributes.Field_6, SalesOrders.Material_Attributes.Field_7,
       SalesOrders.Material_Attributes.Material_Attributes_SRC_SYS_NM,
       SalesOrders.Material_Attributes.Field_9).  The size of Spool 7 is estimated
       with low confidence to be 129,144 rows (13,301,832 bytes).  The
       estimated time for this step is 0.05 seconds. 
    5) We do an all-AMPs JOIN step from Spool 6 (Last Use) by way of an
       all-rows scan, which is joined to Spool 7 (Last Use) by way of an
       all-rows scan.  Spool 6 and Spool 7 are joined using an inclusion
       merge join, with a join condition of ("(material = material) AND
       ((season = season) AND ((theme = theme) AND ((theme =
       theme) AND (((( CASE WHEN (NOT (af_grdval IS NULL )) THEN
       (af_grdval) ELSE ('') END ))= Field_6) AND (((( CASE WHEN (NOT
       (AF_STCAT IS NULL )) THEN (AF_STCAT) ELSE ('') END ))= Field_7)
       AND ((Material_Attributes_SRC_SYS_NM = Material_Attributes_SRC_SYS_NM) AND
       (Material_Attributes_UPD_TS = Field_9 )))))))").  The result goes into Spool
       8 (all_amps), which is duplicated on all AMPs.  The size of Spool
       8 is estimated with low confidence to be 144 rows (5,616 bytes). 
       The estimated time for this step is 0.04 seconds. 
    6) We do an all-AMPs JOIN step from Spool 8 (Last Use) by way of an
       all-rows scan, which is joined to SalesOrders.Allocation_Deliveries in view
       SalesOrders_view.Allocation_Deliveries by way of an all-rows scan with no
       residual conditions.  Spool 8 and SalesOrders.Allocation_Deliveries are
       joined using a single partition hash_ join, with a join condition
       of ("SalesOrders.Allocation_Deliveries.material = material").  The result goes
       into Spool 1 (group_amps), which is built locally on the AMPs. 
       The size of Spool 1 is estimated with low confidence to be 3,858
       rows (146,604 bytes).  The estimated time for this step is 0.44
       seconds. 
    7) Finally, we send out an END TRANSACTION step to all AMPs involved
       in processing the request.
    -&gt; The contents of Spool 1 are sent back to the user as the result of
       statement 1.  The total estimated time is 0.70 seconds.
  </code></pre>
  
  <p>Here is what the record distribution looks like and the SQL I used to generate the result set</p>
  
  <pre><code>SELECT HASHAMP(HASHBUCKET(HASHROW( MATERIAL ))) AS
  "AMP#",COUNT(*)
  FROM EDW_LND_SAP_VIEW.EMDMMU01_CUR
  GROUP BY 1
  ORDER BY 2 DESC;
  </code></pre>
  
  <p>Output
  Highest: AMP 137 with 1093 rows
  Lowest: AMP 72 with 768 rows
  Total AMPs: 144</p>