DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

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

  1. #1
    Tim Guest

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

    Thanks for looking,

    I am trying to optimize a rather lengthy process (a set of Stored
    procedures) that initially combines a number of tables into temporary
    tables, then uses these these as reference/input for a dozen or more other
    procedures. These tables need to exist only for the process, and can go
    away once the process is completed.

    My question is:
    Is it more effective, effecient, faster, etc., and does it make sense to
    tables (with indexes) and populate them for use as these reference tables,
    or just to create them completely on the fly as temporary (#<table name>)
    tables for the subsequent processes?

    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... Any help is appreciated.

    Tim Cornwell

  2. #2
    Mario Figueiredo Guest

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

    In article <3c98daf2@>, says...
    > 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

    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center
Latest Articles
Questions? Contact us.
Web Development
Latest Tips
Open Source

   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center