Which is better Implicit or Explicit drop table for #temp tables

Which is better Implicit or Explicit drop table for #temp tables

Post by Carl A. Dotg » Wed, 30 Jan 2002 00:17:28



Hello All,

We have some locking problems with our tempdb system tables (who
doesn't).  One thing that I noticed was that some new code went up
that creates alot of #temp tables and then throughout the code
executes truncates and drops of the tables.  My question is when the
system automatically cleans up #temp tables upon exit is this done by
some system process?  My thinking is yes, and the truncates and drops
throughout the code are causing more unnecessary hits against tempdb
plus I think it is adding overhead to the timing of the proc.  Would
it be less expensive to let the system clean up the temp tables on
exit?

We are running 11.9.2.3 ebf 9920 on Compaq Tru-Unix64  5.1

TIA,

Carl A. Dotger

 
 
 

Which is better Implicit or Explicit drop table for #temp tables

Post by Larry Coo » Wed, 30 Jan 2002 02:36:49



> We have some locking problems with our tempdb system tables (who
> doesn't).  One thing that I noticed was that some new code went up
> that creates alot of #temp tables and then throughout the code
> executes truncates and drops of the tables.  My question is when the
> system automatically cleans up #temp tables upon exit is this done by
> some system process?  My thinking is yes, and the truncates and drops
> throughout the code are causing more unnecessary hits against tempdb
> plus I think it is adding overhead to the timing of the proc.  Would
> it be less expensive to let the system clean up the temp tables on
> exit?

You'll find that as the number of users increases, the
big performance hit comes from contention on sysobjects
and syscolumns in tempdb, because those tables need to
be updated whenever a temp table is created or dropped.

The solution that worked best for us is to use
permanent tables.  You have to do more to make it work,
but the performance is better.  Your mileage may vary,
of course.  Here's an example:

create table scratch_table (
  spid smallint not null,
  value1 int,
  value2 int
) lock datarows

create procedure inner_proc as
  insert into scratch_table

return

create procedure outer_proc as
  delete from scratch_table

  exec inner_proc

  select *
  from scratch_table

return

These procedures don't use tempdb at all, and they
completely avoid the overhead of creating and
dropping tables all the time.

Larry Coon
University of California



 
 
 

Which is better Implicit or Explicit drop table for #temp tables

Post by Ilya Zvyagin 214748364 » Wed, 30 Jan 2002 04:39:48



Quote:> We have some locking problems with our tempdb system tables (who
> doesn't).  One thing that I noticed was that some new code went up
> that creates alot of #temp tables and then throughout the code
> executes truncates and drops of the tables.  My question is when the
> system automatically cleans up #temp tables upon exit is this done by
> some system process?  My thinking is yes, and the truncates and drops
> throughout the code are causing more unnecessary hits against tempdb
> plus I think it is adding overhead to the timing of the proc.  Would
> it be less expensive to let the system clean up the temp tables on
> exit?
> We are running 11.9.2.3 ebf 9920 on Compaq Tru-Unix64  5.1

I want to note only that if you create already tempdb
tables within an explicit transaction there is no
reason for awoiding dropping it within the same transaction
- sysobjects in tempdb is locked anyway till the end of this
transaction. If you create tempdb tables outside of transactions
there will be no harm to drop them again. So, in both
cases performance or concurrency is not the issue here.
 
 
 

Which is better Implicit or Explicit drop table for #temp tables

Post by who » Wed, 30 Jan 2002 09:36:26


I agree with Larry.  If there is a lot of them then it is best to have
permanent temp tables

Eric


Quote:> Hello All,

> We have some locking problems with our tempdb system tables (who
> doesn't).  One thing that I noticed was that some new code went up
> that creates alot of #temp tables and then throughout the code
> executes truncates and drops of the tables.  My question is when the
> system automatically cleans up #temp tables upon exit is this done by
> some system process?  My thinking is yes, and the truncates and drops
> throughout the code are causing more unnecessary hits against tempdb
> plus I think it is adding overhead to the timing of the proc.  Would
> it be less expensive to let the system clean up the temp tables on
> exit?

> We are running 11.9.2.3 ebf 9920 on Compaq Tru-Unix64  5.1

> TIA,

> Carl A. Dotger

 
 
 

Which is better Implicit or Explicit drop table for #temp tables

Post by Anthony Mand » Wed, 30 Jan 2002 19:40:44



Quote:> We have some locking problems with our tempdb system tables (who
> doesn't).  One thing that I noticed was that some new code went up
> that creates alot of #temp tables and then throughout the code
> executes truncates and drops of the tables.  My question is when the
> system automatically cleans up #temp tables upon exit is this done by
> some system process?  My thinking is yes, and the truncates and drops
> throughout the code are causing more unnecessary hits against tempdb
> plus I think it is adding overhead to the timing of the proc.  Would
> it be less expensive to let the system clean up the temp tables on
> exit?

> We are running 11.9.2.3 ebf 9920 on Compaq Tru-Unix64  5.1

     You'll need to grab a later EBF. There were two known problems
     with some of the early 11.9.2 EBFs (I'm not sure if your EBF
     falls into this category though). The problems were select into
     created temp tables holding locks in the system catalogs for
     longer than required and sprocs not doing temp table clean ups
     on exit. The latter could be addressed by explicit drop table
     statements. As for your particular question, there's no real
     distinction between doing it implicitly or explicitly. Either
     one does the same drop work and if you do it explicitly then
     there won't be a need for an implicit one since they won't exist
     when that part of the code checks for them. As for the need for
     an explicit truncate first, try dropping a very large table that
     you don't need and compare that to a truncate of a similarly
     large table (hint: you can copy an existing large table to test
     this).

-am  ? 2002

 
 
 

Which is better Implicit or Explicit drop table for #temp tables

Post by Maj Sweid » Fri, 01 Feb 2002 01:19:49


Try this idea. I have used this with one of my clients and it helped
speed up tempdb usage significantly.
Set dsync=false on the device used by your tempdb.
sp_deviceattr file_device1, dsync, false

Your I/Os might go a bit faster, resolving any locks as well.

Maj

 
 
 

Which is better Implicit or Explicit drop table for #temp tables

Post by Anthony Mand » Fri, 01 Feb 2002 18:42:34



> Try this idea. I have used this with one of my clients and it helped
> speed up tempdb usage significantly.
> Set dsync=false on the device used by your tempdb.
> sp_deviceattr file_device1, dsync, false

     This assumes he's using file devices for tempdb and is on ASE 12.0
     or later. Unfortunately, he's on ASE 11.9.2.

-am  ? 2002

 
 
 

1. Explicit Temp Table Instead of SubSelect

I have an application in which I updated a status flag based on whether
items in the table have been invoiced.   I was using an UPDATE statement
similar to the following:

UPDATE MyData SET StatCode = 'I' WHERE MyDataID IN
(SELECT DISTINCT MyDataID FROM Invoiced WHERE InvStatus = 'Y')

Due to the sizes of the tables involved I was having problems with timeouts,
so I decided to try breaking the subselect out into an explicit temporary
table.  After all, that's more or less what SQL Server is doing anyway.

I now have something similar to the following:

SELECT DISTINCT MyDataID INTO #temptbl FROM Invoiced WHERE InvStatus = 'Y'
UPDATE MyData SET StatCode = 'I'
  FROM MyData m, #temptbl tpt
 WHERE m.MyDataID = tpt.MyDataID
DROP TABLE #temptbl

I'm seeing a marked improvement (i.e., no time-outs) but I wondered if there
were any pitfalls I might be overlooking.

I'll appreciate any feedback on this subject.  The approach looks so simple
and reasonable that I can't help looking for the catch.

Richard A. Polunsky
Houston, Texas

2. Update statement with subquery

3. Temp table vs Global Temp table

4. Stop a query by setting the session for call back

5. ALTER TABLE on temp tables, table variables

6. DTS and Lotus Notes

7. Error renaming table file to a temp table table caused ingres to crash

8. Design Lib (Shareware components library)

9. Dropping temp tables

10. Simple question: cannot create, drop, recreate temp table

11. Drop Temp Tables

12. Dropping a temp Table

13. Drop temp tables in tempdb