Query in EntityHome Slow
sandman202 Jul 15, 2008 5:17 AMIn one of my EntityHome classes I have a button where I am looping through a list of InCityStateZip database records I imported and copying the records to a CityStateZip database table. This looping seems to work fine. Reading over 42,000 records within about 3 minutes.
One thing I am trying to do before trying to add the records is to check to see if the city, state, zip already exists within the CityStateZip database table. The problem I am seeing is this SQL validation takes about 5 seconds on each record. So, multiple this on 42,000 records and you see the performance issue. If I take the SQL statement and run this directly in MySQL, it takes approximately 0.0054s.
Here is my SQL in my EntityHome class.
private boolean doesCityStateZipExist(String city, String province, String zipcode) { long before = System.currentTimeMillis(); this.info("Checking to see if CityStateZip exists..."); this.info("City: " + city + " State: " + province + " Zip: " + zipcode); boolean result = true; try { CityStateZip csz = (CityStateZip) this.getEntityManager() .createNamedQuery("cityStateZip.findIdFromCityStateZip") .setParameter(CityStateZip.FIELD_CITY, city) .setParameter(CityStateZip.FIELD_PROVINCE, province) .setParameter(CityStateZip.FIELD_ZIPCODE, zipcode) .getSingleResult(); if (csz.equals(null)) { result = false; } } catch (NoResultException ex) { this.info("No result exception"); result = false; } finally { this.info("CityStateZip check complete. Expired ms = " + (System.currentTimeMillis() - before)); } return result; }
Here is what some of the log file is reporting:
21:03:46,136 INFO [InFilesHome] Checking to see if CityStateZip exists... 21:03:46,136 INFO [InFilesHome] City: AGUADILLA State: PR Zip: 00605 21:03:49,464 INFO [STDOUT] Hibernate: select csz.* from CityStateZip csz where csz.city = 'AGUADILLA' and csz.province = 'PR' and csz.zipcode = '00605' 21:03:49,527 INFO [InFilesHome] No result exception 21:03:49,527 INFO [InFilesHome] CityStateZip check complete. Expired ms = 3391
Does anyone have any idea why this is happening? Is there a better way to do this?
Thanks,
Scott