I've used common table expressions in embedded SQL programs, SQL stored procedures, and SQL views.  I have not used global temporary tables, although I've seen examples in SQL stored procedures.  Can someone tell me when it may be appropriate or better to use a global temporary table instead of a common table expression?  Do global temporary tables yield better or worse performance than CTEs?

------------------------------
Amy Vozza
------------------------------

#SQL

A Common Table Expression (CTE) is part of a SELECT statement, i.e. a type of temporary view or table that is only used within this specific SELECT Statement.
The query optimizer will optimize the complete SELECT statement including the CTE.
If you need the same CTE in multiple SQL statements and you want to make it permanent, so creating a SQL view (which is nothing else than a stored SELECT statement) is the way to go.
When using an SQL view in a SELECT statement the query optimizer can use it in the same way as a CTE, i.e. it includes the SELECT statement out of the view in the SELECT statement to be executed, and then optimizes the whole SELECT statement.
Neither with an CTE nor with a View any data has to be copied.

A global temporary Table is nothing else than a (real) table created in the QTEMP. This table must be explicitly populated and maintained.
So you have to copy data, which is on one side time consuming on the otherside when accessing the data in the global temporary table, it may no longer match with the real data.
I'd only create a global temporary table if there is no other way to store and consume the data.

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964
------------------------------

Dear Amy

Kent Milligan used to write an article that demonstrated performance benefit of using CTE over temporary table in a multi-stage query and I attach it herewith if you are interested.  I used to help a few customers in the past who used temp tables in multi-stage queries with bad performance and this article proved effective in delivering a much better run-time of the queries at issue. 

I figure that CTE must be very versatile because I also have several other articles about how to use CTE in many other ways. Let me know if you are interested to know more about CTE and I will upload them for you.

------------------------------
Right action is better than knowledge; but in order to do what is right, we must know what is right.
-- Charlemagne

Satid Singkorapoom
------------------------------

I have attended Kent's session at Common, "SQL and Temp Tables - Good or Bad Combination".  I had not known about "Global" temporary tables until recently and didn't known if those were different than or the same as other temporary tables.  Thanks for all the responses.

------------------------------
Amy Vozza
------------------------------

Original Message:
Sent: Mon August 15, 2022 06:49 AM
From: Satid Singkorapoom
Subject: Common Table Expression vs Global Temporary Table

Dear Amy

Kent Milligan used to write an article that demonstrated performance benefit of using CTE over temporary table in a multi-stage query and I attach it herewith if you are interested.  I used to help a few customers in the past who used temp tables in multi-stage queries with bad performance and this article proved effective in delivering a much better run-time of the queries at issue. 

I figure that CTE must be very versatile because I also have several other articles about how to use CTE in many other ways. Let me know if you are interested to know more about CTE and I will upload them for you.

------------------------------
Right action is better than knowledge; but in order to do what is right, we must know what is right.
-- Charlemagne

Satid Singkorapoom
------------------------------