5 Possible reasons for long running queries in Teradata

One very common question people ask is about possible reasons for long running queries in Teradata. First thing you should understand is Teradata is very good in handling huge volume of data and handling several million or even billion records should not break it. If your query is taking unusual long time then your query needs tuning. Listing down few most common reasons for long running queries in Teradata:

1. Bad Query: This is the most common reason for long running query. Your query is not using any kind of index: Primary Index, Secondary Index or Partition Primary Index. The tables are getting redistributed and those big tables taking long time in it.
2. Delay Time: Check for Delay time in DBQL for your query. It may happen that query execution time is in seconds however it was in Delay queue for hours.
3. Blocking: It may happen that your query is blocked by some other query. Also watch out for TDM jobs. Such jobs generally apply Exclusive locks on the table thereby blocking all other queries requesting the same table. You can check this Teradata Viewpoint Monitor Portlet.
4. Server State: Generally Teradata Server status is classified as Healthy, Degraded, Critical and Down. As the number of concurrent users increase the load on server also increases. With more load on server and AMP doing more work, your query may take more time than usual time.
5. Skewness: Your Source table is highly skewed because the Primary Index chosen is not good. Consider changing PI for the table if skewness factor is high.

One thing which can help you understand everything about your query is Teradata Viewpoint. So use it as much as possible and it should answer most of your questions.