February 3, 2009 at 6:25 am
Hi,
our db is missing out some items while inserting using OPENXML.
we are inserting items one by one. i.e first item will be fed into SP in XML format and it will get inserted,status will be returned to .NET web app, then the second item will follow etc.
In this scenario , some of the items are not getting inserted because of timeout issue since the volume will be very high.
We have adapted a new approach like the following
1) inserting into @TABLE variables from OPENXML
2) then inserting into Main tables from @Table variables.
But in this approach also the same issue happens. The volume of records per day will be 50,000.
Let me know any alternative approaches available because everyday we are getting this issue in production.
Also let me know whether BCP and SQLXMLBulkLoad will fit in the above scenario.
THANX in Advance.
Venkatroyal
February 3, 2009 at 11:22 am
Venkatroyal (2/3/2009)
Hi,our db is missing out some items while inserting using OPENXML.
we are inserting items one by one. i.e first item will be fed into SP in XML format and it will get inserted,status will be returned to .NET web app, then the second item will follow etc.
In this scenario , some of the items are not getting inserted because of timeout issue since the volume will be very high.
We have adapted a new approach like the following
1) inserting into @TABLE variables from OPENXML
2) then inserting into Main tables from @Table variables.
But in this approach also the same issue happens. The volume of records per day will be 50,000.
Let me know any alternative approaches available because everyday we are getting this issue in production.
Also let me know whether BCP and SQLXMLBulkLoad will fit in the above scenario.
THANX in Advance.
Venkatroyal
Can you post a sample code?
What is the problem with using normal parametrized stored procedures ?
50,000 records per day is *not* a lot at all!
* Noel
February 3, 2009 at 10:47 pm
Noel,
Thanks for your reply..
Find below the part of code from Stored Procedure
-- Insertion into @table object from OPENXML
INSERT INTO @TempInwardItems(
ItemSeqNo,RBIItemSeqNo,PayorBankRoutNo,Amount,AccountNum,CheckTranCode,ProcessControl,PresentingRTNumber,
PresentmentDate,CycleNo,DocType,MICRRepairFlags,SpecialHandling,ClearingCode,TruncatingRTNumber,
UserField,IQAIgnoreInd,CurrencyInd,ItemStatus,BOFDRTNumber,BOFDBusDate,DepositorAccount,IFSCCode,
[FileName],SessionNumber,SessionDate,NumOfImageViews,ProcessingDate,
SettlementDate,SessionExtensionHrs,
ReturnReason,IsUnApproved,HoststatusCode,IsUpdated,FinacleAccountNumber,IsMergedBank,
MergedBankRTNumber,FileId,RBICycleNo)--ReturnReason,FinacleAccountNumber,IsMemberBank,MemberBankCode)
SELECT ItemSeqNo,RBIItemSeqNo,PayorBankRoutNo,Amount,AccountNum,CheckTranCode,ProcessControl,PresentingRTNumber,
PresentmentDate,CycleNo,DocType,MICRRepairFlags,SpecialHandling,ClearingCode,TruncatingRTNumber,
UserField,IQAIgnoreInd,CurrencyInd,ItemStatus,BOFDRTNumber,BOFDBusDate,DepositorAccount,IFSCCode,
[FileName],SessionNumber,SessionDate,NumOfImageViews,SessionDate,--CONVERT(VARCHAR,GETDATE(),101),
SettlementDate,SessionExtensionHrs,
ReturnReason,IsUnApproved,HoststatusCode,IsUpdated,FinacleAccountNumber,IsMergedBank,
MergedBankRTNumber, @intFileID, RBICycleNo
FROM OPENXML(@hDoc,'/NewDataSet/PP_InwardItems',2) WITH PP_InwardItems
-- Insertion into Main Table from @table object
INSERT INTO PP_InwardItems(
ItemSeqNo,RBIItemSeqNo,PayorBankRoutNo,Amount,AccountNum,CheckTranCode,ProcessControl,PresentingRTNumber,
PresentmentDate,CycleNo,DocType,MICRRepairFlags,SpecialHandling,ClearingCode,TruncatingRTNumber,
UserField,IQAIgnoreInd,CurrencyInd,ItemStatus,BOFDRTNumber,BOFDBusDate,DepositorAccount,IFSCCode,
[FileName],SessionNumber,SessionDate,NumOfImageViews,ProcessingDate,
SettlementDate,SessionExtensionHrs,
ReturnReason,IsUnApproved,HoststatusCode,IsUpdated,FinacleAccountNumber,IsMergedBank,MergedBankRTNumber,FileId,RBICycleNo)--ReturnReason,FinacleAccountNumber,IsMemberBank,MemberBankCode)
SELECT ItemSeqNo,RBIItemSeqNo,PayorBankRoutNo,Amount,AccountNum,CheckTranCode,ProcessControl,PresentingRTNumber,
PresentmentDate,CycleNo,DocType,MICRRepairFlags,SpecialHandling,ClearingCode,TruncatingRTNumber,
UserField,IQAIgnoreInd,CurrencyInd,ItemStatus,BOFDRTNumber,BOFDBusDate,DepositorAccount,IFSCCode,
[FileName],SessionNumber,SessionDate,NumOfImageViews,SessionDate,-- CONVERT(VARCHAR,GETDATE(),101),
SettlementDate,SessionExtensionHrs,
ReturnReason,IsUnApproved,HoststatusCode,IsUpdated,FinacleAccountNumber,IsMergedBank,MergedBankRTNumber, FileId, RBICycleNo
FROM @TempInwardItems
Pls give me ur suggestions.
Venkatroyal
February 4, 2009 at 4:16 am
How many seconds does it take when you execute it from SSMS with a sample XML document?
Also, make sure that you release the document handle before you exit the stored procedure. That make sure that you wont land up with resource leaks.
If you are on SQL Server 2005, try using XQuery. In certain cases, XQuery is found to be performing better. But you should run a test to see if it helps in this context.
.
February 4, 2009 at 2:09 pm
I would try with normal temporary tables first instead of table variables.
* Noel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply