Sql.rows () in Groovy is slow

Question

I support the Grails web application, which shows various visual effects for a client using AmCharts. On one of the tabs there are three charts, each of which returns the top ten, so only ten rows from the database based on various measures. It takes 4-5, and sometimes more time. The query is executed in the database in less than 10 seconds.

The following method is called to return the results:

List fetchTopPages(params, Map querySettings, String orderClause) { if(!((params['country'] && params['country'].size() > 0) || (params['brand'] && params['brand'].size() > 0) || (params['url'] && params['url'].size() > 0))) { throw new RuntimeException('Filters country or brand or url not selected.') } Sql sql = new Sql(dataSource) sql.withStatement { stmt -> stmt.fetchSize = 100 } Map filterParams = acquisitionService.getDateFilters(params, querySettings) ParamUtils.addWhereArgs(params, filterParams) String query = "This is where the query is" ParamUtils.saveQueryInRequest(ParamUtils.prettyPrintQuery(query, filterParams)) log.debug("engagement pageviews-by-source query: " + ParamUtils.prettyPrintQuery(query, filterParams)) List rows = sql.rows(query, filterParams) rows } 

After some research, it was clear that the List rows = sql.rows(query, filterParams) is the one that takes this load time.

Has anyone else asked this problem before? Why does sql.rows () take so long when it returns only 10 rows of results, and the query is executed quickly on the DB side?

Additional Information:

DB: FSL1D

Running the following command on the DB side: java -jar ojdbc5.jar - getversion returns: "Oracle 11.2.0.3.0 JDBC 3.0 compiled from JDK5 on Thu_Jul_11_15: 41: 55_PDT_2013 Default connection properties resource Wed Dec 16 08:18:32 EST 2015 "

Groovy Version: 2.3.7 Grails Version: 2.4.41 JDK: 1.7.0

Answer 1

My setup using Groovy Version: 2.3.6 JVM: 1.8.0_11 and Oracle 12.1.0.2.0 using ojdbc7.jar driver

Note the activation of 10046 traces before starting to enable diagnostics.

 import oracle.jdbc.pool.OracleDataSource def ods = new OracleDataSource(); ods.setURL('url') ods.setUser('usr') ods.setPassword('pwd') def con = ods.getConnection() def sql = new groovy.sql.Sql(con) sql.withStatement { stmt -> stmt.fetchSize = 100 } def SQL_QUERY = """select id, col1 from table1 order by id""" def offset = 150 def maxRows = 20 // activate trace 10046 con.createStatement().execute "alter session set events '10046 trace name context forever, level 12'" def t = System.currentTimeMillis() def rows = sql.rows(SQL_QUERY, offset, maxRows) println "time1 : ${System.currentTimeMillis()-t} with offset ${offset} and maxRows ${maxRows}" 

Studying the trace shows that the element is being analyzed and executed, which means that if there is an ORDER BY clause, all data is sorted.

The sample size is used correctly and no more than the required records - here 170 = 150 + 20. With a sample size of 100 this is done in two stages (note the parameter r - the number of selected rows).

 FETCH #627590664:c=0,e=155,p=0,cr=5,cu=0,mis=0,r=100,dep=0,og=1,plh=1169613780,tim=3898349818398 FETCH #627590664:c=0,e=46,p=0,cr=0,cu=0,mis=0,r=70,dep=0,og=1,plh=1169613780,tim=3898349851458 

So, basically the only problem, I see that the "missing" data is transmitted over the network to the client (here they are ignored).

This can lead to a very high amount of overhead (and it will take longer for the same request to interactively process the first page).

But the best way to identify your problem is to simply turn on trace 10046 and see what happens. I use level 12 , which means that you also get information about expectations in DB and bind variables.