January 29, 2003 at 5:16 pm
Are there any code practices with using Temp Tables that can slow down an application?
I have an application that uses Temp tables ALOT and it is and has been slow for a while. (Its still in development).
There seems to be a problem when there are over 5 users on the system at the same time.
Timeouts, deadlocks.
January 29, 2003 at 5:41 pm
Doing a select into instead of an explicit create table followed by an insert is one. Using global temp tables might be another. You're seeing deadlocks in tempdb?
Andy
January 29, 2003 at 6:01 pm
Timeouts in tempdb.
Deadlocks in user db.
User db uses alot of temp tables.
January 30, 2003 at 2:16 am
If you are using SQL Server 2000, consider using table variables in lieu of temp tables wherever possible. You should get a sizable performance improvement.
January 30, 2003 at 4:04 am
Might help if we understood the principal for the choice of a temp table. You could use subqueries or derived tables instead. What specifically is the temp table for and how is it put together?
January 30, 2003 at 5:04 am
Are they large enough to benefit from indexes?
Andy
January 30, 2003 at 9:58 am
What is a good way to capture TempDB contention?
I am thinking that on the create temp table(s) there is locking of syscolumns, causing contention.
How does one measure/capture this?
January 30, 2003 at 10:32 am
I've had similar problems with heavy temp table usage in our applications here. One thing we have done to alleviate much of the performance problems is to ensur that DML and DDL are not mixed within the same stored procedure. Create the temp table structures in one procedure and insert into them in another. This helps ensure that both procedures avoid recompilations often. Just thought I'd shoare something that worked for us...
January 30, 2003 at 11:29 am
excellent. Thanks.
Back to my question, what is a good way to capture TempDB contention information?
January 30, 2003 at 4:46 pm
I am seeing alot of LOCK:TIMEOUTS with tempdb(syscolumns and sysobjects.)
Anywhere to look to find the root cause?
January 31, 2003 at 7:11 am
Can you detail how the temp tables is used by all users?
is it the same way all the users are accessing the temp table?
When you see locks at that time which processess is blocked and by checking sp-lock you can see which object is locked?
How application is using this temp table is important to look espacially the sequence....
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
February 1, 2003 at 8:58 am
Here's one sample that is causing contention. (Sorry could not put in the exact code.)
I am concerned about the temp table creation here and the resulting blocks/locks this may cause and the length of time resources are held. This is a heavily used database and alot of asychronous processing.
The temp table is not created before this code sample.
Any problems with this code?
SELECT DISTINCT
a.***,
a.***
INTO #Tmp FROM EVT_*** a
WHERE a.*** = 0
AND a.*** = CASE WHEN @inp_lngEVTUserActionLogUserActionLogID = -2147483647
THEN a.*** ELSE @inp_lngEVTUserActionLogUserActionLogID END
AND a.*** = CASE WHEN @inp_lngEVTUserActionLogUserID = -2147483647 THEN a.***
ELSE @inp_lngEVTUserActionLogUserID END
AND a.*** = CASE WHEN @inp_lngEVTUserActionLogComponentInstanceID = -2147483647
THEN a.*** ELSE @inp_lngEVTUserActionLogComponentInstanceID END
AND a.*** = CASE WHEN @inp_strEVTUserActionLogTriggeringObjectUICode = '~'
THEN a.*** ELSE @inp_strEVTUserActionLogTriggeringObjectUICode END
February 1, 2003 at 10:14 am
Usign SELECT...INTO has known issues with locking. You should try to write so temp table is created with CREATE TABLE and use INSERT.
February 1, 2003 at 11:18 am
What kind of issues?
Is this the case in 7.0 and 2000?
February 1, 2003 at 1:55 pm
(SQL Server 2000)
The interesting thing about the different ways to create temp tables is the number of locks help by each SPID.
Two coding techniques are:
1. SELECT..INTO #
2. CREATE TABLE #, then INSERT INTO #
Results of [sp_lock spid] for each:
1. 55 rows
2. 19 rows
Both of these techniques(before committing them), will not allow a SELECT on tempdb..sysobjects and/or tempdb..syscolumns from another SPID.
The only difference here is the SELECT..INTO has more overhead in terms of locking within in the tempdb.
One would think that the create table #..insert.. would allow a select on sysobjects
and/or syscolumns.
This is what I did:
(separate executions)
--SPID 52
USE PUBS
GO
BEGIN TRANSACTION
SELECT EMP_ID, FNAME, LNAME
INTO #TMP_TBL1
FROM EMPLOYEE
--SPID 55
USE PUBS
GO
BEGIN TRANSACTION
CREATE TABLE #TMP_TBL2
(EMP_IDCHAR(9),
FNAME VARCHAR(20),
LNAME VARCHAR(30))
INSERT INTO #TMP_TBL2 (EMP_ID, FNAME, LNAME)
SELECT EMP_ID, FNAME, LNAME
FROM EMPLOYEE
-- SPID 56
USE TEMPDB
SELECT * FROM SYSOBJECTS
SELECT * FROM SYSCOLUMNS
Edited by - bryan99y on 02/01/2003 1:57:09 PM
Edited by - bryan99y on 02/01/2003 2:01:24 PM
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply