Dblocks with temporary table

Question

Dblocks with temporary table

bheemsen

SSC-Addicted

Points: 415
More actions
January 28, 2003 at 1:07 pm

#159416

Hi,
I am using temp table in my stored procedure. The temp table is first created and rows are inserted. Then I am selecting data from same temp table. Everything is inside the stored procedure. This stored procedure is called from a Java (EJB) program with at least 100 threads at the same time.
But when the Java program runs and calls this stored procedure, it is resulting in many dblocks. Can any one explain why this is happening ? I appreciate any help on this.
Does each thread create its own copy of temp table ?
Is temdb locked in this process ?
Do I need to drop the tamp table at the end ? ( I am not dropping now)
What are the other alternatives ?
I am on SQL Server 7, windows 2000/NT.
Thanx..
-Bheemsen

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

Does each thread create its own copy of temp table ?

It depends on how you are creating the temp table. If you are doing #TempTable (one bang), then one temp table per stored procedure. If you are doing ##TempTable (two bangs), then one temp table that will exist until nothing is accessing it.

Is temdb locked in this process ?

Locks do occur during temp table creation. For instance, sysobjects, sysindexes, and syscolumns come to mind.

Do I need to drop the tamp table at the end ? ( I am not dropping now)

A temp table with one bang (#TempTable) will be dropped automatically when the stored procedure ends. A temp table with two bangs (##TempTable) will be dropped automatically when the last process stops using it (if there are a bunch of processes and it's always in use, it doesn't get dropped).

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

dj_meier SSCrazy Points: 2732 More actions · Answer 2

I would recommend using a table variable instead of a temp table in your stored procs. The table var is much more efficient and it won't lock tempdb like a temp table does. Of couse with a table var, you can only access it from within its own procedure call, it's not global like a ##temptable.

Something like this:

 

DECLARE @TableVar TABLE(col_1 INT PRIMARY KEY, col_2 VARCHAR(20))

Note: You must type the "DECLARE" keyword for each table var used. You can't create indexes on table vars, other than primary keys.

-Dan

bheemsen SSC-Addicted Points: 415 More actions · Answer 3

I don't see any table type variable in SQL server 7. Are you talking about SQL server 2000 ?

When tried to use the statement,

DECLARE @TableVar TABLE(col_1 INT PRIMARY KEY, col_2 VARCHAR(20))

I get an error

Thanx..

-Bheemsen

dj_meier SSCrazy Points: 2732 More actions · Answer 4

Ah, sorry about that...yes, its only in SQL 2000.

-Dan