ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jaggee j <jaggee...@yahoo.com>
Subject increase performance
Date Fri, 02 Sep 2005 06:07:44 GMT

Hello,

This is regarding performance issue for a web log analyzer application,which has following
environments,
  Apache 2.0.44 ,Tomcat 4.1.30, SQL Server 2000 with jdts JDBC driver
  struts , ibatis 2.0.x                                                                  
               

Issue :
i am finding poor response while executing SQL Maps through my DAO class.
i would like to increase the SQL map performance,please advice me what all the 
necessary points to be noted inorder to increase the performance.                

Process,
 - DB table "Log" contains 5 millions of records.
 - I've to fetch the records from this table according to users input and 
   display the log report (jFreechart) to browser.
 - Insertion of data to this table is different batch process.Its not at my scope.

Here is my sql-map-config , and SQL Map file.

-------------------------------------------- sql-map-config ---------------------------------------------------------

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapConfig
PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"
"http://www.ibatis.com/dtd/sql-map-config-2.dtd">

<sqlMapConfig>

  <properties resource="properties/database.properties"/>

  <settings
    cacheModelsEnabled="true"
    enhancementEnabled="true"
    maxSessions="128"
    maxTransactions="32"
    maxRequests="256"/>

  <transactionManager type="JDBC">
 <dataSource type="DBCP">
  <property name="JDBC.Driver" value="${driver}"/>
  <property name="JDBC.ConnectionURL" value="${url}"/>
  <property name="JDBC.Username" value="${username}"/>
  <property name="JDBC.Password" value="${password}"/>
  <property name="JDBC.DefaultAutoCommit" value="true" />
 
  <property name="Pool.MaximumActiveConnections" value="10"/>
  <property name="Pool.MaximumIdleConnections" value="5"/>
  <property name="Pool.MaximumWait" value="60000"/>
 
  <property name="Pool.ValidationQuery" value="select 1 from Contents_T"/>
  <property name="Pool.LogAbandoned" value="ture"/>
  <property name="Pool.RemoveAbandoned" value="true"/>
  <property name="Pool.RemoveAbandonedTimeout" value="50000"/>
 </dataSource>
  </transactionManager>

  <sqlMap resource="or/jp/apec/dao/sql/UserDaoSql.xml"/>
  ........
  ........
</sqlMapConfig>

---------------------------------------------CountryPageDaoSql---------------------------------------------------------------

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">

<sqlMap namespace="CountryPageDaoSql">

  <typeAlias alias="countryPageEntity" type="or.jp.apec.entity.CountryPageEntity"/>
  
  <resultMap id="countryResult"  class="countryPageEntity">   
 <result property="countryId_" column="countryid"/>
 <result property="countryName_" column="countryname"/>
 <result property="countryTotalAccCount_" column="hitcount"/>
  </resultMap>  
  
  <resultMap id="pageResult"  class="countryPageEntity">   
 <result property="pageTitle_" column="pagetitle"/>  
 <result property="pageTitleTotalAccCount_" column="pagehitcount"/>
 <result property="countryTotalAccCount_" column="totalcount"/>
 <result property="grandTotalAccCount_" column="grandtotalcount"/>
  </resultMap>
  
  <statement id="selectCountry_1300" resultMap="countryResult" >
  SELECT
   NewLog_T.country as countryid,
   Level1Domain_M.Country_j as countryname,
   COUNT(NewLog_T.csUriStem) as hitcount
  FROM 
   NewLog_T,
   Page_M,
   Level1Domain_M
  WHERE
   Level1Domain_M.Level1DomainName = NewLog_T.country 
   <isEqual prepend=" AND" property="syukeiKubunId_" compareValue="monthly">
    SUBSTRING(NewLog_T.date,1,4) = #syukeiYear_# AND 
    SUBSTRING(NewLog_T.date,6,2) = #syukeiMonth_# 
   </isEqual>
   <isEqual prepend=" AND" property="syukeiKubunId_" compareValue="daily">
    SUBSTRING(NewLog_T.date,1,4) = #syukeiYear_# AND 
    SUBSTRING(NewLog_T.date,6,2) = #syukeiMonth_# AND
    SUBSTRING(NewLog_T.date,9,2) = #syukeiDay_# 
   </isEqual>    
   <isEqual prepend=" AND" property="syukeiKubunId_" compareValue="other">
    CONVERT(CHAR(10),NewLog_T.date,112) &gt;= CONVERT(CHAR(10),REPLACE(#syukeiFrom_#,'/','-'),112)
AND
    CONVERT(CHAR(10),NewLog_T.date,112) &lt;= CONVERT(CHAR(10),REPLACE(#syukeiTo_#,'/','-'),112)
   </isEqual>
   <isNotEmpty prepend=" AND" property="apecURL_">
    #apecURL_#+NewLog_T.csUriStem = Page_M.URL 
   </isNotEmpty>
  GROUP BY
   NewLog_T.country,
   Level1Domain_M.Country_j 
  ORDER BY
   hitcount desc;
  </statement>
  
  <select id="selectPage_1300" resultMap="pageResult" >
  SELECT TOP 3
      Page_M.PageName as pagetitle,
      COUNT(NewLog_T.csUriStem) as pagehitcount,
      TotalCount.hitcount as totalcount,
      GrandTotalCount.hitcount as grandtotalcount
  FROM
      NewLog_T,
      Page_M,
      (SELECT
          sum(mid_TotalCount.mid_hitcount) as hitcount
      FROM
          (SELECT
              COUNT(NewLog_T.csUriStem) as mid_hitcount
          FROM
              NewLog_T,
              Page_M
          WHERE
    <isEqual prepend="" property="syukeiKubunId_" compareValue="monthly">
     SUBSTRING(NewLog_T.date,1,4) = #syukeiYear_# AND 
     SUBSTRING(NewLog_T.date,6,2) = #syukeiMonth_# 
    </isEqual>
    <isEqual prepend="" property="syukeiKubunId_" compareValue="daily">
     SUBSTRING(NewLog_T.date,1,4) = #syukeiYear_# AND 
     SUBSTRING(NewLog_T.date,6,2) = #syukeiMonth_# AND
     SUBSTRING(NewLog_T.date,9,2) = #syukeiDay_# 
    </isEqual>    
    <isEqual prepend="" property="syukeiKubunId_" compareValue="other">
     CONVERT(CHAR(10),NewLog_T.date,112) &gt;= CONVERT(CHAR(10),REPLACE(#syukeiFrom_#,'/','-'),112)
AND
     CONVERT(CHAR(10),NewLog_T.date,112) &lt;= CONVERT(CHAR(10),REPLACE(#syukeiTo_#,'/','-'),112)
    </isEqual>
    <isNotEmpty prepend=" AND" property="apecURL_">
     #apecURL_#+NewLog_T.csUriStem = Page_M.URL 
    </isNotEmpty>
    <isNotEmpty prepend=" AND" property="countryId_">
     NewLog_T.country = #countryId_#
    </isNotEmpty>
          GROUP BY
              Page_M.PageName
          ) as mid_TotalCount
      ) as TotalCount,
      (SELECT
          sum(mid_GrandTotalCount.mid_hitcount) as hitcount
      FROM
          (SELECT
              COUNT(NewLog_T.csUriStem) as mid_hitcount
          FROM
              NewLog_T,
              Page_M
          WHERE
    <isEqual prepend="" property="syukeiKubunId_" compareValue="monthly">
     SUBSTRING(NewLog_T.date,1,4) = #syukeiYear_# AND 
     SUBSTRING(NewLog_T.date,6,2) = #syukeiMonth_# 
    </isEqual>
    <isEqual prepend="" property="syukeiKubunId_" compareValue="daily">
     SUBSTRING(NewLog_T.date,1,4) = #syukeiYear_# AND 
     SUBSTRING(NewLog_T.date,6,2) = #syukeiMonth_# AND
     SUBSTRING(NewLog_T.date,9,2) = #syukeiDay_# 
    </isEqual>    
    <isEqual prepend="" property="syukeiKubunId_" compareValue="other">
     CONVERT(CHAR(10),NewLog_T.date,112) &gt;= CONVERT(CHAR(10),REPLACE(#syukeiFrom_#,'/','-'),112)
AND
     CONVERT(CHAR(10),NewLog_T.date,112) &lt;= CONVERT(CHAR(10),REPLACE(#syukeiTo_#,'/','-'),112)
    </isEqual>
    <isNotEmpty prepend=" AND" property="apecURL_">
     #apecURL_#+NewLog_T.csUriStem = Page_M.URL 
    </isNotEmpty>
          GROUP BY
              Page_M.PageName
          ) as mid_GrandTotalCount
      ) as GrandTotalCount
  WHERE
   <isEqual prepend="" property="syukeiKubunId_" compareValue="monthly">
    SUBSTRING(NewLog_T.date,1,4) = #syukeiYear_# AND 
    SUBSTRING(NewLog_T.date,6,2) = #syukeiMonth_# 
   </isEqual>
   <isEqual prepend="" property="syukeiKubunId_" compareValue="daily">
    SUBSTRING(NewLog_T.date,1,4) = #syukeiYear_# AND 
    SUBSTRING(NewLog_T.date,6,2) = #syukeiMonth_# AND
    SUBSTRING(NewLog_T.date,9,2) = #syukeiDay_# 
   </isEqual>    
   <isEqual prepend="" property="syukeiKubunId_" compareValue="other">
    CONVERT(CHAR(10),NewLog_T.date,112) &gt;= CONVERT(CHAR(10),REPLACE(#syukeiFrom_#,'/','-'),112)
AND
    CONVERT(CHAR(10),NewLog_T.date,112) &lt;= CONVERT(CHAR(10),REPLACE(#syukeiTo_#,'/','-'),112)
   </isEqual>
   <isNotEmpty prepend=" AND" property="apecURL_">
    #apecURL_#+NewLog_T.csUriStem = Page_M.URL 
   </isNotEmpty>
   <isNotEmpty prepend=" AND" property="countryId_">
    NewLog_T.country = #countryId_#
   </isNotEmpty>
  GROUP BY
      Page_M.PageName,
      TotalCount.hitcount,
      GrandTotalCount.hitcount
  ORDER BY
      pagehitcount desc;
  </select>     
</sqlMap>

Note :
1. This application SQL Map uses only <select> or <statement> ,
 there is no <insert> <update> <delete>. (How should i implement Cache model
for this case?)
2. <select id "selectPage_1300" ..> loops with different parameters according to <select
id "selectCountry_1300" ..> results.
3. other performance improvement solutions like Table index tuning(SQL Server), SQL query
tuning, tomcat and JVM tuning are in the process.

Thanks.



__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
Mime
View raw message