What is the faster statement? A MERGE or Select->Update/Insert depending on if it returns a value?

I'm trying to optimize a database, but I haven't found any speed tests comparing the two.

I found the MERGE command through a StackOverflow answer

2 Answers 2


A MERGE would be more efficient since you're only executing the query once. Even better would be to do a set-based MERGE rather than doing a bunch of single-row MERGE statements which is what I'm assuming you'd be implementing given the alternative of doing a SELECT to see if the row exists.

Rather than doing a SELECT and then determining whether to do an INSERT or an UPDATE, you would be better off doing the UPDATE, checking to see if you updated 0 rows or not, and then doing the INSERT if your UPDATE didn't affect any rows. That way, source rows that require an UPDATE would be a bit more efficient and rows that require an INSERT would be essentially just as fast as if you had done a SELECT and INSERT. This approach is still going to be slower than a MERGE, but it's better than doing the extra SELECT.


Merge is not working for CLOB. it is working only for small string values. In the below sql : eai_msg, msg_req_text, msg_req_rply are all CLOB.

If I pass data like "something", it works.

It is not working for input of 1200+ char:

"merge into some_table  msg using (select 1 from DUAL) d "
      + "on (msg.corr_id= :correlationId) "
      + "when matched then "
      + "update set  msg.rptg_id = :rptgId, "
      + "eai_rcvd_date = :receivedDate, "
      + "msg.eai_msg_text = :msgText, "
      + "msg.msg_req_text = :request, "
      + "msg.msg_rply_text = :reply, "
      + "msg.eai_svc_name = :serviceName, "
      + "msg.eqmt_cnt = :eqmtCount "
      + "when not matched then "
      + "values (:rptgId, :receivedDate, :msgText, :request,:reply,:serviceName, :eqmtCount, :correlationId,:msgKey)"

here is my save method in dao

  public int saveEaiData(final SaveOriginalEAIRequestDto saveOriginalEaiDto) {
    NCDateTime eaiReceivedDateTime = saveOriginalEaiDto.getEaiReceivedDateTime();
    MapSqlParameterSource paramMap = new MapSqlParameterSource();
    paramMap.addValue("msgKey", sequence.nextLongValue());
    paramMap.addValue("rptgId", saveOriginalEaiDto.getReportingId());
    if (eaiReceivedDateTime != null) {
      paramMap.addValue("receivedDate", eaiReceivedDateTime.getCalendarUTC());
    } else {
      paramMap.addValue("receivedDate", null);
    paramMap.addValue("msgText", saveOriginalEaiDto.getEaiText(), Types.CLOB);
    paramMap.addValue("request", saveOriginalEaiDto.getRequestText(), Types.CLOB);
    paramMap.addValue("reply", saveOriginalEaiDto.getReplyText(), Types.CLOB);
    paramMap.addValue("serviceName", saveOriginalEaiDto.getEaiServiceName());
    paramMap.addValue("eqmtCount", saveOriginalEaiDto.getEquipmentCount());
    paramMap.addValue("correlationId", saveOriginalEaiDto.getCorrelationId());
    try {
      return getSimpleJdbcTemplate().update(MERGE_ORIGINAL_EAI_MSG_RECEIVED, paramMap);
    } catch (Exception e) {
      throw new IllegalStateException("Exception occured in saveEaiData" + e);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.