Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-01-29 : 08:53:21
|
| bheemsen writes "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" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-01-29 : 10:12:06
|
| Each spid will have it's own copy of the temp table.If you look at msdb..sysobjects you will see the temp tables with suffixes for each spid.You don't need to drop the temp tables as they will be dropped automatically at the end of the SP (you might consider it good practise to do so though).Creating a temp table is fairly resource hungry and does cause problems in systems with many spids as you are finding.For v2000 they introduced table variables which lock less resources.Could recode so it doesn't use a temp table.Could use a permanent table keyed on spid - but might find deletes and contention on this table cause more problems than the temp tables.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
Dblocks with temporary table