Dblocks with temporary table

  • 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

  • 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.

  • 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