Performance using Cache

Below are some examples showing how having or not having cache, impacts performance.
To give a overall idea how ATG cache works please refer below diagram.(click to enlarge).

Note: To effectively use cache & to improve performance follow green path & avoid red.
If Cache is disabled - 
For example – “siteConfiguration” item-descriptor in SiteConfiguration Repository

  <item-descriptor name="siteConfiguration" cache-mode=" disabled" default="false">
              <table name="www_site_configuration" type="primary" id-column-name="APPLICATION_NAME">
                        <property name="configName" column-name="CONFIG_NAME" data-type="String"> </property>
                      ….  

Cache stats for this item in component browser

In Below example – querying for siteConfiguration.configName=”shopduringdowntime”

<%@ taglib uri="dsp" prefix="dsp"%>
<dsp:page>
<dsp:setvalue param="confName" value="shopduringdowntime"/>

<dsp:droplet name="/atg/dynamo/droplet/RQLQueryForEach">
  <dsp:param name="queryRQL" value="configName=:confName"/>
  <dsp:param name="repository" value="/repositories/siteConfiguration/SiteConfiguration"/>
  <dsp:param name="itemDescriptor" value="siteConfiguration"/>
  <dsp:oparam name="output">
     Config Value : <dsp:valueof param="element.configValue"/> <br>
  </dsp:oparam>
</dsp:droplet>

</dsp:page>

In logs –
First to get id (id-column-name) for the required item with given params (configName=”shopduringdowntime”)

**** debug      Mon Jan 30 16:09:53 CST 2012    1327961393403   /repositories/siteConfiguration/SiteConfiguration       [++SQLQuery++]
 SELECT t1.APPLICATION_NAME
   FROM www_site_configuration t1
  WHERE (t1.CONFIG_NAME = ?)
-- Parameters --
p[1] = {pd: configName} shopduringdowntime (java.lang.String)
[--SQLQuery--]

**** debug      Mon Jan 30 16:09:53 CST 2012    1327961393450   /repositories/siteConfiguration/SiteConfiguration       getItems(siteConfiguration: [quickshop])

Once we got the id load item (all properties) with that id

**** debug      Mon Jan 30 16:21:10 CST 2012    1327962070167   /repositories/siteConfiguration/SiteConfiguration       [++SQLSelect++]
  SELECT CONFIG_NAME,CONFIG_VALUE,APPLICATION_NAME,UPDATE_DTTM,CREATE_DTTM,CREATED_USER_ID,UPDATED_USER_ID,DESCRIPTION
   FROM www_site_configuration
  WHERE APPLICATION_NAME=?
-- Parameters --
p[1] = {pd} quickshop (java.lang.String)
[--SQLSelect--]

So total 2 queries are fired.  In this case, for every iteration 2 queries are fired.
If 2 iterations are there for same rql as below then 4 queries are fired.

<%@ taglib uri="dsp" prefix="dsp"%>
<dsp:page>
<dsp:setvalue param="confName" value="shopduringdowntime"/>

<%-- iteration 1 --%>
<dsp:droplet name="/atg/dynamo/droplet/RQLQueryForEach">
  <dsp:param name="queryRQL" value="configName=:confName"/>
  <dsp:param name="repository" value="/repositories/siteConfiguration/SiteConfiguration"/>
  <dsp:param name="itemDescriptor" value="siteConfiguration"/>
  <dsp:oparam name="output">
     Config Value : <dsp:valueof param="element.configValue"/> <br>
  </dsp:oparam>
</dsp:droplet>

<%-- iteration 2 --%>
<dsp:droplet name="/atg/dynamo/droplet/RQLQueryForEach">
  <dsp:param name="queryRQL" value="configName=:confName"/>
  <dsp:param name="repository" value="/repositories/siteConfiguration/SiteConfiguration"/>
  <dsp:param name="itemDescriptor" value="siteConfiguration"/>
  <dsp:oparam name="output">
     Config Value : <dsp:valueof param="element.configValue"/> <br>
  </dsp:oparam>
</dsp:droplet>

</dsp:page>

Transaction Cache
But if both are in same transaction like below –

<%@ taglib uri="dsp" prefix="dsp"%>
<dsp:page>
<dsp:setvalue param="confName" value="shopduringdowntime"/>

<%-- open transaction --%>
<dsp:droplet name="/atg/dynamo/transaction/droplet/Transaction">
  <dsp:param name="transAttribute" value="required"/>
  <dsp:oparam name="output">

<%-- iteration 1 --%>
<dsp:droplet name="/atg/dynamo/droplet/RQLQueryForEach">
  <dsp:param name="queryRQL" value="configName=:confName"/>
  <dsp:param name="repository" value="/repositories/siteConfiguration/SiteConfiguration"/>
  <dsp:param name="itemDescriptor" value="siteConfiguration"/>
  <dsp:oparam name="output">
     Config Value : <dsp:valueof param="element.configValue"/> <br>
  </dsp:oparam>
</dsp:droplet>

<%-- iteration 2 --%>
<dsp:droplet name="/atg/dynamo/droplet/RQLQueryForEach">
  <dsp:param name="queryRQL" value="configName=:confName"/>
  <dsp:param name="repository" value="/repositories/siteConfiguration/SiteConfiguration"/>
  <dsp:param name="itemDescriptor" value="siteConfiguration"/>
  <dsp:oparam name="output">
     Config Value : <dsp:valueof param="element.configValue"/> <br>
  </dsp:oparam>
</dsp:droplet>

 </dsp:oparam>
</dsp:droplet>
<%-- end transaction --%>

</dsp:page>

For iteration 1: 2 queries are fired.
But for iteration 2: only below query is fired

**** debug      Mon Jan 30 16:37:44 CST 2012    1327963064454   /repositories/siteConfiguration/SiteConfiguration       [++SQLQuery++]

  SELECT t1.APPLICATION_NAME

   FROM www_site_configuration t1

  WHERE (t1.CONFIG_NAME = ?)

-- Parameters --

p[1] = {pd: configName} shopduringdowntime (java.lang.String)

[--SQLQuery--]

Since above query is already cached in Transaction Cache, next query is not fired. So there by only 3 queries (instead of 4) are fired if both are in same transaction.

So If you have 10 same queries in different transaction in same thread, then total queries will be 10*2 = 20.
If you have 10 same queries in same transaction in same thread, then total queries will (1*2)+(9*1) = 11.

If Cache is enabled (& if query-cache is disabled)
For any cache mode (either simple/distributed/hybrid), by default item-cache-size is enabled (default is 1000) & query-cache-size is disabled (default is 0).
For example – “siteConfiguration” item-descriptor in SiteConfiguration Repository.

<item-descriptor name="siteConfiguration" cache-mode=" simple" default="false">
              <table name="www_site_configuration" type="primary" id-column-name="APPLICATION_NAME">
                        <property name="configName" column-name="CONFIG_NAME" data-type="String"> </property>
                      ….  

In Below example – querying for siteConfiguration.configName=”shopduringdowntime”.

<%@ taglib uri="dsp" prefix="dsp"%>
<dsp:page>
<dsp:setvalue param="confName" value="shopduringdowntime"/>

<dsp:droplet name="/atg/dynamo/droplet/RQLQueryForEach">
  <dsp:param name="queryRQL" value="configName=:confName"/>
  <dsp:param name="repository" value="/repositories/siteConfiguration/SiteConfiguration"/>
  <dsp:param name="itemDescriptor" value="siteConfiguration"/>
  <dsp:oparam name="output">
     Config Value : <dsp:valueof param="element.configValue"/> <br>
  </dsp:oparam>
</dsp:droplet>

</dsp:page>

First Iteration –
In logs -
First to get id (id-column-name) for the required item with given params (configName=”shopduringdowntime”)

**** debug      Mon Jan 30 17:14:05 CST 2012    1327965245245   /repositories/siteConfiguration/SiteConfiguration       [++SQLQuery++]

  SELECT t1.APPLICATION_NAME

   FROM www_site_configuration t1

  WHERE (t1.CONFIG_NAME = ?)

-- Parameters --

p[1] = {pd: configName} shopduringdowntime (java.lang.String)

[--SQLQuery--]

Next query is to load item for that id

**** debug      Mon Jan 30 17:14:05 CST 2012    1327965245276   /repositories/siteConfiguration/SiteConfiguration       [++SQLQuery++]
  SELECT t1.APPLICATION_NAME,t1.DESCRIPTION,t1.UPDATE_DTTM,t1.CREATED_USER_ID,t1.CREATE_DTTM,t1.CONFIG_VALUE,t1.UPDATED_USER_ID,t1.CONFIG_NAME
   FROM www_site_configuration t1
  WHERE t1.APPLICATION_NAME = ?
-- Parameters --
p[1] = {pd} quickshop (java.lang.String)
[--SQLQuery--]

Next Iterations –
Once below query is exected –

**** debug      Mon Jan 30 17:23:51 CST 2012    1327965831278   /repositories/siteConfiguration/SiteConfiguration       [++SQLQuery++]
  SELECT t1.APPLICATION_NAME
   FROM www_site_configuration t1
  WHERE (t1.CONFIG_NAME = ?)
-- Parameters --
p[1] = {pd: configName} shopduringdowntime (java.lang.String)
[--SQLQuery--]

When getItems(siteConfiguration: [quickshop]) is called, since “quickshop” item is already cached in item-cache,  another query is not required. So once item cached, for all next iterations querying for same item will have only 1 query (instead of 2, if query is disabled).

In above case

If you have 10 iterations without using item-cache, then total queries will be 10*2 = 20.
If you have 10 iterations using  item-cache, then total queries will (1*2)+(9*1) = 11.

Regarding Transaction cache while cache is enabled – Since items are already cached in item-cache having or not having transaction cache will result in same 1 query (above query to get id)

If Cache is enabled (& if query-cache is enabled)
If query-cache-size is also enabled (suppose size set is 1000) –
If you enable query-cache along with item-cache then the 1 query to get id’s is also not queried since that will be cached in query-cache until the query params are not changed.
For example – “siteConfiguration” item-descriptor in SiteConfiguration Repository.

 <item-descriptor name="siteConfiguration" cache-mode=" simple" default="false" item-cache-size="1000" query-cache-size="1000">

              <table name="www_site_configuration" type="primary" id-column-name="APPLICATION_NAME">

                        <property name="configName" column-name="CONFIG_NAME" data-type="String"> </property>

                      ….   

First Iteration –
In logs -
First to get id (id-column-name) for the required item with given params (configName=”shopduringdowntime”)

**** debug      Mon Jan 30 17:46:14 CST 2012    1327967174714   /repositories/siteConfiguration/SiteConfiguration       [++SQLQuery++]
 SELECT t1.APPLICATION_NAME
   FROM www_site_configuration t1
  WHERE (t1.CONFIG_NAME = ?)
-- Parameters --
p[1] = {pd: configName} shopduringdowntime (java.lang.String)
[--SQLQuery--]

Next query is to load item for that id.

**** debug      Mon Jan 30 17:46:14 CST 2012    1327967174745   /repositories/siteConfiguration/SiteConfiguration       [++SQLQuery++]
  SELECT t1.APPLICATION_NAME,t1.DESCRIPTION,t1.UPDATE_DTTM,t1.CREATED_USER_ID,t1.CREATE_DTTM,t1.CONFIG_VALUE,t1.UPDATED_USER_ID,t1.CONFIG_NAME
   FROM www_site_configuration t1
  WHERE t1.APPLICATION_NAME = ?
-- Parameters --
p[1] = {pd} quickshop (java.lang.String)
[--SQLQuery--]

Next Iterations –
No queries are fired, since above query is already cached.

In above case
If you have 10 iterations without using item-cache & query-cache, then total queries will be 10*2 = 20.
If you have 10 iterations using item-cache & query-cache, then total queries will (1*2)+(9*0) = 2.

Summary of above examples -
If cache is disabled & no transaction cache - for 10 iterations, 20 queries are fired.
If cache is disabled & using transaction cache - for 10 iterations, 11 queries are fired. (only for that transaction)
If item-cache is enabled & query-cache is disabled - for 10 iterations, 11 queries are fired.
If item-cache & query-cache enabled - for 10 iterations, 2 queries are fired.