Beware The Temporary Table
June 2, 2015 Hey, Ted
|
I am writing in response to your article Dynamic Lists in Static SQL Queries. At a recent NEUSG meeting, Tom McKinley of IBM warned us to avoid temporary tables, especially query chains of temporary tables, as they have no history for the optimizer. I like dynamic SQL. I would have left it alone. –Lynne I always enjoy hearing from Lynne because I know I will hear words of wisdom. Lynne raises a good point that I have intended for some time to address in this august publication. Kent Milligan, one of Tom McKinley’s colleagues at IBM’s DB2 for i Center of Excellence, told me awhile back that they see much abuse of temporary tables. Kent said that they recommend using views and common table expressions when possible. This is excellent advice, especially when it comes to the chains of queries, as Lynne mentioned. For example: Query 1 reads some database tables and builds temporary table 1. It is certainly possible to create three SQL queries that mimic these three Query for i queries, but possible is not the same as best. Let me use a simpler query chain to illustrate some preferred alternatives. Table QIWS/QCUSTCDT has one row per customer. Two of the columns are balance due (BALDUE) and credit due (CDTDUE). Assuming that BALDUE is money that the customer owes us and CDTDUE is money we owe the customer, then the total amount that our customers owe us is the sum of (BALDUE minus CDTDUE). Query 1 sums BALDUE minus CDTDUE into a one row temporary table, which we’ll call TEMP1, that has a BALANCE01 column with a value of 5,761.25. Query 2 uses a cross join (cartesian product) QIWS/QCUSTCDT and TEMP1 to find each customer’s share of the debt. Here are the equivalent SQL statements. declare global temporary table temp1 as
(select sum(baldue - cdtdue) as balance01
from qiws/qcustcdt)
with data with replace
select t01.cusnum, t01.lstnam, t01.init,
dec((t01.baldue - t01.cdtdue) / t02.balance01 * 100, 7,4)
from qiws/qcustcdt as t01
cross join qtemp/temp1 as t02
order by 4 desc
And here’s the report: Account Name Share% ------- --------- --- ------- 938485 Johnson J A 68.6309 583990 Abraham M T 8.6786 192837 Lee F L 8.4877 392859 Vine S S 7.6198 475938 Doe J W 2.6036 839283 Jones B D 1.7357 389572 Stevens K L .9937 938472 Henning G K .6422 593029 Williams E D .4339 846283 Alison J S .1735 397267 Tyron W E .0000 693829 Thomas A N .0000 The result set is correct, but the way it was derived is far from optimal. One alternative is to use a common table expression to calculate the sum. with temp1 as
(select sum(baldue - cdtdue) as balance01
from qiws/qcustcdt)
select t01.cusnum, t01.lstnam, t01.init,
dec((t01.baldue - t01.cdtdue) / t02.balance01 * 100, 7,4)
from qiws/qcustcdt as t01
cross join temp1 as t02
order by 4 desc
A second alternative is to use a derived table. select t01.cusnum, t01.lstnam, t01.init,
dec((t01.baldue - t01.cdtdue) / t02.balance01 * 100, 7,4)
from qiws/qcustcdt as t01
cross join
(select sum(baldue - cdtdue) as balance01
from qiws/qcustcdt) as t02
order by 4 desc
Yet a third alternative is to use a view to compute the sum. create view mylibTotalOwed as
(select sum(baldue - cdtdue) as balance01
from qiws/qcustcdt
The view would only be created once, of course. There’s no need to recreate the view every time the query runs. You would query the view as you would a table. select t01.cusnum, t01.lstnam, t01.init,
dec((t01.baldue - t01.cdtdue) / t02.balance01 * 100, 7,4)
from qiws/qcustcdt as t01
cross join TotalOwed as t02
order by 4 desc
The lesson is a good one. If you need a temporary table, by all means create one. My experience is that temporary tables are not needed in most cases. I use a lot of common table expressions and derived tables in my work. RELATED STORIES Dynamic Lists in Static SQL Queries Table Expressions Ease System Conversion A View Of A View Of A View. . .
|
