Data Template Building & Performance
I have been working with data templates for the last few daze ... we are doing some work on getting some performance numbers - at last. I have been building some more complex data templates than those I have built previously on the blog. The engine is extremely powerful and can do alot of the heavy lifting for you when it comes to grouping hierarchy and summary amounts.
Here's my latest.
<dataTemplate name="EMPLOYEES" defaultPackage="" dataSourceRef="demo-hr">
<properties>
<property name="include_parameters" value="true"/>
<property name="include_null_Element" value="true"/>
<property name="include_rowsettag" value="false"/>
<property name="scalable_mode" value="off"/>
<property name="db_fetch_size" value="300"/>
</properties>
<parameters/>
<lexicals/>
<dataQuery>
<sqlStatement name="LOCATIONS" dataSourceRef="">
<![CDATA[select COUNTRIES.COUNTRY_NAME as COUNTRY,
REGIONS.REGION_NAME as REGION,
LOCATIONS.STREET_ADDRESS as STREET_ADDRESS,
LOCATIONS.POSTAL_CODE as POSTAL_CODE,
LOCATIONS.CITY as CITY,
LOCATIONS.STATE_PROVINCE as STATE_PROVINCE,
LOCATIONS.LOCATION_ID as LOCATION_ID1
from HR.LOCATIONS LOCATIONS,
HR.COUNTRIES COUNTRIES,
HR.REGIONS REGIONS
where COUNTRIES.REGION_ID=REGIONS.REGION_ID
and COUNTRIES.COUNTRY_ID=LOCATIONS.COUNTRY_ID]]>
</sqlStatement>
<sqlStatement name="DEPTS" dataSourceRef="">
<![CDATA[select DEPARTMENTS.DEPARTMENT_ID as DEPARTMENT_ID1,
DEPARTMENTS.DEPARTMENT_NAME as DEPARTMENT_NAME
from HR.DEPARTMENTS DEPARTMENTS
where DEPARTMENTS.LOCATION_ID = :LOCATION_ID1]]>
</sqlStatement>
<sqlStatement name="EMPLOYEES" dataSourceRef="">
<![CDATA[select EMPLOYEES.EMPLOYEE_ID as EMPLOYEE_ID,
EMPLOYEES.FIRST_NAME||' '||EMPLOYEES.LAST_NAME as EMP_NAME,
EMPLOYEES.EMAIL as EMAIL,
EMPLOYEES.PHONE_NUMBER as PHONE_NUMBER,
EMPLOYEES.HIRE_DATE as HIRE_DATE,
EMPLOYEES.SALARY as SALARY,
EMPLOYEES.DEPARTMENT_ID as DEPT_ID,
JOBS.JOB_TITLE as JOB_TITLE
from HR.JOBS JOBS,
HR.EMPLOYEES EMPLOYEES
where EMPLOYEES.JOB_ID=JOBS.JOB_ID
and department_id = :DEPARTMENT_ID1 ]]>
</sqlStatement>
</dataQuery>
<dataStructure>
<group name="G_REGION" source="LOCATIONS" groupFilter="">
<element name="LOC_REGION" value="REGION" function=""/>
<group name="G_LOC" source="LOCATIONS" groupFilter="">
<element name="LOC_ADDR" value="STREET_ADDRESS" function=""/>
<element name="LOC_CITY" value="CITY" function=""/>
<element name="LOC_CODE" value="POSTAL_CODE" function=""/>
<element name="LOC_STATE" value="STATE" function=""/>
<element name="LOC_COUNTRY" value="COUNTRY" function=""/>
<group name="G_DEPT" source="DEPTS" groupFilter="">
<element name="DEPT_NUM" value="DEPARTMENT_ID1" function=""/>
<element name="DEPT_NAME" value="DEPARTMENT_NAME" function=""/>
<element name="DEPT_SALARY" value="G_EMP.SALARY" function="sum()"/>
<element name="DEPT_COUNT" value="G_EMP.EMPLOYEE_ID" function="count()"/>
<group name="G_JOB" source="EMPLOYEES" groupFilter="">
<element name="JOB_TITLE" value="JOB_TITLE" function=""/>
<group name="G_EMP" source="EMPLOYEES" groupFilter="">
<element name="EMPNO" value="EMPLOYEE_ID" function=""/>
<element name="EMP_NAME" value="EMP_NAME" function=""/>
<element name="HIRE_DATE" value="HIRE_DATE" function=""/>
<element name="PHONE_NUMBER" value="PHONE_NUMBER" function=""/>
<element name="EMAIL" value="EMAIL" function=""/>
<element name="SALARY" value="SALARY" function=""/>
</group>
</group>
</group>
</group>
</group>
</dataStructure>
</dataTemplate>
Maybe not such a real data case but it shows off some of what you can do with the data engine. You can see I have 3 queries using bind variables LOCATIONS -> DEPARTMENTS -> EMPLOYEES. Now, rather than just then render the three levels in the grouping hierarchy I have created 5 levels:
G_REGION
|
|_ G_LOCATION
|
|_ G_DEPT
|
|_ G_JOB
|
|_G_EMP
I have introduced the G_REGION and G_JOB levels into the data. Even thou we only have 3 levels in the queries the engine will calculate the hierarchies to be applied to the data.
The one drawback with the data templates is the fact that we do not have a builder for creating the templates. Good news on the way, I have been testing a new data template builder. This allows you to build your queries,
set appropriate engine flags
then bind them together into the grouping hierarchy
and finally set properties for the individual columns.
Its a nice tool - needs a little tweaking but its very close - of course usual disclaimers apply and it may look completely different by the time it hits the shelves. Look for it in a release coming soon ... performance numbers will come sooner.
