MSSQL server connection time out groovy grails application

1
votes

I have an application developed in groovy on grails(1.1.1). I've used a hibernate enable pooled configured datasource of MySQL Server in datasource.groovy code snippet as folows-

hibernate {
cache.use_second_level_cache=true
cache.use_query_cache=true
cache.provider_class='com.opensymphony.oscache.hibernate.OSCacheProvider'
}

environments {
development {
    dataSource {
                    logSql = true
        dbCreate = "update" // one of 'create', 'create-drop','update'
                    url = "jdbc:mysql://<dev-server-ip>:3306/<db-name>"                        
    }
}
test {
    dataSource {
        dbCreate = "update"
                    url = "jdbc:mysql://<test-server-ip>:3306/<db-name>"                        
    }
}
production {
    dataSource {
        dbCreate = "update"         
                    url = "jdbc:mysql://<prod-server-ip>:3306/<db-name>"                        
    }
}

For connecting to another MS SQL Server 2005. I've defined a service class with following connection settings

    def connection = Sql.newInstance(
        "jdbc:sqlserver://<mssql-db-server-ip>;databaseName=<db-name>",
        '<username>',
        '<password>',
        'com.microsoft.sqlserver.jdbc.SQLServerDriver')

I know that this connection is not pooled connection. And create session each time when I use this connection for retrieve data. So, Is there any mechanism to create pooled connection in service class?

When I run a query from service class using this ms sql server connection to select a data query like 

String pullData() {
    def userId = "sample_user"
def testData = connection.firstRow("select distinct (COLUMN_NAME1+' > '+replace(COLUMN_NAME2,' > ', '>')+' > '+COLUMN_NAME3+' > '+COLUMN_NAME4+' > '+EMAIL) testRow from VIEW_NAME where lower(substring(EMAIL,1,(select charindex('@',EMAIL))-1 )) = lower(?)",[userId])

def myStringList = testData.testRow.split(' > ')
def myTestDataList = myStringList[4].split('@')
def email = myTestDataList[0]
return email
}

I got an exception. The stacktrace is 

org.codehaus.groovy.runtime.InvokerInvocationException: java.lang.reflect.UndeclaredThrowableException
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:646)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:436)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:374)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:302)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at jcifs.http.NtlmHttpFilter.doFilter(NtlmHttpFilter.java:125)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Thread.java:636)
Caused by: java.lang.reflect.UndeclaredThrowableException
at PullDataService$$EnhancerByCGLIB$$881a39b3.pullData(<generated>)
at PullDataService$$FastClassByCGLIB$$f8a1c77f.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
... 33 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Connection timed out
at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1368)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1355)
at com.microsoft.sqlserver.jdbc.TDSChannel.write(IOBuffer.java:1548)
at com.microsoft.sqlserver.jdbc.TDSWriter.flush(IOBuffer.java:2368)
at com.microsoft.sqlserver.jdbc.TDSWriter.writePacket(IOBuffer.java:2270)
at com.microsoft.sqlserver.jdbc.TDSWriter.endMessage(IOBuffer.java:1877)
at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:4403)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:386)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:281)
at PullDataService.pullData(PullDataService.groovy:38)
at PullDataService$$FastClassByCGLIB$$7ff09ca7.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
... 40 more

Please help me, I am suffering it from a long time. Any suggestion is appreciable. Thanks in advance.

1
sql-servergrails
Check out your error message here :Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Connection timed out. I assume this is because the query you are running is taking too long causing the timeout. That where clause you are using looks a bit crazy, look at changing that to speed it upo. - krock
I need to split email first part before '@'. Is there any simple way to do so? Example: [email protected] I need to extract the only 'abc' to compare with. Thank you. - Firoj Mahmud

1 Answers

0
votes

There are a couple of things I can see that would slow down this query. Mainly you creating a string column based on a bunch of different fields and calling distinct on it. Distinct is not needed as you are only using the first result and the other columns are not needed as you only care about the email. Consider something like this instead:

    def userId = "sample_user".toLowerCase()
    def testData = connection.firstRow("select top 1 EMAIL as email" +
             " from VIEW_NAME" +
             " where lower(EMAIL) like ?",[userId + "@%"])
    def email = testData?.email?.split('@')[0]
    return email

It could still be slow due to the like clause but should be much faster than using distinct.

You may want to consider adding a column to your view/underlying table to have a separate userid column to the email address column.