May 4, 2010 at 9:24 am
Hi Experts,
We are having an issue when we execute a stored procedure using Biztalk server.
All stored procedure is doing is that it selects data from Biztalk and insert into to another tables.
Biztalk converts an XML file into a flat file and send data to MS SQL Server using this procedure.
The problem is when Biztalk does its job its creating lock on the tables as it runs the whole 10000 records at a time.We created time delays in between
insertions but its causing issues as its taking more than 15 Hours to complete.
How can we avoid this situation?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Insert_EEORLM]
(
@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_LM_InvoiceNumber VARCHAR(10),
@EEOR_LM_RecordType VARCHAR(1),
@EEOR_LM_DetailLineNumber VARCHAR(2),
@EEOR_LM_MessageCode VARCHAR(2),
@EEOR_LM_ReasonDescription VARCHAR(80),
--@EEOR_LM_FirstInsertDate DATE,
--@EEOR_LM_LastUpdateDate DATE,
--@EEOR_LM_FirstInsertUser VARCHAR(30),
--@EEOR_LM_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 ) is Null)
Or
(
@EEOR_FH_HeaderKey not in (SELECT [Header_Key] FROM [EEOR_Transmission_Header] with )
And
@EEOR_FH_VendorNumber not in (SELECT [Vendor_Number] FROM [EEOR_Transmission_Header] with )
And
@EEOR_FH_VendorTin not in (SELECT [Vendor_Tin] FROM [EEOR_Transmission_Header] with )
And
@EEOR_FH_FileSequenceNumber not in (SELECT [File_Sequence_Number] FROM [EEOR_Transmission_Header] with )
)
)
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 ) is Null)
Or
(
@EEOR_BH_InvoiceNumber not in (SELECT [Invoice_Number] FROM [EEOR_Bill_Header] with )
And
@EEOR_BH_ClaimNumber not in (SELECT [Claim_Number] FROM [EEOR_Bill_Header] with )
And
@EEOR_BH_BillIDFull not in (SELECT [Bill_ID_Full] FROM [EEOR_Bill_Header] with )
)
)
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
INSERT INTO [EEOR_Bill_Message]
([Invoice_Number]
,[Record_Type]
,[Detail_Line_Number]
,[Message_Code]
,[Reason_Description]
--,[First_Insert_Date]
--,[Last_Update_Date]
--,[First_Insert_User]
--,[Last_Update_User]
)
VALUES
(@EEOR_LM_InvoiceNumber
,@EEOR_LM_RecordType
,@EEOR_LM_DetailLineNumber
,@EEOR_LM_MessageCode
,@EEOR_LM_ReasonDescription
--,@EEOR_LM_FirstInsertDate
--,@EEOR_LM_LastUpdateDate
--,@EEOR_LM_FirstInsertUser
--,@EEOR_LM_LastUpdateUser
)
/* [EEOR_Trailer] */
IF (
( (SELECT TOP 1 pk_File_ID FROM [EEOR_Trailer] with ) is Null)
Or
(
@EEOR_TR_TrailerKey not in (SELECT [Trailer_Key] FROM [EEOR_Trailer] with )
And
@EEOR_TR_BillHeaderRecordCount not in (SELECT [Bill_Header_Record_Count] FROM [EEOR_Trailer] with )
And
@EEOR_TR_BillDetailRecordCount not in (SELECT [Bill_Detail_Record_Count] FROM [EEOR_Trailer] with )
)
)
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 )
WHERE pk_File_ID IS NULL
UPDATE [EEOR_Bill_Header]
SET pk_File_ID = (SELECT MAX(sk_File_ID) FROM [EEOR_Transmission_Header] with )
WHERE pk_File_ID IS NULL
UPDATE [EEOR_Bill_Message]
SET pk_File_ID = (SELECT MAX(sk_File_ID) FROM [EEOR_Transmission_Header] with )
WHERE pk_File_ID IS NULL
UPDATE [EEOR_Bill_Message]
SET pk_Header_ID = (SELECT MAX(sk_Header_ID) FROM [EEOR_Bill_Header] with )
WHERE pk_Header_ID IS NULL
COMMIT TRANSACTION
END
May 4, 2010 at 2:16 pm
Can you Skip over the Flat File process and directly load from XML to SQL Tables using OPENXML.
Is BizTalk passing one record at a Time or All at once.
Also include NOLOCK HINT in the Query and change the ISOLATION LEVEL to DEFAULT READ COMMITTED.
Maninder
www.dbanation.com
May 4, 2010 at 9:32 pm
Thanks for the reply..
As per business requirement we nened to use Biztalk..
We tried using READ COMMITED AND NOLOCK
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply