performance is slow while running a DB query using Struts <p>I am using Struts to build a web application. Application server is Tomcat Server 6, database is Oracle 11G. I am executing an action class which contains the following DB query:</p> <pre><code>SELECT BV_USER__TEMP.CEC_ID AS USER_ID, BV_USER_BEHRCHY_TEMP.LEVEL_NUM AS LEVEL_NUM, BV_IAM_BUSINESS_ENTITY_HIER.BUSINESS_ENTITY_HIER_NAME AS IAM__HIER_NAME, 'Total Development' AS TOTAL_ENGINEERING, BV_IAM_BUSINESS_ENTITY_HIER.BUSINESS_HIER_NAME AS keyCol, BV_USER_BE_HRCHY_TEMP.BUSINESS_ENTITY_DESCR AS val FROM BV_IAM_BUSINESS_ENTITY_HIER IAM INNER JOIN BV_SUB_BUSINESS_ENTITY BSE ON (BV_USER_BE_HRCHY_TEMP.SUB_BUSINESS_ENTITY_DESCR = BV_IAM_BUSINESS_ENTITY_HIER.BUSINESS_ENTITY_HIER_NAME) WHERE IAM_ROLE_NAME='FIN BI_USER LEVEL ACCESS' AND IAM_LEVEL_NUM=2 AND USER_ID=? GROUP BY 1,2,3,4,5,6 UNION ALL SELECT CEC_ID AS USER_ID, BUSINESS_ENTITY_HIER_NAME.IAM_LEVEL_NUM AS LEVEL_NUM, BUSINESS_ENTITY_HIER_NAME AS keyCol, BV_USER_BE_HRCHY_TEMP.BUSINESS_ENTITY_DESCR AS val FROM BV_IAM_BUSINESS_ENTITY_HIER IAM INNER JOIN BV_SUB_BUSINESS_ENTITY BSE ON (BUSINESS_ENTITY_NAME=BUSINESS_ENTITY_HIER_NAME) WHERE BV_IAM_BUSINESS_ENTITY_HIER.IAM_ROLE_NAME='FIN BI_USER LEVEL ACCESS' AND BV_IAM_BUSINESS_ENTITY_HIER.IAM_LEVEL_NUM=1 AND USER_id=2 GROUP BY 1,2,3,4,5,6 UNION ALL SELECT BV_USER_BE_HRCHY_TEMP.USER_ID, BV_IAM_BUSINESS_ENTITY_HIER.IAM_LEVEL_NUM AS LEVEL_NUM, BV_IAM_BUSINESS_ENTITY_HIER.BUSINESS_ENTITY_HIER_NAME AS IAM_ENTITY_HIER_NAME, 'Total Development' AS TOTAL_ENGINEERING, BUSINESS_ENTITY_HIER_NAME AS keyCol, BV_USER_BE_HRCHY_TEMP.BUSINESS_ENTITY_DESCR AS val FROM BV_IAM_BUSINESS_ENTITY_HIER IAM CROSS JOIN BV_USER_BE_HRCHY_TEMP.SUB_BUSINESS_ENTITY_DESCR BSE WHERE BV_IAM_BUSINESS_ENTITY_HIER.IAM_ROLE_NAME='FIN BI_USER LEVEL ACCESS' AND BV_IAM_BUSINESS_ENTITY_HIER.IAM_LEVEL_NUM=0 AND USER_ID=? GROUP BY 1,2,3,4,5,6; </code></pre> <p>In <code>applicationContext.xml</code> the bean:</p> <pre><code>&lt;bean id="tablOrclDataSourceRead" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"&gt; &lt;property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" /&gt; &lt;property name="url" value="jdbc:oracle:oci:@********DEV" /&gt; &lt;property name="username" value="username" /&gt; &lt;property name="password" value="password" /&gt; &lt;property name="initialSize" value="5" /&gt; &lt;property name="maxActive" value="5" /&gt; &lt;property name="maxIdle" value="0" /&gt; &lt;property name="minIdle" value="0" /&gt; &lt;property name="maxWait" value="-1" /&gt; &lt;/bean&gt; </code></pre> <p>JSON returned:</p> <pre><code>{"data":[ {"keyCol":"Utah","val":"Utah"}, {"keyCol":"Seattle","val":"Seattle"}, {"keyCol":Nebraska","val":"Nebraska"}, {"keyCol":"Enterprise Networking","val":"Enterprise Networking"} ]} </code></pre> <p>The time taken to execute the action (and return the JSON) with the above query is around 21 seconds.</p> <p>Please let me know if I am missing something.</p> <p>The same query when executed through SQL developer returns the result set in less than a second.</p>