June 14, 2012 at 8:22 am
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_no char(15),
@bol_no char(15),
--@bol_comment varchar(100),
@user_id char(30),
@new_rev_no int,
@old_rev_no int,
@return_code numeric(1) output,
@return_message varchar(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
June 14, 2012 at 11:27 pm
I would start by putting a WITH(ROWLOCK) hint on the UPDATE statement.
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
June 15, 2012 at 7:44 am
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