I have a performance issue, but, despite what it suggests in the subject, it may not be that I am using createNativeQuery. I have one fairly large query that I pass as a string to createNativeQuery. The query contains several JOINs, and is associated with 5 entities. The query takes, on average, 20 - 25 seconds to run. I have experimented, like trying the createQuery method and using the setFlushMode(FlushModeType.COMMIT), setMaxResults() and setFirstResult() methods, but made no difference.
We are using JBoss EAP 6.2 in a JTA container-managed environment, using EJB 3.0, JPA 2.1, Hibernate 4.2. The IDE I use is NetBeans 8.0.2
Yet, if I run the query directly in a SQL tool like DB Visualizer it takes milliseconds. I suspect it might have something to do with Hibernate, but am not sure. None of the Entity classes contains a collection, although there are specific relationships between the tables, as indicated in the JOIN statements in the query. I have attached some code (including the query) below.
I am hoping to retain the query as-is, but if a suggestion is made that would make it more efficient I am open to that. I appreciate that it might be difficult to answer since the query below may be difficult to analyze without knowing the database schema (this is a company project, so can only provide pseudo code in some instances). I have also not provided the Entity or Facade classes, but can provide those later if necessary. Any help would be greatly appreciated. Thank you!
public List<String> searchSingleView (String sourceSystem, String sourceClientId, String convertedClientId, String policyNum) {
String sqlQuery = "SELECT DISTINCT " +
"MDMCUST_ORS.C_BO_PARTY_XREF.ROWID_XREF, " + "MDMCUST_ORS.C_BO_CONTRACT.LAST_ROWID_SYSTEM, " +
"MDMCUST_ORS.C_BO_CONTRACT.SRC_POLICY_ID, " +
"MDMCUST_ORS.C_BO_CONTRACT.ISSUE_DT, " +
"MDMCUST_ORS.C_BO_PARTY_XREF.SRC_CLIENT_ID, " +
"MDMCUST_ORS.C_BO_PARTY_XREF.PERS_FULL_NAME_TXT, " +
"MDMCUST_ORS.C_BO_PARTY_XREF.PERS_SRC_NAME_TXT, " +
"MDMCUST_ORS.C_BO_PARTY_XREF.ORG_LEGAL_NAME_TXT, " +
"MDMCUST_ORS.C_BO_PARTY_XREF.ORG_SRC_NAME_TXT, " +
"MDMCUST_ORS.C_BO_PARTY_XREF.ORG_LEGAL_SFX_TXT, " +
"MDMCUST_ORS.C_BO_PARTY_XREF.ORG_NAME_TXT, " +
"MDMCUST_ORS.C_BO_PARTY_XREF.SIN_BIN_TEXT, " +
"MDMCUST_ORS.C_BO_PARTY_XREF.PERS_BIRTH_DT, " +
"MDMCUST_ORS.C_BO_PARTY_POSTAL_ADDR_XREF.LAST_UPDATE_DATE, " +
"MDMCUST_ORS.C_BO_PARTY_POSTAL_ADDR_XREF.COMPLETE_ADDRESS_TXT, " +
"MDMCUST_ORS.C_BO_PARTY_POSTAL_ADDR_XREF.TOPLINE_POSTAL_ADDR_TXT, " +
"MDMCUST_ORS.C_BO_PARTY_POSTAL_ADDR_XREF.CITY_NAME, " +
"MDMCUST_ORS.C_BO_PARTY_POSTAL_ADDR_XREF.COUNTRY_NAME, " +
"MDMCUST_ORS.C_BO_PARTY_POSTAL_ADDR_XREF.POSTAL_CD, " +
"MDMCUST_ORS.C_BO_PARTY_POSTAL_ADDR_XREF.POSTAL_ADDR_PURPOSE_CD, " +
"MDMCUST_ORS.C_BO_PARTY_POSTAL_ADDR_XREF.STATE_PROVINCE_NAME, " +
"MDMCUST_ORS.C_BO_PARTY_POSTAL_ADDR_XREF.BAD_ADDR_IND " +
"FROM MDMCUST_ORS.C_BO_PARTY_XREF " +
"LEFT JOIN MDMCUST_ORS.C_BO_PARTY_POSTAL_ADDR_XREF ON " +
"(MDMCUST_ORS.C_BO_PARTY_POSTAL_ADDR_XREF.S_PARTY_ROWID = MDMCUST_ORS.C_BO_PARTY_XREF.SRC_CLIENT_ID " +
"OR MDMCUST_ORS.C_BO_PARTY_POSTAL_ADDR_XREF.S_PARTY_ROWID = MDMCUST_ORS.C_BO_PARTY_XREF.CONVERTED_CLIENT_ID) " +
"LEFT JOIN MDMCUST_ORS.C_LU_CODES ON MDMCUST_ORS.C_BO_PARTY_POSTAL_ADDR_XREF.POSTAL_ADDR_PURPOSE_CD = MDMCUST_ORS.C_LU_CODES.CODE " +
"LEFT JOIN MDMCUST_ORS.C_BO_PARTY_REL ON MDMCUST_ORS.C_BO_PARTY_XREF.ROWID_OBJECT = MDMCUST_ORS.C_BO_PARTY_REL.FROM_PARTY_ROWID " +
"LEFT JOIN MDMCUST_ORS.C_BO_CONTRACT ON MDMCUST_ORS.C_BO_PARTY_REL.CONTRACT_ROWID = MDMCUST_ORS.C_BO_CONTRACT.ROWID_OBJECT " +
"WHERE MDMCUST_ORS.C_BO_CONTRACT.LAST_ROWID_SYSTEM IN ('E-CAPSIL', 'C-CAPSIL', 'INGENIUM') " +
"AND (UPPER(MDMCUST_ORS.C_BO_PARTY_XREF.SRC_CLIENT_ID) = ? " +
" OR UPPER(MDMCUST_ORS.C_BO_PARTY_XREF.CONVERTED_CLIENT_ID) = ? " +
" ) " +
"AND UPPER(MDMCUST_ORS.C_BO_CONTRACT.SRC_POLICY_ID) = ? " +
"AND MDMCUST_ORS.C_BO_PARTY_POSTAL_ADDR_XREF.POSTAL_ADDR_PURPOSE_CD = '56|07'" +
"AND MDMCUST_ORS.C_BO_PARTY_XREF.HUB_STATE_IND = '1' " +
"AND MDMCUST_ORS.C_BO_CONTRACT.HUB_STATE_IND = '1' " +
"AND MDMCUST_ORS.C_BO_PARTY_POSTAL_ADDR_XREF.HUB_STATE_IND = '1' " +
"AND MDMCUST_ORS.C_LU_CODES.HUB_STATE_IND = '1'" +
"AND ROWNUM = 1";
try {
querySingleView = emSingleView.createNativeQuery(sqlSingleViewQuery)
.setParameter(1, sourceClientId)
.setParameter(2, convertedClientId)
.setParameter(3, policyNum);
querySingleViewResult = (List<String>) querySingleView.getResultList();
} catch (NoResultException nre) {
// Issue a validation failure message to prevent query from continuing.
FacesContext.getCurrentInstance().validationFailed();
// Display error message to user
JsfUtil.addErrorMessage(ResourceBundle.getBundle("/Bundle").getString("fatcaSearchSingleViewFailMessage"));
// Generate log entry on server
java.util.logging.Logger.getLogger(Login.class.getName()).log(Level.SEVERE, null, nre);
}
return querySingleViewResult;
}
This
UPPER(MDMCUST_ORS.C_BO_CONTRACT.SRC_POLICY_ID) = ? "
will most likely prevent Oracle from using an index on SRC_POLICY_ID. MIf it is an ID I would expect it to be a number so an UPPER should not be necessary. If you really do need the UPPER than you propably should create a function based index on the column.
But without access to the database performance issues are hard to solve.
P.S.: And do you really need the DISTINCT and ROWNUMBER=1. I think you should write the query that you get only one row by using the proper conditions in the WHERE clause.