Message Title

Chris Cranford commented on an issue

Per Emmanuel's suggestion, I tried the jTDS driver and I observe the same behavior.

I am going to attach various parts of my code to help illustrate what we have here in case there potentially is a mapping or implementation issue from our end on how we're using Hibernate ORM and Search modules that are leading to this unusual behavior with composite keys.

                                                    ItemId.java
                                                

                                                    package com.setech.dw.inventory.domain;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Embeddable;
import javax.persistence.MappedSuperclass;

import org.apache.commons.lang3.builder.EqualsBuilder;
import org.apache.commons.lang3.builder.HashCodeBuilder;

@Embeddable
@MappedSuperclass
public class ItemId implements Serializable {
  private static final long serialVersionUID = 240673572700126892L;
  private Long itemId;
  private Long plantId;

  @Column(name="ITEM_ID",unique=false,nullable=false,insertable=true,updatable=false)
  public Long getItemId() {
    return itemId;
  }
  public void setItemId(Long itemId) {
    this.itemId = itemId;
  }

  @Column(name="PLANT_ID",unique=false,nullable=false,insertable=true,updatable=false)
  public Long getPlantId() {
    return plantId;
  }
  public void setPlantId(Long plantId) {
    this.plantId = plantId;
  }

  @Override
  public int hashCode() {
    return new HashCodeBuilder(17,31)
        .append(getItemId())
        .append(getPlantId())
        .toHashCode();
  }

  @Override
  public boolean equals(Object object) {
    if(!(object instanceof ItemId)) return false;
    ItemId other = (ItemId) object;
    return new EqualsBuilder()
        .append(getItemId(), other.getItemId())
        .append(getPlantId(), other.getPlantId())
        .isEquals();
  }
}

                                                

                                                    ItemTest.java
                                                

                                                    package com.setech.dw.inventory.domain;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

import org.apache.commons.lang3.builder.EqualsBuilder;
import org.apache.commons.lang3.builder.HashCodeBuilder;
import org.hibernate.search.annotations.Analyze;
import org.hibernate.search.annotations.Analyzer;
import org.hibernate.search.annotations.Field;
import org.hibernate.search.annotations.Fields;
import org.hibernate.search.annotations.Index;
import org.hibernate.search.annotations.Indexed;
import org.hibernate.search.annotations.Store;

import com.setech.lucene.analysis.PartNumberRemoveSpecialCharactersAnalyzer;

@Entity
@Table(name="test_items")
@Indexed
public class ItemTest implements Serializable {
  private static final long serialVersionUID = 6263726165830649600L;
  private Long 	 id;
  private String itemNumber;
  private String description;
	
  @Id
  @GeneratedValue
  @Column(name="ITEM_ID")
  public Long getId() {
    return id;
  }
  public void setId(Long id) {
    this.id = id;
  }
	
  @Fields({
    @Field(name="itemTestItemNumber",index=Index.YES, analyze=Analyze.YES, store=Store.YES, analyzer=@Analyzer(definition="keywordLowerCaseAnalyzer")),
    @Field(name="itemTestItemNumberScrubbed", index=Index.YES, analyze=Analyze.YES, store=Store.NO, analyzer=@Analyzer(impl=PartNumberRemoveSpecialCharactersAnalyzer.class))
  })
  @Column(name="ITEM_NUMBER",length=80,nullable=false,insertable=true,updatable=true)
  public String getItemNumber() {
    return itemNumber;
  }
  public void setItemNumber(String itemNumber) {
    this.itemNumber = itemNumber;
  }
	
  @Column(name="DESCRIPTION",length=240,nullable=true,insertable=true,updatable=true)
  @Field(name="itemTestDescription", index=Index.YES, analyze=Analyze.YES, store=Store.YES)
  public String getDescription() {
    return description;
  }
  public void setDescription(String description) {
    this.description = description;
  }
	
  @Override
  public int hashCode() {
    return new HashCodeBuilder(17,31)
        .append(getId())
	.append(getItemNumber())
	.append(getDescription())
	.toHashCode();
  }
	
  @Override
  public boolean equals(Object object) {
    if(!(object instanceof ItemTest)) return false;
    ItemTest other = (ItemTest) object;
    return new EqualsBuilder()	
	.append(getId(), other.getId())
	.append(getItemNumber(), other.getItemNumber())
	.append(getDescription(), other.getDescription())
	.isEquals();
  }
}

                                                

                                                    ItemTestA.java
                                                

                                                    package com.setech.dw.inventory.domain;

import java.io.Serializable;

import javax.persistence.AttributeOverride;
import javax.persistence.AttributeOverrides;
import javax.persistence.Column;
import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import javax.persistence.Table;

import org.apache.commons.lang3.builder.EqualsBuilder;
import org.apache.commons.lang3.builder.HashCodeBuilder;
import org.hibernate.search.annotations.Analyze;
import org.hibernate.search.annotations.Analyzer;
import org.hibernate.search.annotations.DocumentId;
import org.hibernate.search.annotations.Field;
import org.hibernate.search.annotations.FieldBridge;
import org.hibernate.search.annotations.Fields;
import org.hibernate.search.annotations.Index;
import org.hibernate.search.annotations.Indexed;
import org.hibernate.search.annotations.Store;

import com.setech.hibernate.search.bridge.ItemIdBridge;
import com.setech.lucene.analysis.PartNumberRemoveSpecialCharactersAnalyzer;

@Entity
@Table(name="test_items2")
@Indexed
public class ItemTestA implements Serializable {
  private static final long serialVersionUID = 299977430325200598L;
  private ItemId id;
  private String itemNumber;
  private String description;
	
  @EmbeddedId
  @AttributeOverrides({
		@AttributeOverride(name="itemId", column=@Column(name="ITEM_ID", unique=false, nullable=false, insertable=true, updatable=true, scale=0)),
		@AttributeOverride(name="plantId", column=@Column(name="PLANT_ID", unique=false, nullable=false, insertable=true, updatable=true, scale=0))
		}
	)
  @DocumentId
  @FieldBridge(impl=ItemIdBridge.class)
  public ItemId getId() {
    return id;
  }
  public void setId(ItemId id) {
    this.id = id;
  }
	
  @Fields({
    @Field(name="itemTestItemNumber",index=Index.YES, analyze=Analyze.YES, store=Store.YES, analyzer=@Analyzer(definition="keywordLowerCaseAnalyzer")),
    @Field(name="itemTestItemNumberScrubbed", index=Index.YES, analyze=Analyze.YES, store=Store.NO, analyzer=@Analyzer(impl=PartNumberRemoveSpecialCharactersAnalyzer.class))
  })
  @Column(name="ITEM_NUMBER",length=80,nullable=false,insertable=true,updatable=true)
  public String getItemNumber() {
    return itemNumber;
  }
  public void setItemNumber(String itemNumber) {
    this.itemNumber = itemNumber;
  }
	
  @Column(name="DESCRIPTION",length=240,nullable=true,insertable=true,updatable=true)
  @Field(name="itemTestDescription", index=Index.YES, analyze=Analyze.YES, store=Store.YES)
  public String getDescription() {
    return description;
  }
  public void setDescription(String description) {
    this.description = description;
  }
	
  @Override
  public int hashCode() {
    return new HashCodeBuilder(17,31)
        .append(getId())
	.append(getItemNumber())
	.append(getDescription())
	.toHashCode();
  }
	
  @Override
  public boolean equals(Object object) {
    if(!(object instanceof ItemTestA)) return false;
    ItemTestA other = (ItemTestA) object;
    return new EqualsBuilder()	
	.append(getId(), other.getId())
	.append(getItemNumber(), other.getItemNumber())
	.append(getDescription(), other.getDescription())
	.isEquals();
  }
}

                                                

                                                    ItemIdBridge.java
                                                

                                                    package com.setech.hibernate.search.bridge;

import org.hibernate.search.bridge.TwoWayStringBridge;

import com.setech.dw.inventory.domain.ItemId;

public class ItemIdBridge implements TwoWayStringBridge
{
	/**
	 * Converts the object representation of a field into the
	 * string equivalent for the Hibernate Search module.
	 *
	 * @param object
	 * @return string representation of the object
	 */
	@Override
	public String objectToString(Object object) {
		String result = null;
		if(object instanceof ItemId) {
			ItemId id = (ItemId) object;
			System.out.println("objectoString(" + id.getItemId() + "," + id.getPlantId() + ")");
			return String.format("%d_%d", id.getItemId(), id.getPlantId());
			/*
			StringBuilder sb      = new StringBuilder();
			sb.append(id.getItemId() + "_");
			sb.append(id.getPlantId());
			result = sb.toString();
			*/
		}
		return result;
	}

	/**
	 * Converts the string representation used by the Hibernate
	 * Search module back into the object representation.
	 */
	@Override
	public Object stringToObject(String object) {
		System.out.println("stringToObject(" + object + ")");
                // per sanne, there are more appropriate ways to do this that are more efficient
                // TODO: use more efficient mechanism
		String fields[] = object.split("_");
		ItemId id = new ItemId();
		id.setItemId(Long.parseLong(fields[0]));
		id.setPlantId(Long.parseLong(fields[1]));
		return id;
	}
}

                                                

I first created the two entitiy objects ItemTest and ItemTestA because I didn't want to muck with our existing entity Item by removing any mappings and such and I wanted to also test whether the wider table versus a more narrow table made a difference. So I decided to build these two items and test with them.

At first I tested with ItemTest where we were not using a composite key. Using this object, the ftQuery.list() invocation was only several milliseconds in length for a page size of 250 records. This was using both the jTDS as well as the Microsoft SQL JDBC4 drivers.

The next test was where I used a composite key scenario, ItemTestA. In this test, I got the exact same stall in the ftQuery.list() invocation as I saw when using our stock Item entity class. Rather than the stall being around rows 119/120, this class appears to have a stall around rows 178/179.

Here is the DAO search method I am using for a simple fetch page blocks for an entity with a given size.

                                                    public void doTestSearch(int pageNumber, int pageSize, Class<?> clazz) {
  Level logLevel = setHibernateLogLevel(Level.DEBUG);
  FullTextSession ftSession = Search.getFullTextSession(getSession());
  BooleanQuery query = new BooleanQuery();
  query.add(new TermQuery(new Term("_hibernate_class", clazz.getName())), Occur.MUST);
  FullTextQuery ftQuery = ftSession.createFullTextQuery(query, clazz);
		
  ftQuery.setFirstResult((pageNumber-1) * pageSize).setMaxResults(pageSize);
		
  int totalHits = ftQuery.getResultSize();
  log.info("Total hits: " + totalHits);
		
  List<?> results = ftQuery.list();
  log.info("results fetched");
		
  setHibernateLogLevel(logLevel);
}

                                                

As seen below, you can see where the stall happens when the Loader tries to fetch row 179. This ItemTestA table has roughly 275,000 rows in it and it doesn't matter whether I specify page 1 or page 100, the stall happens at the same exact point.

                                                    2013-07-19 12:06:49,245 DEBUG [org.hibernate.loader.Loader]: Result row: EntityKey[com.setech.dw.inventory.domain.ItemTestA#component[itemId,plantId]{plantId=6, itemId=251}]
2013-07-19 12:06:49,245 DEBUG [org.hibernate.loader.Loader]: Result set row: 178
2013-07-19 12:06:49,245 DEBUG [org.hibernate.loader.Loader]: Result row: EntityKey[com.setech.dw.inventory.domain.ItemTestA#component[itemId,plantId]{plantId=6, itemId=252}]
2013-07-19 12:06:55,468 DEBUG [org.hibernate.loader.Loader]: Result set row: 179
2013-07-19 12:06:55,468 DEBUG [org.hibernate.loader.Loader]: Result row: EntityKey[com.setech.dw.inventory.domain.ItemTestA#component[itemId,plantId]{plantId=6, itemId=253}]
2013-07-19 12:06:55,468 DEBUG [org.hibernate.loader.Loader]: Result set row: 180

                                                

Sanne requested that I provide the complete SQL that gets executed. I've attached the complete hibernate debug log from when this method gets executed for ItemTestA so you can see the SQL and other debugging outputs.

I could be wrong, but this seems to be something around using composite keys that is creating this bottleneck. Ideas?

Hibernate Search /

HSEARCH-1367

Add Comment

This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira