wdk applications query modification for … applications query modification for performance oct 6-7,...

1

11

WDK Applications Query Modification for Performance

Oct 6-7, 2004

Performance Engineering Group

2

22

Agenda

Background– The problem with generating queries– DQL hints & other issues

Conceptual solutionOutline of corrective steps to followExample

3

33

The Challanges with Queries in WDK

A standard query is generated to perform on all databases– Oracle, SQL Server, Sybase and DB2

These out-of-the-box queries may not perform quickly– Sub-optimal for database, data model or use case– Query is in an existing java class – Appears to be no way to modify it….

4

44

Something in Webtop runs too slow

2. Request converted to DQL queryWebtop/WDK

Query Construtor

Quer

yTe

x t

DFC

DQL

Q uer

y

Content Server

Database

1. URL sent from browser

SQL

Que

ry

3. DQL sent to Content Server

4. DQL converted to SQL and sent to Database

DQL

Resu

lt sSQ

LRe

s ult s 5. Results pulled back

(as app server calls dmcl ‘next’ method)

6. Results formatted as HTML page, sent to browser

5

55

Example: Unselective queries to SQL Server

Webtop/WDK

Query Construtor

Que

ry

Text

DFC

DQL

Que

ry

Content Server

Database

SQL

Res

ults

DQ

L R

esul

ts

SQL

Q

uery

Webtop URLhttp://localhost:8080/webtop/x

DQL Query: select object_name, … from dm_sysobject where ( … )

SQL Query: select object_name, … from dm_sysobject_sp, dm_sysobject_rp where (…)

Document_1 .002 secDocument_2 .002 sec.....Document_1000 .002 sec1000 rows returned in 2

seconds

All of the keys are materialized into temp DB

6

66

Our Focus: When Webtop sends a ‘sub-optimal DQL’

Webtop/WDK

Query Construtor

Que

ry

Text

DFC

DQL

Que

ry

Content Server

Database

SQL

Res

ults

DQ

L R

esul

ts

SQL

Q

uery

7

77

How to Change or Optimize Queries in DQL

DQL hintsModifying the WHERE clause

– remove of case-insensitivity functions

Improved Caching

8

88

Conceptual Solution

Goals for the WDK Query modifications:– Optimize the DQL for a particular scenario– Only modify the scenario that is sub-optimal– Do not recode the base functionality, so can still upgrade– Extension implies minimal WDK experience to re-work

9

99

What we’ll need?

Webtop Installation on Test SystemWDK Installed over Webtop to be customizedInstall IDE environment as we need to compile classes

– Netbeans.org– Eclipse.org– Others….

Some WDK Documentation– WDK and Client Application Development Guide– WDK and Applications Tutorial

integrated development environment

10

1010

Methodology

6. Modify query string before returning the value

1. Define the problem

2. Find offending Component

3. Find the managing method

4. Create extension for Component

5. Call super class in extension

select object_namefrom dm_sysobject

enable (RETURN_TOP 100)

Reproduce poorly performing Webtop functionGet dmcl trace of the function queryGet session trace finding the WDK Component that handles that query stringCreate custom code that calls Super class then modifies the queryShow results of the customization in dmcl traceReproduce Webtop function and its performance

11

1111

Poorly Performing Webtop Functionhttp://localhost:8088/webtop/component/main?Reload=10&__dmfRequestId=__client1~

SELECT upper(object_name), r_object_id, …FROM dm_cabinetWHERE (is_private = 0 or owner_name = USER) anda_hidden = false ORDER BY 1

1. Define the problem

12

1212

DMCL Trace of the Function

Trace.log File# [ 27 ] Tue Sep 28 14:59:22 2004

927999 ( 1.400 sec) ( 1998 rpc) API> query_cmd, s2,T,F,,,,,select upper(object_name),r_object_id,r_object_type,object_name,owner_name from dm_folderwhere folder('/Folders') and a_is_hidden=false order by 1

Application Server

1. Define the problem

13

1313

Intercept and Modify DQL sent to Content Server

Webtop/WDK

Query Construtor

Query

Te

xt

DFC

DQL Q

uery

Content Server

DatabaseSQ

L Re

sults

DQL R

esults

SQL

Query

URL Issued by a Browser Click

Query Constructed

DFC sends DQL to CS

CS gets DQL

Database gets SQL

select object_namefrom dm_sysobjectenable (RETURN_TOP 100)

Document_1 .002 secDocument_2 .002 secDocument_3 .002 sec...Document_100 .002 sec100 rows returned in 20

milliseconds

14

1414

Session Trace of the Component

App Server Log

/80956219 [http8080-Processor3] DEBUG com.documentum.web.common.Trace - Component: Form ImplClass = com.documentum.webtop.webcomponent.objectlist.ObjectList

Application Server

2, Find Component

15

1515

Session Component Trace Output

80956219 [http8080-Processor3] DEBUG com.documentum.web.common.Trace - Component: Resolved Start Page = /webtop/classic/objectlist/streamline.jsp

80956219 [http8080-Processor3] DEBUG com.documentum.web.common.Trace-Component: Form Impl Class = com.documentum.webtop.webcomponent.streamline.StreamlineView

2, Find Component

16

1616

Three layers of WDK

wdk layer:– <TomcatInstallDir>\webapps\<virtualroot>\wdk\src

webcomponent layer:– <TomcatInstallDir>\webapps\<virtualroot>\webcomponent\src

webtop layer:– <TomcatInstallDir>\webapps\<virtualroot>\webtop\src

2, Find Component

17

1717

com.documentum.webtop.webcomponent.streamline.StreamlineView

2, Find Component

18

1818

Class Component Extensions

MyComponent

Component

Method-1()

Method-2()

Method-1()

This method overrides the one in the super class, however it can call the super class method

19

1919

Managing Methods in the Class

StreamlineView extends DrillDown {

Are any of the methods involved with creating the query?Not in the StreamlineView class directly.

onInit() { ….. }

updateControlsFromPath() { ….. }

}

3, Find Managing Method

20

2020

Does this class extend any other class?

StreamlineView extends DrillDown {

It might not be obvious..

If its not, then its likely the class that this extends has the logic

onInit() { ….. }

updateControlsFromPath() { ….. }

}

3, Find Managing Method

21

2121

StreamlineView extends Drilldown

StreamlineView

DrillDown

StreamlineView extends the DrillDown Class

3, Find Managing Method

22

2222

Look for path of DrillDown…

StreamlineView extends DrillDown {

Where can I find DrillDown.java?

onInit() { ….. }

updateControlsFromPath() { ….. }

}

Import com.documentum……drilldown.DrillDown;

3, Find Managing Method

23

2323

com.documentum.webcomponent.navigation.drilldown.DrillDown

3, Find Managing Method

24

2424

Check the Class that was Extended

DrillDown {

onInit() { ….. }

onRefreshData() { ….. }

onClickBreadcrumb() { ….. }

updateControlsFromPath() { ….. }readConfig() { ….. }

}

3, Find Managing Method

25

2525

Are any Methods Creating the Query?

DrillDown {

In this example, updateControlsFromPath method was constructing the query and putting it into a datagrid

Now we know we need to ‘override’ updateControlsFromPath in our own class

onInit() { ….. }

updateControlsFromPath() { ….. }readConfig() { ….. }

}

3, Find Managing Method

26

2626

Create our extension of Streamline view

StreamlineView

DrillDown

MyStreamlineView extends StreamlineView which extends DrillDownWe only need to add a method called: updateControlsFromPath()

MyStreamlineView

4. Extend Component

27

2727

com.documentum.custom.mystreamlineview.MyStreamlineView

4. Extend Component

28

2828

How to get the original logic?

Don’t want to have to duplicate all of the original logicNeed to ensure all previous logic is called ‘as is’Achieve this by calling:

super.overridden_method_name(variable);

Example:

super.updateControlsFromPath( strpath )

5. Call Super Class

29

2929

Create custom Class to extend StreamlineView

StreamlineView

DrillDown

MyStreamlineView

updateControlsFromPath( strpath ) { …. super.updateControlsFromPath( strpath ); ….. /* Own custom logic */ …

}

updateControlsFromPath( strpath ) { …. super.updateControlsFromPath( strpath ); ….. /* custom logic */ …

}

updateControlsFromPath( strpath ) { …. }

5. Call Super Class

30

3030

Modify Code

Get the stringIn most cases just “append” query hint to stringPut new query into datagrid (if required)Compile your classReboot application server

select object_namefrom dm_sysobject

enable (RETURN_TOP 100)

6. Modify Query String

{bufDql.append(QUERY_DOC_6);

}// DQL HintbufDql.append(" enable(RETURN_TOP 100)");// DQL Hint End// MessageService.addMessage( this, "document query: " + bufDql.toString()); Datagrid dgrid = (Datagrid)getControl(CONTROL_DOCGRID, Datagrid.class);DataProvider dproviderFile = dgrid.getDataProvider();dproviderFile.setQuery(bufDql.toString());

}

31

3131

Other Examples

Advanced Search (will be revamped in 5.3)– Strip Lower()