I m using NHibernate and SqlCe database on wpf application. I create a parent row and add about 10K records of childs on the parent row. After than any simple transaction like inserting a single row on any table takes at least 10 seconrds.
When i restart the application and load the parent row and 10K child row, any any transaction is fast as normal.
What could be the cause?
Trial class
public class Trial : JobsBase, INotifyPropertyChanged, ISoftDelete, IUniqueName, IDataErrorInfo, IFilter
{
private int newrows;
private int rows;
private string name;
private int cols;
private int newcols;
private string description;
public Trial()
{
TrialId = new Guid();
}
public virtual Guid TrialId { get; set; }
public virtual string Name
{
get
{
return name;
}
set
{
if (name == value) return;
name = value;
FirePropertyChangedEvent("Name");
}
}
public virtual int Rows
{
get
{
return rows;
}
set
{
newrows = value;
if (newrows * cols > 10000) { return; }
if (rows == value) return;
BuildCells(rows, cols, value, cols);
rows = value;
FirePropertyChangedEvent("Rows");
}
}
public virtual int Cols
{
get
{
return cols;
}
set
{
newcols = value;
if (newcols * rows > 10000) { return; }
if (value == cols) return;
BuildCells(rows, cols, rows, value);
cols = value;
FirePropertyChangedEvent("Cols");
}
}
public virtual string Description
{
get
{
return description;
}
set
{
if (description == value) return;
description = value;
FirePropertyChangedEvent("Description");
}
}
public virtual void CleanUpRows(int oldrows, int newrows)
{
for (int r = newrows; r < oldrows; r++)
{
for (int c = 0; c < cols; c++)
{
//cell to delete
var delCell = new Cell() { X = c, Y = r };
int index;
if ((index = cells.IndexOf(delCell)) > -1)
{
cells.RemoveAt(index);
}
}
}
}
public virtual void CleanUpColumns(int oldcols, int newcols)
{
for (int c = newcols; c < oldcols; c++)
{
for (int r = 0; r < rows; r++)
{
//cell to delete
var delCell = new Cell() { X = c, Y = r };
int index;
if ((index = cells.IndexOf(delCell)) > -1)
{
cells.RemoveAt(index);
}
}
}
}
public virtual void BuildCells(int oldrows, int oldcols, int newrows, int newcols)
{
//remove cells if rows cols size decreases
if (oldrows > newrows) CleanUpRows(oldrows, newrows);
if (oldcols > newcols) CleanUpColumns(oldcols, newcols);
Debug.WriteLine("build cells: " +DateTime.Now.ToLongTimeString() );
for (int i = 0; i < newcols; i++)
{
for (int j = 0; j < newrows; j++)
{
AddCell(new Cell { X = i, Y = j, Active = true });
}
}
Debug.WriteLine("end build cells: " + DateTime.Now.ToLongTimeString());
}
private IList<Cell> cells = new List<Cell>();
public virtual IList<Cell> Cells
{
get { return cells; }
set { cells = value; }
}
public virtual void AddCell(Cell cell)
{
if (!HasCell(cell))
{
cell.Trial = this;
cells.Add(cell);
}
}
public virtual void RemoveCell(Cell cell)
{
cells.Remove(cell);
}
public virtual bool HasCell(Cell cell)
{
return cells.Contains(cell);
}
public virtual bool HasCell(int x, int y)
{
return HasCell(new Cell() { X = x, Y = y });
}
public virtual Cell SetCellActive(int x, int y, bool active)
{
int i = Cells.IndexOf(new Cell() { X = x, Y = y });
Cells[i].Active = active;
FireCellChanged(new CellEventArgs() { X = x, Y = y });
return Cells[i];
}
public virtual Cell GetCell(int x, int y)
{
int i = Cells.IndexOf(new Cell() { X = x, Y = y });
return i >= 0 ? Cells[i] : null;
}
public virtual event PropertyChangedEventHandler PropertyChanged;
public virtual event EventHandler<CellEventArgs> CellChanged;
private void FireCellChanged(CellEventArgs e)
{
EventHandler<CellEventArgs> changed = CellChanged;
if (changed != null) changed(this, e);
}
private void FirePropertyChangedEvent(string propertName)
{
if (PropertyChanged != null) PropertyChanged(this, new PropertyChangedEventArgs(propertName));
}
public override string ToString()
{
return TrialId.ToString();
}
public override bool Equals(object obj)
{
if (this == obj) return true;
var other = obj as Trial;
if (other == null) return false;
return TrialId.Equals(other.TrialId);
}
public override int GetHashCode()
{
return TrialId.GetHashCode();
}
public virtual string this[string columnName]
{
get
{
string result = null;
if (columnName == "Cols" || columnName == "Rows")
{
if (this.newcols * this.newrows > 10000)
{
result = "No of cells must not exceed 10000";
}
}
return result;
}
}
public virtual string Error
{
get { return null; }
}
}
}
public class CellEventArgs : EventArgs
{
public int X { get; set; }
public int Y { get; set; }
}
Cell
public class Cell : INotifyPropertyChanged
{
public Cell()
{
CellId = Guid.NewGuid();
}
public virtual Guid CellId { get; set; }
public virtual int MagazinNumber { get; set; }
public virtual int X { get; set; }
public virtual int Y { get; set; }
public virtual Trial Trial { get; set; }
public virtual bool Active { get; set; }
public virtual string CustomId { get; set; }
public override string ToString()
{
return CellId.ToString();
}
public virtual bool EqualsById { get; set; }
public override bool Equals(object obj)
{
if (this == obj) return true;
var other = obj as Cell;
if (other == null) return false;
return other.EqualsById ? CellId.Equals(other.CellId) : X.Equals(other.X) && Y.Equals(other.Y);
}
public override int GetHashCode()
{
return CellId.GetHashCode();
}
private IDictionary<string, float> _datalist;
public virtual IDictionary<string, float> Datalist
{
get
{
if(_datalist==null)
{
_datalist= new Dictionary<string, float>();
if (data != null)
{
var document = new XmlDocument();
document.LoadXml(data);
foreach (XmlNode childNode in document.FirstChild.ChildNodes)
{
if (childNode.Attributes == null) continue;
string key = childNode.Attributes["key"].Value;
float val = float.Parse(childNode.Attributes["value"].Value);
SetExtraField(key, val);
}
}
}
return _datalist;
}
}
private void SetExtraField(string key, float value)
{
if (Datalist.ContainsKey(key))
{
Datalist[key] = value;
}
else
{
Datalist.Add(key, value);
}
}
private float? GetExtraField(string key)
{
float value;
return Datalist.TryGetValue(key, out value) ? (float?)value : null;
}
private int? GetExtraFieldInt(string key)
{
float value;
return Datalist.TryGetValue(key, out value) ? (int?)value : null;
}
private string data;
public virtual string Data
{
get
{
string xml = @"<datas>";
foreach (var f in Datalist)
{
xml += string.Format("<data key='{0}' value='{1}' />", f.Key, f.Value);
}
xml += "</datas>";
return xml;
}
set
{
data = value;
}
}
add trial to db
var trial = new Trial { "tset1" };
trial.Rows = 10;
trial.Cols = 1000;
trial.Deleted = false;
trial.BuildCells(10, 1000, 10, 1000);
TrialDAO.Instance().Add(trial);
