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.
