CODEWITHSUNDEEP
sqlxmlperformancet-sqlsql-server-2016
Ivan Pudic
Ivan Pudic

Reputation: 71

Very slow xml import in SQL Server 2016

I'm having trouble optimizing the following update query

SQL query

DECLARE @XmlFile4 XML

SELECT @XmlFile4 = BulkColumn  
FROM OPENROWSET(BULK 'C:\Temp\GeoDynamics\Downloads\GeoBewegingen.xml', SINGLE_BLOB) x;

WITH XMLNAMESPACES (DEFAULT 'http://www.geodynamics.be/webservices')
UPDATE GeoBewegingen
SET voernaam = resource.value('(../../Vehicle/Name)[1]', 'varchar(255)'),
    voercode = resource.value('(../../Vehicle/Code)[1]', 'varchar(255)'),
    pervan = resource.value('(../../FromDateLocal)[1]', 'varchar(255)'),
    pertot = resource.value('(../../ToDateLocal)[1]', 'varchar(255)'),
    tripvan = resource.value('(FromDateLocal)[1]', 'varchar(255)'),
    triptot = resource.value('(ToDateLocal)[1]', 'varchar(255)'),
    tripvanlocstr = resource.value('(FromLocation/Address/Street)[1]', 'varchar(255)'),
    tripvanlocnr = resource.value('(FromLocation/Address/HouseNumber)[1]', 'varchar(255)'),
    tripvanlocpc = resource.value('(FromLocation/Address/PostalCode)[1]', 'varchar(255)'),
    tripvanloccity = resource.value('(FromLocation/Address/City)[1]', 'varchar(255)'),
    tripvanlocsub = resource.value('(FromLocation/Address/Submunicipality)[1]', 'varchar(255)'),
    tripvanloccountry = resource.value('(FromLocation/Address/Country)[1]', 'varchar(255)'),
    tripvanlong = resource.value('(FromLocation/Longitude)[1]', 'varchar(255)'),
    tripvanlat = resource.value('(FromLocation/Latitude)[1]', 'varchar(255)'),
    tripnaarlocstr = resource.value('(ToLocation/Address/Street)[1]', 'varchar(255)'),
    tripnaarlocnr = resource.value('(ToLocation/Address/HouseNumber)[1]', 'varchar(255)'),
    tripnaarlocpc = resource.value('(ToLocation/Address/PostalCode)[1]', 'varchar(255)'),
    tripnaarloccity = resource.value('(ToLocation/Address/City)[1]', 'varchar(255)'),
    tripnaarlocsub = resource.value('(ToLocation/Address/Submunicipality)[1]', 'varchar(255)'),
    tripnaarloccountry = resource.value('(ToLocation/Address/Country)[1]', 'varchar(255)'),
    tripnaarlong = resource.value('(ToLocation/Longitude)[1]', 'varchar(255)'),
    tripnaarlat = resource.value('(ToLocation/Latitude)[1]', 'varchar(255)'),
    triptype = resource.value('(Type)[1]', 'varchar(255)'),
    tripkmgereden = resource.value('(MileageDriven)[1]', 'varchar(255)'),
    tripkmvogelvlucht = resource.value('(MileageBirdFlight)[1]', 'varchar(255)'),
    tripuserid = resource.value('(Users/UserEntity/Id)[1]', 'varchar(255)'),
    tripusername = resource.value('(Users/UserEntity/Name)[1]', 'varchar(255)')
FROM
    @XmlFile4.nodes('//TripOverview_GetByVehicleIdListDateResponse/TripOverview_GetByVehicleIdListDateResult/TripOverviewEntity/TripItemEntities/TripItemEntity') AS XTbl1(resource)
WHERE 
    resource.value('(../../Vehicle/Id)[1]', 'varchar(255)') = id 
    AND resource.value('(FromDateLocal)[1]', 'varchar(255)') = tripvan

I'm trying to import this XML file (https://ufile.io/8d9of) but it takes between 2-3 minutes to complete the import. How can I speed it up since the server is not the issue (SQL Server 2016, enterprise ssd's, dual Xeon cpu's and 256GB ddr4 ram)

Upvotes: 0

Views: 203

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81930

The Load and Parsing of the XML is rather performat... 585 records in 0.108 seconds. I suspect the update is causing the degradation.

Perhaps by placing the XML Parse within a CTE and then perform the update via a JOIN.

Notice I added two fields kID and kFromDateLocal

Example

Declare @XmlFile4 xml
Select @XmlFile4 = BulkColumn FROM  OPENROWSET(BULK 'C:\Working\geobewegingen.xml', SINGLE_BLOB) x;

;WITH XMLNAMESPACES (DEFAULT 'http://www.geodynamics.be/webservices')
,cte as (
Select kID                = resource.value('(../../Vehicle/Id)[1]', 'varchar(255)'),
       kFromDateLocal     = resource.value('(FromDateLocal)[1]', 'varchar(255)'),
       voernaam           = resource.value('(../../Vehicle/Name)[1]', 'varchar(255)'),
       voercode           = resource.value('(../../Vehicle/Code)[1]', 'varchar(255)'),
       pervan             = resource.value('(../../FromDateLocal)[1]', 'varchar(255)'),
       pertot             = resource.value('(../../ToDateLocal)[1]', 'varchar(255)'),
       tripvan            = resource.value('(FromDateLocal)[1]', 'varchar(255)'),
       triptot            = resource.value('(ToDateLocal)[1]', 'varchar(255)'),
       tripvanlocstr      = resource.value('(FromLocation/Address/Street)[1]', 'varchar(255)'),
       tripvanlocnr       = resource.value('(FromLocation/Address/HouseNumber)[1]', 'varchar(255)'),
       tripvanlocpc       = resource.value('(FromLocation/Address/PostalCode)[1]', 'varchar(255)'),
       tripvanloccity     = resource.value('(FromLocation/Address/City)[1]', 'varchar(255)'),
       tripvanlocsub      = resource.value('(FromLocation/Address/Submunicipality)[1]', 'varchar(255)'),
       tripvanloccountry  = resource.value('(FromLocation/Address/Country)[1]', 'varchar(255)'),
       tripvanlong        = resource.value('(FromLocation/Longitude)[1]', 'varchar(255)'),
       tripvanlat         = resource.value('(FromLocation/Latitude)[1]', 'varchar(255)'),
       tripnaarlocstr     = resource.value('(ToLocation/Address/Street)[1]', 'varchar(255)'),
       tripnaarlocnr      = resource.value('(ToLocation/Address/HouseNumber)[1]', 'varchar(255)'),
       tripnaarlocpc      = resource.value('(ToLocation/Address/PostalCode)[1]', 'varchar(255)'),
       tripnaarloccity    = resource.value('(ToLocation/Address/City)[1]', 'varchar(255)'),
       tripnaarlocsub     = resource.value('(ToLocation/Address/Submunicipality)[1]', 'varchar(255)'),
       tripnaarloccountry = resource.value('(ToLocation/Address/Country)[1]', 'varchar(255)'),
       tripnaarlong       = resource.value('(ToLocation/Longitude)[1]', 'varchar(255)'),
       tripnaarlat        = resource.value('(ToLocation/Latitude)[1]', 'varchar(255)'),
       triptype           = resource.value('(Type)[1]', 'varchar(255)'),
       tripkmgereden      = resource.value('(MileageDriven)[1]', 'varchar(255)'),
       tripkmvogelvlucht  = resource.value('(MileageBirdFlight)[1]', 'varchar(255)'),
       tripuserid         = resource.value('(Users/UserEntity/Id)[1]', 'varchar(255)'),
       tripusername       = resource.value('(Users/UserEntity/Name)[1]', 'varchar(255)')
 From @XmlFile4.nodes('//TripOverview_GetByVehicleIdListDateResponse/TripOverview_GetByVehicleIdListDateResult/TripOverviewEntity/TripItemEntities/TripItemEntity') AS XTbl1(resource)
) 
Update A
   set voernaam           = B.voernaam
      ,voernaam           = B.voernaam
      ,voercode           = B.voercode
      ,pervan             = B.pervan
      ,pertot             = B.pertot
      ,tripvan            = B.tripvan
      ,triptot            = B.triptot
      ,tripvanlocstr      = B.tripvanlocstr
      ,tripvanlocnr       = B.tripvanlocnr
      ,tripvanlocpc       = B.tripvanlocpc
      ,tripvanloccity     = B.tripvanloccity
      ,tripvanlocsub      = B.tripvanlocsub
      ,tripvanloccountry  = B.tripvanloccountry
      ,tripvanlong        = B.tripvanlong
      ,tripvanlat         = B.tripvanlat
      ,tripnaarlocstr     = B.tripnaarlocstr
      ,tripnaarlocnr      = B.tripnaarlocnr
      ,tripnaarlocpc      = B.tripnaarlocpc
      ,tripnaarloccity    = B.tripnaarloccity
      ,tripnaarlocsub     = B.tripnaarlocsub
      ,tripnaarloccountry = B.tripnaarloccountry
      ,tripnaarlong       = B.tripnaarlong
      ,tripnaarlat        = B.tripnaarlat
      ,triptype           = B.triptype
      ,tripkmgereden      = B.tripkmgereden
      ,tripkmvogelvlucht  = B.tripkmvogelvlucht
      ,tripuserid         = B.tripuserid
      ,tripusername       = B.tripusername
 From  GeoBewegingen A
 Join  cte B on A.id = B.kID and A.tripvan = B.kFromDateLocal

Upvotes: 1

Related Questions