nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

optimize SQL select statement in data sets

cmkatt · 2015-03-03T21:10:10+00:00

Hi there, i'm newbie in Maximo BIRT reporting. Would like to seek for inputs to optimize the my report performance below. Many thanks. Herewith attached with the report output and the rptdesign file. 1. MainPMDataSet // Add query to…

cmkatt

<p>Hi there,</p>
<p> </p>
<p> i'm newbie in Maximo BIRT reporting. Would like to seek for inputs to optimize the my report performance below. Many thanks.</p>
<p> </p>
<p>Herewith attached with the report output and the rptdesign file. </p>
<p> </p>
<p> </p>
<p> </p>
<p>1. MainPMDataSet</p>
<p> </p>
<div>// Add query to sqlText variable.</div>
<div>sqlText = "select distinct pmsequence.jpnum, (select description from jobplan where "</div>
<div>+ " jobplan.jpnum = pmsequence.jpnum) as pmseq_desc,pmsequence.pmnum, pm.description as pm_desc, "</div>
<div>+ " pm.siteid, pmsequence.interval, pm.frequency, frequnit, pm.supervisor, SUBSTRING(assetnum,4,9) as pm, "</div>
<div>+ " pm.assetnum, (select description from asset where asset.assetnum = pm.assetnum) "</div>
<div>+ " as asset_desc, (select eq1 from asset where asset.assetnum = pm.assetnum) as mfr, " </div>
<div>+ " (select eq2 from asset where asset.assetnum = pm.assetnum) as model, jobplanid, "</div>
<div>+ " (select serialnum from asset where asset.assetnum = pm.assetnum) as serialnum, "</div>
<div>+ " (select criticality from asset where asset.assetnum = pm.assetnum) as criticality, "</div>
<div>+ " pm.revisionnum as pm_rev, jobplan.revisionnum as jp_rev, pm.lastcompdate, safety_con, "</div>
<div>+ " PM.LASTSTARTDATE, DATEADD(M,(pmsequence.interval*pm.frequency),PM.LASTSTARTDATE ) as nxtduedate, "</div>
<div>+ " (select frequency from pmmeter where pmmeter.pmnum = pmsequence.pmnum) as meter_freq "</div>
<div>+ " from pmsequence "</div>
<div>+ " left outer join pm on pm.pmnum = pmsequence.pmnum and pm.siteid = pmsequence.siteid "</div>
<div>+ " left outer join jobplan on jobplan.jpnum = pmsequence.jpnum "</div>
<div>+ " where " + params["where"]</div>
<div>+ " and PM.USETARGETDATE = 1 order by interval "</div>
<div> </div>
<div> </div>
<div>2. SubJobPlanDataset</div>
<div> </div>
<div>
<div>// Add query to sqlText variable.</div>
<div>sqlText = "select distinct pmsequence.jpnum, (select description from jobplan where "</div>
<div>+ " jobplan.jpnum = pmsequence.jpnum) as pmseq_desc, pmsequence.interval, pm.frequency, frequnit, pm.supervisor, "</div>
<div>+ " (select frequency from pmmeter where pmmeter.pmnum = pmsequence.pmnum) as meter_freq, pmsequence.pmnum, jobplan.revisionnum as jp_rev1 "</div>
<div>+ " from pmsequence "</div>
<div>+ " left outer join pm on pm.pmnum = pmsequence.pmnum and pm.siteid = pmsequence.siteid "</div>
<div>+ " left outer join jobplan on jobplan.jpnum = pmsequence.jpnum "</div>
<div>+ " where " + params["where"]</div>
<div>+ " and pmsequence.pmnum = '" + rows[0]["pmnum"] + "' "</div>
<div>+ " and pmsequence.jpnum = '" + rows[0]["jpnum"] + "' "</div>
<div>+ " and PM.USETARGETDATE = 1 order by interval "</div>
<div> </div>
<div> </div>
<div>3. SubJobTaskDataSet</div>
<div> </div>
<div>
<div>// Add query to sqlText variable.</div>
<div>sqlText = " select pmsequence.pmnum, pmsequence.jpnum,jobtask.jptask, jobtask.description, jobtask.jobplanid, ldtext "</div>
<div>+ " from jobtask "</div>
<div>+ " left join pmsequence on jobtask.jpnum = pmsequence.jpnum "</div>
<div>+ " left outer join longdescription on jobtask.jobtaskid = ldkey and ldownertable = 'JOBTASK' and ldownercol = 'DESCRIPTION' "</div>
<div>+ " where pmsequence.pmnum = '" + rows[0]["pmnum"] + "' "</div>
<div>+ " and jobtask.jpnum = '" + rows[0]["jpnum"] + "' "</div>
<div>+ " and jobtask.jobplanid = '" + rows[0]["jobplanid"] + "' "</div>
<div>+ " order by jobtask.jptask "</div>
</div>
</div>
<p> </p>

Find more posts tagged with

Comments

JFreeman

<p>Are you experiencing performance issues you have determined to be caused because of the query?</p>

wwilliams

<p>Try using a join for asset and add siteid. Your queries will most likely not use out of the box indexes without using it.</p>
<p> </p>
<p>select distinct pmsequence.jpnum, jobplan.description pmseq_desc,</p>
<div>pmsequence.pmnum, pm.description as pm_desc,</div>
<div> pm.siteid, pmsequence.interval, pm.frequency, frequnit, pm.supervisor, SUBSTRING(asset.assetnum,4,9) as pm,</div>
<div> pm.assetnum, asset.description as asset_desc, asset.eq1 mfr, </div>
<div> asset.eq2 as model, jobplanid,</div>
<div> asset.serialnum,asset.criticality , pm.revisionnum as pm_rev, jobplan.revisionnum as jp_rev,</div>
<div> pm.lastcompdate, safety_con,</div>
<div> PM.LASTSTARTDATE, DATEADD(M,(pmsequence.interval*pm.frequency),PM.LASTSTARTDATE ) as nxtduedate,</div>
<div> (select frequency from pmmeter where pmmeter.pmnum = pmsequence.pmnum and pmmeter.siteid =pmsequence.siteid ) as meter_freq</div>
<div> </div>
<div> from pmsequence</div>
<div> left outer join pm on pm.pmnum = pmsequence.pmnum and pm.siteid = pmsequence.siteid</div>
<div> join asset on pm.assetnum = asset.assetnum and pm.siteid = asset.siteid</div>
<div> left outer join jobplan on jobplan.jpnum = pmsequence.jpnum and jobplan.siteid = pmsequence.siteid</div>
<div> where </div>
<div>--+ " where " + params["where"] and</div>
<div> PM.USETARGETDATE = 1 order by interval</div>
<div> </div>
<div>You need to update the others as well</div>
<div>
<p>Also you'll probably get bitten using + " where " + params["where"] as it has been used in your main dataset so it is unnecessary in your <span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">SubJobPlanDataset</span></p>
<p> </p>
<div>+ " where " + params["where"]</div>
<div>+ " and pmsequence.pmnum = '" + rows[0]["pmnum"] + "' "</div>
<div>+ " and pmsequence.jpnum = '" + rows[0]["jpnum"] + "' "</div>
</div>
<div> <span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">select distinct pmsequence.jpnum, (select description from jobplan where "</span></div>
<div style="margin:0px;color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">+ " jobplan.jpnum = pmsequence.jpnum) </div>
<div style="margin:0px;color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;"> </div>
<div style="margin:0px;color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">As you already have a join a subselect is not needed to get the description and add siteid

</div>
<div style="margin:0px;color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;"><span>+ " left outer join jobplan on jobplan.jpnum = pmsequence.jpnum "</span></div>
<div style="margin:0px;color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;"> </div>
<div style="margin:0px;color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;"> </div>
<div style="margin:0px;color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">HTH</div>

cmkatt

<p>Hi William,</p>
<p> Thanks for the heads up. no wonder i have multi row of jobplan.. haha... thanks for your time and appreciate your help. :-) </p>