Hi,
I have a process that runs once a week to save a specific snapshot of my data.
I retrieve a list of objects from the original tables.
there is a parent-child mapping that saves these retrieved list to a parent object and a list of its children.
each list contains around 20000 items.
When i try to update the child table it generates 20000 updates which causes big performance issues.
How can i speed up the update by using batch or HQL or whatever...
THX!!!!

here is my code:
public void BackupLocationN(int LocationID)
{
try
{
EntityContextObj context = new EntityContextObj();
int NumberOfDaysBack = -GetLocationSnapShotPeriod(LocationID);
DateTime dt = DateTime.Now.AddDays(NumberOfDaysBack);
using (ISession session = NHibernateHelper.GetCurrentSession(context))
{
ICriteria criteria = session.CreateCriteria(typeof(ItemData));
criteria.Add(Expression.And(Expression.Eq("OrgID", LocationID), Expression.Gt("TimeStamp", dt)));
criteria.AddOrder(Order.Asc("ItemID"));
IList<ItemData> ItemsList = criteria.List<ItemData>();
if (ItemsList.Count == 0) return;
session.Clear();
//try to join criteria 2 and 3
ICriteria criteria2 = session.CreateCriteria(typeof(SnapShotLocation));
criteria2.Add(Expression.Eq("LocationID", LocationID));
SnapShotLocation SSL = (SnapShotLocation)criteria2.UniqueResult();
if (SSL == null)
{
SSL = new SnapShotLocation();
SSL.LocationID = LocationID;
SSL.Version = 0;
SSL.SnapShotDate = DateTime.Now;
}
SSL.SnapShotDate = DateTime.Now;
if (SSL.SnapShotID == 0)
{
for (int i = 0; i < ItemsList.Count; i++)
{
AddNewSnapShotItem(ItemsList[i], SSL);
}
}
else
{
ICriteria criteria3 = session.CreateCriteria(typeof(SnapShotItem));
//get all the current Items that r in the DB for this location.
criteria3.Add(Expression.Eq("SnapShotID", SSL));
criteria3.AddOrder(Order.Asc("ItemID"));
IList<SnapShotItem> SnapShotItemsList = criteria3.List<SnapShotItem>();
int SnapshotCounter = 0;
bool NotOnItemsAllIDList = false;
for (int i = 0; i < ItemsList.Count; i++)
{
NotOnItemsAllIDList = false;
if (SnapShotItemsList.Count > SnapshotCounter)
{
//update the snapshotItem with the data of ItemData and add it to the Parent.
if (ItemsList[i].ItemID == SnapShotItemsList[SnapshotCounter].ItemID)
{
UpdateSnapShotItem(ItemsList, SSL, SnapShotItemsList, SnapshotCounter, i);
SnapshotCounter++;
}
else
{
if (ItemsList[i].ItemID > SnapShotItemsList[SnapshotCounter].ItemID)
{
SnapshotCounter++;
i--;
NotOnItemsAllIDList = true;
}
else AddNewSnapShotItem(ItemsList[i], SSL);
}
}
else AddNewSnapShotItem(ItemsList[i], SSL);
if (!NotOnItemsAllIDList) session.Evict(ItemsList[i]);
}
}
session.SaveOrUpdate(SSL);
session.Flush();
}
NHibernateHelper.CloseSession();
}
catch (Exception ex)
{
NHibernateHelper.CloseSession();
}
}

Hello!

Have you seen this entry 15.1.5 in the documentation?

Hope it helps.

First, when posting large code snippets please use the \[code\] to format it for readability.

Second, section 15.1.5 isn't really going to help: that is in reference to batch fetching, which would not be useful here.

Finally, it is hard to tell what your specific problem is: are you making modifications on 20,000 objects then calling flush? Or are you not modifying all the objects or just modifying the collection and still seeing 20,000 updates?

It would be helpful if you could distill a minimal example.

I prefer to use plain ADO.NET for manipulating mass data. For example in an application i wrote i had to clone a complex model with many 1..n relations (in the database the model is represented by around 5.000 to 10.000 records). Using plain ADO.NET and DataSets boosts the performance around 200%.

Regards
Klaus

Hi i am attahcing the code i wrote:
the first criteria returns 20000 objects and then i modify all the 20000 objects(The date it was updated is important)

THX!!!

the Mapping:
[/code]

Section 15.6. Batch updates describes how to configure Nhibernate for batch updates, given certain limitations. However, as a previous poster suggested, NHibernate isn't really designed for this use case. Updating 20000 records at a time is often better handled by native SQL constructs - you may want to consider using a stored procedure.