Extremely Slow WebService

[Laurel]

My web service is running extrememly slow when consumed in Excel VBA
(using MS Office XP Web Services toolkit to add a Web Reference to
ws). I'm dealing with client spreadsheets calling the same function
160 times or more in one sheet and it takes a minute or more to return
all the values. (Excel func calls proxy class func calls web service
func calls code behind func which gets data from sql that is
returned.)

More info:
1. Cache duration on each web method set to 1 hour.
2. Buffer set to False on web methods.
3. I'm not running in debug mode - web.config debug="false"

I have taken the code behind layer out of the mix so I can rule out
any slowness caused by sql or my code. Now, my amt() function, which
is called hundreds of times in the xls, just returns the decimal value
of 1 each time. It is still taking longer than it should (in my mind)
just to return all of the 1s. How can I speed things up??

When the ws is running, the cpu is nearly maxed out - definite spike.
Also worth mentioning - this is happening on IIS 5.0 with ASP.NET 1.1.

Any advice would be greatly appreciated!

Laurel

 

[Dino Chiesa [Microsoft]]

Hard to give clear answers.
which CPU is maxed? client or server? Is the server separate? Is the SQL
remote? etc

For my local webservices - everything running on the same CPU - I typically
get response times of 0.05s - with a single client thread (no concurrency).
Typically 20 or more requests per second.

Have you characterized the cost of each request?

client invokes service
service receives request
service does SQL lookup
service sends response
client receives response

At each layer there is a time cost. To characterize it, you could
instrument the webservice logic on the server to mark the time at the start
of the request and the end of the request (just before return). This gives
you a cost of the SQL lookup. Do the same thing on the client and you can
measure each segment.

You might want to compare VBA to a .NET client (just for your own interest).

If you are doing 160 requests, a batch request may be appropriate. Have you
considered this? It may be worth doing this before any further perf
analysis (as discussed above) on each individual request. Rather than
sending 160 requestrs for 1 item each, you send 1 request for 160 (or more)
items. Then you store the 160 items (maybe in a temp table in the sheet)
and don't need to traverse the network further. If this doesn't work for
you, you could also try a caching strategy, where you use a wrapper on the
ws proxy. The wrapper would check a local cache (again, possibly stored in
a temp table in the sheet); if the desired value is present, use it. If
not, then call out to the webservice.

-Dino