具有将数据插入多个表的post api。(MySQL)
实现使用jpa。
下面是正在发生的操作顺序,任何建议:如何优化这个。
SQL查询:
1) Select * from University where UID = 'UNI1';
2) If (University Not Exist) then Insert INTO University ...
3) Select * from College where UID = 'UNI1'
4) If (College Not Exist) then Insert INTO College ...
**In Loop (For Each Student)**
5) Delete * from CollegeStudent;
LOOP :
6) Select * from Student where StudentId = 'ST22'
7) If (Student Not Exist) then Insert INTO Student ...
8) Insert INTO CollegeStudent (Student, College);
LOOP ENDS;
代码段:
@Transactional
public void persistStudentResults(String universityId, String collegeId, List<Student> studentList) {
University university= universityRepository.findByUniversityId(universityId);
if (university == null) {
university = createUniversityObject(universityId);
universityRepository.save(university );
}
College college = collegeRepository.getCollegeByCollegeId(university.getUniversityId(), collegeId);
if (college == null) {
college = createCollegeObject(university , collegeId);
collegeRepository.save(deviceDetails);
}
collegeStudentRepository.deleteByCollegeId(university.getUniversityId(), college.getCollegeId());
for (Student student: studentList) {
Student dbStudent = studentRepository.findByStudentId(student.getStudentId());
if (dbStudent == null) {
dbStudent = createStudentObject(student);
studentRepository.save(dbStudent);
}
CollegeStudent collegeStudent = createCollegeStudentObject(dbStudent, college);
collegeStudentRepository.save(collegeStudent);
}
}
休眠日志:
className=org.hibernate.engine.internal.StatisticalLoggingSessionEventListener, methodName=end> StatisticalLoggingSessionEventListener - Session Metrics {
308714170 nanoseconds spent acquiring 1 JDBC connections;
0 nanoseconds spent releasing 0 JDBC connections;
524069 nanoseconds spent preparing 1 JDBC statements;
309001256 nanoseconds spent executing 1 JDBC statements;
0 nanoseconds spent executing 0 JDBC batches;
0 nanoseconds spent performing 0 L2C puts;
0 nanoseconds spent performing 0 L2C hits;
0 nanoseconds spent performing 0 L2C misses;
197852 nanoseconds spent executing 1 flushes (flushing a total of 1 entities and 0 collections);
0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)
}
似乎每个save()都在创建新连接。
时间:
学生人数:5人
MySQL数据库:243毫秒
极光分贝:
32秒
(如果使用dbeaver直接插入数据库:1.5秒)
学生人数:30人
MySQL数据库:1秒
极光分贝:
173秒
(如果使用dbeaver直接插入数据库:9秒)
