Re: Indexing otherwise 'temporary' tables - what's fastest? - Thanks Mario.
Thank you Mario for your comprehensive reply.
"Mario Figueiredo" <firstname.lastname@example.org> wrote in message
> In article <email@example.com>, TC225@.el.closeo.cornwell.edu says...
> > I guess I'll try it both ways & see, but if anyone can shed some light
> > 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
> 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
> 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
> (please only send email if *absolutely* necessary)
> (usually I will not reply to emails)
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL