Re: SQLXMLBulkload performance
- From: "Michael Rys [MSFT]" <mrys@xxxxxxxxxxxxxxxxxxxx>
- Date: Sun, 20 May 2007 16:05:46 -0700
SQLXML Bulkload is probably still the most efficient way to bulkload
hierarchical data.
Do you know where you are losing the time? Is it on the database side when
inserting into the tables? Is it the parsing of the XML? Could it be that
your tables are big and that you are having a clustered index on it that
requires the inserted data to be sorted?
Thanks
Michael
"trillium" <trillium@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:864B1DCA-22D2-4DB1-8E77-49F31888225A@xxxxxxxxxxxxxxxx
I have been using SQLXMLBulkload in an automated process to import data
into
14 tables in SQL Server 2000 from XML files daily for a couple of years.
The
process works well as far as populating the tables, but as the size of the
files has grown (now about 170MB), it is taking a couple of hours for it
to
complete. So I wonder:
Is SQLXMLBulkload the best way to get a lot of hierarchical data into the
database, or should I be looking into alternatives? (For instance, I have
looked a bit at SqlBulkCopy, but it seems like it works best with
non-hierarchical data - but I may just not have found a comprehensive
reference source for it)
If it still is the best way, am I setting the parameters for the best
performance? My code is below. Or is there something else I should be
looking
at?
Try
Dim load As SQLXMLBULKLOADLib.SQLXMLBulkLoad4
load = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.4.0")
load.ConnectionString = strConnection 'variable holding db connection
string
load.ErrorLogFile = "BulkloadErrorFile.xml"
load.SchemaGen = True
load.SGDropTables = True
load.SGUseID = True
load.IgnoreDuplicateKeys = True
load.Execute(xsdfile, xmlfile) 'variables for schema & data files
Catch e As Exception
... error code
End Try
.
- Prev by Date: Re: modify() Top-level attribute nodes are not supported Error
- Next by Date: Re: replace value of empty element?
- Previous by thread: Re: modify() Top-level attribute nodes are not supported Error
- Next by thread: Re: replace value of empty element?
- Index(es):
Relevant Pages
|
