Indexing otherwise 'temporary' tables

In article <3c98daf2@10.1.10.29>, TC225@.el.closeo.cornwell.edu says...
[SNIP]
> I guess I'll try it both ways & see, but if anyone can shed some light on
> the technical aspects of which might be better...

Just because a table is temporary, it doesn't mean you can't index them.
There are 2 types of temp tables. The one I assume you are trying to use
is the Explicit temporary tables (something like SELECT INTO #<temp
table> or create table #<temp table>). In that case you should index
those columns you'll be using for inserting data into the last tables.

[Mother Table] ---> [Indexed temp table] ----> [Final table]
(Where the indexes of the temp table are created with the Final Table in
mind)

You can also place those temp tables on a different device at runtime.
Although I really don't know to do it. But it's possible. This device is
usually a much faster hard disk.

But beware that certain statements also create temporary tables. These
are known as Implicit temporary tables and are usually created when you
invoke commands like 'select into', 'select distinct' and the dreaded
'order by' on a non-indexed column. (BTW, usually all operations on non-
indexed columns make the engine create temporary tables). These temp
tables aren't and can't be indexed. They are created by the engine and
disposed off as soon as the task is finished. They are especially
dangerous if they are being created to retrieve large amounts of data
from non-indexed columns. The worst possible scenario being retrieving a
huge set of data data with an 'order by' and 'sort' both at non-indexed
columns. But they can be great if the amount of data to be retrieved is
small.

I speak of this, because it's very usual to see people creating explicit
temporary tables thinking they are optimizing the task, only to find out
later on that the engine itself had to create its own temp tables
because of the data structure of the 'mother' tables and of poorly
created selects.

No matter what time you will be running this batch and how often, I
would say you should go for explicit temporary tables. At the same time
take care of how you are constructing the SQL statements, especially if
you are working over non-indexed columns. If needed be take a second
look at the mother tables and do some index working on them. But avoid
over-indexing your mother tables. The rule of thumb here is only index
those columns that are being used very often on selects, order by, and
sort statements. If all of them are being used, the most important to
least important are sort, order by, select. (Altough you will not be
needing sort at all for populating tables). Never, ever create an all-
indexed table if it has a huge set of records. Never, ever, ever, ever,
create an all-indexed table if it is subject to a lot of daily queries,
no matter he amount of records.

All this index talking only applies if this batch of yours will be
running very often during the day. If by any chance it will be running
only once or twice each day and outside peek times, then don't even
bother reorganizing the indexes on the mother tables (unless we are
talking of teens of millions of records here).

--
Mario Figueiredo
Web Developer
Portuguese Ministry of Education, DESUP
mario.figueiredo@desup.min-edu.pt
(please only send email if *absolutely* necessary)
(usually I will not reply to emails)

Indexing otherwise 'temporary' tables - what's fastest?