-
November 9th, 2020, 07:28 AM
#1
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.
-
November 9th, 2020, 07:34 AM
#2
Re: Excel writing time is inconsistent.
Maybe using the same machines with the same OS/VB/Office installed?
Victor Nijegorodov
-
November 9th, 2020, 08:05 AM
#3
Re: Excel writing time is inconsistent.
Originally Posted by VictorN
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.
-
November 9th, 2020, 11:06 AM
#4
Re: Excel writing time is inconsistent.
Originally Posted by HemalathaNatarajan
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.
Originally Posted by HemalathaNatarajan
...
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
-
November 9th, 2020, 12:14 PM
#5
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++23 Compiler: Microsoft VS2022 (17.6.5)
-
November 9th, 2020, 09:45 PM
#6
Re: Excel writing time is inconsistent.
Is the reading amd writing occurring over the network?
-
November 9th, 2020, 11:31 PM
#7
Re: Excel writing time is inconsistent.
Originally Posted by VictorN
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.
-
November 9th, 2020, 11:51 PM
#8
Re: Excel writing time is inconsistent.
Originally Posted by Arjay
Is the reading amd writing occurring over the network?
No, reading the xml which is there in the system only.
-
November 10th, 2020, 12:24 AM
#9
Re: Excel writing time is inconsistent.
Are there cpu, memory, or disk speed differences? OS differences? 64-bit?
-
November 10th, 2020, 12:42 AM
#10
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.
-
November 10th, 2020, 01:48 AM
#11
Re: Excel writing time is inconsistent.
Originally Posted by Arjay
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
-
November 10th, 2020, 04:38 AM
#12
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++23 Compiler: Microsoft VS2022 (17.6.5)
-
November 10th, 2020, 04:57 AM
#13
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.
-
November 11th, 2020, 03:00 AM
#14
Re: Excel writing time is inconsistent.
Originally Posted by 2kaud
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.
-
November 11th, 2020, 03:16 AM
#15
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++23 Compiler: Microsoft VS2022 (17.6.5)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|