Slow graph

[redarteht]

Loading a graph into Excel is slow. It takes up to 3 seconds to load a graph with the following instructions: png("1.png", width =800, height = 300) plot(ts) gp1 = current.graphics(filename = "1.png") xlrc["'test'!a30"] = gp1 unlink("1.png")

Is there a way to massively improve the loading speed? Generation of the graph directly in R is instant. So it's only the res = objShell.Run("""" & strPath & """ " & Arch & """" & ScriptPath & """", 0, True), that is extremely slow.

Would be amazingly helpful!!!!!

[gdemin]

Hi! It seems from your code that you run R code from Excel. Is it really so? By now every call from Excel starts R from scratch. So there is a constant overhead for R starting time. I tried several methods for connection between running R and Excel without restarting R for every call but all of them were very unstable. You can see/try one of the such attempts - 'REvaluate' function in 'R_connection_examples.xlsm'. I hope I will find solution in the future but now I can only recommend to minimize number of calls to R from Excel, e. g. put all your R code in single call so R will start only once.

[redarteht]

Hi Gregory Thanks for your quick reply! I am trying to implement a fast way to create R-generated charts in excel to benefit from the ggplot flexibility. Therefore, it would be quite handy to get such a process executing under one second. Do you mean that each line of code is restarting R?

Greetings to Moscow!

[gdemin]

Not each line. Each RExec formula when it is called starts R. So, less RExec's - faster work. For example, if you need several plots you can try make them in the single RExec. I suppose it will be significantly faster then plot them one by one.

[redarteht]

Thanks for your reply! That would be a workaround. The ideal case would be to use it on an ad-hoc basis. It's not a lot, but a bit faster would just make it usable. Is the opening of R the only bottle-neck? Could R be pre-opened?

[gdemin]

No, preopening will not work. By now interaction occurs in a straightforward unclever way. All R code in Excel cells is saved to temporary text file and after that rscript.exe is executed with argument pointing to this file. This method is rather robust but, as you can see, kinda slow. But I hope someday I will find a solution for this annoyance. I tried two other ways of interaction with R without restarting. The first one is exchange with R via stdin without restarting (Revaluate formula) but in this case R became unresponding very quickly. After googling I found out that there is an unresolved problem with stdin in Microsoft wscript object. Second option was DDE (very ancient windows exchange protocol) but it hangs even quickier then stdin. There are other methods but they require registering COM objects in the windows registry and some others preliminary steps. My priority in the developing of this package is simplicity of distribution, possibly in corporate environment without administrative privileges. For example, you can put your Excel file and R in the single folder and users can copy-paste this folder and use your work without any additional efforts. Because of such priority I cannot use connection methods which require installation of additional software and Windows registry editing. If you haven't such restrictions you can look at other packages for R-Excel interaction: RExcel or BERT. Perhaps they will be faster.

[redarteht]

The simplicity of distribution is quite key - i fully agree. I did see how the script works with the temporary files. It's a bit rustic but therefore also very effective. For me it's really just the speed issue. I will evaluate other options or live with the time restriction.

Would there be any difference if the script would trigger a predefined R-script outside of excel? Could that be a workaround?

[gdemin]

I suppose your R code is not very long - so writing to temporary file doesn't take significant amount of time. How long does it take to run Rgui on your machine?

[redarteht]

If you mean the startup of RGui. It's pretty much instant - a fraction of a second.

[redarteht]

I guess I figured out the main problem. I am loading a few packages before I process the time series and the chart. The package loading is possibly the source of the inefficiency.

[redarteht]

I have now be able to speedup the packages substantially and now it works. Thanks for your inputs!