Dblocks with temporary table

[bheemsen]

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 procedures, 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

 

[bheemsen]

Hi,

I could get answers to some of my questions.

1. Each thread will have its own copy of temp table.
2. Not sure whether tempdb is locked.
3. No drop required. Anyway I am going to add this in the procedure.
4. I still could not find any alternate solution for this.

I did a volume testing with at least 50 threads with trace option and saw many dblocks on tempdb for the temp table.

Any ideas ? or suggestions ?

Thanx..
-Bheemsen

 

[SQLSister]

Perhaps we could help more if you posted the stored procedure code.