Multiple Joins in Teradata SQL - Faster to Use Subqueries or Temp Tables?

Question

I am writing SQL for Teradata. I need to use joins to connect data from multiple tables. Is it typically faster to use subqueries or create temporary tables and append columns one join at a time? I'm trying to test it myself but network traffic makes it hard for me to tell which is faster.

Example A:

SELECT a.ID, a.Date, b.Gender, c.Age
FROM mainTable AS a
LEFT JOIN (subquery 1) AS b ON b.ID = a.ID
LEFT JOIN (subquery 2) AS c ON c.ID = a.ID

Or I could...

Example B:

CREATE TABLE a AS (
 SELECT mainTable.ID, mainTable.Date, sq.Gender
 FROM mainTable
 LEFT JOIN (subquery 1) AS sq ON sq.id = mainTable.ID
)
CREATE TABLE b AS (
  SELECT a.ID, a.Date, a.Gender, sq.Age
  FROM a
  LEFT JOIN (subquery 2) AS sq ON sq.id = a.ID
)

Assuming I clean everything up afterward, is one approach preferable to another? Again, I would like to just test this myself but the network traffic is kind of messing me up.

EDIT: The main table has anywhere from 100k to 5 million rows. The subqueries return a 1:1 relationship to the main table's IDs, but require WHERE clauses to filter dates. The subquery SQL isn't trivial, I guess is what I'm trying to convey.

The amount of network traffic in both of the cases would remain the same I guess, since you are just filtering the result with your inner queries. — Akash, Aug 14 '13 at 18:25

score 5 · Accepted Answer · answered Aug 14 '13 at 18:33

Of course it's recommended to write joins, that's why there's an optmizer :-) If you create temporary tables you force a specific order of processing instead of letting the optimizer decide which is the best plan.

Creating temporary tables might be usefull in some rare cases when you got a really complex query with dozens of joins and you need to break it into a more easily maintainable parts or you would like to get a specific PI for further processing.

Regarding testing different approaches: Runtime should never be used for that, it might vary greatly based on the load on the server. You need to access Teradata's Query Log (DBQL: dbc.QryLogV, etc.) to get details about actual CPU/IO/spool usage. If you don't have access to it you might ask your DBA to grant it to you.

Btw, instead of real tables you should create VOLATILE TABLES which are automatically dropped when you logoff.

Thanks so much, this really helps. I'll make note of the `VOLATILE` tables, this would make life easier in almost every case. — Jeffrey Kramer, Aug 14 '13 at 18:41
With Teradata 14 or 14.10 you should be able to use multiple CTEs as well, if my memory serves correct. — Rob Paller, Aug 15 '13 at 16:10

Multiple Joins in Teradata SQL - Faster to Use Subqueries or Temp Tables?

1 Answers1