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