Re: select performance

mehmet.gunacti@xxxxxxxxx wrote:
of course i agree with method 3 (left outer join instead of inner
join).

If you're looking to implement an object layer based upon objects
representing customers and orders,

let's say i would need to list 100 customers with or without orders.

but my question is actually, wouldn't it be a performance loss when
first fetching all customers and afterwards fetching all orders for
each customer.
that would make for 100 customers 101 queries. one for the customers
table and 100 for each customer on orders table.

with method 3, there would be only 1 query. how would hibernate handle
this ? would it calculate the performance overhead and choose and
appropriate method ? btw, i'm very new to hibernate.

Hibernate will do what you ask it to do ;)

3 possibilities:
1) Look for customers using JOIN fetch mode on orders: 1 query everything is
loaded
2) Look for custormers with a SELECT fetch mode on orders: 101 queries
3) use method 2) but add a batch size=100 on orders: 2 queries, the second
one being select * from orders where customerID=? or customerID=? or .... or
customerID=? (100 "or")

Do performance and memory tests to choose the best method.
It takes 2 seconds to switch between them with hibernate.


.

Relevant Pages

  • Re: Re: Need some input on an Event Procedure for a search
    ... could try it with just needed a slight alteration to the SQL query. ... specific Features which some Customers would be looking for. ... The Customers table includes contact details, ... and the specific command for a mailout which is the event ...
    (microsoft.public.access.gettingstarted)
  • Re: Sum of numbers
    ... "Evi" wrote: ... You say the union query 'only shows fields from the first table'. ... Do you mean that you want a multicolumn report with all customers' names ...
    (microsoft.public.access.reports)
  • RE: Store Added Value List Items
    ... You could even union that query with the actual list from the form ... Each user in this database needs to have access to all the same customers. ... is praticle to create a seperate one field table to store values for a lookup ...
    (microsoft.public.access.formscoding)
  • RE: Report prints data twice on two different pages
    ... I'm not sure I fully understand what a "totals query" is nor do I totally ... Thanks again Duane! ... FROM Customers AS C INNER JOIN Appointments As A ON C.customer_id = ... My main report is based on a query that is based on the main table only. ...
    (microsoft.public.access.reports)
  • Re: I need a customer sales listing that includes no-sales months.
    ... > query of sales per month per customer, but I need the non-sales months ... > All my data is in one query called QryINVOICE. ... This sounds like a perfect candidate for a crosstab ... FROM (Customers INNER JOIN Orders ...
    (microsoft.public.access.queries)