My question
I have an application generate a list, which contains multiple layers of nested lists. My current implementation generates the desired results, but it also executes lots of query, exponential to the number of layers depth, to the database. So the overall performance is pretty poor (taking too much time). I'm trying to find a way to improve it but, to tell the true, I don't know which way is the better way.
So what I would like to know are:
I have an improvement. Is my improvement acceptable? (won't create other issue)
Is there any other way to improve, like:
a. denormalize database tables
b. use other ORM framework with better support
c. Other better implementations for database design or for java code (could really use some help from you guys)
The original need
What I have are some tables with one-to-many relation:
Family
id name
1 Smiths
2 Johnson
3 Williams
Member
id family_id name
1 1 David Smiths
2 1 Mary Smiths
3 1 William Smiths
3 2 David Johnson
4 3 David Williams
Asset
id member_id type value
1 1 cash 100.00
2 1 share 200.00
3 1 security 100.00
4 2 cash 50.00
What I need is to generate a list of Families and their Asset. This should be a list of Family, each Family contains a list of Member, and each Member contains a list of Asset:
class Family{
Integer id;
String name;
List<Member> members;
// Getter and setters
}
class Member{
Integer id;
Integer family_id;
String name;
List<Asset> assets;
// Getter and setters
}
class Asset{
Integer id;
Integer member_id;
String type;
BigDecimal value;
}
The result should be List<Family> families. Output to JSON:
{
"families": [{
"id": 1,
"name": "Smiths",
"members": [{
"id": 1,
"family_id": 1,
"name": "David Smiths",
"assets": [{
"id": 1,
"member_id": 1,
"type": "cash",
"value": "100.00"
}, {
"id": 2,
"member_id": 1,
"type": "share",
"value": "200.00"
}, {
"id": 3,
"member_id": 1,
"type": "security",
"value": "100.00"
}]
}]
}]
}
So far, I got two ways to do this:
Solution 1 Nested Loop Query
Java code
List<Family> generateFamilyList(){
List<Family> families = resultMapper.selectFamily();
// select Member for each Family
for(Family family: families){
List<Member> membersInFamily = resultMapper.selectMemberByFamily(family.getId());
//select Asset for each Member
for(Member member: membersInFamily){
List<Asset> assetsOfMember = resultMapper.selectAssetByMember(member.getId());
member.setAssets(assetsOfMember);
}
family.setMembers(membersInFamily );
}
return families;
}
And statements, using Mybatis.
<select id="selectFamily" resultType="Family">
select id, name from family;
</select>
<select id="selectMemberByFamily" resultType="Member">
select id, family_id, name from member
where family_id = #{familyId};
</select>
<select id="selectAssetByMember" resultType="Asset">
select id, member_id, type, value from asset
where member_id = #{memberId};
</select>
This method does its job and generates correct JSON. But it also creates N^3 times of query to the database at the inner most select selectAssetByMember(member.getId())
I switch to use Mybatis collection select in the mapper, but found out this only make Mybatis Mapper do the for-loop query instead of doing in java code. The database still receives N^3 query.
So I came up with another solution:
Solution 2 Select all in a query, then filtered in Java
Java code
List<Family> generateFamilyList(){
List<Family> families = resultMapper.selectFamily();
List<Integer> allFamilyIds = familes.stream()
.map(Family::getId)
.collect(Collectors.toList());
if(familyIds.isEmpty())
//throw exception or return
// select all members in one query.
// !Watch out! for Max Number of Allowable Parameters
List<Member> allMembers = resultMapper.selectMemberByFamilyIds(allFamilyIds);
List<Integer> allMemberIds = allMembers.stream()
.map(Member::getId)
.collect(Collectors.toList());
if(allMemberIds.isEmpty())
//throw exception or
//return a new empty ArrayList for this member's asset
// select all ssets in one query.
// !Watch out! for Max Number of Allowable Parameters
List<Asset> allAssets = resultMapper.selectAssetByMemberIds(allMemberIds );
// filter and set Members for each Family
for(Family family: families){
List<Member> membersInFamily = allMembers.stream()
.filter(member -> member.getFamilyId().equals(family.getId()))
.collect(Collectors.toList());
//select Asset for each Member
for(Member member: membersInFamily){
List<Asset> assetsOfMember = allAssets.stream()
.filter(asset -> asset.getMemberId().equals(member.getId()))
.collect(Collectors.toList());
member.setAssets(assetsOfMember);
}
family.setMembers(membersInFamily );
}
return families;
}
And statements. The mybatis support a list of parameters:
<select id="selectFamily" resultType="Family">
select id, name from family;
</select>
<select id="selectMemberByFamilyIds" resultType="Member">
select id, family_id, name from member
where family_id IN
<foreach> #{familyId} </foreach>;
<!-- ( 1, 2, 3, ....); the input list familyIds-->
<!-- could cause SQLException if the ids more than the Max Number of Allowable Parameters of database -->
</select>
<select id="selectAssetByMemberIds" resultType="Asset">
select id, member_id, type, value from asset
where member_id IN
<foreach> #{memberId} </foreach>
<!-- ( 1, 2, 3, ....); the input list memberIds-->
<!-- could cause SQLException if the ids more than the Max Number of Allowable Parameters of database -->
</select>
Result and Issues
Query Number Reduced
Memory usage in Java application Increased
Overall application response time remarkably reduced(down to 20% - 10% in my cases)
New Issue: Max Number of Allowable Parameters. Need to do paging before query if the parameter list
memberIdssize too big. The max number is limited and differs from types of database.
That's all I got for now. And I'm kind of stuck, don't know where should I improve my codes.
Thank you guys for watching my long long questions.
