source: trunk/www/views/experiments/explorer/search/list.jsp @ 5711

Last change on this file since 5711 was 5711, checked in by Nicklas Nordborg, 10 years ago

References #1618: Improve "Reporter search" performance in experiment explorer

Different implementation on "Reporter search" that uses the cached reporter ids and use a regular query against the Reporters table only. As a result, the "Create reporter list" function needed to fixed as it can no longer use the query. Instead it also uses the cached reporter ids.

Added filter condition for bioassays when loading the "Reporter view" page since it seems to be a lot quicker (see comment 4).

Fixed an issue with generating incorrect SQL when filtering on a reporter list. I think this broke as part of #903, but has gone unnoticed since then. A join to the reporter list table was missing due to no auto-join support in DynamicPositionQuery and DynamicReporterQuery. Auto-join support has now been added to those query implementations.

Everything is a lot quicker now on my development machine. I have 2.5M reporters in the reporter table and an experiment with 24M rows of spot data in 500 bioassays. So far no action seems to take more than a couple of seconds to complete.

  • Property svn:eol-style set to native
  • Property svn:keywords set to Date Id
File size: 10.4 KB
Line 
1<%-- $Id: list.jsp 5711 2011-08-30 13:49:58Z nicklas $
2  ------------------------------------------------------------------
3  Copyright (C) 2006 Johan Enell, Nicklas Nordborg
4  Copyright (C) 2007 Johan Enell
5
6  This file is part of BASE - BioArray Software Environment.
7  Available at http://base.thep.lu.se/
8
9  BASE is free software; you can redistribute it and/or
10  modify it under the terms of the GNU General Public License
11  as published by the Free Software Foundation; either version 3
12  of the License, or (at your option) any later version.
13
14  BASE is distributed in the hope that it will be useful,
15  but WITHOUT ANY WARRANTY; without even the implied warranty of
16  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
17  GNU General Public License for more details.
18
19  You should have received a copy of the GNU General Public License
20  along with BASE. If not, see <http://www.gnu.org/licenses/>.
21  ------------------------------------------------------------------
22
23  @author Nicklas
24  @version 2.0
25--%>
26<%@ page pageEncoding="UTF-8" session="false"
27  import="net.sf.basedb.core.SessionControl"
28  import="net.sf.basedb.core.DbControl"
29  import="net.sf.basedb.core.Item"
30  import="net.sf.basedb.core.ItemContext"
31  import="net.sf.basedb.core.Permission"
32  import="net.sf.basedb.core.BioAssaySet"
33  import="net.sf.basedb.core.Experiment"
34  import="net.sf.basedb.core.RawDataType"
35  import="net.sf.basedb.core.ExtendedProperties"
36  import="net.sf.basedb.core.ExtendedProperty"
37  import="net.sf.basedb.core.DynamicReporterQuery"
38  import="net.sf.basedb.core.DynamicResultIterator"
39  import="net.sf.basedb.core.VirtualColumn"
40  import="net.sf.basedb.core.data.ReporterData"
41  import="net.sf.basedb.core.query.Restrictions"
42  import="net.sf.basedb.core.query.Expressions"
43  import="net.sf.basedb.core.query.Dynamic"
44  import="net.sf.basedb.core.query.Orders"
45  import="net.sf.basedb.core.query.Selects"
46  import="net.sf.basedb.core.query.Aggregations"
47  import="net.sf.basedb.core.query.JoinType"
48  import="net.sf.basedb.core.query.SqlResult"
49  import="net.sf.basedb.clients.web.Base"
50  import="net.sf.basedb.clients.web.DynamicUtil"
51  import="net.sf.basedb.clients.web.ExperimentExplorer"
52  import="net.sf.basedb.clients.web.WebException"
53  import="net.sf.basedb.util.Values"
54  import="net.sf.basedb.clients.web.util.HTML"
55  import="net.sf.basedb.clients.web.taglib.table.TableColumn"
56  import="java.util.Date"
57  import="java.util.Map"
58  import="java.util.HashMap"
59  import="java.util.List"
60  import="java.util.LinkedList"
61%>
62<%@ taglib prefix="base" uri="/WEB-INF/base.tld" %>
63<%@ taglib prefix="tbl" uri="/WEB-INF/table.tld" %>
64<%@ taglib prefix="t" uri="/WEB-INF/tab.tld" %>
65<%@ taglib prefix="p" uri="/WEB-INF/path.tld" %>
66<%!
67  private static final Item itemType = Item.REPORTER;
68%>
69
70<%
71final SessionControl sc = Base.getExistingSessionControl(pageContext, true);
72final String ID = sc.getId();
73final float scale = Base.getScale(sc);
74final String root = request.getContextPath();
75
76final int bioAssaySetId = Values.getInt(request.getParameter("bioassayset_id"));
77final DbControl dc = sc.newDbControl();
78DynamicResultIterator reporters = null;
79long totalReporters = 0;
80try
81{
82  String title = null;
83  final BioAssaySet bioAssaySet = BioAssaySet.getById(dc, bioAssaySetId);
84  final Experiment experiment = bioAssaySet.getExperiment();
85  final RawDataType rawDataType = experiment.getRawDataType();
86 
87  final ExperimentExplorer explorer = ExperimentExplorer.getExplorer(bioAssaySet);
88  final ItemContext cc = explorer.getAndSetReporterContext(sc, null);
89  final String subContext = cc.getSubContext();
90
91  List<TableColumn> columns = new LinkedList<TableColumn>();
92  DynamicUtil.addReporterColumns(columns, dc, "", "@", "");
93 
94  DynamicUtil.SelectedInfo selected = DynamicUtil.getSelectedColumns(cc, columns, false);
95  Map<String, Integer> selectedIndex = new HashMap<String, Integer>();
96  for (int i = 0; i < selected.selectedIds.size(); ++i)
97  {
98    selectedIndex.put(selected.selectedIds.get(i), i+2);
99  }
100 
101  try
102  {
103    DynamicReporterQuery reporterQuery = explorer.getReporterQuery(dc, selected.selectedProperties);
104    totalReporters = explorer.getMatchingReporters(dc);
105    reporters = reporterQuery.iterate(dc);
106  }
107  catch (Throwable t)
108  {
109    cc.setMessage(t.getMessage());
110    t.printStackTrace();
111  }
112  int numListed = 0;
113  %>
114
115  <base:page title="<%=title%>">
116  <base:head scripts="table.js,tabcontrol.js,newjoust.js" styles="table.css,toolbar.css,headertabcontrol.css,path.css,newjoust.css">
117  <script language="JavaScript">
118    var submitPage = 'index.jsp';
119    var formId = 'reporters';
120    function editItem(itemId)
121    {
122      Main.viewOrEditItem('<%=ID%>', '<%=itemType.name()%>', itemId, true);
123    }
124    function viewItem(itemId)
125    {
126      Main.viewOrEditItem('<%=ID%>', '<%=itemType.name()%>', itemId, false);
127    }
128    function itemOnClick(evt, itemId)
129    {
130      Table.itemOnClick(formId, evt, itemId, 'DEFAULT', viewItem, editItem, null);
131    }   
132    function configureColumns()
133    {
134      Table.configureColumns('<%=ID%>', formId, '<%=itemType.name()%>', '<%=(String)cc.getObject("defaultColumns")%>', '<%=subContext%>');
135    }
136    function presetOnChange()
137    {
138      Table.presetOnChange('<%=ID%>', formId, '<%=itemType.name()%>', '<%=(String)cc.getObject("defaultColumns")%>', '<%=subContext%>');
139    }
140    function viewReporter(index)
141    {
142      var url = '../view/index.jsp?ID=<%=ID%>&bioassayset_id=<%=bioAssaySetId%>';
143      if (index >= 0)
144      {
145        url += '&cmd=SetReporterIndex&reporterIndex='+index;
146      }
147      location.href = url;
148    }
149
150    function switchTab(tabControlId, tabId)
151    {
152      if (tabId == 'view')
153      {
154        viewReporter(-1);
155      }
156      else
157      {
158        TabControl.setActiveTab(tabControlId, tabId);
159      }
160    }
161    function newReporterList()
162    {
163      Table.submitToPopup(formId, 'CreateReporterList', 540, 400);
164    }
165  </script>
166  </base:head>
167  <base:body>
168    <p>
169    <p:path>
170      <p:pathelement title="Experiments" href="<%="../../index.jsp?ID="+ID%>" />
171      <p:pathelement title="<%=HTML.encodeTags(experiment.getName())%>" 
172        href="<%="../../bioassaysets/index.jsp?ID="+ID+"&experiment_id="+experiment.getId()%>" />
173      <p:pathelement title="<%=HTML.encodeTags(bioAssaySet.getName())%>" 
174        href="<%="../../bioassaysets/index.jsp?ID="+ID+"&cmd=ViewItem&item_id="+bioAssaySetId%>" />
175      <p:pathelement title="Explorer" />
176    </p:path>
177   
178    <t:tabcontrol id="explorer" switch="switchTab" remember="false" active="search">
179    <t:tab id="search" title="Reporter search">
180    <%
181    if (cc.getMessage() != null)
182    {
183      %>
184      <div class="error"><%=cc.getMessage()%></div>
185      <%
186      cc.setMessage(null);
187    }
188    %>
189    <tbl:table 
190      id="reporters" 
191      clazz="itemlist" 
192      columns="<%=cc.getSetting("columns")%>"
193      sortby="<%=cc.getSortProperty()%>" 
194      direction="<%=cc.getSortDirection()%>"
195      title="<%=title%>"
196      action="index.jsp"
197      sc="<%=sc%>"
198      item="<%=itemType%>"
199      subcontext="<%=subContext%>"
200      >
201      <tbl:hidden 
202        name="bioassayset_id" 
203        value="<%=String.valueOf(bioAssaySetId)%>" 
204      />
205      <tbl:columndef 
206        id="reporterList"
207        property="£reporterList"
208        datatype="int"
209        title="Reporter list"
210        filterable="true"
211        enumeration="<%=Base.getReporterListsEnum(dc)%>"
212        multiple="false"
213      />     
214      <%
215      for (TableColumn tc : columns)
216      {
217        %>
218        <tbl:columndef
219          id="<%=tc.getId()%>"
220          clazz="<%="externalId".equals(tc.getId()) ? "uniquecol" : "columnheader"%>"
221          property="<%=tc.getProperty()%>"
222          datatype="<%=tc.getDatatype().getStringValue()%>"
223          title="<%=tc.getTitle()%>"
224          sortable="<%=tc.getSortable()%>"
225          filterable="<%=tc.getFilterable()%>"
226          exportable="<%=tc.getExportable()%>"
227          show="<%=tc.getShow()%>"
228          formatter="<%=tc.getFormatter()%>"
229        />
230        <%
231      }
232      %>
233       
234      <tbl:toolbar>
235        <tbl:button 
236          image="columns.gif" 
237          onclick="configureColumns()" 
238          title="Columns&hellip;" 
239          tooltip="Show, hide and re-order columns" 
240        />
241        <tbl:button
242          image="add.png"
243          onclick="newReporterList()"
244          title="New reporter list&hellip;"
245          tooltip="Create a new reporter list from matching reporters"
246          visible="<%=sc.hasPermission(Permission.CREATE, Item.REPORTERLIST)%>"
247        />
248      </tbl:toolbar>
249      <tbl:navigator
250        page="<%=cc.getPage()%>" 
251        rowsperpage="<%=cc.getRowsPerPage()%>" 
252        totalrows="<%=totalReporters%>" 
253      />
254      <tbl:data>
255        <tbl:columns>
256        <tbl:presetselector 
257          clazz="columnheader"
258          colspan="1"
259          onchange="presetOnChange()"
260        />
261        </tbl:columns>
262        <tr>
263          <tbl:header 
264            clazz="icons"
265            >&nbsp;</tbl:header>
266          <tbl:propertyfilter />
267        </tr>
268        <tbl:rows>
269       
270      <%
271      int index = cc.getPage()*cc.getRowsPerPage();
272      if (reporters != null)
273      {
274        while (reporters.hasNext())
275        {
276          SqlResult item = reporters.next();
277          int itemId = item.getInt(1);
278          index++;
279          numListed++;
280          %>
281          <tbl:row>
282            <tbl:header 
283              clazz="icons"
284              ><a href="javascript:viewReporter(<%=index-1%>)"><%=index%></a></tbl:header>
285            <tbl:cell column="name"><div class="link" onclick="itemOnClick(event, <%=itemId%>)" 
286              title="View this item (use CTRL, ALT or SHIFT to edit)"><%=HTML.encodeTags(item.getString(selectedIndex.get("name")))%></div></tbl:cell>
287            <tbl:cell column="externalId"><tbl:cellvalue value="<%=item.getString(selectedIndex.get("externalId"))%>"/></tbl:cell>
288            <tbl:cell column="symbol"><tbl:cellvalue value="<%=item.getString(selectedIndex.get("symbol"))%>"/></tbl:cell>
289            <tbl:cell column="description"><tbl:cellvalue value="<%=item.getString(selectedIndex.get("description"))%>"/></tbl:cell>
290            <tbl:cell column="lastUpdate"><tbl:cellvalue value="<%=item.getObject(selectedIndex.get("lastUpdate"))%>"/></tbl:cell>
291            <%
292            List<ExtendedProperty> reporterProperties = ExtendedProperties.getProperties("ReporterData");
293            if (reporterProperties != null)
294            {
295              for (ExtendedProperty ep : reporterProperties)
296              {
297                String name = ep.getName();
298                %>
299                <tbl:cell column="<%=name%>"><tbl:cellvalue value="<%=item.getObject(selectedIndex.get(name))%>" /></tbl:cell>
300              <%
301              }
302            }
303            %>
304          </tbl:row>
305          <%
306        }
307      }
308      %>
309      </tbl:rows>
310      </tbl:data>
311      <%
312      if (numListed == 0)
313      {
314        %>
315        <tbl:panel><%=totalReporters == 0 ? "No reporters were found" : "No reporters on this page. Please select another page!" %></tbl:panel>
316        <%
317      }
318      else
319      {
320        %>
321        <tbl:navigator
322          page="<%=cc.getPage()%>" 
323          rowsperpage="<%=cc.getRowsPerPage()%>" 
324          totalrows="<%=totalReporters%>"
325          locked="true"
326        />
327        <%
328      }
329      %>
330      </tbl:table>
331    </t:tab>
332
333    <t:tab id="view" title="Reporter view" />
334    </t:tabcontrol>
335
336  </base:body>
337  </base:page>
338  <%
339}
340finally
341{
342  if (reporters != null) reporters.close();
343  if (dc != null) dc.close();
344}
345
346%>
Note: See TracBrowser for help on using the repository browser.