Hibernate : JDBC Vs Hibernate Performance in bulk records

Question

Description : LoadoutOperatingSession represents a particular time period and it belongs to a certain loadoutId. There can be a lot of LoadoutOperatingSession objects with the same loadoutId

Before inserting a new session, a check had to be performed for overlappings.

Below is the model object that I have designed.

public class LoadoutOperatingSession extends Entity implements Serializable, Comparable<LoadoutOperatingSession>{

private Long loadoutId;
private Date effectiveFromDate;
private Date effectiveToDate;
private String sessionStartTime;
private String sessionEndTime;
private String isSchedule;


/**
 *  Compares the given session with the current one and return 1 if this session is greater than the given session,
 * -1 if the this session is less than the given session and
 *  0 is the sessions are overlapping.
 *  
 * @param  session1
 *         First Session
 *         
 * @param session
 *         Second Session
 */

@Override
public int compareTo(LoadoutOperatingSession session) {

    if (session.getEffectiveToDate().getTime() < this.getEffectiveFromDate().getTime()) {
        return 1;
    } else if (this.getEffectiveToDate().getTime() < session.getEffectiveFromDate().getTime()) {
        return -1;
    } else if (this.getEffectiveFromDate().getTime() == session.getEffectiveFromDate().getTime()) {

        int thisStartTime = Integer.parseInt(this.getSessionStartTime());
        int thisEndTime = Integer.parseInt(this.getSessionEndTime());

        int sessionStartTime = Integer.parseInt(session.getSessionStartTime());
        int sessionEndTime = Integer.parseInt(session.getSessionEndTime());

        if (thisEndTime < sessionStartTime) {
            return -1;
        } else if (thisStartTime > sessionEndTime) {
            return 1;
        }
        return 0;
    }
    return 0;
}

}

Assume there are lots of LoadoutOperatingSession objects with the same loadoutId. In order to check for overlaps, I have fetched all the LoadoutOperatingSession objects and used the compareTo method to compare with each other.

Note : This check is done before persisting the current Session.

fetchLoadoutOperatingSessionsList method will return all the LoadoutOperatingSession Objects for a given loadoutId

validateForOverlappings(model, fetchLoadoutOperatingSessionsList(model));

private <T extends Comparable> void validateForOverlappings(T obj, List<T> objList){        

    for (Comparable c : objList) {
        if(obj.compareTo((T) c) == 0){
            throw new IllegalArgumentException("Overlapping Sessions are not allowed!");
        }
    }
}

Question : This same validation can be done by executing a JDBC query and taking a count of the overlapping sessions.

Would it be more efficient than the above mentioned java solution?

Please Justify.

Jan Hruby · Answer 1 · 2015-08-05T13:47:09.640

2

Using this SQL statement it is possible to perform the check on DB level:

select top 1 a.id 
from LoadoutOperatingSession a, LoadoutOperatingSession b 
where a.FROM_DATE <= b.TO_DATE and b.FROM_DATE <= a.TO_DATE

Why is it faster:

it is same as on application level. SQL returns first overlapping record
BUT, you are not returning list of records from DB, but simple integer - no serialization overhead

However I think there is something else wrong if you are throwing IllegalArgument on application level after validating records which are read from DB. I do not know your use case but it would be much safer to prevent saving such records

EDIT

My previous answer was not correct. This is the equal check done on DB level

select top 1 a.id 
from LoadoutOperatingSession a 
where a.FROM_DATE <= :insertedStartDate and a.TO_DATE >= :insertedEndDate

edited Aug 05 '15 at 13:47

answered Aug 05 '15 at 12:20

Jan Hruby

1,477
1
13
26

Thank You for sharing your knowledge. Actually this is a backend validation and the message from the exception will be extracted and will be shown to the user. User will not see any exception details. Kindly explain if there is something wrong in it. – tharindu_DG Aug 05 '15 at 13:04
1

ok, there is nothing wrong with your validation, technically. It is more a common sense issue - you allow user to save overlapping session. But, when the sessions are being retrieved, there is backend validation if they are not overlapping which results in exception if it is so. Wouldn't it be better to prevent saving overlapping sessions? This is quite good reference: http://stackoverflow.com/questions/4023160/prevent-inserting-overlapping-date-ranges-using-a-sql-trigger – Jan Hruby Aug 05 '15 at 13:16
Exception is only thrown if there is an overlapping and it is checked before saving the session. Current session will be checked with the sessions that are already saved. Your suggestion is not to throw an exception and just avoid saving if an overlapping occurs ? Kindly Explain. – tharindu_DG Aug 05 '15 at 13:37
aha, now I understand. It depends on your usecase what should happen, but in such case exception seems correct. Also, my answer is inaccurate, because the check on DB level is done without the record you are trying to insert. I'll adapt the query – Jan Hruby Aug 05 '15 at 13:44
I guess my question is unclear. I have edited the question. Thank You for sharing your knowledge. – tharindu_DG Aug 05 '15 at 13:49

Hibernate : JDBC Vs Hibernate Performance in bulk records

1 Answers1