 |
|||||
|
As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Join the TIBCO Community
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community. | |||||
Focal Point 
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point How to optimize Chained drop down lists
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point How to optimize Chained drop down lists Read-Only TopicGo | Search | Notify | Tools |
| How to optimize Chained drop down lists | Login/Join |
| Member |
I have an HTML Launch page that has several chained dynamic drop down lists based on 2 Oracle tables. This method works great! However, I recently added a product drop down list to my lauch page and the initial population slowed from 10 seconds to 2 minutes. How do I capture the SQL select that is being created in the back ground in order to optimize this process or am I doing something wrong in my html? Here's a stripped down version my HTML launch page ---------------------
<HTML>
<HEAD>
<script id=IBI_OptionsScript type=text/javascript>
var rltVersion = "76";
var cgipath = "cgipath";
var ibirls = "ibirls2";
var rltdyncalendar = "rltdyncalendar";
var olap="olap";
var olappanebase="olappanebase";
var olapdrill="olapdrill";
var ibixmltree="ibixmltree";
var ibiOptions = new Array(cgipath,ibirls);
</SCRIPT>
<script id=IBI_nls src="/ibi_html/javaassist/nls.js" type=text/javascript></SCRIPT>
<script id=IBI_ibigbl src="/ibi_html/javaassist/ibi/html/js/ibigbl.js" type=text/javascript></SCRIPT>
<script id=IBI_ibigblloadCss type=text/javascript>
ibigblloadCss(null);</SCRIPT>
<script id=IBI_RelCallBack type=text/javascript>function AdjustChildrenPosition(){
}
</SCRIPT>
<TITLE>HtmlPage</TITLE>
<LINK id=ITEM2 href="/smards_html/menuparm.css" type=text/css rel=stylesheet>
<script language=JavaScript id=ITEM3 src="/smards_html/smards.js" type=text/javascript></SCRIPT>
<script id=clientEventHandlersJS type=text/javascript>
//Begin function window_onload
function window_onload() {
UpdateData();
// TODO: Add your event handler code here
}
//End function window_onload
</SCRIPT>
<script for=window eventname="onload">window.onload = function() { window_onload(); }</SCRIPT>
</HEAD>
<body>
<FORM id=form2 style="z-index: 2; left: 27; position: absolute; top: 30 " tabIndex=2 name=form2 onsubmit="OnExecute[this);return false;" method=post requests_list="0" form_dist_between_desc_and_input="10" form_hor_dist_between_controls="10" vert_dist_between_controls="10" form_number_of_columns="5" form_number_of_visible_rows="5" form_prompt_location="1" default_slider_type="4" fexlist_list="report1">
<INPUT id=ibiapp_app style="LEFT: -100px; POSITION: absolute; TOP: -100px" type=hidden value="" name=ibiapp_app>
<table style="table-layout: fixed" id="table1" cellspacing="0" >
<tr><td class="bodheadlogol" width="59"> </td><td class="bodheadleft" width="42"> </td><td >
<a class="bodheadcell"> Product Family Sales by Product</a></td><td class="bodheadright" width="90"> </td><td class="bodheadlogor" width="90"> </td></tr>
</table>
<FIELDSET id=form2_formbodyid
style="PADDING-RIGHT: 0px; PADDING-LEFT: 0px; Z-INDEX: 5; LEFT: 5px;
PADDING-BOTTOM: 0px; OVERFLOW: auto; BORDER-TOP-STYLE: none;
PADDING-TOP: 0px; BORDER-RIGHT-STYLE: none; BORDER-LEFT-STYLE: none;
POSITION: static; TOP: 5px; BORDER-BOTTOM-STYLE: none" tabIndex=5 formbody="1">
<INPUT id=layoutinfo style="LEFT: -100px;
POSITION: absolute; TOP: -100px" type=hidden
inputcontrolids="cmb_DMKT;cmb_DREG;cmb_DTER;cmb_DCUS;cmb_DFAM;cmb_DPROD" resourcectrlids="ITEM2;ITEM3">
<div class="Section1" >
<table border="0" cellspacing="0" height="148" >
<tr>
<td width="115" class="prpnorm" bordercolorlight="#000000" bordercolordark="#000000" height="23" valign="bottom">Marketing</td>
<td width="160" class="prpnorm" bordercolorlight="#000000" bordercolordark="#000000" height="23" valign="bottom">Market
<font size="1" face="Arial Narrow">(Use Ctrl to select multiple)</font></td>
<td class="prpnorm" bordercolorlight="#000000" bordercolordark="#000000" valign="bottom" height="23" width="176">Region
<font size="1" face="Arial Narrow">(Use Ctrl to select multiple)</font></td>
<td class="prpnorm" bordercolorlight="#000000" bordercolordark="#000000" valign="bottom" height="23">Territory
<font size="1" face="Arial Narrow">(Use Ctrl to select multiple)</font></td>
</tr>
<tr>
<td width="115" bordercolorlight="#000000" bordercolordark="#000000" height="91" valign="top"><font color="#FF0000" size="1">[b]<u>Note:</u>[/b]
List Values take
5 to 10 sec to populate.
</font></td>
<td width="160" bordercolorlight="#000000" bordercolordark="#000000" height="91" valign="top">
<SELECT class=inpselect id=cmb_DMKT style="Z-INDEX: 2; width=170 "
tabIndex=2 size=5 name=DMKT newchainnumber="0"
sourcetype="typeMaster"
datafieldtype="CHAR" datatype="1" requiredfield="53643712"
datasource="DW_HAWE_CUSTOMER_HIERARCHY.mas"
datafield="SITE_PRIM_MKT_NAME"
displayfield="SITE_PRIM_MKT_NAME"
operation="OR" accept="0" addalloption="1" ibiformat="A25V" dfformat="A25V"
dynalldisplayvalue="ALL" defaultlocation="29,0,51,251"
chainnumber="0" inchainindex="1"
cacheruntimedata="1" MULTIPLE>
<OPTION value=ALL selected >ALL</OPTION>
</SELECT>
</td>
<td width="176" bordercolorlight="#000000" bordercolordark="#000000" valign="top" height="91">
<SELECT class=inpselect id=cmb_DREG style="Z-INDEX: 3; width=170 "
tabIndex=3 size=5 name=DREG newchainnumber="0"
sourcetype="typeMaster" datafieldtype="CHAR" datatype="1"
requiredfield="53643712"
datasource="DW_HAWE_CUSTOMER_HIERARCHY.mas"
datafield="SITE_PRIM_REG_NAME"
displayfield="SITE_PRIM_REG_NAME"
operation="OR" accept="0" addalloption="1" ibiformat="A25V" dfformat="A25V"
dynalldisplayvalue="ALL" defaultlocation="29,0,51,251"
chainnumber="0" inchainindex="2" cacheruntimedata="1" MULTIPLE>
<OPTION value=ALL selected >ALL</OPTION>
</SELECT>
</td>
<td bordercolorlight="#000000" bordercolordark="#000000" valign="top" height="91">
<SELECT class=inpselect id=cmb_DTER style="Z-INDEX: 4; width=170 "
tabIndex=4 size=5 name=DTER newchainnumber="0"
sourcetype="typeMaster" datafieldtype="CHAR" datatype="1"
requiredfield="53643712"
datasource="DW_HAWE_CUSTOMER_HIERARCHY.mas"
datafield="SITE_PRIM_TER_NAME"
displayfield="SITE_PRIM_TER_NAME"
operation="OR" accept="0" addalloption="1" ibiformat="A25V" dfformat="A25V"
dynalldisplayvalue="ALL" defaultlocation="29,0,51,251"
chainnumber="0" inchainindex="3"
cacheruntimedata="1" MULTIPLE>
<OPTION value=ALL selected >ALL</OPTION>
</SELECT>
</td>
</tr>
</table>
<table border="0" cellspacing="0" height="185" >
<tr>
<td width="115" class="prpnorm" bordercolorlight="#000000" bordercolordark="#000000" height="97" > <SPAN valign="top">Customer:
<font size="1" face="Arial Narrow">(Use Ctrl to select multiple)</font></SPAN>
</td>
<td bordercolorlight="#000000" bordercolordark="#000000" height="97" colspan="3" >
<SELECT class=inpselect id=cmb_DCUS style="Z-INDEX: 5; WIDTH=350"
tabIndex=5 size=5
name=DCUS newchainnumber="0"
sourcetype="typeMaster" datafieldtype="CHAR" datatype="1"
requiredfield="53643712"
datasource="DW_HAWE_CUSTOMER_HIERARCHY.mas"
datafield="CUST_NUMBER"
displayfield="CUSTOMER"
operation="OR" accept="0" addalloption="1"
ibiformat="A30V"
dfformat="A81V"
dynalldisplayvalue="ALL"
defaultlocation="29,0,51,296"
chainnumber="0" inchainindex="4"
cacheruntimedata="1" MULTIPLE>
<OPTION value=ALL selected >ALL</OPTION>
</SELECT>
</td>
</tr>
<tr>
<td width="115" class="prpnorm" valign="top" bordercolorlight="#000000" bordercolordark="#000000" height="84" >
Family:
<font size="1" face="Arial Narrow">(Use Ctrl to select multiple)</font>
</td>
<td bordercolorlight="#000000" bordercolordark="#000000" height="84" >
<SELECT class=inpselect id=cmb_DFAM style="Z-INDEX: 6; width=170 "
tabIndex=6 size=4 name=DFAM newchainnumber="1"
sourcetype="typeMaster"
datafieldtype="CHAR" datatype="1" requiredfield="104459416"
datasource="DW_HAWE_PRODUCT_HIERARCHY.mas"
datafield="EUR_FAM_CAT_CHAPTER"
displayfield="EUR_FAM_CAT_CHAPTER_DESC"
operation="OR" accept="0" addalloption="1" ibiformat="A100V" dfformat="A40V"
dynalldisplayvalue="ALL" defaultlocation="29,0,51,251"
chainnumber="1" inchainindex="1"
cacheruntimedata="1" MULTIPLE>
<OPTION value=ALL selected >ALL</OPTION>
</SELECT>
</td>
<td class="prpnorm" bordercolorlight="#000000" bordercolordark="#000000" align="right" valign="top" height="84" >
Product: <font size="1" face="Arial Narrow">(Use Ctrl
to select multiple)
</font>
</td>
<td bordercolorlight="#000000" bordercolordark="#000000" height="84" >
<SELECT class=inpselect id=cmb_DPROD style="Z-INDEX: 7; WIDTH=350"
tabIndex=7
size=4
name=DPROD
newchainnumber="0"
sourcetype="typeMaster"
datafieldtype="CHAR"
datatype="1"
datasource="DW_HAWE_PRODUCT_HIERARCHY.mas"
datafield="PRODUCT_NUMBER"
displayfield="PRODUCT"
operation="OR" accept="0"
addalloption="1"
ibiformat="A30V"
dfformat="A50V"
dynalldisplayvalue="ALL"
defaultlocation="29,0,51,296"
chainnumber="1"
inchainindex="2"
cacheruntimedata="1"
MULTIPLE>
<OPTION value=ALL selected >ALL</OPTION>
</SELECT>
</td>
</tr>
</table>
<table border="0" cellspacing="0" cellpadding="0" >
<tr>
<td width="104" class="prpnorm" bordercolorlight="#000000" bordercolordark="#000000" height="18" valign="bottom">
<INPUT class=prpnorm id=form2Submit title=Run style="z-index: 14; left: 7; width: 35; color: #0000ff; background-repeat: no-repeat; font-family: Arial; background-color: transparent; text-decoration: underline; height: 19; border-left: 1px solid #808080; border-right: 1px solid #808080; padding-left: 1px; padding-right: 1px; padding-top: 0px; padding-bottom: 0px"
tabIndex=14 type=submit size=15 value=Run name=SUBMIT_BOTTON>
</p>
</td>
</tr>
</table>
<table border="0" cellspacing="0" >
<tr>
<td bordercolorlight="#000000" bordercolordark="#000000" height="30" ><INPUT id=ibiapp_app style="LEFT: -100px; POSITION: absolute; TOP: -100px" type=hidden value="" name=ibiapp_app>
</td>
</tr>
</table>
</div>
</FIELDSET>
<script id=IBI_ChainScript type=text/javascript>
< !--
window.chain0=new Array(1);
window.chain0[0]=new String("cmb_DMKT;cmb_DREG;cmb_DTER;cmb_DCUS");
window.chain0[1]=1
window.chain1=new Array(1);
window.chain1[0]=new String("cmb_DFAM;cmb_DPROD");
window.chain1[1]=1
//-->
</SCRIPT>
<xml id=ibi_requests>
<requests>
<request requestid="0"
sourcetype="typeFex" targettype="1" targetname="_blank"
ibif_ex="437SA440.fex"
ibic_server=""
ibiapp_app=""
paramremovedbyuser="DMKT&DREG&DTER&DCUS&DFAM&DPROD">
<variables>
<variable field="DMKT" file="" desc="DMKT" datatype="0" operation="" default="ALL" name="DMKT" textvarname="" accept="0" type="unresolved" select="0" min="" max="" controltype="7" create="1"></variable>
<variable field="DREG" file="" desc="DREG" datatype="0" operation="" default="ALL" name="DREG" textvarname="" accept="0" type="unresolved" select="0" min="" max="" controltype="7" create="1"></variable>
<variable field="DTER" file="" desc="DTER" datatype="0" operation="" default="ALL" name="DTER" textvarname="" accept="0" type="unresolved" select="0" min="" max="" controltype="7" create="1"></variable>
<variable field="DCUS" file="" desc="DCUS" datatype="0" operation="" default="ALL" name="DCUS" textvarname="" accept="0" type="unresolved" select="0" min="" max="" controltype="7" create="1"></variable>
<variable field="DFAM" file="" desc="DFAM" datatype="0" operation="" default="ALL" name="DFAM" textvarname="" accept="0" type="unresolved" select="0" min="" max="" controltype="7" create="1"></variable>
<variable field="DPROD" file="" desc="DPROD" datatype="0" operation="" default="ALL" name="DPROD" textvarname="" accept="0" type="unresolved" select="0" min="" max="" controltype="7" create="1"></variable>
</variables>
</request></requests>
</xml>
</form>
</BODY></FORM>
</HTML>---------------------------------------This message has been edited. Last edited by: Kerry, Bob | ||
|
| Expert |
In the "Developing Reporting Applications" manual, there is a section on 'Customizing Dynamic Parameters' which shows how to use a procedure to populate a drop down box. There is a small discussion on how to handle chained parameters. Writing a procedure with WHERE clauses can significantly improve the performance of populating drop down boxes as opposed to letting WebFOCUS use its generic query. Ginny --------------------------------- Prod: WF 7.7.01 Dev: WF 7.6.9-11 Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google | |||
|
| Master |
what you're talking about is basic query optimization. What database are you running? Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database. | |||
|
| Member |
I'm running on Windows Server 2003 Standarde Edition, Webfocus version 7.6.1, connecting to an Oracle 9i database. The retrieving data msg appears then is replaced by the Populating controls msg very quickly. Then the populating controls msg stays for approx. 2 minutes. This makes me believe that the problem is more in how java handles the data after retrieval. If java is the problem, as I believe, is there any way to format or presort the data that will allow java to more efficiently build the chained drop down lists? Thanks for Your Help! Bob | |||
|
| Member |
Thanks Ginny, I'll take a look at manual... Bob | |||
|
| Expert |
Since you are running 7.6.1, I must tell you that one of my users discovered a 5.3.3 created page with a drop down, when run under 7.6.1 ran very slowly and eventually got a script timeout message. If you reply to continue, the drop down boxed actually loaded but it did take a long time. And she was not using a chained list, just a single drop down box. I opened a case on this and with further testing did discover, as you mentioned, that it was the 'populating controls' that was the problem as opposed to retrieving the data. The manual reference I gave you is a good technique but this may not be what you are experiencing right now. I don't know if you created this page with 7.6 or a previous release. I have had problems with ibirls2 in converted pages as well regarding drop down boxes and have had to change back to ibirls and I've also received a skinny version of ibirls2 to use. Ginny --------------------------------- Prod: WF 7.7.01 Dev: WF 7.6.9-11 Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google | |||
|
| Master |
Talk to your DBA, see if they can put a performance monitor on the IBI user, then load your page. They can view the explain plan and tell you where there are issues that need to be optimized. Since these are CHAINED, you should create indexes on the fields that are chained together. For example, if you have a table of Region Division Store then you would want at least two indexes, One for Region, Store, ... the other for Region, Disision, Store...sorted ascending. Only the Oracle optimizer knows if your indexes, or a full table scan will be used for your chaining. As a rule of thumb for most RDMS optimizer, if you're querying over 20% of the table, the optimizer will do a full table scan, so your indexes aren't so great. When you're chaining, you should be eliminating a lot of the results... On my HTML forms, large data sets take a while to "populating controls..." but my tables are very well index, and the optimizer is hitting the exact indexes that it should. I have a list of 20,000 customers that I set up to populate as a test just now, it takes right at 10 seconds when I set a limit of 1000 values. When I take off the limit it takes so long that I have yet to see it finish, and DB2 performance monitor shows query time of .05 econds. I don't think this is actually something that can be optimized database side....with these many values I guess the javascript is just going to take a very very long time.This message has been edited. Last edited by: Jason K., Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database. | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point How to optimize Chained drop down lists
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point How to optimize Chained drop down listsCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |