Bulk Insert using Xquery is very slow.

Posted on 2008-10-24
Last Modified: 2013-11-10
I am using Xquery to fetch information from many xml files and bulk insert to the database. My query follows

insert into RKDFndStdFinVal_TempMain
ref.value ('(xRepNo/.)[1]', 'varchar(20)') as RepNo,
ref.value ('(xPerEndDate/.)[1]', 'varchar(20)') as PerEndDate,
ref.value ('(xPerType/.)[1]','varchar(15)') as PerType,
ref.value ('(xStmtDate/.)[1]', 'varchar(20)') as StmtDate,
ref.value ('(xStmtType/.)[1]', 'varchar(5)') as StmtType,
ref.value ('(xCOACode/.)[1]', 'varchar(10)') as COACode,
ref.value ('(xValue/.)[1]', 'float') as [Value],
ref.value ('(xLicFlag/.)[1]', 'varchar(5)') as LicFlag
CROSS APPLY XMLDoc.nodes ('//GNF_ConvertedXML/RKDFndStdFinVal/row') R(ref) ;

I have around 60,000 xml files and the number of data row is huge. For 100 XML files itslef it will exceed 1 million +.  For 100 xml files its taking 1 hour for insertion. But i want all the insertion for 60000 files to be done in 15 hours.

Is there any way i can optimize the query or change any property in mssql server configuration to achive this result?
Question by:anand_dan27
  • 2

Author Comment

ID: 22801792
i have changed SQL Server database recovry model to Bulk_Logged. Still the same result.

LVL 17

Expert Comment

ID: 22808193
XQuery is probably the best solution, have you tried opening multiple threads using SSIS Packages? You could use XML Source in SSIS but it might be slower than XQuery, not sure.
If you can divide the files into logical groups, process multiple groups simultaneously using synchronous threads. One SSIS Package can execute all of the processes. If you use XML Source, you could use the Union All component to contol the inserts to the same table.
LVL 17

Expert Comment

ID: 22808207
I meant to add this link, as it might also be useful if you have not already seen it. Good luck.
LVL 51

Accepted Solution

Mark Wills earned 500 total points
ID: 22808780
Why not expand the xml into temp table and then run your queeries ? ie do the bulk insert, or openrowsets to insert into either a variable, table, whatever, and then use openxml to query from. Found that for large files, openxml can be a bit quicker than Xquery.

maybe you could post a small sample ?

e.g. (for you need to swap My_Views to RKDFndStdFinVal_TempMain - or your temp staging table as appropriate) ...

--views.xml contains : <NewDataSet><Views><SystemID>1</SystemID><ViewName>sam_view</ViewName></Views></NewDataSet>

CREATE TABLE My_VIEWS (SystemID varchar,ViewName  varchar(20))

DECLARE @xmlstring xml

DECLARE @idoc int;

SET @xmlstring = (select CONVERT(varchar(max), BulkColumn, 2) FROM  OPENROWSET(Bulk 'c:\ee\views.xml', SINGLE_BLOB) [rowsetresults])

EXEC sp_xml_preparedocument @idoc OUTPUT,@xmlstring;

--insert would go here

SELECT * FROM OPENXML (@idoc,'/NewDataSet/Views',2) with My_Views ;

EXEC sp_xml_removedocument @idoc 

Open in new window


Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now