def myObjects = Domain.findAllBySomething(thing)
....
....
....
myObjects.each{obj ->
def dto = new Dto()
dto.property = obj.innerDomain.property
dto.propertyTwo = obj.innerDomain.anotherDomain.property
dtos << dto
}
It seems fairly innocent but try telling that the database. Each call on any of the properties within obj that are themselves domains resulted in another database call. Creating the DTO above resulted in about 3 queries just to set two properties. This might not sound too bad, but imagine if myObjects contains 1,000 or so objects.... not the best strategy. The actual code I was maintaining was typically returning about 1,000 results and this resulted in about 13,000 queries running on the database. This was a user driven event so it could be happening quite a lot. This was obviously terrible for performance. Of course there is an argument to use paging and caching of results etc. but that's a different blog post. Long story short, a colleague (who I won't name for social reasons) and I were able to get the 13,000 queries down to 25 for the entire service method by just configuring the query correctly.
The main point here is the difference between eager and lazy fetching. A long time ago, Grails' default fetching strategy was eager. This basically meant that when you queried for domains, using dynamic finders or criteria, your object model would return fully populated. Unfortunately this could also result in you returning the entire database in one query. Not good.
Around about Grails 1.0.4, the default fetching strategy was changed to lazy. This meant that your object model returned partially populated, that is, proxy objects with only their id's, were returned for properties that were themselves domains. When you called for a property on one of these proxies, Grails would request it from the database. All this happened pretty transparently to the user.
Grails provides many ways of forcing earger fetching. It can be done in the domain mappings and in the domain itself by setting the fetch mode. It can also be done at a query level which I am more in favour of. Setting on the domain itself is too much of a commitment in my opinion as you can never imagine all use cases at time of coding. I will illustrate setting it in dynamic finders with the following domains :
class Customer {
String name
Account account
static constraints = {
account unique: true, nullable: false, blank: false
name nullable: false, blank: false
}
}
class Account {
static belongsTo = [owner: Customer]
Long accountNumber
static constraints = {
accountNumber min: 0l, nullable: false, unique: true
}
}
class BankingTransaction {
Long transactionNumber
BigDecimal amount
Account fromAccount
Account toAccount
static constraints = {
transactionNumber min: 0l, unique: true, nullable: false
fromAccount nullable: false, validator: {Account thisFromAccount, BankingTransaction thisTransaction ->
if (thisFromAccount?.accountNumber == thisTransaction?.toAccount?.accountNumber) {
return false//cannot have a transaction to the same account... probably
}
}
toAccount nullable: false
amount nullable: false
}
BankingTransactionDTO toBankingTransactionDTO() {
def dto = new BankingTransactionDTO()
dto.with {//probably better ways of doing this but this illustrated the example
transactionNumber = this.transactionNumber
amount = this.amount
fromCustomersName = fromAccount.owner.name
toCustomersName = toAccount.owner.name
}
return dto
}
}
So to illustrate the lazy and eager fetching I wrote some tests that were bootstrapped on a MySQL database. First we need to set up some test data and an additional Grooy Sql connection to run some status queries on our DB.class BankingTransactionTests {
Sql sql
SessionFactory sessionFactory
BankingTransactionService bankingTransactionService
@Before
void setUp() {
BankingTransaction.withNewSession {session ->
100.times {num ->
new Customer(name: "Customer ${num}", account: new Account(accountNumber: num)).save(failOnError: true)
}
}
BankingTransaction.withNewSession {session ->
final allCustomers = Customer.list()
def generousCustomer = allCustomers[0]
allCustomers[1..-1].eachWithIndex {customer, index ->
new BankingTransaction(fromAccount: generousCustomer.account, toAccount: customer.account, transactionNumber: index, amount: index * 100).save(failOnError: true)
}
}
sql = new Sql(sessionFactory.currentSession.connection())
sql.metaClass.getNumberOfSelects = {->
return delegate.rows('SHOW STATUS LIKE "Com_select"')[0].Value as Long
}
}
....
}
I won't go into the details, it is just an integration test, the code is on GitHub anyway for the forever curious. So first off lets do a simple test that queries for all BankingTransactions.
@Test
void testSimpleQuery() {
//get the current number of selects in this session
def numberOfQueriesBeforeTest = sql.numberOfSelects
//carry out the query
final transaction = BankingTransaction.list()//1 query
//now calculate the number of queries
def numberOfQueriesAfterTest = sql.numberOfSelects
final numberOfSelects = numberOfQueriesAfterTest - numberOfQueriesBeforeTest
//is it what we expect??
assert numberOfSelects == 1
}
We run the test, and huzzah, test passes. As we would expect BankingTransaction.list() is a simple select query.
OK, now lets access some properties on the first BankingTransaction
@Test
void testSimplePropertyAccess() {
//get the current number of selects in this session
def numberOfQueriesBeforeTest = sql.numberOfSelects
//carry out the query
final transaction = BankingTransaction.list()[0]
def fromAccountNumber = transaction.fromAccount.accountNumber
def fromAccountCustomerName = transaction.fromAccount.owner.name
//now calculate the number of queries
def numberOfQueriesAfterTest = sql.numberOfSelects
final numberOfSelects = numberOfQueriesAfterTest - numberOfQueriesBeforeTest
//is it what we expect??
assert numberOfSelects == 1
}
Uh oh, test fails with :
Assertion failed:
assert numberOfSelects == 1
| |
3 false
Looking closely at the test we can see the three queries, the first is the the call the list(), the second is the call to fromAccount and the third, the call to fromAccount.owner.
OK, so, lets fix the query by setting the fetch mode to join in the query.
@Test
void testSimplePropertyAccess() {
//get the current number of selects in this session
def numberOfQueriesBeforeTest = sql.numberOfSelects
def queryParams = [fetch: [fromAccount: 'join', 'fromAccount.owner': 'join']]
//carry out the query
final transaction = BankingTransaction.list(queryParams)[0]
def fromAccountNumber = transaction.fromAccount.accountNumber
def fromAccountCustomerName = transaction.fromAccount.owner.name
//now calculate the number of queries
def numberOfQueriesAfterTest = sql.numberOfSelects
final numberOfSelects = numberOfQueriesAfterTest - numberOfQueriesBeforeTest
//is it what we expect??
assert numberOfSelects == 1
}
Test passes! Sweet! This is because we told GORM to fetch the associated domains using a join. I suppose we should look at a more real work example. Suppose we have a BankingTransactionService with the following method :
class BankingTransactionService {
Collection retrieveTransactions() {
def transactions = BankingTransaction.list()
return transactions.collect {it.toBankingTransactionDTO()}
}
}
With the following test :
@Test
void testBankingService() {
//get the current number of selects in this session
def numberOfQueriesBeforeTest = sql.numberOfSelects
//carry out the query
def dtos = bankingTransactionService.retrieveTransactions()
//now calculate the number of queries
def numberOfQueriesAfterTest = sql.numberOfSelects
final numberOfSelects = numberOfQueriesAfterTest - numberOfQueriesBeforeTest
//is it what we expect??
assert numberOfSelects == 1
}
We run the test, and eh... not looking good :
Assertion failed:
assert numberOfSelects == 1
| |
201 false
Hmmm 201 queries, this will not please the performance junkies at all. What is happening in toBankingTransactionDTO() is that as the objects are created a query happens each time a property that is a domain is accessed. These slowly build up in the first level cache reducing the amount of queries, but I think we can do better.
Let's go all out and reduce the number of queries to 1 for this service method. It's gonna be a long road.... not really :
Collection retrieveTransactions() {
final queryParams = [fetch: [
fromAccount: 'join',
toAccount: 'join',
'toAccount.owner': 'join',
'fromAccount.owner': 'join']
]
def transactions = BankingTransaction.list(queryParams)
return transactions.collect {it.toBankingTransactionDTO()}
}
Test passes. There we go, 201 queries to 1. Not really that much work, just don't take the simplicity provided by GORM for granted or you will have some angry DBA's on your tail... you have been warned!
Slán
Code on GitHub.
