Performance of DSResponse.getRecords()

[aarontsmith]

When I run a getRecords() from a DSResponse on the server, it can
take 15-20 seconds. There are 1425 records in the DSResponse from
an SQL datasource. Is this to be expected? The actual fetch of the
records takes around 3 seconds and this is search for records out of
a table with 50,000,000 entries.

On a related note, is there a way to get one record at a time to avoid
having to convert the entire group of records to an intermediate
data structure before consuming the data?

Here is the datasource description:

<DataSource ID="historical_timeseries_all" serverType="sql"
tableName="historical_timeseries" autoDeriveSchema="false"
mappedBeanClass="com.proliphix.sst.server.reports.ZoneDataBean">
<fields>
<field name="id" type="bigint" />
<field name="stamped_id" type="bigint" primaryKey="true" />
<field name="modified_timestamp" type="datetime" />
<field name="therm_average_temp" type="integer"/>
<field name="therm_setback_heat" type="integer"/>
<field name="therm_setback_cool" type="integer"/>
<field name="therm_relative_humidity" type="integer"/>
<field name="sensor0" type="integer"/>
<field name="sensor1" type="integer"/>
<field name="sensor2" type="integer"/>
<field name="sensor3" type="integer"/>
<field name="sensor4" type="integer"/>
<field name="therm_hvac_state" type="integer" />
<field name="therm_fan_state" type="integer" />
<field name="therm_setback_status" type="integer" />
<field name="therm_config_auxiliary_heat" type="integer" />
<field name="therm_config_heat" type="integer" />
<field name="therm_hvac_state_delay_reason" type="integer" />
</fields>

<operationBindings>
<operationBinding operationType="fetch-quanta">
<selectClause>stamped_id,modified_timestamp,therm_hvac_state,therm_fan_state,
therm_setback_status,therm_config_auxiliary_heat,
therm_config_heat,therm_hvac_state_delay_reason,
therm_average_temp,therm_setback_heat,therm_setback_cool,
therm_relative_humidity,sensor0,sensor1,sensor2,sensor3,sensor4
</selectClause>
<tableClause>fn_graph_all( $criteria.start_time::timestamp,
$criteria.end_time::timestamp, ARRAY[ $criteria.pro_ids ],
$criteria.quanta)</tableClause>
<whereClause>(1 = 1)</whereClause>
</operationBinding>
</operationBindings>

<operationBindings>
<operationBinding operationType="fetch">
<selectClause>stamped_id,modified_timestamp,therm_hvac_state,therm_fan_state,
therm_setback_status,therm_config_auxiliary_heat,
therm_config_heat,therm_hvac_state_delay_reason,
therm_average_temp,therm_setback_heat,therm_setback_cool,
therm_relative_humidity,sensor0,sensor1,sensor2,sensor3,sensor4
</selectClause>
<tableClause>fn_graph_all( $criteria.start_time::timestamp,
$criteria.end_time::timestamp, ARRAY[ $criteria.pro_ids ])</tableClause>
<whereClause>(1 = 1)</whereClause>
</operationBinding>
</operationBindings>

<schema>public</schema>
<dbName></dbName>
<dataSourceVersion>1</dataSourceVersion>
<generatedBy>SC_SNAPSHOT-2011-01-06/EVAL Deployment 2011-01-06
</generatedBy>
</DataSource>

Here is the code to use the datasource:

public long fetch() throws Exception
{
DSRequest childReq;
if ( quanta > 1 ) {
childReq = new DSRequest(dataSource,"fetch-quanta");
} else {
childReq = new DSRequest(dataSource,"fetch");
}

if ( startTime == null || endTime == null || quanta == null || id == null) {
return 0;
}

Map<String,Object> hm = new HashMap<String, Object>();

//hm.put("pro_ids", Integer.valueOf(17825));
hm.put("pro_ids", Integer.valueOf(id));
hm.put("start_time", startTime);
hm.put("end_time",endTime);
hm.put("quanta",Integer.valueOf(quanta));
childReq.setCriteria(hm);

childResponse = childReq.execute(); // takes 3 seconds
data = childResponse.getRecords(); // takes 15-20 secons

return childResponse.getRowCount();
}

[Isomorphic]

Can you not just do a fetch with a PK value? That's the way to get a fetch of a particular record.

[aarontsmith]

I need all 1400 records to generate the graph.
I think it is more efficient to go into the dbase one time rather
than 1400 times if I am understanding your response.

Can you not just do a fetch with a PK value? That's the way to get a fetch of a particular record.

[Isomorphic]

It's not necessary to call getRecords() in this case, which is designed to introspect properties from JPA/Hibernate results consisting of POJOs. Just call getData() directly instead.

[aarontsmith]

getRecord() just returns the first record of the list as far as I can
tell. Repeated calls to getRecord() return the same record.

It's not necessary to call getRecords() in this case, which is designed to introspect properties from JPA/Hibernate results consisting of POJOs. Just call getData() directly instead.

[aarontsmith]

Ahhh...

getDataList() is what I really want!