当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Grails filter stats insert performance

问题描述:

I have a filter in grails to capture all controller requests and insert a row into the database with the controllerName, actionName, userId, date, and guid. This works fine, but I would like to find a way to increase the performance. Right now it takes ~100 milliseconds to do all this with 70-80ms of that time creating a statement. I've used both a domain object insert, groovy Sql, and raw java connection/statement. Is there any faster way to improve the performance of inserting a single record within a filter? Alternatively, is there a different pattern that can be used for the inserts? Code (using groovy SQL) Below:

class StatsFilters {

def grailsApplication

def dataSource

def filters =

{

logStats(controller:'*', action:'*')

{

before = {

if(controllerName == null || actionName == null)

{

return true

}

def logValue = grailsApplication.config.statsLogging

if(logValue.equalsIgnoreCase("on") && session?.user?.uid != null & session?.user?.uid != "")

{

try{

def start = System.currentTimeMillis()

Sql sql = new Sql(dataSource)

def userId = session.user.uid

final String uuid = "I" + UUID.randomUUID().toString().replaceAll("-","");

String insert = "insert into STATS(ID, CONTROLLER, ACTION, MODIFIED_DATE, USER_ID) values ('${uuid}','${controllerName}','${actionName}',SYSDATE,'${userId}')"

sql.execute(insert)

sql.close()

def end = System.currentTimeMillis()

def total = end - start

println("total " + total)

}

catch(e)

{

log.error("Stats failed to save with exception " + e.getStackTrace())

return true

}

}

return true

}

}

}

}

And my current data source

dataSource {

pooled = true

dialect="org.hibernate.dialect.OracleDialect"

properties {

maxActive = 50

maxIdle = 10

initialSize = 10

minEvictableIdleTimeMillis = 1800000

timeBetweenEvictionRunsMillis = 1800000

maxWait = 10000

validationQuery = "select * from resource_check"

testWhileIdle = true

numTestsPerEvictionRun = 3

testOnBorrow = true

testOnReturn = true

}

//loggingSql = true

}

----------------------Solution-------------------------

The solution was to simply spawn a thread and do the stats save. This way user response time isn't impacted, but the save is done in near real time. The number of users in this application (corporate internal, limited user group) doesn't merit anything more robust.

void saveStatData(def controllerName, def actionName, def userId)

{

Thread.start{

Sql sql = new Sql(dataSource)

final String uuid = "I" + UUID.randomUUID().toString().replaceAll("-","");

String insert = "insert into STATS(ID, CONTROLLER, ACTION, MODIFIED_DATE, USER_ID) values ('${uuid}','${controllerName}','${actionName}',SYSDATE,'${userId}')"

sql.execute(insert)

sql.close()

}

}

网友答案:

Better pattern is not to insert the row in the filter instead just add a record to some list and flush the list into database regularly by asynchronous job (using Quartz plugin for example).

You could loose some data if application crashes, but if you schedule the job to run often (like every x minutes), it should not be an issue.

分享给朋友:
您可能感兴趣的文章:
随机阅读: