You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by charlie bird <ze...@yahoo.co.uk> on 2008/01/24 12:00:26 UTC

Query timeout (I think)

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/

Re: Query timeout (I think)

Posted by j-lists <ja...@gmail.com>.
> [TDS Driver]Read timed out
This looks to me like a network-level timeout, not a database timeout.
I've only used the open-source jdbc driver for SQL Server but we  have
had problems with connection instability if our app is idle for a long
time (MSSQL2005), a ping-query fixed that though. Doesn't seem like
this is your problem but it doe snot seem like actual performance is
your problem either.

-J

On Jan 24, 2008 7:00 PM, charlie bird <ze...@yahoo.co.uk> wrote:
> 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/
>