• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Multiple values in condition.

 
avihai marchiano
Ranch Hand
Posts: 342
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey,

I need to load entities by set of attributes values.

I can do it with query like this:
select from supplier where ( (name ="name1" and address="address1" and city="city1") OR (name="name1" and address="address2" and city="city2") .... )


Queries like this suffer from performance issues, so i do this by creating temp table in the DB before run the query. (the input for the query and the number of criterion set are changed for each input).

In hibernate i can do it by criteria or by HQL (any idea if i can do any tricky name-query here?).

Just want to hear from you about your implementation for this.
do you do it in hibernate at all?

Thank you

 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you have indexes on those columns, then I think your performance would be much quicker. Get an explain plan for that query and I think you will see because you don't have an index for them that they are doing table scans.

Mark
 
avihai marchiano
Ranch Hand
Posts: 342
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
cant have index.

the keys is defined by the customers and each customer has its own keys.
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
avihai marchiano wrote:cant have index.

the keys is defined by the customers and each customer has its own keys.


Then you will have slow performance. It is all about the database in this case. Not the query or Hibernate.

Mark
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic