ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Larry Meadors <larry.mead...@gmail.com>
Subject Re: increase performance
Date Fri, 02 Sep 2005 15:00:04 GMT
How much data are you getting back with your query? 10 rows? 100 rows?
1000 rows? 10000 rows?

If you are getting back more than you *need* for the report, you
should look at doing all the data processing with a stored procedure
instead of in Java code. This will improve performance in several ways
- fast data access, less network traffic, reduced object creation,
etc, etc...

Any time i see an SQL statement more than a dozen or so lines long, I
think there has to be a better way. Can you summarize that data in a
batch process?

Optimizing SQL is not always the easiest route - think outside the box. ;-)

Larry


On 9/2/05, jaggee j <jaggee_05@yahoo.com> wrote:
>  
>  
> 
> 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