March 2009 Archives

Why the Slowwwww API Searches?

| | Comments (0)
Last month I shared a diagnostic application for the WCI API Service. It worked great on my machine, but...

slow.snail.jpg

Performance was terrible when I deployed it to my customer's environment. It commonly would take 8 seconds (8300 ms) to load. Why the hang up? On my laptop it only took 20 ms.

I looked at the spy logs, and I found that nearly all the processing time went to this  command from the API Service when it tried querying users:

SQueryRequest.execute() executing query: "(((not null) TAG phraseQ OR null) AND ((subtype:"PTUSER")[0])) AND ((((@type:"PTPORTAL")[0]) OR ((@type:"PTCONTENTTEMPLATE")[0])) AND (((ptacl:"u1") OR (ptacl:"9994") OR (ptacl:"9992") OR (ptacl:"51") OR (ptacl:"1"))[0]) AND (((ptfacl:"u1") OR (ptfacl:"9994") OR (ptfacl:"9992") OR (ptfacl:"51") OR (ptfacl:"1"))[0])) METRIC logtf [1]"

The customer has 1.1 million users in the database, so maybe the search index is just very slow? I deleted a few hundred thousand users, and the query could then regularly return in about 6200 ms, so this was a major improvement. Also, I used the standard administrative UI to search for all users, and it took a similarly long time to return.

Ahh, so the problem is that the search index is doing a very bad job querying against a large set of objects.

Does anyone know whether there is a way to overcome this problem? Some uninspired attempts to tune the search service's cache size gave me no material change.

So in the absence of good tuning knowledge? I changed the apicheck diagnostic app so that now it verifies it can query communities (of which there are hundreds) instead of the users. The performance problems for my portlet have magically gone away