Calling SetRange for a single row of 10 cells takes around 400ms. Is this typical performance Is there anything I could do to improve the performance
Thanks.
Calling SetRange for a single row of 10 cells takes around 400ms. Is this typical performance Is there anything I could do to improve the performance
Thanks.
Since SetRange involves calling the web services API there is the overhead associated with any web service call. Apart from factors like network speed etc. the performance depends on several factors here;
1. The topology - The call goes to Excel Web Services running the WFE(Web Front End) which in turn calls ECS (Excel Calculation Services). If you have WFE and ECS on separate machines the round trip will take longer.
2. The complexity of the workbook - SetRange might trigger re-calculation of the workbook. This might take some time depending on the complexity of the workbook.
3. The logging level might affect the performance. If you have logging set at verbose in SharePoint 2007 then you will see slower performance.
In general I have seen an overhead of about 50 ms for SetRange assuming no workbook recalcualtion and WFE/ECS running on the same machine.
Hope this helps,
-Sanjay.
Sanjay,
Thank you for the quick response. I truly appreciate that.
I've written an ASMX web service, running within SharePoint Server 2007, that calls Excel Services using Direct Linking. Is there a way to set calculation to manual I've set this particular workbook to use manual calculation and then saved it back to SharePoint, but it didn't make any difference. Is there something else I need to do to keep calculation from happening on every update
Thanks.
Setting the workbook to calculate manually in Excel Client should be sufficient. Did you check the logging level Also you should perhaps check if setting one cell gives you the same performance. Checkout the performance counters under Excel Calculation Services and Excel Services WFE that give you the request processing times. That way you can narrow down on where the time is being used ECS or on the WFE. Sometimes it could be you network (for example DNS) that is taking the time in case you have separate machines for WFE and ECS.
Note that though you are using Direct Linking there is still the web service call between the WFE and ECS.
-Sanjay.
Sanjay,
I ran this to turn off logging:
stsadm -o setlogginglevel -category "general" -tracelevel none
It had no real impact on the performance of SetRange. My single call to SetRange is 368ms. I ran my application while perfmon was running, watching the ECS and WFE counters. Here were the results:
ECS: 0.097
WFE: 0.106
Is there anything further that I can do to improve the performance
Thanks!
Jason,
Looks like ECS preocessing time is close to 100 ms. The WFE time (which includes the ECS processing time) is also pretty close to this. You should profile your code to see where the remaining 250+ms are going.
Also can you try calling SetCell in a loop 10 times and check the time The average will give a better measure of the processing time.
-Sanjay.
Sanjay,
I tried SetCell as well, and a single call to SetCell is about the same as a call to SetRange. I need to call SetRange multiple times, so I'm still concerned that a single call to SetRange is ~400ms. That could add up quickly. As far as your suggestion to profile my code goes, I wrapped the call to SetRange in a Stopwatch instance, so I'm only reporting the time for the single call to SetRange. There isn't any other code there that I wrote.
Any other ideas
Thanks!
Jason,
You should try calling SetCell/SetRange multiple times (say 10 times) to get an average. It is tough to pinpoint the cause without knowing your specific topology, hardware, code or the workbook.
I will see if I can get some data that we may have in the testing we have done and post it on this thread. Keep in mind that this strictly represents the results we got with specific hardware, workbooks etc. But these will give an idea of the order of the overhead involved.
Regards,
-Sanjay.
Hi Jason,
Some questions:
1. How big is the workbook on disk
2. What happens when you try to make the SetRange call on an empty workbook
3. What's your topology Are you running in WAN or LAN Could you use something like Fiddler to see how long it gets to get back the HTML result from http://server/_vti_bin/ExcelService.asmx
Thanks,
s
Shahar,
1. The workbook I was using to test the performance was 15mb.
2. Using my 'direct linking' web service, a single call to SetCell on an empty workbook took 15ms. Using my 15mb workbook, it takes over 300ms to set a single cell to the same value.
3. I'm running this all within a VMware virtual machine running SharePoint Server 2007. I modified my cliient to call ExcelService.asmx, rather than the web service I wrote, and the same call to SetCell took 39ms.
I've since moved on to evaluating other products at this point. There seems to be too much overhead with using Excel Services for this project.
Thanks for your help.
Hi Jason,
Just want to make sure that I provide the data I promised in the previous reply. In our labs the time we saw for SetCell/SetRange in a typical test scenario was around 200 milli-seconds. The time you get on your installation will depend on a numebr of things including the workbook content, topology, network speed, load on the server etc. However this should give you a ballpark estimate of what is achievable.
Thanks,
-Sanjay.