-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Projects » Search, Validator, Shards

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 3 posts ] 
  Previous topic | Next topic 
Author Message
tbdsi785
 Post subject: Multiple Unnecessary Hibernate Database Queries
PostPosted: Sat Feb 23, 2013 4:02 am 
Newbie

Joined: Wed Dec 02, 2009 8:50 pm
Posts: 5
Hi,

I have a simple item table (no joins) which I indexed using hibernate search. When I search for a word in a field, if there are 5 records which match the result, Hibernate is executing 1 + 5 queries.

My Item Class:

Code:
package com.model.item;

import java.util.Date;

import org.hibernate.search.annotations.Analyze;
import org.hibernate.search.annotations.DocumentId;
import org.hibernate.search.annotations.Field;
import org.hibernate.search.annotations.FieldBridge;
import org.hibernate.search.annotations.Indexed;

@Indexed
public class Item {
  @DocumentId
  protected Integer id;
 
  protected Integer status;
  @Field(analyze = Analyze.NO)
  protected Character watching;

  protected Date startDate;
  @Field(name = "enddate", analyze = Analyze.NO)
  @FieldBridge(impl = MyDateBridge.class)
  protected Date endDate;

  protected Double price;
  protected Integer bidCount;

  @Field(name = "highbidder", analyze = Analyze.NO)
  protected String highBidder;

  public Item() {
  }

  public Integer getID() {
    return id;
  }

  public void setID(Integer id) {
    this.id = id;
  }

  public Integer getStatus() {
    return status;
  }

  public void setStatus(Integer status) {
    this.status = status;
  }

  public Character getWatching() {
    return watching;
  }

  public void setWatching(Character watching) {
    this.watching = watching;
  }

  public Date getStartDate() {
    return startDate;
  }

  public void setStartDate(Date startDate) {
    this.startDate = startDate;
  }

  public Date getEndDate() {
    return endDate;
  }

  public void setEndDate(Date endDate) {
    this.endDate = endDate;
  }

  public Double getPrice() {
    return price;
  }

  public void setPrice(Double price) {
    this.price = price;
  }

  public Integer getBidCount() {
    return bidCount;
  }

  public void setBidCount(Integer bidCount) {
    this.bidCount = bidCount;
  }

  public String getHighBidder() {
    return highBidder;
  }

  public void setHighBidder(String highBidder) {
    this.highBidder = highBidder;
  }

  public boolean equals(Object other) {
    if (this == other)
      return true;
    if (!(other instanceof Item))
      return false;

    final Item that = (Item) other;
    return this.getID().equals(that.getID());
  }

  public int hashCode() {
    return id.hashCode();
  }
}


I also have the corresponding item.hbm.xml file

Here is the search code:

Code:
FullTextSession fullTextSession = Search.getFullTextSession(session);
   
QueryParser queryParser = new QueryParser(Version.LUCENE_35, "highbidder", new KeywordAnalyzer());
org.apache.lucene.search.Query luceneQuery = queryParser.parse("asdfg");  //Two records match the result
System.out.println(luceneQuery.toString());
FullTextQuery fullTextQuery = fullTextSession.createFullTextQuery(luceneQuery, Item.class);

System.out.println("Here1");
List<Item> items = fullTextQuery.list();
   
System.out.println("HERE2");
System.out.flush();


Here are the results when I run the above code (I have show SQL & logging set to TRACE):

Code:
[DEBUG,logging,main] Logging Provider: org.jboss.logging.Log4jLoggerProvider
[DEBUG,BasicTypeRegistry,main] Adding type registration boolean -> org.hibernate.type.BooleanType@16ff2d9
[DEBUG,BasicTypeRegistry,main] Adding type registration boolean -> org.hibernate.type.BooleanType@16ff2d9
[DEBUG,BasicTypeRegistry,main] Adding type registration java.lang.Boolean -> org.hibernate.type.BooleanType@16ff2d9
[DEBUG,BasicTypeRegistry,main] Adding type registration numeric_boolean -> org.hibernate.type.NumericBooleanType@19846fd
[DEBUG,BasicTypeRegistry,main] Adding type registration true_false -> org.hibernate.type.TrueFalseType@1f1a3a8
[DEBUG,BasicTypeRegistry,main] Adding type registration yes_no -> org.hibernate.type.YesNoType@673c9f
[DEBUG,BasicTypeRegistry,main] Adding type registration byte -> org.hibernate.type.ByteType@165d118
[DEBUG,BasicTypeRegistry,main] Adding type registration byte -> org.hibernate.type.ByteType@165d118
[DEBUG,BasicTypeRegistry,main] Adding type registration java.lang.Byte -> org.hibernate.type.ByteType@165d118
[DEBUG,BasicTypeRegistry,main] Adding type registration character -> org.hibernate.type.CharacterType@b08f1
[DEBUG,BasicTypeRegistry,main] Adding type registration char -> org.hibernate.type.CharacterType@b08f1
[DEBUG,BasicTypeRegistry,main] Adding type registration java.lang.Character -> org.hibernate.type.CharacterType@b08f1
[DEBUG,BasicTypeRegistry,main] Adding type registration short -> org.hibernate.type.ShortType@14b9b80
[DEBUG,BasicTypeRegistry,main] Adding type registration short -> org.hibernate.type.ShortType@14b9b80
[DEBUG,BasicTypeRegistry,main] Adding type registration java.lang.Short -> org.hibernate.type.ShortType@14b9b80
[DEBUG,BasicTypeRegistry,main] Adding type registration integer -> org.hibernate.type.IntegerType@111985e
[DEBUG,BasicTypeRegistry,main] Adding type registration int -> org.hibernate.type.IntegerType@111985e
[DEBUG,BasicTypeRegistry,main] Adding type registration java.lang.Integer -> org.hibernate.type.IntegerType@111985e
[DEBUG,BasicTypeRegistry,main] Adding type registration long -> org.hibernate.type.LongType@1af1915
[DEBUG,BasicTypeRegistry,main] Adding type registration long -> org.hibernate.type.LongType@1af1915
[DEBUG,BasicTypeRegistry,main] Adding type registration java.lang.Long -> org.hibernate.type.LongType@1af1915
[DEBUG,BasicTypeRegistry,main] Adding type registration float -> org.hibernate.type.FloatType@404eb2
[DEBUG,BasicTypeRegistry,main] Adding type registration float -> org.hibernate.type.FloatType@404eb2
[DEBUG,BasicTypeRegistry,main] Adding type registration java.lang.Float -> org.hibernate.type.FloatType@404eb2
[DEBUG,BasicTypeRegistry,main] Adding type registration double -> org.hibernate.type.DoubleType@cd2163
[DEBUG,BasicTypeRegistry,main] Adding type registration double -> org.hibernate.type.DoubleType@cd2163
[DEBUG,BasicTypeRegistry,main] Adding type registration java.lang.Double -> org.hibernate.type.DoubleType@cd2163
[DEBUG,BasicTypeRegistry,main] Adding type registration big_decimal -> org.hibernate.type.BigDecimalType@7cf883
[DEBUG,BasicTypeRegistry,main] Adding type registration java.math.BigDecimal -> org.hibernate.type.BigDecimalType@7cf883
[DEBUG,BasicTypeRegistry,main] Adding type registration big_integer -> org.hibernate.type.BigIntegerType@151a712
[DEBUG,BasicTypeRegistry,main] Adding type registration java.math.BigInteger -> org.hibernate.type.BigIntegerType@151a712
[DEBUG,BasicTypeRegistry,main] Adding type registration string -> org.hibernate.type.StringType@add57
[DEBUG,BasicTypeRegistry,main] Adding type registration java.lang.String -> org.hibernate.type.StringType@add57
[DEBUG,BasicTypeRegistry,main] Adding type registration url -> org.hibernate.type.UrlType@27de24
[DEBUG,BasicTypeRegistry,main] Adding type registration java.net.URL -> org.hibernate.type.UrlType@27de24
[DEBUG,BasicTypeRegistry,main] Adding type registration date -> org.hibernate.type.DateType@3e48f2
[DEBUG,BasicTypeRegistry,main] Adding type registration java.sql.Date -> org.hibernate.type.DateType@3e48f2
[DEBUG,BasicTypeRegistry,main] Adding type registration time -> org.hibernate.type.TimeType@7148e9
[DEBUG,BasicTypeRegistry,main] Adding type registration java.sql.Time -> org.hibernate.type.TimeType@7148e9
[DEBUG,BasicTypeRegistry,main] Adding type registration timestamp -> org.hibernate.type.TimestampType@d6a0e0
[DEBUG,BasicTypeRegistry,main] Adding type registration java.sql.Timestamp -> org.hibernate.type.TimestampType@d6a0e0
[DEBUG,BasicTypeRegistry,main] Adding type registration java.util.Date -> org.hibernate.type.TimestampType@d6a0e0
[DEBUG,BasicTypeRegistry,main] Adding type registration dbtimestamp -> org.hibernate.type.DbTimestampType@7b0fb
[DEBUG,BasicTypeRegistry,main] Adding type registration calendar -> org.hibernate.type.CalendarType@94d313
[DEBUG,BasicTypeRegistry,main] Adding type registration java.util.Calendar -> org.hibernate.type.CalendarType@94d313
[DEBUG,BasicTypeRegistry,main] Adding type registration java.util.GregorianCalendar -> org.hibernate.type.CalendarType@94d313
[DEBUG,BasicTypeRegistry,main] Adding type registration calendar_date -> org.hibernate.type.CalendarDateType@7ee46a
[DEBUG,BasicTypeRegistry,main] Adding type registration locale -> org.hibernate.type.LocaleType@223d9b
[DEBUG,BasicTypeRegistry,main] Adding type registration java.util.Locale -> org.hibernate.type.LocaleType@223d9b
[DEBUG,BasicTypeRegistry,main] Adding type registration currency -> org.hibernate.type.CurrencyType@586403
[DEBUG,BasicTypeRegistry,main] Adding type registration java.util.Currency -> org.hibernate.type.CurrencyType@586403
[DEBUG,BasicTypeRegistry,main] Adding type registration timezone -> org.hibernate.type.TimeZoneType@9fa12f
[DEBUG,BasicTypeRegistry,main] Adding type registration java.util.TimeZone -> org.hibernate.type.TimeZoneType@9fa12f
[DEBUG,BasicTypeRegistry,main] Adding type registration class -> org.hibernate.type.ClassType@1a896a4
[DEBUG,BasicTypeRegistry,main] Adding type registration java.lang.Class -> org.hibernate.type.ClassType@1a896a4
[DEBUG,BasicTypeRegistry,main] Adding type registration uuid-binary -> org.hibernate.type.UUIDBinaryType@1aaf194
[DEBUG,BasicTypeRegistry,main] Adding type registration java.util.UUID -> org.hibernate.type.UUIDBinaryType@1aaf194
[DEBUG,BasicTypeRegistry,main] Adding type registration uuid-char -> org.hibernate.type.UUIDCharType@55cf3f
[DEBUG,BasicTypeRegistry,main] Adding type registration pg-uuid -> org.hibernate.type.PostgresUUIDType@93c5e7
[DEBUG,BasicTypeRegistry,main] Adding type registration binary -> org.hibernate.type.BinaryType@260829
[DEBUG,BasicTypeRegistry,main] Adding type registration byte[] -> org.hibernate.type.BinaryType@260829
[DEBUG,BasicTypeRegistry,main] Adding type registration [B -> org.hibernate.type.BinaryType@260829
[DEBUG,BasicTypeRegistry,main] Adding type registration wrapper-binary -> org.hibernate.type.WrapperBinaryType@53033e
[DEBUG,BasicTypeRegistry,main] Adding type registration Byte[] -> org.hibernate.type.WrapperBinaryType@53033e
[DEBUG,BasicTypeRegistry,main] Adding type registration [Ljava.lang.Byte; -> org.hibernate.type.WrapperBinaryType@53033e
[DEBUG,BasicTypeRegistry,main] Adding type registration image -> org.hibernate.type.ImageType@1fc7b3a
[DEBUG,BasicTypeRegistry,main] Adding type registration characters -> org.hibernate.type.CharArrayType@456a0c
[DEBUG,BasicTypeRegistry,main] Adding type registration char[] -> org.hibernate.type.CharArrayType@456a0c
[DEBUG,BasicTypeRegistry,main] Adding type registration [C -> org.hibernate.type.CharArrayType@456a0c
[DEBUG,BasicTypeRegistry,main] Adding type registration wrapper-characters -> org.hibernate.type.CharacterArrayType@119ca2c
[DEBUG,BasicTypeRegistry,main] Adding type registration [Ljava.lang.Character; -> org.hibernate.type.CharacterArrayType@119ca2c
[DEBUG,BasicTypeRegistry,main] Adding type registration Character[] -> org.hibernate.type.CharacterArrayType@119ca2c
[DEBUG,BasicTypeRegistry,main] Adding type registration text -> org.hibernate.type.TextType@19c2931
[DEBUG,BasicTypeRegistry,main] Adding type registration blob -> org.hibernate.type.BlobType@13e846f
[DEBUG,BasicTypeRegistry,main] Adding type registration java.sql.Blob -> org.hibernate.type.BlobType@13e846f
[DEBUG,BasicTypeRegistry,main] Adding type registration materialized_blob -> org.hibernate.type.MaterializedBlobType@4adb34
[DEBUG,BasicTypeRegistry,main] Adding type registration clob -> org.hibernate.type.ClobType@10ae3fb
[DEBUG,BasicTypeRegistry,main] Adding type registration java.sql.Clob -> org.hibernate.type.ClobType@10ae3fb
[DEBUG,BasicTypeRegistry,main] Adding type registration materialized_clob -> org.hibernate.type.MaterializedClobType@cc17f1
[DEBUG,BasicTypeRegistry,main] Adding type registration serializable -> org.hibernate.type.SerializableType@8e2fb5
[DEBUG,BasicTypeRegistry,main] Adding type registration object -> org.hibernate.type.ObjectType@1371a59
[DEBUG,BasicTypeRegistry,main] Adding type registration java.lang.Object -> org.hibernate.type.ObjectType@1371a59
[DEBUG,BasicTypeRegistry,main] Adding type registration imm_date -> org.hibernate.type.AdaptedImmutableType@a87e7b
[DEBUG,BasicTypeRegistry,main] Adding type registration imm_time -> org.hibernate.type.AdaptedImmutableType@214f6b
[DEBUG,BasicTypeRegistry,main] Adding type registration imm_timestamp -> org.hibernate.type.AdaptedImmutableType@993aaa
[DEBUG,BasicTypeRegistry,main] Adding type registration imm_dbtimestamp -> org.hibernate.type.AdaptedImmutableType@f00e0f
[DEBUG,BasicTypeRegistry,main] Adding type registration imm_calendar -> org.hibernate.type.AdaptedImmutableType@76e1db
[DEBUG,BasicTypeRegistry,main] Adding type registration imm_calendar_date -> org.hibernate.type.AdaptedImmutableType@11be57f
[DEBUG,BasicTypeRegistry,main] Adding type registration imm_binary -> org.hibernate.type.AdaptedImmutableType@11775bc
[DEBUG,BasicTypeRegistry,main] Adding type registration imm_serializable -> org.hibernate.type.AdaptedImmutableType@1e6385e
[TRACE,TypeFactory,main] Scoping types to session factory org.hibernate.internal.SessionFactoryImpl@efb2ab
[DEBUG,HibernateFactory,main] Successfully created hibernate session factory
highbidder:asdfg
Here1


[DEBUG,SQL,main] select this_.ID as ID4_0_, this_.STATUS as STATUS4_0_, this_.WATCHING as WATCHING4_0_, this_.START_DATE as START6_4_0_, this_.END_DATE as END7_4_0_, this_.PRICE as PRICE4_0_, this_.BID_COUNT as BID17_4_0_,
this_.HIGH_BIDDER as HIGH20_4_0_ from ITEM this_ where (this_.ID in (?, ?))
Hibernate: select this_.ID as ID4_0_, this_.STATUS as STATUS4_0_, this_.WATCHING as WATCHING4_0_, this_.START_DATE as START6_4_0_, this_.END_DATE as END7_4_0_, this_.PRICE as PRICE4_0_, this_.BID_COUNT as BID17_4_0_,
this_.HIGH_BIDDER as HIGH20_4_0_ from ITEM this_ where (this_.ID in (?, ?))


[TRACE,BasicBinder,main] binding parameter [1] as [INTEGER] - 526016
[TRACE,BasicBinder,main] binding parameter [2] as [INTEGER] - 511306
[TRACE,BasicExtractor,main] Found [511306] as column [ID4_0_]
[TRACE,BasicExtractor,main] Found [90] as column [STATUS4_0_]
[TRACE,BasicExtractor,main] Found [n] as column [WATCHING4_0_]
[TRACE,BasicExtractor,main] Found [2013-01-27 19:07:31.0] as column [START6_4_0_]
[TRACE,BasicExtractor,main] Found [2013-02-03 19:07:31.0] as column [END7_4_0_]
[TRACE,BasicExtractor,main] Found [55.0] as column [PRICE4_0_]
[TRACE,BasicExtractor,main] Found [0] as column [BID17_4_0_]
[TRACE,BasicExtractor,main] Found [asdfg] as column [HIGH20_4_0_]
[TRACE,BasicExtractor,main] Found [526016] as column [ID4_0_]
[TRACE,BasicExtractor,main] Found [40] as column [STATUS4_0_]
[TRACE,BasicExtractor,main] Found [y] as column [WATCHING4_0_]
[TRACE,BasicExtractor,main] Found [2013-02-18 16:12:35.0] as column [START6_4_0_]
[TRACE,BasicExtractor,main] Found [2013-03-20 17:12:35.0] as column [END7_4_0_]
[TRACE,BasicExtractor,main] Found [17.5] as column [PRICE4_0_]
[TRACE,BasicExtractor,main] Found [null] as column [BID17_4_0_]
[TRACE,BasicExtractor,main] Found [asdfg] as column [HIGH20_4_0_]



[DEBUG,SQL,main] select this_.ID as ID4_0_, this_.STATUS as STATUS4_0_, this_.WATCHING as WATCHING4_0_, this_.START_DATE as START6_4_0_, this_.END_DATE as END7_4_0_, this_.PRICE as PRICE4_0_, this_.BID_COUNT as BID17_4_0_,
this_.HIGH_BIDDER as HIGH20_4_0_ from ITEM this_ where this_.ID=?
Hibernate: select this_.ID as ID4_0_, this_.STATUS as STATUS4_0_, this_.WATCHING as WATCHING4_0_, this_.START_DATE as START6_4_0_, this_.END_DATE as END7_4_0_, this_.PRICE as PRICE4_0_, this_.BID_COUNT as BID17_4_0_,
this_.HIGH_BIDDER as HIGH20_4_0_ from ITEM this_ where this_.ID=?
[TRACE,BasicBinder,main] binding parameter [1] as [INTEGER] - 526016
[TRACE,BasicExtractor,main] Found [526016] as column [ID4_0_]




Hibernate: select this_.ID as ID4_0_, this_.STATUS as STATUS4_0_, this_.WATCHING as WATCHING4_0_, this_.START_DATE as START6_4_0_, this_.END_DATE as END7_4_0_, this_.PRICE as PRICE4_0_, this_.BID_COUNT as BID17_4_0_,
this_.HIGH_BIDDER as HIGH20_4_0_ from ITEM this_ where this_.ID=?
[DEBUG,SQL,main] select this_.ID as ID4_0_, this_.STATUS as STATUS4_0_, this_.WATCHING as WATCHING4_0_, this_.START_DATE as START6_4_0_, this_.END_DATE as END7_4_0_, this_.PRICE as PRICE4_0_, this_.BID_COUNT as BID17_4_0_,
this_.HIGH_BIDDER as HIGH20_4_0_ from ITEM this_ where this_.ID=?
[TRACE,BasicBinder,main] binding parameter [1] as [INTEGER] - 511306
[TRACE,BasicExtractor,main] Found [511306] as column [ID4_0_]


HERE2

[DEBUG,HibernateFactory,Thread-5] Closing hibernate session factory...
[DEBUG,HibernateFactory,Thread-5] Successfully closed hibernate session factory


Why do the last two SQL statements need to be executed. I tested this. If there are 100 matching results, then hibernate search is executing 1 query to retrieve the 100 results, plus 100 additional queries (one for each record found). Can anyone please let me know if the additional queries are necessary?

Note that each sql query is printed twice in log because I have showSQL=true and logging enabled. I put the spaces around the SQL Statements in logs so that they can be easily seen.

Thank you
Top
 Profile  
 
sanne.grinovero
 Post subject: Re: Multiple Unnecessary Hibernate Database Queries
PostPosted: Tue Feb 26, 2013 6:36 pm 
Hibernate Team
Hibernate Team

Joined: Fri Oct 05, 2007 4:47 pm
Posts: 2536
Location: Third rock from the Sun
Looks bad! Please open a JIRA issue, I'll have a look.

https://hibernate.onjira.com/browse/HSEARCH

I guess it's the same if you reduce your entity to the bare minimum? Or can you identify what in your entity triggers this?

_________________
Sanne
http://in.relation.to/

Top
 Profile  
 
tbdsi785
 Post subject: Re: Multiple Unnecessary Hibernate Database Queries
PostPosted: Wed Feb 27, 2013 4:32 pm 
Newbie

Joined: Wed Dec 02, 2009 8:50 pm
Posts: 5
Sanne,

Thank you for looking at this issue. I will open the JIRA issue in a day. I will test with base minimum entity and include them as part of JIRA issue.

I forgot to mention in my original post, I am using latest version of Hibernate and Hibernate Search jars.

Thank you
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 3 posts ] 

Board index » Projects » Search, Validator, Shards

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.