ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From j-lists <jamisonli...@gmail.com>
Subject Re: Query timeout (I think)
Date Wed, 30 Jan 2008 04:06:37 GMT
> [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 <zebthecat@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/
>

Mime
View raw message