I am working on some code (not my own code) that updates items in UCommerce. This particular block of code updates the UDF's for Products. I am experiencing a problem with speed of execution. Each iteration through the foreach loop gets slower than the previous. When execution starts it is looping around 5-6 times a second but by the time it has processed 500 iterations it has slowed to 1 every 5-6 seconds, it is now at 910 items and slowed to 1 every 10-11 seconds. As the code stands it is processing 200 (_batchsize) items before committing the transaction and flushing the session.
Memory, CPU and Disk IO all look fine. No excessive CPU usage, plenty of available memory and no disk bottle necks. When running the app memory usage stays steady at around 350Mb.
I am seeing this same problem whether I run it from the IDE or the compiled exe.
I have tried reducing the batch size and I have also tried committing the transaction and flushing the session for each item but it makes very little difference. I just wondered if anyone could suggest anything I could try or even identify the possible problem areas.
public class ccProductUDFs
{
public string ProductName { get; set; }
public string FieldName { get; set; }
public string DataType { get; set; }
public string DisplayName { get; set; }
public string SKU { get; set; }
public bool Facet { get; set; }
public bool Searchable { get; set; }
public bool RenderInEditor { get; set; }
public string DefinitionName { get; set; }
public string DefinitionDescription { get; set; }
public string UdfValue { get; set; }
public bool UdfValueHasChanged { get; set; }
public bool UdfFieldHasChanged { get; set; }
public bool UdfFieldDeleted { get; set; }
public bool DisplayOnSite { get; set; }
public string CultureCode { get; set; }
public override string ToString()
{
return string.Format("{0} {1} : {2} = {3}", this.SKU, this.ProductName, this.DisplayName, this.UdfValue);
}
}
//This will load approx. 173000 item for a SQL database
List<ccProductUDFs> listudfs = ccProductUDFsData.Load(DBConfiguration.GetDBContext());
//_batchsize is set to 200
//processUdFs is a Boolean = true stored in AppSettings
#region Update any UDFs...
_startrange = 0;
started = DateTime.Now;
timeTaken = TimeSpan.MinValue;
timeLeft = TimeSpan.MinValue;
doneCount = 0;
_totalCount = listudfs.Count();
while (_processUdFs && _startrange < listudfs.Count())
{
int sz = listudfs.Count() - _startrange >= _batchsize ? _batchsize : listudfs.Count() - _startrange;
List<ccProductUDFs> shallowlist = listudfs.GetRange(_startrange, sz);
_startrange = _startrange + sz;
_session = SessionContext.Session;
// create a response list to hold results of uploads...
List<ccResponse> response_categoryUDFs = new List<ccResponse>();
// start the transaction
using (var tx = _session.BeginTransaction())
{
_dbcontext.BeginTransaction();
int counter = 0;
int listcount = listudfs.Count();
// loop through each remaining UDF
// these are UDFs where the FIELD or the VALUE has changed, and we have not dealt with it as part of the product
foreach (ccProductUDFs udf in shallowlist)
{
if (ShowLog(verbose, (_startrange - sz) + ++counter, listcount))
{
TimeSpan elapsed = (DateTime.Now - started);
WriteLogV2("product udfs {0} of {1}", (_startrange - sz) + counter, listcount, elapsed);
}
// get the product for this UDF...
var product = _session.Query<Product>().FirstOrDefault(a => a.Sku == udf.SKU);
if (product != null)
{
// check that product has a product definition...
if (product.ProductDefinition == null)
{
// product has no definition assigned, so check that the product definition exists in data...
ProductDefinition definition = _session.Query<ProductDefinition>().FirstOrDefault(a => a.Description == udf.DefinitionName);
if (definition == null)
{
// product definition doesn;t exist in data, so create it...
definition = new ProductDefinition();
definition.Description = udf.DefinitionDescription;
definition.Name = udf.DefinitionName;
// save the changes...
_session.SaveOrUpdate((ProductDefinition)definition);
}
// assign this product definition to the product record...
product.ProductDefinition = definition;
}
// determine if the UDF FIELD exists...
ProductDefinitionField definitionfield = product.ProductDefinition.ProductDefinitionFields.FirstOrDefault(a => a.Name == udf.FieldName);
if (definitionfield == null)
{
// the UDF FIELD does NOT exist, so we shall add it.
definitionfield = new ProductDefinitionField();
definitionfield.Name = udf.FieldName;
definitionfield.ProductDefinition = product.ProductDefinition;
// locate the data type record and assign it to this UDF FIELD
var dt = _session.Query<DataType>().FirstOrDefault(a => a.TypeName == udf.DataType);
if (dt != null)
{
definitionfield.DataType = dt;
}
// add the UDF FIELD to the product category...
product.ProductDefinition.ProductDefinitionFields.Add(definitionfield);
// save the changes...
_session.SaveOrUpdate((Product)product);
}
bool changed = definitionfield.Deleted != udf.UdfFieldDeleted;
// assign properties to this UDF FIELD...
definitionfield.Deleted = udf.UdfFieldDeleted;
if (changed)
{
// save the changes...
_session.SaveOrUpdate((ProductDefinitionField)definitionfield);
}
// determine if the UDF VALUE record exists...
ProductProperty property = product.ProductProperties.FirstOrDefault(a => a.ProductDefinitionField.Name == definitionfield.Name && a.Product.Id == product.ProductId);
if (property == null)
{
// the UDF VALUE does NOT exist, so we shall add it.
property = new ProductProperty();
property.ProductDefinitionField = definitionfield;
property.Product = product;
// add the UDF VALUE to the product category...
product.ProductProperties.Add(property);
}
changed = false;
string v = udf.UdfValue == null ? string.Empty : udf.UdfValue.Trim();
changed = property.Value != v;
// assign properties to this UDF FIELD...
property.Value = v;
if (changed)
{
// save the changes...
_session.SaveOrUpdate((ProductProperty)property);
// save the changes...
_session.SaveOrUpdate((Product)product);
}
// update the response with a successful result...
response_categoryUDFs.Add(new ccResponse(udf.SKU, udf.FieldName, ccCategoryUDFsData.Source, true, "", 0));
}
object[] prodparam =
{
_dbcontext.NewParameter("@Sku", udf.SKU),
_dbcontext.NewParameter("@udfName", udf.FieldName)
};
_dbcontext.ExecuteNonQuery(CommandType.Text,
"UPDATE [LOAD_ProductUdfValues] SET HasChanged = 0 WHERE ProductId = @Sku And FieldName = @udfName",
prodparam);
TimeSpan ts = DateTime.Now - started;
doneCount++;
Console.WriteLine("Done {0} of {1} in {2}", doneCount, _totalCount, ts.ToString(@"hh\:mm\:ss"));
}
try
{
// commit all changes...
tx.Commit();
_dbcontext.CommitTransaction();
}
catch (Exception ex)
{
// Error Handler
tx.Rollback();
_dbcontext.RollbackTransaction();
response_categoryUDFs.Clear();
response_categoryUDFs.Add(new ccResponse("", "", ccCatalogData.Source, false, "Commit failed [" + ex.ToString() + "]", 1));
}
}
// send any response...
ccResponse.Write(_dbcontext, response_categoryUDFs, ccCatalogData.ResponseTarget);
// tidy up the session before is it disposed...
_session.Flush();
// }
}
#endregion
In case anyone finds this in a search I have fixed my issue by adding _session.Clear(); just after the _session.Flush();