ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From charlie bird <zebthe...@yahoo.co.uk>
Subject Query timeout (I think)
Date Thu, 24 Jan 2008 11:00:26 GMT
Hi - Hope someone can help.

I am using ibatis 2.3.0.667 within Spring 2.0 on
Tomcat 5.5.9A connecting to SQL Server 7 and Merlia
SQL server driver and getting this stack trace: 

2008-01-24 10:45:49,276 DEBUG [java.sql.Connection] -
<{conn-100027} Connection>
2008-01-24 10:45:49,292 DEBUG [java.sql.Connection] -
<{conn-100027} Preparing Statement:    select
s.SearchID, s.FieldID, s.UserID, s.SearchType,
s.timestamp, convert(datetime, convert(varchar,
s.Timestamp, 104), 104) as thedate, s.Thesaurus,
s.UserSaved,       s.SuccessfulSearch, s.FieldValue,
f.FieldName, f.methodName  from
EU_Users_Saved_Searches s    inner join
wf_Prospectus_Fields f  on s.FieldId = f.ID      inner
join (   select top 10 s.SearchID , max(s.Timestamp)
as timestamp from EU_Users_Saved_Searches s     where
s.UserID = ?        and s.SearchType = ?           
group by s.SearchID   order by max(s.Timestamp)  desc 
 ) as sub1   on s.SearchID = sub1.SearchID  order by
s.Timestamp  desc   >
2008-01-24 10:45:49,292 DEBUG
[java.sql.PreparedStatement] - <{pstm-100028}
Executing Statement:    select s.SearchID, s.FieldID,
s.UserID, s.SearchType, s.timestamp, convert(datetime,
convert(varchar, s.Timestamp, 104), 104) as thedate,
s.Thesaurus, s.UserSaved,       s.SuccessfulSearch,
s.FieldValue, f.FieldName, f.methodName  from
EU_Users_Saved_Searches s    inner join
wf_Prospectus_Fields f  on s.FieldId = f.ID      inner
join (   select top 10 s.SearchID , max(s.Timestamp)
as timestamp from EU_Users_Saved_Searches s     where
s.UserID = ?        and s.SearchType = ?           
group by s.SearchID   order by max(s.Timestamp)  desc 
 ) as sub1   on s.SearchID = sub1.SearchID  order by
s.Timestamp  desc   >
2008-01-24 10:45:49,292 DEBUG
[java.sql.PreparedStatement] - <{pstm-100028}
Parameters: [4163, 1]>
2008-01-24 10:45:49,292 DEBUG
[java.sql.PreparedStatement] - <{pstm-100028} Types:
[java.lang.Integer, java.lang.Integer]>
2008-01-24 10:46:09,870 INFO
[org.springframework.beans.factory.xml.XmlBeanDefinitionReader]
- <Loading XML bean definitions from class path
resource
[org/springframework/jdbc/support/sql-error-codes.xml]>
2008-01-24 10:46:09,917 INFO
[org.springframework.jdbc.support.SQLErrorCodesFactory]
- <SQLErrorCodes loaded: [DB2, Derby, H2, HSQL,
Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]>
org.springframework.jdbc.UncategorizedSQLException:
SqlMapClient operation; uncategorized SQLException for
SQL []; SQL state [S1T00]; error code [0];   
--- The error occurred while applying a parameter map.
 
--- Check the
EU_Users_Saved_Searches.getSearchesByUser-InlineParameterMap.
 
--- Check the statement (query failed).  
--- Cause: java.sql.SQLException: [TDS Driver]Read
timed out; nested exception is
com.ibatis.common.jdbc.exception.NestedSQLException:  

--- The error occurred while applying a parameter map.
 
--- Check the
EU_Users_Saved_Searches.getSearchesByUser-InlineParameterMap.
 
--- Check the statement (query failed).  
--- Cause: java.sql.SQLException: [TDS Driver]Read
timed out
Caused by:
com.ibatis.common.jdbc.exception.NestedSQLException:  

--- The error occurred while applying a parameter map.
 
--- Check the
EU_Users_Saved_Searches.getSearchesByUser-InlineParameterMap.
 
--- Check the statement (query failed).  
--- Cause: java.sql.SQLException: [TDS Driver]Read
timed out
	at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:185)
	at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList(GeneralStatement.java:123)
	at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:615)
	at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:589)
	at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118)
	at
org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient(SqlMapClientTemplate.java:268)
	at
org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:193)
	at
org.springframework.orm.ibatis.SqlMapClientTemplate.executeWithListResult(SqlMapClientTemplate.java:219)
	at
org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList(SqlMapClientTemplate.java:266)
	at
com.globalfilings.domain.dao.SavedSearchDAOImpl.getUserSearches(SavedSearchDAOImpl.java:128)
	at
com.globalfilings.services.UserServices.getSavedSearchesByUser(UserServices.java:195)
	at
sun.reflect.NativeMethodAccessorImpl.invoke0(Native
Method)
	at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:585)
	at
org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:304)
	at
org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:172)
	at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:139)
	at
org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)
	at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:161)
	at
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
	at $Proxy6.getSavedSearchesByUser(Unknown Source)
	at
com.globalfilings.controller.useraccount.UsersAccountSearchesFormController.onSubmit(UsersAccountSearchesFormController.java:52)
	at
org.springframework.web.servlet.mvc.SimpleFormController.processFormSubmission(SimpleFormController.java:267)
	at
org.springframework.web.servlet.mvc.AbstractFormController.handleRequestInternal(AbstractFormController.java:250)
	at
org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
	at
org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
	at
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:857)
	at
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:792)
	at
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:461)
	at
org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:426)
	at
javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
	at
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
	at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
	at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
	at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:265)
	at
org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:97)
	at
org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:72)
	at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
	at
org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:110)
	at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
	at
org.acegisecurity.ui.rememberme.RememberMeProcessingFilter.doFilter(RememberMeProcessingFilter.java:142)
	at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
	at
com.globalfilings.security.UserCookieWriteFilter.doFilter(UserCookieWriteFilter.java:75)
	at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
	at
com.globalfilings.security.IntranetUserCookieFilter.doFilter(IntranetUserCookieFilter.java:75)
	at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
	at
org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:229)
	at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
	at
com.globalfilings.security.AbstractUserProcessingFilter.doFilter(AbstractUserProcessingFilter.java:65)
	at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
	at
com.globalfilings.security.AbstractUserProcessingFilter.doFilter(AbstractUserProcessingFilter.java:65)
	at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
	at
com.globalfilings.security.AbstractUserProcessingFilter.doFilter(AbstractUserProcessingFilter.java:65)
	at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
	at
org.acegisecurity.ui.logout.LogoutFilter.doFilter(LogoutFilter.java:106)
	at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
	at
org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java:198)
	at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
	at
com.globalfilings.security.ExpiredSubscriptionFilter.doFilter(ExpiredSubscriptionFilter.java:66)
	at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
	at
org.acegisecurity.concurrent.ConcurrentSessionFilter.doFilter(ConcurrentSessionFilter.java:95)
	at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
	at
org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:149)
	at
org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98)
	at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
	at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
	at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:265)
	at
org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:107)
	at
org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:72)
	at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
	at
org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:110)
	at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
	at
org.acegisecurity.ui.rememberme.RememberMeProcessingFilter.doFilter(RememberMeProcessingFilter.java:142)
	at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
	at
com.globalfilings.security.UserCookieWriteFilter.doFilter(UserCookieWriteFilter.java:75)
	at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
	at
com.globalfilings.security.IntranetUserCookieFilter.doFilter(IntranetUserCookieFilter.java:75)
	at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
	at
org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:229)
	at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
	at
com.globalfilings.security.AbstractUserProcessingFilter.doFilter(AbstractUserProcessingFilter.java:65)
	at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
	at
com.globalfilings.security.AbstractUserProcessingFilter.doFilter(AbstractUserProcessingFilter.java:65)
	at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
	at
com.globalfilings.security.AbstractUserProcessingFilter.doFilter(AbstractUserProcessingFilter.java:65)
	at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
	at
org.acegisecurity.ui.logout.LogoutFilter.doFilter(LogoutFilter.java:106)
	at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
	at
org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java:286)
	at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
	at
com.globalfilings.security.ExpiredSubscriptionFilter.doFilter(ExpiredSubscriptionFilter.java:66)
	at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
	at
org.acegisecurity.concurrent.ConcurrentSessionFilter.doFilter(ConcurrentSessionFilter.java:95)
	at
org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
	at
org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:149)
	at
org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98)
	at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
	at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
	at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
	at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
	at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
	at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
	at
org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:526)
	at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
	at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
	at
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
	at
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
	at
org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
	at
org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
	at
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
	at java.lang.Thread.run(Thread.java:595)
Caused by: java.sql.SQLException: [TDS Driver]Read
timed out
	at com.inet.tds.TdsDriver.a(Unknown Source)
	at com.inet.tds.a.a(Unknown Source)
	at com.inet.tds.a.a(Unknown Source)
	at com.inet.tds.a.a(Unknown Source)
	at com.inet.tds.b.try(Unknown Source)
	at com.inet.tds.b.execute(Unknown Source)
	at
org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:168)
	at
sun.reflect.NativeMethodAccessorImpl.invoke0(Native
Method)
	at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:585)
	at
com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(PreparedStatementLogProxy.java:62)
	at $Proxy11.execute(Unknown Source)
	at
com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:186)
	at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQuery(GeneralStatement.java:205)
	at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173)
	... 111 more
2008-01-24 10:46:11,620 ERROR
[com.globalfilings.resolver.NotSoSimpleMappingExceptionResolver]
- <Something has gone badly awry>

When attempting to execute this query:

  <resultMap id="SavedSearchResultWithFields"
class="com.globalfilings.domain.SavedSearch" 
  groupBy="searchId">
    <result column="SearchID" property="searchId"
jdbcType="VARCHAR" />
    <result column="UserID" property="userId"
jdbcType="INTEGER" />
    <result column="SearchType" property="searchType"
jdbcType="INTEGER" />
    <result column="timestamp" property="timestamp"
jdbcType="TIMESTAMP" />
    <result column="Thesaurus"
typeHandler="booleanHandler" property="thesaurus"
jdbcType="BIT" />
    <result column="UserSaved"
typeHandler="booleanHandler" property="userSaved"
jdbcType="BIT" />
    <result column="SuccessfulSearch"
typeHandler="booleanHandler"
property="successfulSearch" jdbcType="BIT" />
  	<result property="searchFields"
resultMap="EU_Users_Saved_Searches.SearchFieldResult"
/>
  </resultMap>
  <resultMap id="SearchFieldResult"
class="com.globalfilings.domain.SearchField"
groupBy="id">
    <result column="FieldID" property="id"
jdbcType="INTEGER" />
    <result column="FieldName" property="fieldName"
jdbcType="VARCHAR" />
    <result column="methodName" property="methodName"
jdbcType="VARCHAR" />
    <result column="FieldValue" property="value"
jdbcType="CLOB" />
  </resultMap>
  <select id="getSearchesByUser"
resultMap="SavedSearchResultWithFields"
parameterClass="com.globalfilings.domain.search.SavedSearchesSearch"
>
 	select s.SearchID, s.FieldID, s.UserID,
s.SearchType, s.timestamp, convert(datetime,
convert(varchar, s.Timestamp, 104), 104) as thedate,
s.Thesaurus, s.UserSaved,       s.SuccessfulSearch,
s.FieldValue, f.FieldName, f.methodName
	from EU_Users_Saved_Searches s  
	inner join wf_Prospectus_Fields f  on s.FieldId =
f.ID    
	inner join (
		select top 10 s.SearchID , max(s.Timestamp) as
timestamp from EU_Users_Saved_Searches s  
		where s.UserID = #userId:INTEGER#     
		and s.SearchType = #searchType:INTEGER#      
		<isGreaterThan property="userSaved"
compareValue="0">
			and s.UserSaved = #userSaved:SHORT#
		</isGreaterThan>
		group by s.SearchID
		order by max(s.Timestamp)  desc 
	) as sub1 
	on s.SearchID = sub1.SearchID
	order by s.Timestamp  desc
  </select>

I think it times out after 10 seconds but the weird
thing is - if I run the exact same query which is
actually this:

select s.SearchID, s.FieldID, s.UserID, s.SearchType, 
s.timestamp, convert(datetime, convert(varchar,
s.Timestamp, 104), 104) as thedate, 
s.Thesaurus, s.UserSaved,       
s.SuccessfulSearch, s.FieldValue, f.FieldName,
f.methodName  
from EU_Users_Saved_Searches s    
inner join wf_Prospectus_Fields f  on s.FieldId = f.ID
     
inner join ( 
		select top 10 s.SearchID , max(s.Timestamp) as
timestamp 
		from EU_Users_Saved_Searches s     
		where s.UserID = 4163        and s.SearchType = 1   
		group by s.SearchID   order by max(s.Timestamp) 
desc   
	) as sub1   
on s.SearchID = sub1.SearchID  
order by s.Timestamp  desc

in the SQL server query analyser from the same remote
box that our dev app runs on connected to the same SQL
server the query return in less than 4 seconds. I've
tried tweaking the driver configuration xml (maxWait,
loginTimeout, queryTimeout) and whatever I do it still
explodes at this point.

I'm stumped - does anyone have any clues to how I
change the timeout or indeed why jdbc is taking so
damn long!

Thanks all

Charlie


      ___________________________________________________________
Support the World Aids Awareness campaign this month with Yahoo! For Good http://uk.promotions.yahoo.com/forgood/

Mime
View raw message