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
-~----------~----~----~----~------~----~------~--~---
