Solved

Optimize Insert performance on grid

  • 24 May 2021
  • 5 replies
  • 294 views

Hi,

I need to insert 300+ (or more, depending on the # of batches selected) rows of records on grid but the time it consumes is way too long, it takes 10 mins to insert 298 rows, but as I tweaked the code, I was able to lessen it to 8 minutes.

AddProperty Action - This is where I add records to the grid.

public PXAction<REMeterReading> AddProperty;
[PXUIField(DisplayName = REMessages.AddProperty)]
[PXButton()]
public IEnumerable addProperty(PXAdapter adapter)
{
ClearDetails();

if (Filter.AskExt() == WebDialogResult.OK)
{
string[] floors = Filter.Current.Floors.Split(',').Select(_ => _.Trim()).ToArray();

REMeterReading document = MeterReading.Current;

List<REProperty> properties = GetMeterReadingProperties(propertyClassID: document.PropertyClassID,
rateID: document.RateID,
floors: floors);

foreach (REProperty property in properties)
{
REMeterReadingDetail detail = new REMeterReadingDetail();
detail.PropertyID = property.PropertyID;
MeterReadingLines.Insert(detail);
}

Filter.Cache.Clear();
}

return adapter.Get();
}

 

ClearDetails Method
 

private void ClearDetails()
{
foreach (REMeterReadingDetail d in MeterReadingLines.Select())
{
MeterReadingLines.Cache.Delete(d);
MeterReadingLines.Cache.IsDirty = true;
}
}

 

GetMeterReadingProperties Method

        private List<REProperty> GetMeterReadingProperties(int? propertyClassID, int? rateID, string[] floors = null)
{


var properties = new PXSelectJoin<REProperty, InnerJoin<REPropertyRateDetail, On<REProperty.propertyID, Equal<REPropertyRateDetail.propertyID>>>,
Where<REProperty.propertyClassID, Equal<Required<REMeterReading.propertyClassID>>,
And<REPropertyRateDetail.rateID, Equal<Required<REMeterReading.rateID>>,
And<REProperty.isSharedUtilities, Equal<True>>>>>(this);

if (!floors.Any())
{
return properties.Select(propertyClassID, rateID).RowCast<REProperty>().AsEnumerable().ToList();
}

properties.WhereAnd(typeof(Where<REProperty.floorNbr, In<Required<REProperty.floorNbr>>>));

return properties.Select(new object[] { propertyClassID, rateID, floors.ToArray() }).RowCast<REProperty>().AsEnumerable().ToList();
}

I am curious about these things:

  • What are the things to be considered on adding dynamically adding records on grid?
  • Does the defaulting of fields, field calculation affects the row insert? If so, is there a better way to handle these things?
  • Also, Is it better to do CacheAttached on fields rather than specifying it early on the DAC?
  • What can I do to speed up the insertion of records to the grid

I hope you can give me an advice. Thank you so much and have a nice day.

icon

Best answer by Naveen Boga 25 May 2021, 06:59

View original

5 replies

Userlevel 7
Badge +17

Hi @igalm15 ,

I just wanted to share my thoughts here and here are the observations.

Assume, if you wanted to insert the 300 records, then you have below 2 methods.

  • ClearDetails (Deleting the data from the grid → looing 300 times)
  • GetMeterReadingProperties (Inserting the data → looing 300 times)

Totally, this code is looing 600 items, and could you please clarify from the below items.

  • As you are inserting the data into the grid is it only for displaying OR the user may update the grid records data well?
  • Are you saving the grid the into database also? because I have seen the only cache.insert but NOT the  this.Actions.PressSave()

Hi @igalm15 ,

I just wanted to share my thoughts here and here are the observations.

Assume, if you wanted to insert the 300 records, then you have below 2 methods.

  • ClearDetails (Deleting the data from the grid → looing 300 times)
  • GetMeterReadingProperties (Inserting the data → looing 300 times)

Totally, this code is looing 600 items, and could you please clarify from the below items.

  • As you are inserting the data into the grid is it only for displaying OR the user may update the grid records data well?
  • Are you saving the grid the into database also? because I have seen the only cache.insert but NOT the  this.Actions.PressSave()

 

  • Hey @Naveen B , Thanks for answering.

    Q: As you are inserting the data into the grid is it only for displaying OR the user may update the grid records data well?
    A: Yes, I’m indeed inserting data into the grid for the user to update some fields on the grid later on and then save it to the database.

    Q: Are you saving the grid the into database also? because I have seen the only cache.insert but NOT the  this.Actions.PressSave()
    A: Yes, I’m saving the data later on, as soon as the user completely finish updating the field needed on the grid (on which, I suppose will be another long time. :D)

 

ClearDetails (Deleting the data from the grid → looing 300 times)

- Suppose that I’m not going to loop here on clearing the items on the grid, What is the possible way for me to implement data clear on the grid? I’ve tried using ViewName.Cache.Clear() but it doesn’t clear the data on the grid. Are there any way for me to clear the data?

 



Also, just want to ask, if the DEFAULTING of the fields causes some load during insert, Is there a way to optimize it, OR are there any good practices on field defaulting? Is it good to implement defaulting on the DAC or maybe on the CacheAttached?

Thank you for the inputs. Hope you can give me another advice. Maybe I’ll find a way to remove some of the loops. :) 

Userlevel 7
Badge +17

Hi @igalm15 

 Below is my thought process, which may help you to improve the performance of this screen. Rest of the code looks good to me :) 
 
 1. Please add this logic under the long-run operation.
 2. As every time, you are deleting the data from a table, you can use PXDatabaseDelete instead of looping 300 times every time when we click on the button.
 3. Coming to the Defaulting events, it's again the logic that you have written that event. If you have BQL's with huge logic, definitely this causes the performance issue.
 
 I have attached the source code below.. Hope this helps!!

 

 public PXAction<REMeterReading> AddProperty;
[PXUIField(DisplayName = REMessages.AddProperty)]
[PXButton()]
public IEnumerable addProperty(PXAdapter adapter)
{
ClearDetails();

if (Filter.AskExt() == WebDialogResult.OK)
{
PXLongOperation.StartOperation(this, delegate ()
{
string[] floors = Filter.Current.Floors.Split(',').Select(_ => _.Trim()).ToArray();

REMeterReading document = MeterReading.Current;

List<REProperty> properties = GetMeterReadingProperties(propertyClassID: document.PropertyClassID,
rateID: document.RateID,
floors: floors);

foreach (REProperty property in properties)
{
REMeterReadingDetail detail = new REMeterReadingDetail();
detail.PropertyID = property.PropertyID;
MeterReadingLines.Cache.Insert(detail);
}

Filter.Cache.Clear();
});
}

return adapter.Get();
}

private void ClearDetails()
{
//foreach (REMeterReadingDetail d in MeterReadingLines.Select())
//{
// MeterReadingLines.Cache.Delete(d);
// MeterReadingLines.Cache.IsDirty = true;
//}

if (MeterReadingLines.Select().Count > 0)
PXDatabase.Delete<DACName>();
}


private List<REProperty> GetMeterReadingProperties(int? propertyClassID, int? rateID, string[] floors = null)
{


var properties = new PXSelectJoin<REProperty, InnerJoin<REPropertyRateDetail, On<REProperty.propertyID, Equal<REPropertyRateDetail.propertyID>>>,
Where<REProperty.propertyClassID, Equal<Required<REMeterReading.propertyClassID>>,
And<REPropertyRateDetail.rateID, Equal<Required<REMeterReading.rateID>>,
And<REProperty.isSharedUtilities, Equal<True>>>>>(this);

if (!floors.Any())
{
return properties.Select(propertyClassID, rateID).RowCast<REProperty>().AsEnumerable().ToList();
}

properties.WhereAnd(typeof(Where<REProperty.floorNbr, In<Required<REProperty.floorNbr>>>));

return properties.Select(new object[] { propertyClassID, rateID, floors.ToArray() }).RowCast<REProperty>().AsEnumerable().ToList();
}

 

 

Hi @igalm15 

 Below is my thought process, which may help you to improve the performance of this screen. Rest of the code looks good to me :) 
 
 1. Please add this logic under the long-run operation.
 2. As every time, you are deleting the data from a table, you can use PXDatabaseDelete instead of looping 300 times every time when we click on the button.
 3. Coming to the Defaulting events, it's again the logic that you have written that event. If you have BQL's with huge logic, definitely this causes the performance issue.
 
 I have attached the source code below.. Hope this helps!!

 

 public PXAction<REMeterReading> AddProperty;
[PXUIField(DisplayName = REMessages.AddProperty)]
[PXButton()]
public IEnumerable addProperty(PXAdapter adapter)
{
ClearDetails();

if (Filter.AskExt() == WebDialogResult.OK)
{
PXLongOperation.StartOperation(this, delegate ()
{
string[] floors = Filter.Current.Floors.Split(',').Select(_ => _.Trim()).ToArray();

REMeterReading document = MeterReading.Current;

List<REProperty> properties = GetMeterReadingProperties(propertyClassID: document.PropertyClassID,
rateID: document.RateID,
floors: floors);

foreach (REProperty property in properties)
{
REMeterReadingDetail detail = new REMeterReadingDetail();
detail.PropertyID = property.PropertyID;
MeterReadingLines.Cache.Insert(detail);
}

Filter.Cache.Clear();
});
}

return adapter.Get();
}

private void ClearDetails()
{
//foreach (REMeterReadingDetail d in MeterReadingLines.Select())
//{
// MeterReadingLines.Cache.Delete(d);
// MeterReadingLines.Cache.IsDirty = true;
//}

if (MeterReadingLines.Select().Count > 0)
PXDatabase.Delete<DACName>();
}


private List<REProperty> GetMeterReadingProperties(int? propertyClassID, int? rateID, string[] floors = null)
{


var properties = new PXSelectJoin<REProperty, InnerJoin<REPropertyRateDetail, On<REProperty.propertyID, Equal<REPropertyRateDetail.propertyID>>>,
Where<REProperty.propertyClassID, Equal<Required<REMeterReading.propertyClassID>>,
And<REPropertyRateDetail.rateID, Equal<Required<REMeterReading.rateID>>,
And<REProperty.isSharedUtilities, Equal<True>>>>>(this);

if (!floors.Any())
{
return properties.Select(propertyClassID, rateID).RowCast<REProperty>().AsEnumerable().ToList();
}

properties.WhereAnd(typeof(Where<REProperty.floorNbr, In<Required<REProperty.floorNbr>>>));

return properties.Select(new object[] { propertyClassID, rateID, floors.ToArray() }).RowCast<REProperty>().AsEnumerable().ToList();
}

 

 

 

Thanks a lot @Naveen B , I’ll get back to you as soon as i try this. :)

Userlevel 6
Badge +5

Are you able to use the Web Services API, or do you have to do this through the Acumatica Framework? I have imported ~50,000 records through the web services API and it goes pretty fast. I wrote a PHP binding to do it.

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved