RE: increase performance

[Jean-Francois Poilpret]

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:[EMAIL PROTECTED] 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