JBossDeveloper

No, I have not tried Netbeans Profiler. It would take some time for me to setup Netbeans, since I am using Eclipse.

I have not tried disabling the hibernate logging.

I did prepare the statement outside the loop to where I only needed to add the parameters and get the result. It still took 5 seconds per record.

I looked online lastnight and came across other people having the same problem. It looks like a JPA thing on getResultList() or getSingleResult().

Just tried it with the logging turned off. It shaved between 1 to 1.5 seconds off.

Just completed testing by creating a connection and a prepared statement, then populating the fields in the prepared statement and executing. This is turning out to be as fast as the stored procedure.

From the testing I've done, something within the JPA's getResultList() and getSingleResult() is causing a bottleneck.

Update...

Rather than initially getting all 42,000 inCityStateZip records, I broke the call into smaller records for processing to something like:

               utx.begin();

               Query cszQuery = em.createNamedQuery("cityStateZip.findIdFromCityStateZip");
               Query searchQuery = em.createNamedQuery(
                         "inCityStateZip.findRecordsByInFilesId").setParameter(
                         InCityStateZip.FIELD_TO_INFILES, inFiles.getId());

               long resultCount = (Long) em.createNamedQuery("inCityStateZip.findCountByInFilesId")
                    .setParameter(InCityStateZip.FIELD_TO_INFILES, inFiles.getId())
                    .getSingleResult();
               int totalPageCount = (int) (resultCount / (long) maxResults);
               if (resultCount%maxResults > 0) {
                    totalPageCount = totalPageCount + 1;
               }

               while (pageCount < totalPageCount) {
                    resultList = (List<InCityStateZip>)searchQuery.setFirstResult(firstResult)
                              .setMaxResults(maxResults).getResultList();
                    int count = 0;
                    for (InCityStateZip inCityStateZip : resultList) {
                                (Other Code)
                    }
                    firstResult = firstResult + maxResults;
                    pageCount++;
               }
               log.info("flushing...");
               flushAndClear(maxResults);
               utx.commit();

          } catch (Exception ex) {
               utx.rollback();
               ex.printStackTrace();
          }                         

The performance has greatly increased.