![Page 1: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/1.jpg)
1
11
WDK Applications Query Modification for Performance
Oct 6-7, 2004
Performance Engineering Group
![Page 2: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/2.jpg)
2
22
Agenda
Background– The problem with generating queries– DQL hints & other issues
Conceptual solutionOutline of corrective steps to followExample
![Page 3: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/3.jpg)
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….
![Page 4: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/4.jpg)
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
![Page 5: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/5.jpg)
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
![Page 6: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/6.jpg)
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
![Page 7: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/7.jpg)
7
77
How to Change or Optimize Queries in DQL
DQL hintsModifying the WHERE clause
– remove of case-insensitivity functions
Improved Caching
![Page 8: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/8.jpg)
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
![Page 9: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/9.jpg)
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
![Page 10: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/10.jpg)
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
![Page 11: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/11.jpg)
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
![Page 12: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/12.jpg)
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
![Page 13: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/13.jpg)
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
![Page 14: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/14.jpg)
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
![Page 15: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/15.jpg)
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
![Page 16: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/16.jpg)
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
![Page 17: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/17.jpg)
17
1717
com.documentum.webtop.webcomponent.streamline.StreamlineView
2, Find Component
![Page 18: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/18.jpg)
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
![Page 19: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/19.jpg)
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
![Page 20: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/20.jpg)
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
![Page 21: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/21.jpg)
21
2121
StreamlineView extends Drilldown
StreamlineView
DrillDown
StreamlineView extends the DrillDown Class
3, Find Managing Method
![Page 22: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/22.jpg)
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
![Page 23: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/23.jpg)
23
2323
com.documentum.webcomponent.navigation.drilldown.DrillDown
3, Find Managing Method
![Page 24: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/24.jpg)
24
2424
Check the Class that was Extended
DrillDown {
onInit() { ….. }
onRefreshData() { ….. }
onClickBreadcrumb() { ….. }
updateControlsFromPath() { ….. }readConfig() { ….. }
}
3, Find Managing Method
![Page 25: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/25.jpg)
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
![Page 26: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/26.jpg)
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
![Page 27: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/27.jpg)
27
2727
com.documentum.custom.mystreamlineview.MyStreamlineView
4. Extend Component
![Page 28: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/28.jpg)
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
![Page 29: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/29.jpg)
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
![Page 30: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/30.jpg)
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());
}
![Page 31: WDK Applications Query Modification for … Applications Query Modification for Performance Oct 6-7, 2004 Performance Engineering Group 2 2 Agenda zBackground – The problem with](https://reader034.vdocuments.us/reader034/viewer/2022051308/5ad9c9447f8b9afc0f8b9e55/html5/thumbnails/31.jpg)
31
3131
Other Examples
Advanced Search (will be revamped in 5.3)– Strip Lower()