Thank you Mario for your comprehensive reply.

Tim


"Mario Figueiredo" <marfig@netcabo.pt> wrote in message
news:MPG.170a89957bc9878198968c@news.devx.com...
> 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)