Thanks, Diego. That worked (sort of) for my one-to-many. I set the batch size to 20 (knowing I would only get 15 based on the data I'm testing with) and it gave me a query for 10 and a query for 5. Still an improvement, but I'm a bit puzzled in the discrepancy. I'll play around with it a bit more tomorrow.
That still leaves the question of how to deal with my one-to-one, as I'm not aware of any batching parameters for that type of relationship. On Mar 23, 9:43 pm, Diego Mijelshon <[email protected]> wrote: > Have you tried with batch-size? > It's usually more efective than fetch strategies, because: > - It doesn't force loading of not-always-used elements > - It doesn't create a bigger, denormalized result set > ...And it still turns a select N+1 problem into a "select N/x+1" one (which > is effectively "select 1+1" if you adjust the batch-size to your page size. > I do that). This can be more efficient than a single select in many cases. > > Diego > > > > On Tue, Mar 23, 2010 at 21:45, jprid64 <[email protected]> wrote: > > I have two tables that have a 1 to 0,1 relationship. I have the basic > > mapping set up (read-only for now) for my ASP.NET MVC app using lazy > > loading but NHProf shows that I am making separate SQL queries to the > > secondary table for each row returned from the primary table. I've > > played with various combinations of lazy, outer-join, and fetch, but > > all of them still generate N queries. FWIW, I have a 1 to many in here > > as well that exhibits the same behavior. Please note that all data > > returned is correct -- I just think it's doing too much work. > > > Here are my XML mappings (reverted to basic association): > > > Primary table: > > <class name="AladdinDb.Models.OrderStatus, AladdinDb" > > table="vorder_status"> > > <id name="CommitNumber" type="decimal" column="commit_no"> > > <generator class="assigned"> > > <param name="property"> > > Plan > > </param> > > </generator> > > </id> > > <property name="MainCommit" column="main_commit" > > type="decimal" /> > > <property name="InvoiceNumber" column="invoice_no" > > type="string" /> > > <property name="ShipTo" column="ship_to" type ="string"/> > > <property name="CustomerOrderNumber" column="cust_order_no" > > type="string" /> > > <property name="Station" column="station" type="string" /> > > <property name="RequestedShipDate" column="req_ship_date" > > type="DateTime" /> > > <property name="EstimatedValue" column="estimated_value" > > type="decimal"/> > > <property name="EstimatedWeight" column="estimated_weight" > > type="decimal" /> > > <property name="Customer" column="customer" type="string" /> > > <property name="InvoiceDate" column="invoice_date" /> > > <one-to-one name="Plan" class="AladdinDb.Models.PlanCommit, > > AladdinDb" /> > > <set name="Promises" > > > <key column="commit_no"></key> > > <one-to-many class="AladdinDb.Models.Promise, AladdinDb" / > > > </set> > > </class> > > > One-to-zero/one table: > > <class name="AladdinDb.Models.PlanCommit, AladdinDb" > > table="plan_commit"> > > <id name="CommitNumber" type="decimal" column="commit_no"> > > <generator class="assigned" /> > > </id> > > <property name="Priority" column="priority" /> > > <property name="Done" column="done" /> > > <property name="CStatus" column="c_status" /> > > <property name="Hauler" column="hauler" /> > > <property name="WarehouseRandomWeight" column="whar_rand" /> > > <property name="WarehouseCutsWeight" column="whar_cuts" /> > > <property name="YardRandomWeight" column="yard_rand" /> > > <property name="YardCutsWeight" column="yard_cuts" /> > > <property name="ShipDate" column="ship_date" /> > > </class> > > > One-to-many table: > > <class name="AladdinDb.Models.Promise, AladdinDb" table="promise"> > > <id name="CommitNumber" type="decimal" column="commit_no"> > > <generator class="assigned" /> > > </id> > > <property name="PromiseDate" column="promise_date" /> > > <property name="WhoAsked" column="who_asked" /> > > <property name="WhoGave" column="who_gave" /> > > <property name="Iffy" column="iffy" /> > > </class> > > > -- > > You received this message because you are subscribed to the Google Groups > > "nhusers" group. > > To post to this group, send email to [email protected]. > > To unsubscribe from this group, send email to > > [email protected]<nhusers%[email protected] > > > > > . > > For more options, visit this group at > >http://groups.google.com/group/nhusers?hl=en. -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
