Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

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.

share|improve this answer

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 "
      + "insert (RPTG_ID, EAI_RCVD_DATE, EAI_MSG_TEXT, MSG_REQ_TEXT, MSG_RPLY_TEXT, EAI_SVC_NAME, EQMT_CNT, CORR_ID,MSG_KEY) "
      + "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);
    }
  }
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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