wdk applications query modification for … applications query modification for performance oct 6-7,...
TRANSCRIPT
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()