UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Xml Import Speed Issue With Change From Access 2013 To 2016, Access 2016    
 
   
SnoopDog
post Nov 2 2017, 05:11 AM
Post#1



Posts: 2
Joined: 2-November 17



Dear Gurus

We are using MS Access to import data from approx. 6,000 XML files in one session per day into our system. Each XML import file is >=30KB and <=85KB and contains info for one master record change that is structured across 25 different tables (example attached).

We originally built this process in Access 2013 and it worked really well, averaging 60+ mins to upload the 6,000 XML files and append these records to our table structure.

We recently changed from Office 2013 to Office 2016 (to address another critical issues)- both 64 bit versions. This change has had a huge negative impact on this XML import process and we can't figure out why - same accdb database running via Access 2016 instead of Access 2013. The import now starts running at 2 x XML imports per second and after 1,000 XML imports has slowed to 1 x XML import every 5 seconds, ultimately grinding to a halt and not working.

We found that Access 2016 had auto-indexed many fields in our local import tables (now unindexed) but it's still very slow...

Because the XML file structure doesn't include the parent id in each of its child tables, for each XML import file we import the data to local import tables then append this data with a record ID in another set of local tables.

Below is our upload process VBA from Access. We use the code to:

1. Clear our local import tables.

2. Loop through each XML file to:

-Import its data to the local import tables.
-Append this data along with the parent id for this XML file into each of our local working tables.
-Move the XML file into an archive directory.
-Clear our local import tables.

Hoping you can provide feedback on what the issue might be?

Kind regards

Snoop Dog

CODE
Function McoXMLImport()

On Error GoTo McoXMLImport_Err

Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim filename As String
Dim path As String

  DoCmd.SetWarnings False
  
   'Delete all Import tables
    DoCmd.OpenQuery "QryDELApplicationArea", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELContract", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELCustomer", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDelDataArea", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELDistributionChannel", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELFeature", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELLifeCycleEvent", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELManufacture", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELMisc", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELNSC", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELOrderEvent", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELRouting", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELScheduling", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELSender", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELSpecification", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELStatus", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELVehicle", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELVehicleOrderDetails", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELLocalOption", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELShippingData", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELRegistration", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELNotes", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELHold", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELDistribution", acViewNormal, acEdit
  
  'XML Import directory
  path = "D:\AA_Current\JLR\XML_Feed\"

  'Loop through the folder & build file list
  strFile = Dir(path & "*.xml")

  While strFile <> ""
     'add files to the list
     intFile = intFile + 1
     ReDim Preserve strFileList(1 To intFile)
     strFileList(intFile) = strFile
      strFile = Dir()
  Wend

  'see if any files were found
  If intFile = 0 Then
    MsgBox "No files found"
    Exit Function
  End If

  'cycle through the list of files
  For intFile = 1 To UBound(strFileList)
    filename = path & strFileList(intFile)
      
   'identify current filename on Main Menu, then use to capture in query
   Forms![FrmMainMenu]![tbFilename].Value = CStr(Right(filename, 32))
  
   'import xml
   Application.ImportXML filename, acAppendData
  
    'move current xml file to archive directory
  
    Dim XMLFile As String
    
    XMLFile = Dir(filename)
    
    'Do Until XMLFiles = ""
    Name "D:\AA_Current\JLR\XML_Feed\" & XMLFile As "D:\AA_Current\JLR\XML_Feed_Archive\" & XMLFile
  
    'Appends import into Tbl structure with identifier
    DoCmd.OpenQuery "QryAPPApplicationArea", acViewNormal, acEdit
    DoCmd.OpenQuery "QryAPPContract", acViewNormal, acEdit
    DoCmd.OpenQuery "QryAPPCustomer", acViewNormal, acEdit
    DoCmd.OpenQuery "QryAPPDataArea", acViewNormal, acEdit
    DoCmd.OpenQuery "QryAPPDistributionChannel", acViewNormal, acEdit
    DoCmd.OpenQuery "QryAPPFeature", acViewNormal, acEdit
    DoCmd.OpenQuery "QryAPPLifeCycleEvent", acViewNormal, acEdit
    DoCmd.OpenQuery "QryAPPManufacture", acViewNormal, acEdit
    DoCmd.OpenQuery "QryAPPMisc", acViewNormal, acEdit
    DoCmd.OpenQuery "QryAPPNSC", acViewNormal, acEdit
    DoCmd.OpenQuery "QryAPPOrderEvent", acViewNormal, acEdit
    DoCmd.OpenQuery "QryAPPRouting", acViewNormal, acEdit
    DoCmd.OpenQuery "QryAPPScheduling", acViewNormal, acEdit
    DoCmd.OpenQuery "QryAPPSender", acViewNormal, acEdit
    DoCmd.OpenQuery "QryAPPSpecification", acViewNormal, acEdit
    DoCmd.OpenQuery "QryAPPStatus", acViewNormal, acEdit
    DoCmd.OpenQuery "QryAPPVehicle", acViewNormal, acEdit
    DoCmd.OpenQuery "QryAPPVehicleOrderDetails", acViewNormal, acEdit
    DoCmd.OpenQuery "QryAPPLocalOption", acViewNormal, acEdit
    DoCmd.OpenQuery "QryAPPShippingData", acViewNormal, acEdit
    DoCmd.OpenQuery "QryAPPRegistration", acViewNormal, acEdit
    DoCmd.OpenQuery "QryAPPNotes", acViewNormal, acEdit
    DoCmd.OpenQuery "QryAPPHold", acViewNormal, acEdit
    DoCmd.OpenQuery "QryAPPDistribution", acViewNormal, acEdit
    
   'Delete all Import tables
    DoCmd.OpenQuery "QryDELApplicationArea", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELContract", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELCustomer", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDelDataArea", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELDistributionChannel", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELFeature", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELLifeCycleEvent", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELManufacture", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELMisc", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELNSC", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELOrderEvent", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELRouting", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELScheduling", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELSender", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELSpecification", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELStatus", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELVehicle", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELVehicleOrderDetails", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELLocalOption", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELShippingData", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELRegistration", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELNotes", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELHold", acViewNormal, acEdit
    DoCmd.OpenQuery "QryDELDistribution", acViewNormal, acEdit
      
  'end actions and move to next XML file
  
  Next intFile
  
  DoCmd.SetWarnings True
  
   MsgBox "Latest XML Import and Upload Complete", vbInformation, "JLR XML Vista Data"

McoXMLImport_Exit:
    Exit Function

McoXMLImport_Err:
    MsgBox Error$
    Resume McoXMLImport_Exit
  
  
End Function

Attached File(s)
Attached File  jlr002_201711012226321368761.zip ( 5.27K )Number of downloads: 2
 
Go to the top of the page
 
SnoopDog
post Nov 2 2017, 06:10 AM
Post#2



Posts: 2
Joined: 2-November 17



Hi Colin

Thanks so much for your quick response!

I'm keen to implement your advice tomorrow and will let you know how it goes.

Could you please provide an example of how to EMPTY a table?

Re JSON, our data provider's very inflexible but we'll ask.

All feedback appreciated.

Kind regards

Scott
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th December 2017 - 05:22 PM