Using Excel Files as data sources - very slow behaviour

Ask general questions here.
ajw
Posts: 12
Joined: Wed Sep 26, 2012 8:28 am

Using Excel Files as data sources - very slow behaviour

Post by ajw » Thu Apr 04, 2013 11:01 am

I have a number of data-driven tests which use Excel files as the data sources. Recently I have noticed the performance when binding a source to a test case (and also in the "Manage Data Sources" menu) has become very slow. As an example, yesterday it took me 45 minutes to bind 12 data sources (all were approximately 30 rows * 7 columns).

I have used Task Manager to try and monitor what is happening when Ranorex conducts the bind - neither CPU or memory usage rises by much. However, in the process list it seems to open numerous copies of excel.exe (so many that I lost count). Is this normal?

Although I am reluctant to change the files to another format (I am actually quite fond of Excel!) I am reaching a point where it looks like I have no option, as the performance is far too slow. Does anyone know anything that can be done to improve this?

User avatar
Support Team
Site Admin
Site Admin
Posts: 12145
Joined: Fri Jul 07, 2006 4:30 pm
Location: Houston, Texas, USA
Contact:

Re: Using Excel Files as data sources - very slow behaviour

Post by Support Team » Fri Apr 05, 2013 3:36 pm

Hi,

May I ask you which Ranorex version you are using?

Regards,
Markus

ajw
Posts: 12
Joined: Wed Sep 26, 2012 8:28 am

Re: Using Excel Files as data sources - very slow behaviour

Post by ajw » Mon Apr 15, 2013 2:21 pm

Sorry for the slow reply: I am using Ranorex version 4.0.2.21666

User avatar
Support Team
Site Admin
Site Admin
Posts: 12145
Joined: Fri Jul 07, 2006 4:30 pm
Location: Houston, Texas, USA
Contact:

Re: Using Excel Files as data sources - very slow behaviour

Post by Support Team » Tue Apr 16, 2013 2:26 pm

Hello,

We had some performance issues which are fixex in our new version (4.0.3)
Please try to update Ranorex to 4.0.3 and let me know if the performance is better.
Please uninstall Ranorex 4.0.2, restart your comupter and install Ranorex 4.0.3.
Please use the setup.exe file or the Ranorex-4.0.3.exe file in order to install Ranorex because if you do this, all prerequisites will be installed automatically.

Regards,
Bernhard

ajw
Posts: 12
Joined: Wed Sep 26, 2012 8:28 am

Re: Using Excel Files as data sources - very slow behaviour

Post by ajw » Fri Apr 19, 2013 10:34 am

I have updated to 4.0.3 and there is definitely a performance improvement within the "Manage Data Sources" and "Data Source" menus. However the performance is still pretty dreadful when I exit the "Data Binding" menu:

- I right-click on a test case and choose "Data Sources..."
- I choose the spreadsheet that is my Data Source
- In the Data Binding tab I usually use the 'Auto-Bind' option, as typically the top row of the spreadsheet matches the names of my variables
- I click OK to return to Test Suite: the Data Sources menu vanishes almost immediately but then Ranorex spends between 3 and 5 minutes working (the "working cursor" is visible, and I am unable to interact with anything within Ranorex) before I have control over Ranorex again. It is during this period I can see in Task Manager that multiple excel.exe processes are being created.

In the current test suite I am developing there are close to 50 different spreadsheets I need to bind, so you can understand my frustration when I have to wait up to 5 minutes to bind just one.

User avatar
Support Team
Site Admin
Site Admin
Posts: 12145
Joined: Fri Jul 07, 2006 4:30 pm
Location: Houston, Texas, USA
Contact:

Re: Using Excel Files as data sources - very slow behaviour

Post by Support Team » Mon Apr 22, 2013 5:11 pm

Hi,

We will analyze in detail what could be the cause for this issue and we will get back to you as soon as we know more about it.

Regards,
Markus

ajw
Posts: 12
Joined: Wed Sep 26, 2012 8:28 am

Re: Using Excel Files as data sources - very slow behaviour

Post by ajw » Tue Apr 30, 2013 9:11 am

Please note: I tried replacing one of the ExcelDataConnector data sources with a SimpleDataConnector data source, but I had the same problem when binding it, i.e. a dozen or so Excel.exe processes opened and it took about 3 minutes to complete the operation.

If it assists your investigation, I am using Windows Vista Business SP2, 3GHz dual-core Intel processor with 4GB RAM (of which typically about 2GB is free when I am testing with Ranorex); for Excel, I use MS Office 2007. I have tried freeing-up more memory to see if it helps, but it has not made a difference.

ajw
Posts: 12
Joined: Wed Sep 26, 2012 8:28 am

Re: Using Excel Files as data sources - very slow behaviour

Post by ajw » Thu May 02, 2013 11:40 am

Some extra information regarding this problem: the more data sources I bind, the longer each bind takes. It is approaching 6 minutes per bind for me now :(

Below is a picture of my CPU and RAM usage during a bind. By my estimate, the total length of the "Physical Memory Usage History" graph is 4 minutes - that graph is not wide enough to display the memory usage for the duration of a single bind! You can see that my memory and CPU use are moderate throughout, although the CPU usage spikes every 6 seconds.

Image

We have also examined the Resource Monitor while a bind is happening - we noticed there was a large amount of hard-disk I/O, so it has been suggested I defragment my hard-disk tonight (I'll let you know the results tomorrow). What is unusual is that both the Ranorex project and the spreadsheets are on a server, yet I barely see any network usage.

User avatar
odklizec
Ranorex Guru
Ranorex Guru
Posts: 7470
Joined: Mon Aug 13, 2012 9:54 am
Location: Zilina, Slovakia

Re: Using Excel Files as data sources - very slow behaviour

Post by odklizec » Thu May 02, 2013 12:12 pm

Just a suggestion, did you try to use the CSV instead of xls? I know, it may not be a perfect solution, but who knows, maybe a lighter and straightforward CSV could help here?
Pavel Kudrys
Ranorex explorer at Descartes Systems

Please add these details to your questions:
  • Ranorex Snapshot. Learn how to create one >here<
  • Ranorex xPath of problematic element(s)
  • Ranorex version
  • OS version
  • HW configuration

User avatar
Support Team
Site Admin
Site Admin
Posts: 12145
Joined: Fri Jul 07, 2006 4:30 pm
Location: Houston, Texas, USA
Contact:

Re: Using Excel Files as data sources - very slow behaviour

Post by Support Team » Thu May 02, 2013 2:48 pm

Hi,

We analyzed the performance issue with huge Excel files and the slowdown is unfortunately an issue on our site.
This should be solved, the performance should be increased with one of the next Ranorex releases.
What is unusual is that both the Ranorex project and the spreadsheets are on a server, yet I barely see any network usage.
Do you execute the tests via a RDP window? If so is the Task Manager of the screenshot the one of the server or of your client machine? Or are the files located on a "network drive" and you execute them on your machine?

Regards,
Markus

pragyaware
Posts: 1
Joined: Sat May 04, 2013 8:38 am
Contact:

Re: Using Excel Files as data sources - very slow behaviour

Post by pragyaware » Sat May 04, 2013 9:05 am

I think you must uninstall Microsoft office documents all file then reinstall it with a original version.

ajw
Posts: 12
Joined: Wed Sep 26, 2012 8:28 am

Re: Using Excel Files as data sources - very slow behaviour

Post by ajw » Tue May 07, 2013 4:44 pm

Thanks for replies:
Just a suggestion, did you try to use the CSV instead of xls? I know, it may not be a perfect solution, but who knows, maybe a lighter and straightforward CSV could help here?
I did try the CSV and it didn't make any noticable difference.
We analyzed the performance issue with huge Excel files and the slowdown is unfortunately an issue on our site.
This should be solved, the performance should be increased with one of the next Ranorex releases.
Sweet :)
Do you execute the tests via a RDP window? If so is the Task Manager of the screenshot the one of the server or of your client machine? Or are the files located on a "network drive" and you execute them on your machine?
RDP is not used - the files are on the network drive and I execute them on my machine.
I think you must uninstall Microsoft office documents all file then reinstall it with a original version.
Having devoted all that time to bind the data sources I don't want to risk anything that could cause them to unbind! I will await the Ranorex update and see how it improves.

User avatar
Support Team
Site Admin
Site Admin
Posts: 12145
Joined: Fri Jul 07, 2006 4:30 pm
Location: Houston, Texas, USA
Contact:

Re: Using Excel Files as data sources - very slow behaviour

Post by Support Team » Wed May 08, 2013 10:44 am

Hi,

Just one last question, have you also tried to copy them to a local drive respectively to your project (by checking the "Copy File to Project)?

Regards,
Markus

ajw
Posts: 12
Joined: Wed Sep 26, 2012 8:28 am

Re: Using Excel Files as data sources - very slow behaviour

Post by ajw » Wed May 08, 2013 4:24 pm

The Ranorex project files are on the network drive while Ranorex Studio is installed on my computer, so I access the project files across our network; this ensures the project files are included within our server's nightly backup routine. Whenever I create a new data source from an Excel spreadsheet, I always use the 'Copy File To Project' option, to ensure the spreadsheets are also backed-up.

User avatar
Support Team
Site Admin
Site Admin
Posts: 12145
Joined: Fri Jul 07, 2006 4:30 pm
Location: Houston, Texas, USA
Contact:

Re: Using Excel Files as data sources - very slow behaviour

Post by Support Team » Mon May 13, 2013 11:51 am

Hi,

Thanks for the information!
In general it is not recommended to run your projects from a network drive, since this could cause several problems.

Regards,
Markus