I just noticed that JOINs with temporary tables are much slower than JOINs
with base tables and like to ask if that is typical and what the technical
cause is.

Example:

BEGIN
SELECT DISTINCT t_at.vid_auftrag INTO #temp FROM t_at WHERE
t_at.abgeschlossen_am > CAST('2009-01-01' AS DATE);
SELECT DISTINCT t_atmk.vid_auftrag, t_atmk.vid_qmerkmal
FROM t_at JOIN t_atmk
JOIN t_hsp
JOIN t_hm
WHERE t_hm.picture_id_bild IS NOT NULL AND EXISTS (SELECT * FROM #temp
WHERE #temp.vid_auftrag=t_at.vid_auftrag);
END

This simply fills a temporary table with PKs, and then uses these PKs to
filter the second SELECT. On my lab machine, this needs 24 seconds.

When not using the temporary table, but directly filtering the base table
(it is joined anyways), this runs in 12 seconds -- 100% better performance:

BEGIN
SELECT DISTINCT t_atmk.vid_auftrag, t_atmk.vid_qmerkmal
FROM t_at JOIN t_atmk
JOIN t_hsp
JOIN t_hm
WHERE t_hm.picture_id_bild IS NOT NULL AND t_at.abgeschlossen_am >
CAST('2009-01-01' AS DATE);
END

Since the sole difference between both attempts is whether or not to buffer
the intermediate PK list in a temporary table, I thought that the problem
must be the creation of the temporary table.

So I measured just the table creation:

SELECT DISTINCT t_at.vid_auftrag INTO #temp FROM t_at WHERE
t_at.abgeschlossen_am > CAST('2009-01-01' AS DATE);

But actually, that just needs 0,016 seconds...!

So in fact, the difference of 12 second is needed to JOIN with the
intermediate result (which, BTW, contains just 567 PKs). I wonder why...!?

Is there any technical explanation of this? Are JOINs with temporary tables
(rember, it contains just 567 PKs) always so slow compared to JOINs with
base tables?

Regards
Markus

Any news on this? I mean, anybody besides me interested in an answer why
temp tables need 12 seconds more than base tables?

"Markus KARG" <karg (AT) quipsy (DOT) de> schrieb im Newsbeitrag
news:4a76bded$3 (AT) forums-3-dub (DOT) sybase.com...

The two queries are vastly different... they don't just substitute a
temporary table for a base table in a join, they use quite different
techniques, one of which happens to use a temporary table.

The temporary table doesn't have any indexes. SELECT INTO is not
generally used if you care about performance IMO.

What do the two different plans look like?

Did you flush the cache before each test? If not, one test may benefit
from a previous test having loaded the cache.

Did the test using the temporary table write a lot of pages to the
temporary file?

Is the temporary file on the same or different drive? (this may not
matter)

Was the temporary file fragmented? The answer is probably yes, if it
is Windows, and if the temporary file was not defragmented after it
grew in size; see
http://sqlanywhere.blogspot.com/2008...rary-file.html

One test does not support a conclusion. Techniques that introduce
temporary tables are SOMETIMES faster, but they are often SLOWER. The
differences here are much larger than just "using a temp table in a
join".

You probably didn't receive an answer because folks cannot repeat your
test without the data.

Breck

On 13 Oct 2009 02:52:34 -0700, "Markus KARG" <karg (AT) quipsy (DOT) de> wrote:

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter (AT) risingroad (DOT) com