April 26, 2010 at 11:40 pm
Hi Experts,
When i tried to execute the below query in biztalk I am getting locks ..
Please help
/****** Object: StoredProcedure [dbo].[Insert_EEORBD] Script Date: 04/26/2010 13:51:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Insert_EEORBD]
(
@EEOR_FH_HeaderKey varchar(10),
@EEOR_FH_RecordType varchar(1),
@EEOR_FH_CreationDate date,
@EEOR_FH_CreationTime time,
@EEOR_FH_VendorNumber varchar(2),
@EEOR_FH_VendorTin varchar(14),
@EEOR_FH_FileSequenceNumber varchar(9),
--@EEOR_FH_FSNConverted integer,
--@EEOR_FH_FirstInsertDate date,
--@EEOR_FH_LastUpdateDate date,
--@EEOR_FH_FirstInsertUser varchar(30),
--@EEOR_FH_LastUpdateUser varchar(30),
@EEOR_BH_InvoiceNumber varchar(10),
@EEOR_BH_RecordType varchar(1),
@EEOR_BH_ClaimNumber varchar(9),
@EEOR_BH_ProviderNameType varchar(1),
@EEOR_BH_ProviderBillingName varchar(31),
@EEOR_BH_ProviderTitle varchar(5),
@EEOR_BH_ProviderBillingAddress1 varchar(25),
@EEOR_BH_ProviderBillingAddress2 varchar(25),
@EEOR_BH_ProviderBillingCity varchar(20),
@EEOR_BH_ProviderBillingState varchar(2),
@EEOR_BH_ProviderBillingZip varchar(9),
@EEOR_BH_ProviderSpecialty varchar(3),
@EEOR_BH_ProviderBillingPhone varchar(10),
@EEOR_BH_ProviderPhysicalName varchar(31),
@EEOR_BH_ProviderPhysicalAddress1 varchar(25),
@EEOR_BH_ProviderPhysicalAddress2 varchar(25),
@EEOR_BH_ProviderPhysicalCity varchar(20),
@EEOR_BH_ProviderPhysicalState varchar(2),
@EEOR_BH_ProviderPhysicalZip varchar(9),
@EEOR_BH_ProviderPhysicalphone varchar(10),
@EEOR_BH_ProviderTin varchar(9),
@EEOR_BH_EmployerName varchar(30),
@EEOR_BH_BillFinalizedDate date,
@EEOR_BH_AlternateClaimNumber varchar(22),
@EEOR_BH_ClientReceivedBillDate date,
@EEOR_BH_PatientAccountNumber varchar(17),
@EEOR_BH_CheckID varchar(4),
@EEOR_BH_DateBillReceived date,
@EEOR_BH_BillStatusIndicator varchar(1),
@EEOR_BH_ExternalRefNumber varchar(10),
@EEOR_BH_AttendingPhysician varchar(30),
@EEOR_BH_AttendingPhysicianTin varchar(3),
@EEOR_BH_AdmissionHour varchar(2),
@EEOR_BH_DischargeHour varchar(2),
@EEOR_BH_PPOContractID varchar(13),
@EEOR_BH_TotalBilledCharges numeric,
@EEOR_BH_TotalRecommendedAllowance numeric,
@EEOR_BH_BillReviewFees numeric,
@EEOR_BH_PPOFees numeric,
@EEOR_BH_TotalEnhancedSystemFees numeric,
@EEOR_BH_TotalBillReviewSavings numeric,
@EEOR_BH_TotalPPOSavings numeric,
@EEOR_BH_TotalEnhancedSystemSavings numeric,
@EEOR_BH_TotalDuplicateSavings numeric,
@EEOR_BH_NYSurcharge numeric,
@EEOR_BH_URReductions numeric,
@EEOR_BH_URFees numeric,
@EEOR_BH_TCMFees numeric,
@EEOR_BH_ExclusionSavings numeric,
@EEOR_BH_ProviderIDNumber varchar(10),
@EEOR_BH_ServiceFromDate date,
@EEOR_BH_ServiceToDate date,
@EEOR_BH_NumberOfDetailLineRecords integer,
@EEOR_BH_BillIDFull varchar(20),
--@EEOR_BH_BillIDUserNumber numeric,
--@EEOR_BH_BillIDBillFormType varchar(1),
--@EEOR_BH_BillIDSequenceNumber numeric,
--@EEOR_BH_BillIDAmendSuffix numeric,
--@EEOR_BH_FirstInsertDate date,
--@EEOR_BH_LastUpdateDate date,
--@EEOR_BH_FirstInsertUser varchar(30),
--@EEOR_BH_LastUpdateUser varchar(30),
@EEOR_BD_InvoiceNumber varchar(10),
@EEOR_BD_RecordType varchar(1),
@EEOR_BD_FromServiceDate date,
@EEOR_BD_ToServiceDate date,
@EEOR_BD_PaidProcedureCode varchar(5),
@EEOR_BD_NDCDrugCode varchar(11),
@EEOR_BD_MessageCode1 varchar(2),
@EEOR_BD_MessageCode2 varchar(2),
@EEOR_BD_MessageCode3 varchar(2),
@EEOR_BD_MessageCode4 varchar(2),
@EEOR_BD_DiagnosisCode1 varchar(6),
@EEOR_BD_DiagnosisCode2 varchar(6),
@EEOR_BD_DiagnosisCode3 varchar(6),
@EEOR_BD_DiagnosisCode4 varchar(6),
@EEOR_BD_DiagnosisCode5 varchar(6),
@EEOR_BD_Units varchar(7),
@EEOR_BD_BilledProcedureCode varchar(5),
@EEOR_BD_BilledModifier varchar(2),
@EEOR_BD_Modifier2 varchar(2),
@EEOR_BD_Modifier3 varchar(2),
@EEOR_BD_TypeOfService varchar(1),
@EEOR_BD_FeeScheduleSavings varchar(9),
@EEOR_BD_EnhancedSystemSavings varchar(9) ,
@EEOR_BD_PPOSavings varchar(9),
@EEOR_BD_NetworkID varchar(3),
@EEOR_BD_LineNumber varchar(3),
@EEOR_BD_BilledCharges varchar(9),
--@EEOR_BD_FeeSchedule varchar(9),
--@EEOR_BD_RecommendedAllowance varchar(9) ,
@EEOR_BD_MessageCode5 varchar(2),
@EEOR_BD_MessageCode6 varchar(2),
@EEOR_BD_MessageCode7 varchar(2),
@EEOR_BD_RXNumber varchar(7),
@EEOR_BD_PlaceOfService varchar(2),
--@EEOR_BD_FirstInsertDate date,
--@EEOR_BD_LastUpdateDate date,
--@EEOR_BD_FirstInsertUser varchar(30),
--@EEOR_BD_LastUpdateUser varchar(30),
@EEOR_TR_TrailerKey VARCHAR(10),
@EEOR_TR_RecordType VARCHAR(1),
@EEOR_TR_BillHeaderRecordCount VARCHAR(6),
@EEOR_TR_BillDetailRecordCount VARCHAR(6),
@EEOR_TR_LineMessageRecordCount VARCHAR(6),
@EEOR_TR_TotalBilledCharges VARCHAR(10),
@EEOR_TR_TotalRecommendedAllowance VARCHAR(10),
@EEOR_TR_TotalBillReviewFees VARCHAR(10),
@EEOR_TR_TotalPPOFees VARCHAR(10),
@EEOR_TR_TotalEnhancedSystemFees VARCHAR(10),
@EEOR_TR_TotalBillReviewSavings VARCHAR(10),
@EEOR_TR_TotalPPOSavings VARCHAR(10),
@EEOR_TR_TotalEnhancedSystemSavings VARCHAR(10),
@EEOR_TR_TotalDuplicateSystemSavings VARCHAR(10),
@EEOR_TR_TotalNewYorkSurcharges VARCHAR(10),
@EEOR_TR_TotalURReductions VARCHAR(10),
@EEOR_TR_TotalURFees VARCHAR(10),
@EEOR_TR_TotalTCMFees VARCHAR(10),
@EEOR_TR_TotalExclusionSavings VARCHAR(10)
--,@EEOR_TR_FirstInsertDate DATE,
--@EEOR_TR_LastUpdateDate DATE,
--@EEOR_TR_FirstInsertUser VARCHAR(30),
--@EEOR_TR_LastUpdateUser VARCHAR(30)
)
AS
BEGIN
BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
/* EEOR_Transmission_Header */
IF (
( (SELECT TOP 1 sk_File_ID FROM [EEOR_Transmission_Header] with (nolock)) is Null)
Or
(
@EEOR_FH_HeaderKey not in (SELECT [Header_Key] FROM [EEOR_Transmission_Header] with (nolock))
And
@EEOR_FH_VendorNumber not in (SELECT [Vendor_Number] FROM [EEOR_Transmission_Header] with (nolock))
And
@EEOR_FH_VendorTin not in (SELECT [Vendor_Tin] FROM [EEOR_Transmission_Header] with (nolock))
And
@EEOR_FH_FileSequenceNumber not in (SELECT [File_Sequence_Number] FROM [EEOR_Transmission_Header] with (nolock))
)
)
BEGIN
INSERT INTO [EEOR_Transmission_Header]
([Header_Key]
,[Record_Type]
,[Creation_Date]
,[Creation_Time]
,[Vendor_Number]
,[Vendor_Tin]
,[File_Sequence_Number]
--,[FSN_Converted]
--,[First_Insert_Date]
--,[Last_Update_Date]
--,[First_Insert_User]
--,[Last_Update_User]
)
VALUES
(@EEOR_FH_HeaderKey
,@EEOR_FH_RecordType
,@EEOR_FH_CreationDate
,@EEOR_FH_CreationTime
,@EEOR_FH_VendorNumber
,@EEOR_FH_VendorTin
,@EEOR_FH_FileSequenceNumber
--,@EEOR_FH_FSNConverted
--,@EEOR_FH_FirstInsertDate
--,@EEOR_FH_LastUpdateDate
--,@EEOR_FH_FirstInsertUser
--,@EEOR_FH_LastUpdateUser
)
END
/* [EEOR_Bill_Header] */
IF (
( (SELECT TOP 1 sk_Header_ID FROM [EEOR_Bill_Header] with (nolock)) is Null)
Or
(
@EEOR_BH_InvoiceNumber not in (SELECT [Invoice_Number] FROM [EEOR_Bill_Header] with (nolock))
And
@EEOR_BH_ClaimNumber not in (SELECT [Claim_Number] FROM [EEOR_Bill_Header] with (nolock))
And
@EEOR_BH_BillIDFull not in (SELECT [Bill_ID_Full] FROM [EEOR_Bill_Header] with (nolock))
)
)
BEGIN
INSERT INTO [EEOR_Bill_Header]
([Invoice_Number]
,[Record_Type]
,[Claim_Number]
,[Provider_Name_type]
,[Provider_Billing_Name]
,[Provider_Title]
,[Provider_Billing_Address_1]
,[Provider_Billing_Address_2]
,[Provider_Billing_City]
,[Provider_Billing_State]
,[Provider_Billing_Zip]
,[Provider_Specialty]
,[Provider_Billing_Phone]
,[Provider_Physical_Name]
,[Provider_Physical_Address_1]
,[Provider_Physical_Address_2]
,[Provider_Physical_City]
,[Provider_Physical_State]
,[Provider_Physical_Zip]
,[Provider_Physical_phone]
,[Provider_Tin]
,[Employer_Name]
,[Bill_Finalized_Date]
,[Alternate_Claim_Number]
,[Client_Received_Bill_Date]
,[Patient_Account_Number]
,[Check_ID]
,[Date_Bill_Received]
,[Bill_Status_Indicator]
,[External_Ref_Number]
,[Attending_Physician]
,[Attending_Physician_Tin]
,[Admission_Hour]
,[Discharge_Hour]
,[PPO_Contract_ID]
,[Total_Billed_Charges]
,[Total_Recommended_Allowance]
,[Bill_Review_Fees]
,[PPO_Fees]
,[Total_Enhanced_System_Fees]
,[Total_Bill_Review_Savings]
,[Total_PPO_Savings]
,[Total_Enhanced_System_Savings]
,[Total_Duplicate_Savings]
,[NY_Surcharge]
,[UR_Reductions]
,[UR_Fees]
,[TCM_Fees]
,[Exclusion_Savings]
,[Provider_ID_Number]
,[Service_From_Date]
,[Service_To_Date]
,[Number_Of_Detail_Line_Records]
,[Bill_ID_Full]
--,[Bill_ID_User_Number]
--,[Bill_ID_Bill_Form_Type]
--,[Bill_ID_Sequence_Number]
--,[Bill_ID_Amend_Suffix]
--,[First_Insert_Date]
--,[Last_Update_Date]
--,[First_Insert_User]
--,[Last_Update_User]
)
VALUES
(@EEOR_BH_InvoiceNumber
,@EEOR_BH_RecordType
,@EEOR_BH_ClaimNumber
,@EEOR_BH_ProviderNameType
,@EEOR_BH_ProviderBillingName
,@EEOR_BH_ProviderTitle
,@EEOR_BH_ProviderBillingAddress1
,@EEOR_BH_ProviderBillingAddress2
,@EEOR_BH_ProviderBillingCity
,@EEOR_BH_ProviderBillingState
,@EEOR_BH_ProviderBillingZip
,@EEOR_BH_ProviderSpecialty
,@EEOR_BH_ProviderBillingPhone
,@EEOR_BH_ProviderPhysicalName
,@EEOR_BH_ProviderPhysicalAddress1
,@EEOR_BH_ProviderPhysicalAddress2
,@EEOR_BH_ProviderPhysicalCity
,@EEOR_BH_ProviderPhysicalState
,@EEOR_BH_ProviderPhysicalZip
,@EEOR_BH_ProviderPhysicalphone
,@EEOR_BH_ProviderTin
,@EEOR_BH_EmployerName
,@EEOR_BH_BillFinalizedDate
,@EEOR_BH_AlternateClaimNumber
,@EEOR_BH_ClientReceivedBillDate
,@EEOR_BH_PatientAccountNumber
,@EEOR_BH_CheckID
,@EEOR_BH_DateBillReceived
,@EEOR_BH_BillStatusIndicator
,@EEOR_BH_ExternalRefNumber
,@EEOR_BH_AttendingPhysician
,@EEOR_BH_AttendingPhysicianTin
,@EEOR_BH_AdmissionHour
,@EEOR_BH_DischargeHour
,@EEOR_BH_PPOContractID
,@EEOR_BH_TotalBilledCharges
,@EEOR_BH_TotalRecommendedAllowance
,@EEOR_BH_BillReviewFees
,@EEOR_BH_PPOFees
,@EEOR_BH_TotalEnhancedSystemFees
,@EEOR_BH_TotalBillReviewSavings
,@EEOR_BH_TotalPPOSavings
,@EEOR_BH_TotalEnhancedSystemSavings
,@EEOR_BH_TotalDuplicateSavings
,@EEOR_BH_NYSurcharge
,@EEOR_BH_URReductions
,@EEOR_BH_URFees
,@EEOR_BH_TCMFees
,@EEOR_BH_ExclusionSavings
,@EEOR_BH_ProviderIDNumber
,@EEOR_BH_ServiceFromDate
,@EEOR_BH_ServiceToDate
,@EEOR_BH_NumberOfDetailLineRecords
,@EEOR_BH_BillIDFull
--,@EEOR_BH_BillIDUserNumber
--,@EEOR_BH_BillIDBillFormType
--,@EEOR_BH_BillIDSequenceNumber
--,@EEOR_BH_BillIDAmendSuffix
--,@EEOR_BH_FirstInsertDate
--,@EEOR_BH_LastUpdateDate
--,@EEOR_BH_FirstInsertUser
--,@EEOR_BH_LastUpdateUser
)
END
/* [EEOR_Bill_Detail] */
INSERT INTO [EEOR_Bill_Detail]
([Invoice_Number]
,[Record_Type]
,[From_Service_Date]
,[To_Service_Date]
,[Paid_Procedure_Code]
,[NDC_Drug_Code]
,[Message_Code_1]
,[Message_Code_2]
,[Message_Code_3]
,[Message_Code_4]
,[Diagnosis_Code_1]
,[Diagnosis_Code_2]
,[Diagnosis_Code_3]
,[Diagnosis_Code_4]
,[Diagnosis_Code_5]
,[Units]
,[Billed_Procedure_Code]
,[Billed_Modifier]
,[Modifier_2]
,[Modifier_3]
,[Type_Of_Service]
,[Fee_Schedule_Savings]
,[Enhanced_System_Savings]
,[PPO_Savings]
,[Network_ID]
,[Line_Number]
,[Billed_Charges]
--,[Fee_Schedule]
--,[Recommended_Allowance]
,[Message_Code_5]
,[Message_Code_6]
,[Message_Code_7]
,[RX_Number]
,[Place_Of_Service]
--,[First_Insert_Date]
--,[Last_Update_Date]
--,[First_Insert_User]
--,[Last_Update_User]
)
VALUES
(@EEOR_BD_InvoiceNumber
,@EEOR_BD_RecordType
,@EEOR_BD_FromServiceDate
,@EEOR_BD_ToServiceDate
,@EEOR_BD_PaidProcedureCode
,@EEOR_BD_NDCDrugCode
,@EEOR_BD_MessageCode1
,@EEOR_BD_MessageCode2
,@EEOR_BD_MessageCode3
,@EEOR_BD_MessageCode4
,@EEOR_BD_DiagnosisCode1
,@EEOR_BD_DiagnosisCode2
,@EEOR_BD_DiagnosisCode3
,@EEOR_BD_DiagnosisCode4
,@EEOR_BD_DiagnosisCode5
,@EEOR_BD_Units
,@EEOR_BD_BilledProcedureCode
,@EEOR_BD_BilledModifier
,@EEOR_BD_Modifier2
,@EEOR_BD_Modifier3
,@EEOR_BD_TypeOfService
,@EEOR_BD_FeeScheduleSavings
,@EEOR_BD_EnhancedSystemSavings
,@EEOR_BD_PPOSavings
,@EEOR_BD_NetworkID
,@EEOR_BD_LineNumber
,@EEOR_BD_BilledCharges
--,@EEOR_BD_FeeSchedule
--,@EEOR_BD_RecommendedAllowance
,@EEOR_BD_MessageCode5
,@EEOR_BD_MessageCode6
,@EEOR_BD_MessageCode7
,@EEOR_BD_RXNumber
,@EEOR_BD_PlaceOfService
--,@EEOR_BD_FirstInsertDate
--,@EEOR_BD_LastUpdateDate
--,@EEOR_BD_FirstInsertUser
--,@EEOR_BD_LastUpdateUser
)
/* [EEOR_Trailer] */
IF (
( (SELECT TOP 1 pk_File_ID FROM [EEOR_Trailer] with (nolock)) is Null)
Or
(
@EEOR_TR_TrailerKey not in (SELECT [Trailer_Key] FROM [EEOR_Trailer] with (nolock))
And
@EEOR_TR_BillHeaderRecordCount not in (SELECT [Bill_Header_Record_Count] FROM [EEOR_Trailer] with (nolock))
And
@EEOR_TR_BillDetailRecordCount not in (SELECT [Bill_Detail_Record_Count] FROM [EEOR_Trailer] with (nolock))
)
)
BEGIN
INSERT INTO [EEOR_Trailer]
([Trailer_Key]
,[Record_Type]
,[Bill_Header_Record_Count]
,[Bill_Detail_Record_Count]
,[Line_Message_Record_Count]
,[Total_Billed_Charges]
,[Total_Recommended_Allowance]
,[Total_Bill_Review_Fees]
,[Total_PPO_Fees]
,[Total_Enhanced_System_Fees]
,[Total_Bill_Review_Savings]
,[Total_PPO_Savings]
,[Total_Enhanced_System_Savings]
,[Total_Duplicate_System_Savings]
,[Total_New_York_Surcharges]
,[Total_UR_Reductions]
,[Total_UR_Fees]
,[Total_TCM_Fees]
,[Total_Exclusion_Savings]
--,[First_Insert_Date]
--,[Last_Update_Date]
--,[First_Insert_User]
--,[Last_Update_User]
)
VALUES
(@EEOR_TR_TrailerKey
,@EEOR_TR_RecordType
,@EEOR_TR_BillHeaderRecordCount
,@EEOR_TR_BillDetailRecordCount
,@EEOR_TR_LineMessageRecordCount
,@EEOR_TR_TotalBilledCharges
,@EEOR_TR_TotalRecommendedAllowance
,@EEOR_TR_TotalBillReviewFees
,@EEOR_TR_TotalPPOFees
,@EEOR_TR_TotalEnhancedSystemFees
,@EEOR_TR_TotalBillReviewSavings
,@EEOR_TR_TotalPPOSavings
,@EEOR_TR_TotalEnhancedSystemSavings
,@EEOR_TR_TotalDuplicateSystemSavings
,@EEOR_TR_TotalNewYorkSurcharges
,@EEOR_TR_TotalURReductions
,@EEOR_TR_TotalURFees
,@EEOR_TR_TotalTCMFees
,@EEOR_TR_TotalExclusionSavings
--,@EEOR_TR_FirstInsertDate
--,@EEOR_TR_LastUpdateDate
--,@EEOR_TR_FirstInsertUser
--,@EEOR_TR_LastUpdateUser
)
END
UPDATE [EEOR_Trailer]
SET pk_File_ID = (SELECT MAX(sk_File_ID) FROM [EEOR_Transmission_Header] with (nolock))
WHERE pk_File_ID IS NULL
UPDATE [EEOR_Bill_Header]
SET pk_File_ID = (SELECT MAX(sk_File_ID) FROM [EEOR_Transmission_Header] with (nolock))
WHERE pk_File_ID IS NULL
UPDATE [EEOR_Bill_Detail]
SET pk_File_ID = (SELECT MAX(sk_File_ID) FROM [EEOR_Transmission_Header] with (nolock))
WHERE pk_File_ID IS NULL
UPDATE [EEOR_Bill_Detail]
SET pk_Header_ID = (SELECT MAX(sk_Header_ID) FROM [EEOR_Bill_Header] with (nolock))
WHERE pk_Header_ID IS NULL
COMMIT TRANSACTION
END
Thanks In Advance
April 27, 2010 at 5:28 am
First off, setting your transaction to read uncommitted and setting nolocks on the reads is redudant & wasteful. Second, just becaue you put nolock everywhere doesn't mean you can eliminate locks. You're performing inserts and updates. SQL Server must lock rows and/or pages in order to update them.
From the looks of things, your transaction is too large. You're hitting a number of tables, many of them multiple times, within this transaction. I'm not going to try to parse it all apart to identify where you can reduce the number of hits, but you simply need to figure out ways to hit the table once. Doing an insert and then an update is problematic. You should be able to simply do the insert and be done. You can do INSERT... SELECT and mix in the sub queries with the parameters in order to do a single pass at the tables. That alone will reduce, but not eliminate your locking.
Locks are not to be avoided or eliminated, merely reduced in size and duration, especially when you're modifying data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 27, 2010 at 8:58 am
You are always going to have locks of one type or another. I guess the real problem you're wanting to describe is that you're encountering blocks on your DML statements. Use sp_who2 to determine what process is blocking.
Also, look at the Execution Plan to confirm all those various "select max()" and "select top 1" queries are covered by and index, so the duration of your transaction is as short as possible. Even if you're not declaring it, BizTalk is probably wrapping a transaction around the stored procedure call.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 27, 2010 at 10:56 pm
Thanks alot grant & Eric for that informative and quick reply..
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply