Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2002
    Posts
    29

    Question Unanswered: temp tables (# tables)

    Hi All,

    Is there any restriction creating number of temp tables (# tables) in sybase db?. What all the overheads?. Most of the my procedures creates # tables(since it has to access the from atleast 3-4 tables). My DBA says creating more # tables causes locks and reduces the performance. The reason I am creating # tables to avoid more read locks on a table, say A, which constantly getting updated. While selecting the data from A, B and C , insteaded of join query, my proc does first select data from table A into # table and then update data from B and C to # table. Which approach is better? #table approach or join query?

    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159

    Re: temp tables (# tables)

    There is no restriction on creating # temp. tables, other than the restrictions for creating permanent tables in other database. However your DBA is correct to bring this to your attention because creating temporary tables causes exclusive locks on sysobjects, syscolumns and sysindexes. If you create the tables using the create table command, the locks exist for the time it takes to create the tables. If you create the tables using select into, the locks may exist for the time it takes to populate those tables.

    Read (shared) locks will still need to be taken out on the permanent tables to populate the temporary ones. Verify your queries are quicker against the temp. tables and if the temp. tables are more than a few hundred rows that there are indexes and they are being fully utilised by your queries.

  3. #3
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    I don't think that, in most circumstances, creating a temp table will reduce contention on A -- you have to SELECT from it anyway to generate the temp table which is exactly what you'd do in your join.

    How do you know that this query caused locking contention in A?
    Thanks,

    Matt

  4. #4
    Join Date
    Aug 2002
    Location
    Omaha, Nebraska
    Posts
    21

    Exclamation Re: temp tables (# tables)

    You wrote:
    Originally posted by richardcrossley
    There is no restriction on creating # temp. tables, other than the restrictions for creating permanent tables in other database. However your DBA is correct to bring this to your attention because creating temporary tables causes exclusive locks on sysobjects, syscolumns and sysindexes. If you create the tables using the create table command, the locks exist for the time it takes to create the tables. If you create the tables using select into, the locks may exist for the time it takes to populate those tables.
    ...
    No. That is not correct. Using SELECT INTO will only lock the tempdb catalog tables for the time it takes to execute the step which creates the table. The locks are released before the table is populated. So the locks on the catalog tables are only held for as long as they would be if you used the CREATE TABLE approach instead. The benefit of SELECT INTO over CREATE/INSERT is minimal logging.

  5. #5
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159

    Re: temp tables (# tables)

    Originally posted by Kevin Sherlock
    You wrote:

    No. That is not correct. Using SELECT INTO will only lock the tempdb catalog tables for the time it takes to execute the step which creates the table. The locks are released before the table is populated. So the locks on the catalog tables are only held for as long as they would be if you used the CREATE TABLE approach instead. The benefit of SELECT INTO over CREATE/INSERT is minimal logging.
    I stand corrected.

  6. #6
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66

    Re: temp tables (# tables)

    >> select data from table A into # table
    >> and then update data from B and C to # table

    I did this quite often, with good results. Just make sure you don't lose much performance on the subsequent updates.

    One aspect to consider is that #table has no usable indeces by default, unless you break the stored proc into multiple pieces, etc. The full query joining A,B,C might have had a convenient index, e.g. a query plan going C->B->A (This is unlikely, since you are happy with the number of rows you get into #temp from A alone, so you don't need B or C to narrow the result set any further). But, even if the full query was going A->B->C, it could have used an index which may not exist when your updates execute the query plan #temp->B or #temp->C

    Another issue is with growing row-size in #temp. When created, the original #temp table column may have null or short values for columns you update later. In physical terms, the additional data you bring into #temp may result in longer rows. The result is page splits, or, if all rows are updated, then the entire #temp is in effect rewritten. So, instead of having a single I/O when creating #temp. you have twice the I/O. This may repeat itself on every update you do. If possible, avoid 'null' numbers in #temp if you intend to replace all the rows with a non-null value later. For example, with integers I've used 0 as a default, since this can support an "update in place" later.

    In my experience, rewriting #temp is still preferable to having locks, since most Sybase servers choke on contention, while still having enough capacity to handle I/O on separate disks. The DBA may notice an increased amount of I/O on a statistical ool, yet the system as a whole may work better, compared to the scenario of holding locks on three or more tables.

    Andrew Schonberger

  7. #7
    Join Date
    Oct 2002
    Posts
    29
    Thank you guys for all you suggestions/ideas.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •