Performance Tuning for Content Manager

Chapter 12. Troubleshooting performance problems 367

About Microsoft system

If you are using a Microsoft Windows system and you want to trace TCP/IP on

Windows platform, Microsoft offers tuning guidance on their Web site. The link is:

http://www.microsoft.com/technet/itsolutions/network/deploy/depovg/tcpi

p2k.mspx

12.4 Slow search

When a user performs a basic search or an advanced search in Windows client,

eClient, or a custom client (using Information Integrator for Content API), the

Information Integrator for Content API (both Windows client and eClient are

based on Information Integrator for Content API also) gets an XQuery from the

clients (or application), and then the API side query engine (within the

Information Integrator for Content API package) creates a partial SQL based on

the Xquery, and then the Content Manager Library Server finishes the entire SQL

with server-side knowledge that only the server can know, such as filling in ACL

place-holders.

Thus, the SQL statement sent to the DB2 UDB is partially composed by

Information Integrator for Content API, and is partially composed by the Library

Server, based on the XQuery. XQuery is the Content Manager query language

which is an XML-based query language that conforms to XQuery Path

Expressions (XQPE), a subset of W3C XML Query working draft. The query

language searches hierarchical item types and locates items quickly and easily.

An XQuery is translated to a SELECT SQL statement in Content Manager. Then,

Content Manager runs this SELECT SQL statement in the backend database.

The database server tries to pick the best access plan for any given SELECT

SQL based on statistics and other intelligence. In some cases for this particular

situation, some databases might choose the most optimal plan and others can

choose a plan that might result in poor performance. Among reasons for the

choice, a less-than optimal plan can result if the statistics are not current for the

database or it lacks some index on the tables. Additionally, the same XQuery can

be written several ways.

For most of slow search scenarios, it is caused by some SELECT SQL

statements which run slowly on the DB2 UDB server. If we confirm from the

ICMSERVER.LOG file that some SELECT SQL statements caused the

performance issue, we can then tune these SELECT SQL statements and the

DB2 UDB server.

There are multiple ways to write an XQuery to get the same result. Although the

different ways are functionally equivalent, some of these ways might be better

368 Performance Tuning for Content Manager

than others in terms of performance since Content Manager can generate a

different SQL statement which might has better performance. So, rewriting

XQuery can resolve the search performance issue in some cases.

For example, we set the Library Server trace level at 15 and perform a search on

Resumes item type in the Windows client. In the log file, you can find the XQuery

and the exact SQL statement. Example 12-12 shows the partial

ICMSERVER.LOG file. In the example, the ICMSEARCH stored procedure exited

with rc=0. The ICMSEARCH used 317 msec (1000 msec= 1 second).

Example 12-12 ICMSERVER.LOG: Slow search

ICMPLSQU ICMSEARCH 00321 06/02 05:49:46.850 GMT ;02054733826423

15126134622165964457 ICMADMIN Entry

......

ICMPLSQU parseInitialParms 00509 06/02 05:49:46.850 GMT ;02054733826423

15126134622165964457 ICMADMIN Search Parms:

StructID 0

Max Results 101

Timeout 0

Order By <>

XQPE String </'Resumes'[@SEMANTICTYPE BETWEEN 1 AND 2]>

Num Query Parts 2

Latest Ver Only 1

Opt Z 500

Opt Y -1

Opt X -1

Num ITypes Sent 0

ICMPLSQU parseInitialParms 00561 06/02 05:49:46.850 GMT ;02054733826423

15126134622165964457 ICMADMIN Return rc=0

ICMPLSQU openQueryCursor 00778 06/02 05:49:46.850 GMT ;02054733826423

15126134622165964457 ICMADMIN Entry

......

ICMPLSQU outputLongStringToLog 02423 06/02 05:49:47.121 GMT ;02054733826423

15126134622165964457 ICMADMIN Entry

ICMPLSQU outputLongStringToLog 02438 06/02 05:49:47.121 GMT ;02054733826423

15126134622165964457 ICMADMIN Query Stmt Length

SQL Stmt Length: 791

ICMPLSQU outputLongStringToLog 02446 06/02 05:49:47.121 GMT ;02054733826423

15126134622165964457 ICMADMIN Query Stmt Text

SQL Stmt: <SELECT T.* FROM ( SELECT DISTINCT Resumes_1.ITEMID, Resumes_1.COMPONENTID,

Resumes_1.VERSIONID, 1032 AS COMPONENTTYPEID, 1015 AS ITEMTYPEID FROM ICMUT01032001 Resumes_1

WHERE (Resumes_1.SEMANTICTYPE BETWEEN 1 AND 2) AND ((( ( (EXISTS (SELECT 1 FROM

ICMSTCOMPILEDACL C , ICMSTITVIEWDEFS V WHERE 1015=V.ITEMTYPEID AND V.ITEMTYPEVIEWID IN (1015)

AND (C.ACL = V.ACLCODE OR C.ACL = -1) AND C.UNUM=2 AND C.RPRIV='1' )) OR (EXISTS (SELECT 1

FROM ICMSTCOMPILEDACL C, ICMSTCOMPILEDPERM P , ICMSTITVIEWDEFS V WHERE 1015=V.ITEMTYPEID AND

V.ITEMTYPEVIEWID IN (1015) AND (C.ACL = V.ACLCODE OR C.ACL = -1) AND C.UNUM=1 AND C.RPRIV='1'

AND P.PRIVDEFCODE=121 AND P.UNUM=2 )) ))))) T , ICMSTITEMS001001 I WHERE T.ITEMID = I.ITEMID

AND T.VERSIONID = I.VERSIONID OPTIMIZE FOR 500 ROWS FOR READ ONLY WITH UR>