January 27, 2003 at 11:20 am
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
January 27, 2003 at 3:28 pm
1) Each connection can create it's own copy of a temp table that is referenced with #tblname. Those with ##tblname will be available to all connections until the last one disconnects then it will autodestroy itself.
2) Should not be, should be a lock on the Temp Table by the connection using it at the most. However if you use SELECT INTO to create the table there can be locking issues on the original table. It is better to do a CREATE TABLE for temp table and then an INSERT with a SELECT FROM.
3) It is suggested even thou it will cleanup when the connection goes away. The reason is if the connection does other things the data will remain there and you really want to free resources ASAP when not needed.
4) Depends on what you are doing. Could be simply to use a SubQuery or Derived Table instead of a temp table. To know more you need to psot query statements and work flow for us to be able to help more.
January 27, 2003 at 3:56 pm
Thanx for the reply. See my entire stored procedure below:
BEGIN
Declare LU_AddCursor CURSOR LOCAL FAST_FORWARD for
SELECT living_unit_id
FROM LU_ADDRESS (nolock)
WHERE taper_code = @p_taper_cd
AND terminal_name = @p_terminal_name
AND wc_code = @p_wc_code
for read only
OPEN LU_AddCursor
FETCH NEXT from LU_AddCursor into @v_living_unit_id
While @@FETCH_STATUS <> -1
BEGIN
insert into #temp_eml (eml, dlc_ind, measured_dt, wtn)
(Select a.elec_measured_length, a.dlc_ind, a.measured_dt, a.wtn
From RP_MLT a (nolock), LU_WTN b (nolock)
where a.wire_cntr_cd = b.wc_code
And a.wtn = b.wtn
Andb.living_unit_id = @v_living_unit_id
Andb.wc_code = @p_wc_code
And a.dlc_ind = 'N'
And a.elec_measured_length > 0)
set @nMLTCount = @nMLTCount + @@ROWCOUNT
FETCH NEXT from LU_AddCursor into @v_living_unit_id
END
--- find the median
If @nMLTCount > 0
BEGIN
select @p_elec_measured_length = eml,
@p_dlc_ind = dlc_ind,
@p_measured_dt = measured_dt,
@p_mlt_wtn_used = wtn
from #temp_eml
where id = 1
set @n_res_count = (@nMLTCount/2)
If (@nMLTCount%2) = 0
set @c_type = 'even'
else
set @c_type = 'odd'
Declare TEMP_EMLcursor CURSOR LOCAL FAST_FORWARD for
SELECT eml
FROM #temp_eml (nolock)
order by eml
for read only
OPEN TEMP_EMLcursor
FETCH NEXT from TEMP_EMLcursor into @n_temp_eml
While @@FETCH_STATUS <> -1
BEGIN
set @n_count = @n_count + 1
If @c_type = 'odd'
BEGIN
If @n_count = (@n_res_count +1)
BEGIN
SET ROWCOUNT 1
set @n_coeff_num = null
SELECT @n_coeff_num = coefficient_num
FROM ref_mlt_coefficient (nolock)
WHERE @n_temp_eml > minimum_length
AND @n_temp_eml <= maximum_length
ORDER BY created_dt desc
If @n_coeff_num is not NULL
Set @n_eq_eml = @n_temp_eml * @n_coeff_num
else
Set @n_eq_eml = @n_temp_eml
END
END
else
If @c_type = 'even'
BEGIN
If (@n_count = @n_res_count or @n_count = (@n_res_count + 1))
BEGIN
SET ROWCOUNT 1
set @n_coeff_num = null
SELECT @n_coeff_num = coefficient_num
FROM ref_mlt_coefficient (nolock)
WHERE @n_temp_eml > minimum_length
AND @n_temp_eml <= maximum_length
ORDER BY created_dt desc
If @n_coeff_num is not NULL
Set @n_eq_eml = @n_temp_eml * @n_coeff_num
else
Set @n_eq_eml = @n_temp_eml
set @n_eq_eml_accum = @n_eq_eml_accum + @n_eq_eml
END
END
FETCH NEXT from TEMP_EMLcursor into @n_temp_eml
END
If @c_type = 'even'
set @p_eq_elec_measured_length = @n_eq_eml_accum/2.0
else
set @p_eq_elec_measured_length = @n_eq_eml
Set @p_mlt_found_ind = 'Y'
Set @nRetcode = 0
CLOSE TEMP_EMLcursor
DEALLOCATE TEMP_EMLcursor
CLOSE LU_AddCursor
DEALLOCATE LU_AddCursor
END
else
BEGIN
CLOSE LU_AddCursor
DEALLOCATE LU_AddCursor
Set @p_elec_measured_length = NULL
Set @p_eq_elec_measured_length = NULL
Set @p_dlc_ind = NULL
Set @p_measured_dt = NULL
Set @p_mlt_wtn_used = NULL
Set @p_mlt_found_ind = NULL
Set @nRetcode = 0
END
END
-- #debug
if (@vDebugVal = @cDebugFlag)
begin
print ('-- END spFindMLT-- ' + CONVERT(char(24), getDate(), 120))
end
-- #end of debug
set nocount off
return @nRetCode
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply