Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
Clear All
new posts

    Performance of DSResponse.getRecords()

    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();
    }

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

    Comment


      #3
      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.


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

      Comment


        #4
        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.

        Comment


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

          Originally posted by 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.

          Comment


            #6
            Ahhh...

            getDataList() is what I really want!

            Comment

            Working...
            X