hello,

i'm trying to compare the performance between SQL database(PostgreSQL) and NoSQL database(MongoDB) in JAVA/JEE application.
So i have done two project:
-first project: Hibernate, JPA 2.1 and PostgreSQL
-Second Project : Hibernate OGM,JPA 2.1, Hibernate Search and Mongodb
but when i compare the performance between the 2 databases i find that PostreSQL is faster then MongoDB
for example:
Reading 1440 Entity from database took 0.4 seconde with PostgreSQL and 3 seconde with Mongodb.
My question is which solution can improve the performance with my NoSQL solution(second project)?

Thanks,
Bassem

Hi,

It's hard to give an answer without knowing more details. E.g. how are you accessing the entities, through a query or by id? Is it single entities or joins? How are you using Hibernate Search? Could you share some more details of your benchmark?

Thanks,

--Gunnar

Hello Gunnar,

i have 2 Entities in my project NoSQL(Mongodb).the first collection contains the consumption of clients and the second collections contains the clients.

this is the Entitie of client:
@Entity
@Indexed
@Table(name = "GeneralEntityy")
public class GeneralEntity implements Serializable{

@DocumentId
@Id
@GeneratedValue
private int identifiant;

@Column(name="customerIdent")
@Field(index = Index.YES, analyze = Analyze.NO, store = Store.NO)
private int customerIdent;

@Column(name="terminalIdent")
@Field(index = Index.YES, analyze = Analyze.NO, store = Store.NO)
private int terminalIdent;

@Column(name="submeterIdent")
@Field(index = Index.YES, analyze = Analyze.NO, store = Store.NO)
private int submeterIdent;

@Column(name="machineIdent")
@Field(index = Index.YES, analyze = Analyze.NO, store = Store.NO)
private int machineIdent;

@Column(name="gatewayIdent")
@Field(index = Index.YES, analyze = Analyze.NO, store = Store.NO)
private int gatewayIdent;

@Column(name="siteIdent", nullable = false)
@Field(index = Index.YES, analyze = Analyze.NO, store = Store.NO)
private int siteIdent;
}

[color=#FF00BF]and this is the entity of cunsumption

@Entity
@Indexed
@Table(name = "consumption")

public class consumption implements Serializable{

@DocumentId
@Id
@GeneratedValue(generator = "uuid")
@GenericGenerator(name = "uuid", strategy = "uuid2")
private String id;

@Field(index=Index.YES, analyze=Analyze.NO, store=Store.NO)
@DateBridge(resolution = Resolution.MILLISECOND)
@Temporal(javax.persistence.TemporalType.TIMESTAMP)
@Column(name = "mesure_datetime")
private Date mesure_datetime;



@Column(name="mesure_identifier")
@Field(index = Index.YES, analyze = Analyze.NO, store = Store.NO)
private int mesure_identifier;

@Column(name="iph3")
@Field(index = Index.YES, analyze = Analyze.NO, store = Store.NO)
private double mesure_iph3;

@Column(name="ip")
@Field(index = Index.YES, analyze = Analyze.NO, store = Store.NO)
private double mesure_ip;

@Column(name="ipp")
@Field(index = Index.YES, analyze = Analyze.NO, store = Store.NO)
private double mesure_ipp;

@Column(name="uph1")
@Field(index = Index.YES, analyze = Analyze.NO, store = Store.NO)
private double mesure_uph1;


@DateBridge(resolution = Resolution.DAY)
@Temporal(javax.persistence.TemporalType.DATE)
@Column(name = "decision")
private Date decision;

@IndexedEmbedded
@ManyToOne()
private GeneralEntity gen;

}[/color]

So i have one join
and this is my query hibernate search to get the list of consumption of client:

SimpleDateFormat sdf = new SimpleDateFormat("dd-M-yyyy hh:mm:ss");
sdf.setTimeZone(TimeZone.getTimeZone("GMT"));

FullTextEntityManager fullTextEntityManager = org.hibernate.search.jpa.Search.getFullTextEntityManager(HibernateUtilNoSQL.getEntityManager());
QueryBuilder queryBuilder = fullTextEntityManager.getSearchFactory().buildQueryBuilder().forEntity(consumption .class).get();
org.apache.lucene.search.Query query = queryBuilder.bool().must(queryBuilder.keyword().onField("general.customerIdent").matching(1123).createQuery())
.must(queryBuilder.keyword().onField("general.terminalIdent").matching(ter).createQuery())
.must( queryBuilder
.range()
.onField("mesure_datetime")
.from(sdf.parse( "01-"+day+"-2014 00:00:00"))
.to(sdf.parse( "01-"+(day+1)+"-2014 00:00:00"))
.createQuery() )
.createQuery();

FullTextQuery fullTextQuery = fullTextEntityManager.createFullTextQuery(query,MesureparamNo.class);
fullTextQuery.initializeObjectsWith(ObjectLookupMethod.SKIP, DatabaseRetrievalMethod.FIND_BY_ID);
List<MesureparamNo> results = fullTextQuery.getResultList();
System.out.println(results.size());

return results;

In this specific experiment, from PostgreSQL we'll be loading many entities at once for each round trip to the database (as large as any set batch sizes and the JDBC driver allow).

To load each entity from MongoDB, that's a new load operation for each result.

So assuming paging allows 100 results to be fetched from PostgreSQL at once, you'll have 100 times the network packets going to MongoDB.. in such a test the network latency will dominate on other factors.
For example MongoDB might better deal with very large database sizes and writes, but doesn't magically speed up your network.
In a different test, for example *storing* a single entity, you would probably experience a better result from MongoDB. It's a great example of considerations which should be taken into account when choosing the storage technology.

We might be able to do something about this in Hibernate OGM; in most cases the underlying NoSQL store doesn't support batch loading, but I think this should be possible to implement on MongoDB.

Hello Sanne ,
thank you for your answer.
So, in our company i propose the solution to migrate our actual database from SQL to NoSQL(MongoDB) because i read in forums that mongodb has a great peformance but i can't test the performance of mongodb with a big data(terabyte of data) because i don't have the hard disk to test this.
do u adivice me to continue in this solution (migration to mongodb) ?

thanks

The "great peformance" of MongoDB only applies to specific use cases, and there are some drawbacks too (like not having transactions).

I would never migrate to a different technology if you're unable to verify that it fits all your requirements, and performance is just one of them. If all you need is faster query performance you can get a long way by learning effective usage of 2nd level caching of Hibernate ORM and how some queries map better to Hibernate Search; I would suggest to learn those first and only as last resort look for an alternative storage technology.