Page 2 of 2 FirstFirst 12
Results 16 to 24 of 24
  1. #16
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Replacing the rs with an append query gets rid of the rs entirely and shaves a bit more additional time off Vlad's vast improvement.

    Code:
        For Each node In TitleNodes
            mo = node.getAttribute("class")
            If mo = "RETU_R" Then
                DN_Name = node.getAttribute("distName")
                sSql = "Insert Into RETU_R (DN_Name, Class, CreateDate) Values ('" & DN_Name & "', " & """ & RETU_R & """ & ",#" & Now & "#)"
                'Debug.Print sSql
                db.Execute sSql, dbFailOnError
                sSql = ""
                For Each par In node.childNodes
                    If par.baseName = "p" Then
                        p_name = par.getAttribute("name")
                        p_value = par.Text
                    End If


  2. #17
    venom is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2021
    Posts
    12
    Still it doesnt make huge difference. I have over 500MB xml file which need to be parsed / loop through the update / Insert option.

    I dont know why insert option is time consuming. If i just remove sql lines and update function then code runs instant. Is there a way i can store all results in a datatable (like in .Net) and then send update command once datatable is loaded with all values ?

  3. #18
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I found some examples using XPath (https://analystcave.com/vba-xml-working-xml-files/) but I am not familiar with that so I'll let you investigate further. Maybe you can set up your imports at night using a scheduler task so they have enough time to run.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #19
    venom is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2021
    Posts
    12
    Quote Originally Posted by Gicu View Post
    I found some examples using XPath (https://analystcave.com/vba-xml-working-xml-files/) but I am not familiar with that so I'll let you investigate further. Maybe you can set up your imports at night using a scheduler task so they have enough time to run.

    Cheers,
    Thanks vlad

    I will have a go with it but i think it is still update function that takes most time

    XML is read fairly quickly if i execute the code without update function.

  5. #20
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Here's another upgrade. I took Vlad's improvement a step further. Instead of separate queries to insert a record and then another to update it, I combined both into an Insert query.
    It runs about 30% faster than separate queries. I think this is the best it will get.

    Audit_DB_Vlad-davegri.zip

  6. #21
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #22
    venom is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2021
    Posts
    12
    Thanks Davegri, yes i see it has improved the performance. Not sure how effective it will be if i run huge XML in it, but i will have a go , thanks for your kind support

  8. #23
    venom is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2021
    Posts
    12
    Nice post there Gicu, thanks for sharing. I was thinking to move out of DOM as i feel that it loads the xml document completely, maybe reading line by line is faster approach?

  9. #24
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Maybe upgrade to SQL 2019 express? max db size: 10 GB and far better suited to work with XML.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 17
    Last Post: 12-24-2019, 10:35 PM
  2. Replies: 4
    Last Post: 10-09-2017, 03:17 PM
  3. Updating the Existing record with new data
    By rd.prasanna in forum Import/Export Data
    Replies: 4
    Last Post: 10-02-2013, 07:04 AM
  4. Importing Excel data to an existing table
    By tonyrhills in forum Import/Export Data
    Replies: 3
    Last Post: 12-23-2011, 09:19 AM
  5. updating existing report with new data in table
    By newtoaccess123 in forum Reports
    Replies: 2
    Last Post: 10-18-2011, 09:50 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums