5 Replies Latest reply on Feb 16, 2007 6:24 PM by Ale Fred

    slow queries using lobs in entities

    Ale Fred Newbie

      I have a problem querying an entity with a lob property, thats my entity's definition :

      @Entity
      public class File{
      @Id @GeneratedValue(strategy=GenerationType.AUTO)
      long code ;
      @ManyToOne
      Person person = null;
      Date timestamp = Calendar.getInstance().getTime();
      String fileName = "";
      @OneToOne
      User creator = null;
      @OneToOne
      User lastUpdater = null;
      String description = "";
      @Lob @Basic(fetch = FetchType.LAZY)
      Blob contents = null;
      
      ...geters/setters
      }
      


      and that's the way a query the objects :

      List<File> results =entityManager.createQuery(" from File f").getResultList();


      entityManager is injected from the seam's context.
      I'm using mysql with mysql inno dialect, the query is very very slow and very memory consuming cause hibernate includes the lob field into the sql.

      What am i not doing in the right way?

      thanks.



        • 1. Re: slow queries using lobs in entities
          Fernando Montaño Expert

          I'm not sure, but I don't know if using BLOB (I suppose is a SQL type class) as bean property is the right way.

          Maybe you can try changing it by:



          @Lob @Basic(fetch = FetchType.LAZY)
          byte[] contents = null;
          


          or

          @Lob @Basic(fetch = FetchType.LAZY)
          InputStrem contents = null;
          



          HTH.



          • 2. Re: slow queries using lobs in entities
            Ale Fred Newbie

            i modified the code as you suggested but nothing new happened.
            The problem is that i cannot have hibernate to skip the blob column in the generated sql.

            mmm..... it seems a so easy issue to solve...

            • 3. Re: slow queries using lobs in entities
              Gavin King Master

              Ask this stuff in the Hibernate forums.

              • 4. Re: slow queries using lobs in entities
                John Ray Newbie

                I had this problem yesterday. I'm using postgresql and defined the field like:

                byte[] contents = null;

                This create a bytea (similar to a blob) field in the database. If I annotated it with @Lob then a uid field is created. and contents is stored in a different location in the database. I believe that either ways works and there are pro and cons to using either a bytea or uid. I personally just went with the bytea.

                I also had problems getting lazy fetching to work. In order to get lazy fetching to work you have to "instrument' your class files. This involves running an ant task on your compiled .class file. The easier and possibly better way is to use a query that does a "select new". That way you don't have to instrument your class files. For your class you would do a query like

                select new File(f.person,f.filename) from File f

                The above query only loads the person and filename fields. In order to get it to work you need to have 2 constructors in your class. The regular no args one and the constructor that takes a person and filename which is called by the above query. The downside to this method is that it is read only. You can't make modifications to the File object loaded with a select new and then write it to the database.



                • 5. Re: slow queries using lobs in entities
                  Ale Fred Newbie

                  thank you very much, it helped me a lot for the moment.