• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Multiple values in condition.

 
Ranch Hand
Posts: 342
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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

 
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic