s*****p
发帖数: 5342 | 1 When I use hibernate, I found a problem. There are 4-5 tables which have one
-to-many in between. I have one object map to one table. The lowest and 2nd
lowest children level table have millions records. The top ones are just
hundreds at most. When I set search critia on both top and bottom level
table, the query is very slow. Something wrong with my implementation? Any
good suggestion?
3x! | f*******4
发帖数: 345 | 2 It seems you don't have to use projection at all, but you have to use left
outer join for sure.
The query could be like this:
select count(distinct s.id) from State s left join s.customer as c left join
c.purchases as p where c.gender="male" and p.item="pc" group by c.id having
count(p)>1000
If the query is really slow, you've to figure out some quirky way such as
adding a column into the state table as customer counter, and in your
business logic whenever a purchase happens, update the column | s*****p
发帖数: 5342 | 3 For your sample, I need add another table top on customer table, say state
for example. Assumely, one-to-many relationship is between state and
customer. It means one state has many customers. There is no relationship
between state and purchase.
Say, if I want to query how many states have male customer with pc purchases
over 1000, it seems hibernate use outer join to join all three tables and
return millions records with duplicated state name. If I use projection, it
is slow and also only retur
【在 f*******4 的大作中提到】
: It seems you don't have to use projection at all, but you have to use left
: outer join for sure.
: The query could be like this:
: select count(distinct s.id) from State s left join s.customer as c left join
: c.purchases as p where c.gender="male" and p.item="pc" group by c.id having
: count(p)>1000
: If the query is really slow, you've to figure out some quirky way such as
: adding a column into the state table as customer counter, and in your
: business logic whenever a purchase happens, update the column
| f*******4
发帖数: 345 | 4 Can you post your code?
To simpily the question, say, you have a customer table which contains 100
rows,
each customer has thousands of purchases. the foreign key in customer table
is purchase_id which is the primary key of purchase table. What are your
mapping file and search query? |
|
