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) >= CONVERT(CHAR(10),REPLACE(#syukeiFrom_#,'/','-'),112) AND
CONVERT(CHAR(10),NewLog_T.date,112) <= 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) >= CONVERT(CHAR(10),REPLACE(#syukeiFrom_#,'/','-'),112) AND
CONVERT(CHAR(10),NewLog_T.date,112) <= 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) >= CONVERT(CHAR(10),REPLACE(#syukeiFrom_#,'/','-'),112) AND
CONVERT(CHAR(10),NewLog_T.date,112) <= 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) >= CONVERT(CHAR(10),REPLACE(#syukeiFrom_#,'/','-'),112) AND
CONVERT(CHAR(10),NewLog_T.date,112) <= 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
