Microsoft KB Archive/891553

= BUG: There is a decrease in performance when you frequently create and drop temporary tables in SQL Server 2000 =

Article ID: 891553

Article Last Modified on 11/2/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Developer Edition
 * Microsoft SQL Server 2000 Personal Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 Desktop Engine (Windows)
 * Microsoft SQL Server 2000 64-bit Edition

-



Bug #: 472280 (SQL Server 8.0)



SYMPTOMS
Assume the following scenario: You try to perform a repeated operation in a single connection in Microsoft SQL Server 2000 by using the following procedure:
 * 1) You create a temporary table.
 * 2) You run an Insert statement to insert data into the temporary table by using the sp_executesql stored procedure.
 * 3) You drop the temporary table.

In this scenario, performance times may steadily increase. Additionally, performance times continue to increase as long as the connection is not dropped. This problem does not occur if you use a permanent table.

Note This problem slows down only the current connection, and it occurs only in the global temporary tables.



WORKAROUND
To work around this problem, use one of the following methods:
 * Use a TRUNCATE statement instead of a CREATE statement and a DROP statement.
 * Use permanent tables instead of temporary tables.
 * Use an INSERT statement directly instead of calling the sp_executesql stored procedure.

Note The call to the sp_executesql stored procedure may not be coded in your application. However, the stored procedure may be added by the underlying database API. For example, the underlying database API may be ADO, OLE DB, or ODBC. You can use SQL Profiler to determine whether the sp_executesql stored procedure was sent to the computer that is running SQL Server.
 * Run the SQL script from an application. When you run the SQL script from an application, you can disconnect and then reconnect more frequently. Or, when you do this, you can wrap your code inside a stored procedure in SQL Server.



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the &quot;Applies to&quot; section.



Steps to reproduce the problem
 Click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer. Log on to the computer that is running SQL Server 2000 in SQL Query Analyzer.  Paste the following code into SQL Query Analyzer: use pubs go

set nocount on declare @i int set @i = 0 declare @tt as datetime set @tt = getdate

while @i < 100000 begin if @i % 10 = 0 begin if exists (select * from tempdb.dbo.sysobjects where id =object_id('tempdb.dbo.#T1')) drop table #T1 CREATE TABLE #T1(Id int NULL) end exec sp_executesql N'Insert into #T1 values(@P1)', N'@P1 int', @i select @i = @i + 1 if @i % 10000 = 0 and @i > 0 begin print datediff(ms,@tt,getdate) select @tt = getdate end end  Press F5 to run the code.

As you run the code, performance decreases.

Keywords: kbtshoot kbbug KB891553

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.