CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 25

Thread: Excel writing time is inconsistent.

  1. #1
    Join Date
    Nov 2020
    Location
    Chennai
    Posts
    7

    Excel writing time is inconsistent.

    Excel writing time is inconsistent.

    We are having vb script to read XML and write it into excel.In one machine excel writing is taking 15 mins in another
    machine it is taking more than 1 hr. what is the reason behind this issue and how to make the excel writing time constant for all the machines?
    Please help me to find a solution for this issue.

  2. #2
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    19,886

    Re: Excel writing time is inconsistent.

    Maybe using the same machines with the same OS/VB/Office installed?
    Victor Nijegorodov

  3. #3
    Join Date
    Nov 2020
    Location
    Chennai
    Posts
    7

    Re: Excel writing time is inconsistent.

    Quote Originally Posted by VictorN View Post
    Maybe using the same machines with the same OS/VB/Office installed?
    OS might be differ, but VB script is same for all machines.
    And in same machine also we are facing the same issue.

  4. #4
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    19,886

    Re: Excel writing time is inconsistent.

    Quote Originally Posted by HemalathaNatarajan View Post
    Excel writing time is inconsistent.

    We are having vb script to read XML and write it into excel.In one machine excel writing is taking 15 mins in another
    machine it is taking more than 1 hr.
    Quote Originally Posted by HemalathaNatarajan View Post
    ...
    And in same machine also we are facing the same issue.
    Sorry, I cannot understand you: is the time for the same script to read the same "XML and write it into excel" on the same machine always the same or it can differ?
    Victor Nijegorodov

  5. #5
    2kaud's Avatar
    2kaud is offline Super Moderator Power Poster
    Join Date
    Dec 2012
    Location
    England
    Posts
    7,371

    Re: Excel writing time is inconsistent.

    In one machine excel writing is taking 15 mins in another
    machine it is taking more than 1 hr.
    What is the size of the XML? What is the size of the resultant excel (rows/columns?). Is the version of Excel the same on both machines? What are the specs of both machines(cpu/memory/type of drive etc).
    All advice is offered in good faith only. All my code is tested (unless stated explicitly otherwise) with the latest version of Microsoft Visual Studio (using the supported features of the latest standard) and is offered as examples only - not as production quality. I cannot offer advice regarding any other c/c++ compiler/IDE or incompatibilities with VS. You are ultimately responsible for the effects of your programs and the integrity of the machines they run on. Anything I post, code snippets, advice, etc is licensed as Public Domain https://creativecommons.org/publicdomain/zero/1.0/ and can be used without reference or acknowledgement. Also note that I only provide advice and guidance via the forums - and not via private messages!

    C++17 Compiler: Microsoft VS2019 (16.8.4)

  6. #6
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,441

    Re: Excel writing time is inconsistent.

    Is the reading amd writing occurring over the network?

  7. #7
    Join Date
    Nov 2020
    Location
    Chennai
    Posts
    7

    Re: Excel writing time is inconsistent.

    Quote Originally Posted by VictorN View Post
    Sorry, I cannot understand you: is the time for the same script to read the same "XML and write it into excel" on the same machine always the same or it can differ?
    Same script to read the same "xml and write it into excel" on same machine also taking different time.

  8. #8
    Join Date
    Nov 2020
    Location
    Chennai
    Posts
    7

    Re: Excel writing time is inconsistent.

    Quote Originally Posted by Arjay View Post
    Is the reading amd writing occurring over the network?
    No, reading the xml which is there in the system only.

  9. #9
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,441

    Re: Excel writing time is inconsistent.

    Are there cpu, memory, or disk speed differences? OS differences? 64-bit?

  10. #10
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,360

    Re: Excel writing time is inconsistent.

    The time sounds excessive but how do you expect to make the write time constant on all machines if it isn't even constant on the same machine? That alone should tell you that the time it takes is going to vary. That said it surely should not vary by that much unless one machine is much better suited for it than the other.

    For example if the one machine has a SATA SSD and the other has a HDD then you could expect the one with the HDD to take about 5 times as long to do the same write. A good PCIe M2 drive on the other hand can be up to 10 times faster than an average SATA SSD and abotu 6 times faster than a really good SATA SSD.

    So I would suspect the big differences you see from one pc to another is the pc hardware or possibly a fragmented or failing disk.
    Always use [code][/code] tags when posting code.

  11. #11
    Join Date
    Nov 2020
    Location
    Chennai
    Posts
    7

    Re: Excel writing time is inconsistent.

    Quote Originally Posted by Arjay View Post
    Are there cpu, memory, or disk speed differences? OS differences? 64-bit?

    Below table is the bench marking time of excel writing in 3 different machine and added observation on same machine also(same machine also excel writing is taking different time Refer row1 and row2).

    Code:
    Machine Name	             Excel writing time	       OS	                                                     Processor
    
    Machine 1   (32 GB)	         1 hr	              windows 10 pro	                        Intel(R) Xeon(R) CPU E5-1620 v4 @ 3.50GHz, 3501 Mhz, 4 Core(s), 8 Logical Processor(s)
    Machine 1  (32 GB)	      36 mins	      windows 10 pro	                        Intel(R) Xeon(R) CPU E5-1620 v4 @ 3.50GHz, 3501 Mhz, 4 Core(s), 8 Logical Processor(s)
    Machine 2   (32 GB)	      15 mins	      windows 10 pro	                        Intel(R) Xeon(R) CPU E5-1620 v4 @ 3.50GHz, 3501 Mhz, 4 Core(s), 8 Logical Processor(s)
    Machine 2   (32 GB)	      30 mins	      windows 10 pro	                        Intel(R) Xeon(R) CPU E5-1620 v4 @ 3.50GHz, 3501 Mhz, 4 Core(s), 8 Logical Processor(s)
    Machine 3  (64 GB)	  3 hrs 26 mins   Microsoft Windows 7 Professional  	Intel(R) Xeon(R) CPU E5-1620 v4 @ 3.50GHz, 3501 Mhz, 4 Core(s), 4 Logical Processor(s)
    Last edited by Arjay; November 10th, 2020 at 04:55 AM. Reason: Added code tags

  12. #12
    2kaud's Avatar
    2kaud is offline Super Moderator Power Poster
    Join Date
    Dec 2012
    Location
    England
    Posts
    7,371

    Re: Excel writing time is inconsistent.

    But you haven't answered my question in post #5 re the size of the XML file and the size of the excel spreadsheet.

    Is there anything else being done on these computers while this is being done?

    Have you used Windows Task Manager to view the process info and performance info? Have you monitored the resources used via the Resource Monitor?

    If you post the VB script used, a VB guru may be able to spot something or provide advice re improving performance.
    Last edited by 2kaud; November 10th, 2020 at 04:40 AM.
    All advice is offered in good faith only. All my code is tested (unless stated explicitly otherwise) with the latest version of Microsoft Visual Studio (using the supported features of the latest standard) and is offered as examples only - not as production quality. I cannot offer advice regarding any other c/c++ compiler/IDE or incompatibilities with VS. You are ultimately responsible for the effects of your programs and the integrity of the machines they run on. Anything I post, code snippets, advice, etc is licensed as Public Domain https://creativecommons.org/publicdomain/zero/1.0/ and can be used without reference or acknowledgement. Also note that I only provide advice and guidance via the forums - and not via private messages!

    C++17 Compiler: Microsoft VS2019 (16.8.4)

  13. #13
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,441

    Re: Excel writing time is inconsistent.

    32-bit or 64-bit OS? 32-bit app?

    [Edit] Nevermind. You said vb script.

    Why vb script? Why not something more modern? Why not PowerShell?
    Last edited by Arjay; November 10th, 2020 at 05:01 AM.

  14. #14
    Join Date
    Nov 2020
    Location
    Chennai
    Posts
    7

    Re: Excel writing time is inconsistent.

    Quote Originally Posted by 2kaud View Post
    But you haven't answered my question in post #5 re the size of the XML file and the size of the excel spreadsheet.

    Is there anything else being done on these computers while this is being done?

    Have you used Windows Task Manager to view the process info and performance info? Have you monitored the resources used via the Resource Monitor?

    If you post the VB script used, a VB guru may be able to spot something or provide advice re improving performance.

    xml size is 48 KB and generated Excel will be around 10 MB.
    In Task manager everything seems to be fine.
    Vb script logic is simple like,reading the xml nodes and writing into excel cell.

  15. #15
    2kaud's Avatar
    2kaud is offline Super Moderator Power Poster
    Join Date
    Dec 2012
    Location
    England
    Posts
    7,371

    Re: Excel writing time is inconsistent.

    To parse and generate the Excel file shouldn't take anywhere near 30 minutes - never mind over an hour.

    Can you post an XML file, the VB script and a link to a generated Excel file. There's quicker ways of doing this.
    All advice is offered in good faith only. All my code is tested (unless stated explicitly otherwise) with the latest version of Microsoft Visual Studio (using the supported features of the latest standard) and is offered as examples only - not as production quality. I cannot offer advice regarding any other c/c++ compiler/IDE or incompatibilities with VS. You are ultimately responsible for the effects of your programs and the integrity of the machines they run on. Anything I post, code snippets, advice, etc is licensed as Public Domain https://creativecommons.org/publicdomain/zero/1.0/ and can be used without reference or acknowledgement. Also note that I only provide advice and guidance via the forums - and not via private messages!

    C++17 Compiler: Microsoft VS2019 (16.8.4)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)