Here is some Output from the tests i run: NHibernate: SELECT batch0_.Batch_ID as Batch1_0_0_, batch0_.timestamp as timestamp0_0_ FROM tbl_Batch batch0_ WHERE batch0_.batch_...@p0;@p0 = 79 NHibernate: SELECT list0_.Batch_ID as Batch1_1_, list0_.Account_ID as Account2_1_, account1_.Account_ID as Account1_17_0_, account1_.AccountLabel as AccountL2_17_0_, account1_.username as username17_0_, account1_.password as password17_0_, account1_.style as style17_0_, account1_.type as type17_0_ FROM tbl_Batch_Account list0_ left outer join vw_Account account1_ on list0_.Account_ID=account1_.Account_ID WHERE list0_.batch_...@p0;@p0 = 79 NHibernate: SELECT list0_.Account_ID as Account1_1_, list0_.Environment_ID as Environm2_1_, environmen1_.Environment_ID as Environm1_15_0_, environmen1_.name as name15_0_ FROM tbl_Batch_Account_Environment list0_ left outer join tbl_Environment environmen1_ on list0_.Environment_ID=environmen1_.Environment_ID WHERE @p0 = list0_.[Batch_ID] and list0_.account_...@p1;@p0 = 79, @p1 = 682606 NHibernate: SELECT list0_.Account_ID as Account1_1_, list0_.Environment_ID as Environm2_1_, environmen1_.Environment_ID as Environm1_15_0_, environmen1_.name as name15_0_ FROM tbl_Batch_Account_Environment list0_ left outer join tbl_Environment environmen1_ on list0_.Environment_ID=environmen1_.Environment_ID WHERE @p0 = list0_.[Batch_ID] and list0_.account_...@p1;@p0 = 79, @p1 = 682608 NHibernate: SELECT list0_.Account_ID as Account1_1_, list0_.Environment_ID as Environm2_1_, environmen1_.Environment_ID as Environm1_15_0_, environmen1_.name as name15_0_ FROM tbl_Batch_Account_Environment list0_ left outer join tbl_Environment environmen1_ on list0_.Environment_ID=environmen1_.Environment_ID WHERE @p0 = list0_.[Batch_ID] and list0_.account_...@p1;@p0 = 79, @p1 = 682610 NHibernate: SELECT list0_.Account_ID as Account1_1_, list0_.Environment_ID as Environm2_1_, environmen1_.Environment_ID as Environm1_15_0_, environmen1_.name as name15_0_ FROM tbl_Batch_Account_Environment list0_ left outer join tbl_Environment environmen1_ on list0_.Environment_ID=environmen1_.Environment_ID WHERE @p0 = list0_.[Batch_ID] and list0_.account_...@p1;@p0 = 79, @p1 = 682612 NHibernate: SELECT list0_.Account_ID as Account1_1_, list0_.Environment_ID as Environm2_1_, environmen1_.Environment_ID as Environm1_15_0_, environmen1_.name as name15_0_ FROM tbl_Batch_Account_Environment list0_ left outer join tbl_Environment environmen1_ on list0_.Environment_ID=environmen1_.Environment_ID WHERE @p0 = list0_.[Batch_ID] and list0_.account_...@p1;@p0 = 79, @p1 = 682613
The last 5 queries should be combined to one, now this isn't a big peformance killer with 5 records, but when working with 1000 or even 10000 it get's troublesum. Greetings, Folkert On 13 nov, 11:31, "F.B. ten Kate" <[email protected]> wrote: > I think i'm running into the N+1 problem i've found a few times while > googling but i'm unable to resolve to problem thus far. Maybe this is > due to bad database design or bad mapping eitherway i'm hoping someone > here can help point me in the right direction. > > First ill attempt to sketch the situation: > > We have got an internal account database, these accounts (600K) come > from difference environments. Meaning there is a N:N relationship op > Account to Environments. This data needs to get synced through a > webservice, we want to do this in batches to make sure we don't lose > data. Which gives us the following situation. > > Batch 1 : M Accounts M : M Environments > > It is possible for an account to exsist in one environment and then > get added to another environment a week later, meaning there is a > "flag" to indicate if the environment has already been synced. Meaning > the Database currently holds the following tables: > > tbl_Account > tbl_Account_Environment (Linking environments to accounts and vise > versa) > tbl_Environment > tbl_Batch > tbl_Batch_Account (Containing the accounts linked to batches) > tbl_Batch_Account_Environment (Containing a link to tbl_Batch_Account > specifying which environments are contained by this batch) > > i am struggling to get this mapped effeciently, here are my mapping > files: > > Batch: > <?xml version="1.0" encoding="utf-8" ?> > <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" > assembly="DirectPay.Backoffice.Entities" > > namespace="DirectPay.Backoffice.Entities.CreditExchangeAccount"> > <class name="batch" table="tbl_Batch" lazy="false" mutable="false"> > <id name="batchid" column="Batch_ID"> > <generator class="native" /> > </id> > <property name="timestamp"/> > <component name="accounts"> > <bag name="List" lazy="false" > table="tbl_Batch_Account"> > <key column ="Batch_ID"/> > <many-to-many column="Account_ID" > class="account"> > </many-to-many > > </bag> > </component> > </class> > <sql-query name="CommitBatch"> > <query-param name="Batch_ID" type="int" /> > exec sp_CommitBatch @Batch_ID=:Batch_ID > </sql-query> > <sql-query name="CreateBatchSize"> > <query-param name="Size" type="int" /> > exec sp_CreateBatch @Size=:Size > </sql-query> > <sql-query name="CreateBatch"> > <query-param name="Size" type="int" /> > exec sp_CreateBatch > </sql-query> > </hibernate-mapping> > > Account: > <?xml version="1.0" encoding="utf-8" ?> > <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" > assembly="DirectPay.Backoffice.Entities" > > namespace="DirectPay.Backoffice.Entities.CreditExchangeAccount"> > <class name="account" table="tbl_Account" lazy="false" > mutable="false"> > <id name="accountid" column="Account_ID"> > <generator class="native" /> > </id> > <property name="accountlabel" column="AccountLabel" /> > <property name="username"/> > <property name="password"/> > <component name="environments"> > <bag name="List" > table="tbl_Batch_Account_Environment"> > <key column ="Account_ID"/> > <many-to-many class ="environment" > column="Environment_ID"/> > <filter condition =":isNew = [new]" name > ="isNew"/> > <filter condition =":batchID = [Batch_ID]" > name ="batchID"/> > </bag> > </component> > <join table="tbl_Style"> > <key column="Style_ID"> > </key> > <property name="style" column="name"/> > </join> > <join table="tbl_Type"> > <key column="Type_ID"/> > <property name="type" column="name"/> > </join> > </class> > <filter-def name="isNew"> > <filter-param name="isNew" type="System.Boolean"/> > </filter-def> > <filter-def name="batchID"> > <filter-param name="batchID" type="System.Int32"/> > </filter-def> > </hibernate-mapping> > > Environment > > <?xml version="1.0" encoding="utf-8" ?> > <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" > assembly="DirectPay.Backoffice.Entities" > > namespace="DirectPay.Backoffice.Entities.CreditExchangeAccount"> > <class name="environment" table="tbl_Environment" mutable="false"> > <id name="id" column="Environment_ID"/> > <property name="name"/> > </class> > </hibernate-mapping> > > While typing i'm running into another problem concerning my joins: > > <join table="tbl_Style"> > <key column="Style_ID"/> > <property name="style" column="name"/> > </join> > <join table="tbl_Type"> > <key column="Type_ID"/> > <property name="type" column="name"/> > </join> > > The columns Style_ID is in the tbl_account table and tbl_Style, sadly > the join wants to check if Style_ID = Account_ID. But the biggest > problem atm is the Bad peformance. > > Hoping someone can slap me in the face a few times to tell my about > the things i'm doing wrong. > > Greetings, > > F.B. ten Kate --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
