One difference to remember about "DECLARE GLOBAL TEMPORARY TABLE
SESSION.[insert table here] as ( [insert query here] ) ...", is this little
gem of info that pertains in that case (taken from the SQL reference
"The select-statement must not refer to variables or include parameter
markers (question marks)."
I highly doubt there is much difference in performance between a QTEMP and
a SESSION table.
I use SESSION tables typically when I truly need a temp table, simply
because it is more of an SQL standard and they support REPLACE (TR10 not
installed yet here for REPLACE w/ CREATE TABLE), and only use QTEMP tables
when I need to work around the above SESSION table limitation.
That said, a lot of SESSION or QTEMP tables that people use shouldn't be
used at all. A lot of times I see them used, is in conjunction with single
row SQL processing. SQL excels at SET based processing. The more people
improve their SET based SQL skills, the less they'll use SESSION or QTEMP
tables. A fair amount of temp tables out there should be implemented as
common table expressions instead, because performance improves. I'm not
saying you can totally eliminate them, but their use should be reduced.
This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact