Blocking

  • I have a procedure that gets called that at times seems to cause a block that last for avout 15 minutes. It doesn't happen all the time. Can you see anything that may be a bad practice or could be causing the blocking? The tables being inserted generally consist of clustered indexes of bol_no and rev_no.

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    go

    ALTER PROCEDURE [dbo].[blsp_insert_truck_dest_bol_revision]

    @dest_bol_nochar(15),

    @bol_nochar(15),

    --@bol_commentvarchar(100),

    @user_idchar(30),

    @new_rev_noint,

    @old_rev_noint,

    @return_codenumeric(1)output,

    @return_messagevarchar(255)output

    AS

    BEGIN

    /*return code for BOL that doesnt exist.*/

    if @old_rev_no is null

    begin

    set @new_rev_no = -1

    set @return_code = -1

    set @return_message = 'Failed to insert new truck dest revision number'

    return

    end

    */

    UPDATE BL_TRUCK_BOL_DEST

    SET max_rev_yorn = 'N'

    WHERE bol_no = @dest_bol_no

    AND rev_no = @old_rev_no

    /*1.*/

    if not exists (select 1 from BL_TRUCK_BOL_DEST where dest_bol_no = @dest_bol_no and rev_no = @new_rev_no ) --and max_rev_yorn = 'Y')

    INSERT INTO BL_TRUCK_BOL_DEST

    ([dest_bol_no] ,[rev_no] ,[bol_no] ,[orig_stop_sequence] ,[dest_stop_sequence] ,[freight_billto_no] ,[freight_bill_to_entity] ,[template_id] ,[cust_no] ,[cust_entity] ,[cust_name] ,[cust_name2] ,[cust_addr1] ,[cust_addr2] ,[cust_addr3] ,[cust_city] ,[cust_state] ,[cust_zip] ,[origin_city] ,[origin_state] ,[origin_splc] ,[dest_city] ,[dest_state] ,[dest_splc] ,[freight_code] ,[fob] ,[freight_billto_name] ,[freight_billto_name2] ,[freight_billto_addr1] ,[freight_billto_addr2] ,[freight_billto_addr3] ,[freight_billto_city] ,[freight_billto_state] ,[freight_billto_zip] ,[freight_billto_contract] ,[third_party_yorn] ,[rqst_dlv_date_time] ,[shipper_no] ,[shipper_entity] ,[shipper_name] ,[shipper_name2] ,[shipper_addr1] ,[shipper_addr2] ,[shipper_addr3] ,[shipper_city] ,[shipper_state] ,[shipper_zip] ,[csr_no] ,[sales_rep] ,[origin_code] ,[carrier_code] ,[bol_status] ,[max_rev_yorn] ,[revision_date] ,[ship_date_time] ,[bill_type] ,[process_in_tp] ,[dest_status] ,[user_id],[cust_edi_id_code],[cust_edi_id_qualifier],[freight_billto_edi_id_code],[freight_billto_edi_id_qual],[shipper_edi_id_code],[shipper_edi_id_qualifier])

    select

    dest_bol_no ,@new_rev_no ,@bol_no ,orig_stop_sequence ,dest_stop_sequence ,freight_billto_no ,freight_bill_to_entity ,template_id ,cust_no ,cust_entity ,cust_name ,cust_name2 ,cust_addr1 ,cust_addr2 ,cust_addr3 ,cust_city ,cust_state ,cust_zip ,origin_city ,origin_state ,origin_splc ,dest_city ,dest_state ,dest_splc ,freight_code ,fob ,freight_billto_name ,freight_billto_name2 ,freight_billto_addr1 ,freight_billto_addr2 ,freight_billto_addr3 ,freight_billto_city ,freight_billto_state ,freight_billto_zip ,freight_billto_contract ,third_party_yorn ,rqst_dlv_date_time ,shipper_no ,shipper_entity ,shipper_name ,shipper_name2 ,shipper_addr1 ,shipper_addr2 ,shipper_addr3 ,shipper_city ,shipper_state ,shipper_zip ,csr_no ,sales_rep ,origin_code ,carrier_code ,bol_status , 'Y' ,getdate() ,ship_date_time ,bill_type ,process_in_tp ,dest_status ,@user_id, cust_edi_id_code, cust_edi_id_qualifier,freight_billto_edi_id_code,freight_billto_edi_id_qual,shipper_edi_id_code,shipper_edi_id_qualifier

    from BL_TRUCK_BOL_DEST where dest_bol_no = @dest_bol_no and rev_no = @old_rev_no

    /*2.*/

    if not exists (select 1 from BL_TRUCK_BOL_DEST_COMM where dest_bol_no = @dest_bol_no and rev_no = @new_rev_no)

    INSERT INTO BL_TRUCK_BOL_DEST_COMM

    ([dest_bol_no] ,[rev_no] ,[comm_code] ,[trailer_id] ,[pup_trailer_id] ,[compart_no] ,[comm_name] ,[bol_no] ,[bol_dscr] ,[comm_stcc] ,[moist_percent] ,[moist_percent_amt] ,[comm_um] ,[hazmat_yorn] ,[quantity] ,[lfvc] ,[sub_class1] ,[sub_class2] ,[sub_class3] ,[sub_risk_ind] ,[net_explosive_qty] ,[special_comm_ind] ,[french_prop_ship_name] ,[unit_price] ,[price_um] ,[odor_ind] ,[odor_rate] ,[odor_rate_um] ,[odor_comm_code] ,[odor_amt] ,[odor_um] ,[french_bol_dscr] ,[waste_yorn] ,[oil_spill_plan_yorn] ,[load_slip] ,[comm_ref1] ,[comm_ref2] ,[comm_ref3] ,[comm_ref4] ,[prod_charge_no] ,[prod_charge_name] ,[malodor_code] ,[maloder_dscr] ,[maloder_qty] ,[maloder_per_10000_gal] ,[prod_storage_id] ,[release_code] ,[rqst_load_date_time] ,[request_qty] ,[request_qty_um])

    select

    dest_bol_no ,@new_rev_no ,comm_code ,trailer_id ,pup_trailer_id ,compart_no ,comm_name ,@bol_no ,bol_dscr ,comm_stcc ,moist_percent ,moist_percent_amt ,comm_um ,hazmat_yorn ,quantity ,lfvc ,sub_class1 ,sub_class2 ,sub_class3 ,sub_risk_ind ,net_explosive_qty ,special_comm_ind ,french_prop_ship_name ,unit_price ,price_um ,odor_ind ,odor_rate ,odor_rate_um ,odor_comm_code ,odor_amt ,odor_um ,french_bol_dscr ,waste_yorn ,oil_spill_plan_yorn ,load_slip ,comm_ref1 ,comm_ref2 ,comm_ref3 ,comm_ref4 ,prod_charge_no ,prod_charge_name ,malodor_code ,maloder_dscr ,maloder_qty ,maloder_per_10000_gal ,prod_storage_id ,release_code ,rqst_load_date_time ,request_qty ,request_qty_um

    from BL_TRUCK_BOL_DEST_COMM where dest_bol_no = @dest_bol_no and rev_no = @old_rev_no

    /*3.*/

    if exists (select 1 from BL_TRUCK_BOL_DEST_COMM_ORDER where dest_bol_no = @dest_bol_no and rev_no = @old_rev_no)

    and not exists (select 1 from BL_TRUCK_BOL_DEST_COMM_ORDER where dest_bol_no = @dest_bol_no and rev_no = @new_rev_no)

    INSERT INTO BL_TRUCK_BOL_DEST_COMM_ORDER

    ([dest_bol_no] ,[rev_no] ,[order_no] ,[comm_code] ,[quantity] ,[bol_no] ,[csr_no] ,[sales_rep] ,[record_seq_no])

    select

    dest_bol_no ,@new_rev_no ,order_no ,comm_code ,quantity ,@bol_no ,csr_no ,sales_rep ,record_seq_no

    from BL_TRUCK_BOL_DEST_COMM_ORDER where dest_bol_no = @dest_bol_no and rev_no = @old_rev_no

    /*4.*/

    if exists (select 1 from BL_ADDITIONAL_ADDRESS where bol_no = @dest_bol_no and rev_no = @old_rev_no)

    and not exists (select 1 from BL_ADDITIONAL_ADDRESS where bol_no = @dest_bol_no and rev_no = @new_rev_no)

    INSERT INTO BL_ADDITIONAL_ADDRESS

    ([bol_no] ,[rev_no] ,[address_code] ,[company_name] ,[addr1] ,[addr2] ,[city] ,[state] ,[zip] ,[additional_address_no] ,[contact] ,[company_name2] ,[addr3] ,[additional_address_entity] ,[truck_bol_no] ,[reference_code] ,[reference_dscr] ,[edi_contact_code] ,[edi_contact_phone] ,[edi_contact_fax] ,[edi_contact_email] ,[edi_contact_ext] ,[edi_id_qualifier] ,[edi_id_code])

    select

    bol_no ,@new_rev_no ,address_code ,company_name ,addr1 ,addr2 ,city ,state ,zip ,additional_address_no ,contact ,company_name2 ,addr3 ,additional_address_entity ,@bol_no ,reference_code ,reference_dscr ,edi_contact_code ,edi_contact_phone ,edi_contact_fax ,edi_contact_email ,edi_contact_ext ,edi_id_qualifier ,edi_id_code

    from BL_ADDITIONAL_ADDRESS where bol_no = @dest_bol_no and rev_no = @old_rev_no

    /*5.*/

    if exists (select 1 from BL_REFERENCE_NO where bol_no = @dest_bol_no and rev_no = @old_rev_no)

    and not exists (select 1 from BL_REFERENCE_NO where bol_no = @dest_bol_no and rev_no = @new_rev_no)

    INSERT INTO BL_REFERENCE_NO

    ([bol_no] ,[rev_no] ,[reference_code] ,[reference_dscr] ,[road] ,[truck_bol_no] ,[reference_date] ,[comm_code] ,[first_route_segment_road] ,[first_route_segment_splc] ,[freight_code] ,[freight_pay_address_code])

    select

    bol_no ,@new_rev_no ,reference_code ,reference_dscr ,road ,@bol_no ,reference_date ,comm_code ,first_route_segment_road ,first_route_segment_splc ,freight_code ,freight_pay_address_code

    from BL_REFERENCE_NO where bol_no = @dest_bol_no and rev_no = @old_rev_no

    /*6.*/

    if exists (select 1 from BL_BOL_SHIP_INSTRUCT where bol_no = @dest_bol_no and rev_no = @old_rev_no)

    and not exists (select 1 from BL_BOL_SHIP_INSTRUCT where bol_no = @dest_bol_no and rev_no = @new_rev_no)

    INSERT INTO BL_BOL_SHIP_INSTRUCT

    ([bol_no],[rev_no],[ship_instruct_code],[instruction],[truck_bol_no],[display_order],[comm_code],[last_viewed_by],[last_viewed_datetime])

    select

    bol_no,@new_rev_no,ship_instruct_code,instruction,@bol_no,display_order,comm_code,@user_id,getdate()

    from BL_BOL_SHIP_INSTRUCT

    where bol_no = @dest_bol_no and rev_no = @old_rev_no

    /*7.*/

    if exists (select 1 from BL_BOL_SPEC_HANDLING where bol_no = @dest_bol_no and rev_no = @old_rev_no)

    and not exists (select 1 from BL_BOL_SPEC_HANDLING where bol_no = @dest_bol_no and rev_no = @new_rev_no)

    INSERT INTO BL_BOL_SPEC_HANDLING

    ([bol_no],[rev_no],[spec_hand_code],[code_dscr],[truck_bol_no])

    select

    bol_no,@new_rev_no,spec_hand_code,code_dscr,@bol_no

    from BL_BOL_SPEC_HANDLING where bol_no = @dest_bol_no and rev_no = @old_rev_no

    END

  • Could you possibly post the actual execution plan?



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • I would start by putting a WITH(ROWLOCK) hint on the UPDATE statement.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • I think after further research it's not so much to do with the sp but with the DB servers resources.

    Thank you

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

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