1 2 Previous Next 19 Replies Latest reply on Jul 28, 2008 9:46 PM by scott duke

    Query in EntityHome Slow

    scott duke Novice

      In 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

        • 1. Re: Query in EntityHome Slow
          System Administrator Expert

          Is that code above in a EJB3 component (e.g. SLSB or SFSB)?


          In any event, it's possible that at least some of the performance hit is due to the opening and closing of db connections by Hibernate via JDBC driver (although don't quote me on that!)


          Investigate 2nd level cache and possibly using stored procs to improve performance.


          For example, in section 13.4 - Caching in practice of the Bauer and King book, you'll see how to setup and use a 2nd level cache for particular entity classes.  You will have to select a cache provider and I don't think JPA provides an equivalent to 2nd level cache that Hibernate does.


          Perhaps somebody with more Hibernate experience can correct me here if I'm wrong...

          • 2. Re: Query in EntityHome Slow
            scott duke Novice

            Right now it is inside my EntityHome class, but I plan on moving it into a SLSB.
            Stored procedures are nice, but when you deal with multiple databases, the stored procedures are completely different. This will add to the overall maintenance, etc. I want to keep my code portable.


            As I stated originally, looping through 42,000 records took about 3 minutes. That is just copying each record from one database table to another. So, I am thinking this additional query has to do with something else to dramatically slow it down to over an hour.


            I had intended to add Hibernate at some point. I might go ahead and add it.

            • 3. Re: Query in EntityHome Slow
              Francisco Jose Peredo Noguez Master

              Do you have an unique index in those fields?

              • 4. Re: Query in EntityHome Slow
                scott duke Novice

                I did not before, but I do now. I just tried it and it still is taking 5 seconds.


                I am going to try creating a stored procedure as per Arbi. I'll post a response in a few on my results.

                • 5. Re: Query in EntityHome Slow
                  Francisco Jose Peredo Noguez Master

                  How many fields are there in the table CityStateZip ? only three?

                  • 6. Re: Query in EntityHome Slow
                    Francisco Jose Peredo Noguez Master

                    Have you tried getting this.getEntityManager() out of the loop? (maybe the lookup for it is what is making things slower)

                    • 7. Re: Query in EntityHome Slow
                      scott duke Novice

                      Francisco, I have 10 fields in my City-State-Zip database table.


                      I just added the stored procedure and still trying to work out a few bugs.


                      I will try taking the getEntityManager() outside of the loop.

                      • 8. Re: Query in EntityHome Slow
                        Francisco Jose Peredo Noguez Master

                        Hi!



                        scott duke wrote on Jul 16, 2008 05:42:


                        Francisco, I have 10 fields in my City-State-Zip database table.



                        Hi... can you post the code in CityStateZip.java? (If you have a field with lots of data, like for example a BLOB (byte[]) that could be what is making this slow.


                        If  all you want is to know if the record is already there, you should be using the count function, and you should be seeing something like this in the log output :


                         select
                                count(csz.*) 
                            from
                                CityStateZip csz 
                            where
                                csz.city = 'AGUADILLA' 
                                and csz.province = 'PR' 
                                and csz.zipcode = '00605'
                        
                        



                        But by the log output you posted I see you are not using count... why is that? (remember you can use aggregate functions like count in JPAQL)




                        I just added the stored procedure and still trying to work out a few bugs.



                        And it got faster? are you doing all the copying in the stored procedure? or you are using it only for querying?


                        Regards,

                        • 9. Re: Query in EntityHome Slow
                          Francisco Jose Peredo Noguez Master

                          And remember if you use count it will not return a CityStateZip, it will return a number of long type. (all you have to do then is to check if it is <0.

                          • 10. Re: Query in EntityHome Slow
                            scott duke Novice

                            The whole idea behind checking to see if the data already exists is when I add other imports. For some imports I want to check to see if the data exists. If it does, then I want to update the existing record.


                            I'm still working on the stored procedure part. I have the SQL script working, but for some reason I am not getting a database connection in my java code. Also, the stored procedure is using count.


                            The CityStateZip.java does not contain a blob. Here is what it looks like:



                            @NamedQueries( { @NamedQuery(name = "cityStateZip.findIdFromCityStateZip", query = "select csz from CityStateZip csz where csz.city = :city and csz.province = :province and csz.zipcode = :zipcode") })
                            @Table(uniqueConstraints = @UniqueConstraint(columnNames = {"City", "Province", "ZipCode"}))
                            @org.hibernate.annotations.Table(appliesTo = "cityStateZip", indexes = {
                                 @Index(name = "IDX_CITY", columnNames = {"city"}),
                                 @Index(name = "IDX_PROVINCE", columnNames = {"province"}),
                                 @Index(name = "IDX_ZIPCODE", columnNames = {"zipcode"})})
                            @Entity
                            public class CityStateZip implements Serializable {
                                 
                                 private static final long serialVersionUID = -5785841250898149436L;
                                 // Field names which can be accessed from other programs.
                                 public static final String FIELD_ID = "id";
                                 public static final String FIELD_VERSION = "version";
                                 public static final String FIELD_ZIPCODE = "zipcode";
                                 public static final String FIELD_LATITUDE = "latitude";
                                 public static final String FIELD_LONGITUDE = "longitude";
                                 public static final String FIELD_CITY = "city";
                                 public static final String FIELD_PROVINCE = "province";
                                 public static final String FIELD_COUNTY = "county";
                                 public static final String FIELD_ZIPCLASS = "zipclass";
                                 public static final String FIELD_COUNTRY = "country";
                            
                                 //seam-gen attributes (you should probably edit these)
                                 private Long id;
                                 private Integer version;
                                 private String zipcode;
                                 private float latitude;
                                 private float longitude;
                                 private String city;
                                 private String province;
                                 private String county;
                                 private String zipclass;
                                 private String country;
                                 
                                //add additional entity attributes
                                 
                                 //seam-gen attribute getters/setters with annotations (you probably should edit)
                                      
                                 @Id @GeneratedValue
                                 @Column(name = FIELD_ID)
                                 public Long getId() {
                                      return id;
                                 }
                            
                                 public void setId(Long id) {
                                      this.id = id;
                                 }
                                 
                                 @Version
                                 @Column(name = FIELD_VERSION)
                                 public Integer getVersion() {
                                      return version;
                                 }
                            
                                 @SuppressWarnings("unused")
                                 private void setVersion(Integer version) {
                                      this.version = version;
                                 }
                            
                                 /**
                                  * @return the zipcode
                                  */
                                 @Column(name = FIELD_ZIPCODE)
                                 public String getZipcode() {
                                      return zipcode;
                                 }
                            
                                 /**
                                  * @param zipcode the zipcode to set
                                  */
                                 public void setZipcode(String zipcode) {
                                      this.zipcode = zipcode;
                                 }
                            
                                 /**
                                  * @return the latitude
                                  */
                                 @Column(name = FIELD_LATITUDE)
                                 public float getLatitude() {
                                      return latitude;
                                 }
                            
                                 /**
                                  * @param latitude the latitude to set
                                  */
                                 public void setLatitude(float latitude) {
                                      this.latitude = latitude;
                                 }
                            
                                 /**
                                  * @return the longitude
                                  */
                                 @Column(name = FIELD_LONGITUDE)
                                 public float getLongitude() {
                                      return longitude;
                                 }
                            
                                 /**
                                  * @param longitude the longitude to set
                                  */
                                 public void setLongitude(float longitude) {
                                      this.longitude = longitude;
                                 }
                            
                                 /**
                                  * @return the city
                                  */
                                 @Column(name = FIELD_CITY)
                                 public String getCity() {
                                      return city;
                                 }
                            
                                 /**
                                  * @param city the city to set
                                  */
                                 public void setCity(String city) {
                                      this.city = city;
                                 }
                            
                                 /**
                                  * @return the province
                                  */
                                 @Column(name = FIELD_PROVINCE)
                                 public String getProvince() {
                                      return province;
                                 }
                            
                                 /**
                                  * @param province the province to set
                                  */
                                 public void setProvince(String province) {
                                      this.province = province;
                                 }
                            
                                 /**
                                  * @return the county
                                  */
                                 @Column(name = FIELD_COUNTY)
                                 public String getCounty() {
                                      return county;
                                 }
                            
                                 /**
                                  * @param county the county to set
                                  */
                                 public void setCounty(String county) {
                                      this.county = county;
                                 }
                            
                                 /**
                                  * @return the zipclass
                                  */
                                 @Column(name = FIELD_ZIPCLASS)
                                 public String getZipclass() {
                                      return zipclass;
                                 }
                            
                                 /**
                                  * @param zipclass the zipclass to set
                                  */
                                 public void setZipclass(String zipclass) {
                                      this.zipclass = zipclass;
                                 }
                            
                                 /**
                                  * @return the country
                                  */
                                 @Column(name = FIELD_COUNTRY)
                                 public String getCountry() {
                                      return country;
                                 }
                            
                                 /**
                                  * @param country the country to set
                                  */
                                 public void setCountry(String country) {
                                      this.country = country;
                                 }       
                            }
                            


                            • 11. Re: Query in EntityHome Slow
                              scott duke Novice

                              I got the stored procedure to work. It has sped up the program to right at 5 minutes.


                              I'm going to try a few other ways as suggested earlier rather than using a stored procedure to be more portable.

                              • 12. Re: Query in EntityHome Slow
                                scott duke Novice

                                I moved the getEntityManager() outside the loop. It is still taking about 5 seconds.

                                • 13. Re: Query in EntityHome Slow
                                  Francisco Jose Peredo Noguez Master

                                  Have you tried running this with Netbeans Profiler (that is what I do whan I have to deal with this kind of puzzling slow behavior) (I have tried with TPTP but it crashes)

                                  • 14. Re: Query in EntityHome Slow
                                    Francisco Jose Peredo Noguez Master

                                    Have you tried disabling hibernate logging? (logging of the SQL generated by hibernate is very very slow)

                                    1 2 Previous Next