ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jean-Francois Poilpret" <jfpoilp...@hcm.vnn.vn>
Subject RE: increase performance
Date Fri, 02 Sep 2005 07:12:12 GMT
Hi,

 

>From what I can see, this is not in iBATIS that you should look for better
performance but in your SQL select statements.

It is always a bad idea, in a where clause, to use a function on an indexed
column like in:

    SUBSTRING(NewLog_T.date,1,4) = #syukeiYear_# AND 



This way, the index will never be used: be prepared for a full table scan.
If your table has millions of rows, then you can go and take a coffee (maybe
several) until the request is terminated.

 

So my advice is really to optimize your SQL (this might also require
changing table structure and adding indexes). Then when you could check its
performance directly (I mean without iBATIS) you can start (only if
necessary, and I don't believe it would be) to think about iBATIS
optimizations.

 

Cheers

 

            Jean-Francois

 

  _____  

From: jaggee j [mailto:jaggee_05@yahoo.com] 
Sent: Friday, September 02, 2005 1:08 PM
To: user-java@ibatis.apache.org
Subject: increase performance

 

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>
    <isNotEmpt y 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