CODEWITHSUNDEEP
sqlteradata
Jeffrey Kramer
Jeffrey Kramer

Reputation: 1345

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

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.

Upvotes: 5

Views: 18131

Answers (1)

dnoeth
dnoeth

Reputation: 60482

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.

Upvotes: 5

Related Questions