Performance with deletion of data

[Wolfgang]

I search for a performant way to update an objectview/XtraGrid.

Background: we are using Telerik OpenAccess for Data andDEvExpress XtraGrid for showing the data.

Lets assume the following to snippets.

We remove an object from teleriks objectview with objectView.List.Remove. If we do a DevXpress gridView BeginUpdate and BeginDataUpdate before (and the corresponding End*Update after) the deletion of just one object can take a lot of time (e.g. 8s by 1 object out of 3000 (the deletion of the very first object is always fast, but as soon as a second one is deleted, low speed occurs.)).

  gridView.DataSource = objectView; // somewhere before bind gridView to BindingSource

     
gridView.BeginUpdate();

  gridView.BeginDataUpdate();

  objectView.List.Remove(objectToBeRemoved); gridView.EndDataUpdate();

  gridView.BeginDataUpdate();

 

 

/*Please note: we do not do a objectView.Remove() as it would delete the data from database,, some data should just be invisible. As this data will never be displayed to a user (or a certain user will never the the data on userchange) it does not make sense to filter later. (e.g. give a workitem form one user to another, the user A should no longer see the workitem, or "delete" an object, but only set a remove flag in db (some data needs to be kept by law),...)*/

Second try: 
We remove an object from teleriks objectview with If we do a DevXpress gridControl BeginUpdate before (and the EndUpdate after) the deletion of just one object is displayed instantly (again 1 object out of 3000).

gridView.GridControl.BeginUpdate();
objectView.List.Remove(objectToBeRemoved);
gridView.GridControl.BeginUpdate();

The problem with the later is, that also that the line looks deleted instantly, there appears an empty line at the end of the grid (for each deleted item). Only with the gridView "*DataUpdate()" methods the empty line disappears. It looks like the complete time is spend in the EndDataUpdate() method.

Now comes the interesting part why the question is asked on Tleriks site: 
objectView.ResetBinding() would also cause a long runtime. Therefore I think it is not only an XtraGrid problem, but has somethign to do with the datasource itself. From the description: "Causes a control bound to the  to reread all the items in the list and refresh their displayed values. (inherited from

The interesting thing is where the time is spend:
If using Teleriks JustTrace, it is shown that the XtraGrid gets row values for the sorting of rows.

The time itself seems mainly be spend in Telerik.OpenAcces.RT.PersistentPropertyDescriptor.GetValue(). Telerik seems to trigger a long - running OpenAcessRunTime.RelationalStorageManager.fetch

Why is the ObjectView rereading the data?

Is there a way to do a performant remove on the objectviews list / between OpenAccess and the XtraGrid?

[Ady]

Hi Wolfgang,

 Is it possible to send me a windows application without the DevXPress grid where I can observe the behavior? Do you experience the same slow performance with a simple Windows Forms grid? It might be helpful if we have a Windows Forms example so that we can isolate the problem to OpenAccess.

All the best,
Ady
the Telerik team

[Wolfgang]

Hi,
To be honest i'm having Problems creating a Winforms solution with DGV.

The problem mostly occurs when data is sorted in some way (e.g. user clicked a column Header, but i see no fast solution to put the telerik datasource to something sortable without loosing the binding, so something else would be tested)

The funny Thing is that i now have a behaviour which was happenign sporadic with DX all the time with MS DGV:

If i delete a row, an empty line occur in the DGV. The code for DGV is quite simple so far:

public Form1()
{
    InitializeComponent();
    InitGrid();
}

DGVEntitiesModel dbContext = new DGVEntitiesModel();

void InitGrid()
{
    myBindingSourceZips.DataSource = dbContext.ZIPs;
    dataGridView1.DataSource = myBindingSourceZips;

    foreach (DataGridViewColumn column in dataGridView1.Columns)
    {
        dataGridView1.Columns[column.Name].SortMode = DataGridViewColumnSortMode.Automatic;
    }
}

private void Delete_Click(object sender, EventArgs e)
{
    ZIP aZip = (from c in dbContext.ZIPs
                      where c.ZipCode > 45000
                      select c).FirstOrDefault();
    dataGridView1.BeginEdit(true);

    dbContext.Delete(aZip);
    dbContext.SaveChanges();

    dataGridView1.EndEdit();

}

But haven't found out how to sort the columns.


--------------------

Additional Information: If i don't use openaccess, but have a simple BindingList of objects, the behaviour seems to be fast.

[Ady]

Hi Wolfgang,

 I am really confused as to what you want to exactly achieve. Can you tell me the exact problem so that  I can  prepare a sample Windows Forms application that achieves that.
Is the deletion of an item (row) from the grid slow for you? If yes, can you provide me the code that you use to bind the data to the grid and the code you use to delete the item? 

Hope to resolve this issue soon.

Ady

[Wolfgang]

Hi Ady,

I'll give it a try to strip it down 

1. Attach objectvierw and data to GridView
// we have a list of items (per form)

private List<Items> itemList;

//In each form there is also an objectview

private Telerik.OpenAccess.ObjectView objectViewItems;

 

// In initializeComponent the objectview is attached to a Grid (gridControl is the GridControl of the GridView)

this.gridControl.DataSource = this.objectViewItems;

 

// Frm_Load

this.itemList= base.bl.GetAll<Items>();     

this.objectViewItems.SetObjectSource(base.bl.CurrentObjectScope, this.itemList);

 

// where GetAll is a generic method to catch items from an objectscope

public List<T> GetAll<T>() where T : MainType

{

    return  (from sub in this.objectScope.Extent<T>() select sub).ToList();

}

At a later point in time the deletion is done (that were the two code parts from original post). The object is not deleted from the same thread (e.g. another user deleted the item and the items on a second users screen should be updated. )

gridView.BeginUpdate(); // a DevExpress XtraGrid


gridView.BeginDataUpdate();

//first the item is removed from the list in the GridControl to prevent Exceptions about not found items
IList list = gridvIew.Gridcontrol.Datasource as IList;
list.Remove(objectRoBeRemoved);
// second it is removed from objectview

objectView.List.Remove(objectToBeRemoved); gridView.EndDataUpdate();
// third it is removed from all GridLookUpEdit's

lookUp.List.Remove(objectToBeRemoved);
gridView.EndDataUpdate();

gridView.EndUpdate();

with this or a gridView.RefreshData() data deletion from grid took a long time (data is only removed using objectView.List.Remove() and not objectView.Remove(), so that it is not deleted from database - it should only no longer be visible for user, so it can be recovered for a period of time, and deleted later). What I left out in the code is error handling, checking that the objects/list etc.pp are not null, are available in a list, how the corresponding objectviews/controls are found/handled.

The second item which was removed in that way took a long time perion to refresh the XtraGrid.
If OpenAccess was not used, but the DataSource was a normal BindingList everything was acting fast. 

So what i wanted to achieve was only to remove an object from the list of objects from objectview, so that it is no longer be shown in the XtraGrid.

Wolfgang

[Wolfgang]

Hi Ady,

some additional information from DevExpress:

"I have researched your project, and found that the cause of the issue is in your data source. When the grid control's data is sorted and you delete a row, the grid should resort its data. (This is the expected behavior.) However, I have detected that when the grid tries to access a field from your DataSource, the DataSource executes the Select query to fetch this data. In your case, the DataSource fetches data more then 50,000 times. This is the cause of this slowdown. It will be quicker if data is cached by a data source. "
[info following small demoproject was build with DevExpress XtraGRid and Telerik OpenAccess]

public Form1()
{
    InitializeComponent();
    InitGrid();

}

DGVEntitiesModel dbContext = new DGVEntitiesModel();

void InitGrid()
{
    gridControl.DataSource = dbContext.ZIPs;
}

private void Delete_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
    gridView1.BeginUpdate();
    gridView1.BeginDataUpdate();
    dbContext.Delete(dbContext.ZIPs.FirstOrDefault());
    dbContext.SaveChanges();
    gridView1.EndDataUpdate();
    gridView1.EndUpdate();

}

private void DeleteAllData_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
    List<ZIP> zips = (from c in dbContext.ZIPs
                      select c).ToList();
    gridView1.BeginUpdate();
    gridView1.BeginDataUpdate();
    for (int i = zips.Count; i > 0; i--) dbContext.Delete(zips[i - 1]);
    dbContext.SaveChanges();
    gridView1.EndDataUpdate();
    gridView1.EndUpdate();

}

private void CreateData_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
    gridView1.BeginUpdate();
    gridView1.BeginDataUpdate();
    var reader = new StreamReader(File.OpenRead(@"C:\OpenGeoDB_bundesland_plz_ort_de.csv"));
    while (!reader.EndOfStream)
    {
        var line = reader.ReadLine();
        var values = line.Split('\t');

        ZIP aZip = new ZIP();
        aZip.City = values[2];
        aZip.ZipCode = int.Parse(values[1]);
        aZip.Country = values[0];

        dbContext.Add(aZip);
    }
    dbContext.SaveChanges();
    gridView1.EndDataUpdate();
    gridView1.EndUpdate();

}

[Ady]

Hi Wolfgang,

  Have you tried setting the value of the 'ObjectView.PersistenceBehavior' property to 'none' or 'markForAdd'? (The default value is 'markForAddAndDelete'.) This will cause a ObjectView.Remove to just remove the object from the view and not from the database.

Kind regards,
Ady
the Telerik team

[Wolfgang]

Hi Ady,

That would also be a remove.

We have 2 different Problems we wanted to solve with a "soft remove"

1. If an user deletes data by error, it should be recoverable. And this recover can be in same session or in another one. Also users have independend databases which are synchronized. So if one user deletes data, also the other ones should no longer see it (normal usecase, the recovery is the extension where all users should then see it again).

2. If a remove is triggered on another machine, it is done like in example below.

gridView.BeginUpdate();
gridView.BeginDataUpdate();
objectView.Remove(objectToBeRemoved); gridView.EndDataUpdate();
gridView.EndDataUpdate();
gridView.EndUpdate();

 If it is done in this way, sometimes an exception occures, and the DevExpress grid only shows a red cross.

The last steps of a trace Shows that the XtraGrid tries to get data, but Telerik throws an exception instead. It no longer finds an objects where properties are asked for.

Telerik.OpenAccess.Exceptions.NoSuchObjectException: No row for
PersistentClasses.XYZ ('XYZ') GenericOID@fa3099dd XYZ
RowId=9f943b3c-946b-430b-ad51-aac6b9b42803<BR>    bei
Telerik.OpenAccess.RT.PersistentPropertyDescriptor.GetValue(Object
component)<BR>    bei
DevExpress.Data.Helpers.BaseListDataControllerHelper.GetRowValue(Int32
listSourceRow, Int32 column, OperationCompleted completed)<BR>
   bei
DevExpress.Data.Storage.DataStorageObjectComparer.CreateStorage(VisibleListSourceRowCollection
rows, BaseDataControllerHelper dataHelper, Int32 column)

Normally with databinding and just a BindingList items can be removed / added / altered without a problem and changes are reflected on UI immediatly. with the objectscope as BindingSource changes on the datasource does not seem forward INotifyPropertyChanged to XtraGrid. So it should also be a Workaround to only delete data when the data is not bound to a Grid. (Ugly, but works quite well).

Very ugly workaround:
Funilly it is also much faster if we copy the complete objectview.list in a new list<T> and remove deleted items form new list while copying and then asserting the new list as the objectsviews source. This is very ugly to go each time one time through complete list, but nevertheless it seems to be fast (but this is a point where using the ORM lets us create ugly Workarounds, for the project an openaccess subscription was made since the days of Vanatec, and a lot of extras where coded for the old api so it will not so easy replaced with the new one or another product (even the demo project with new api showed similiar speed problems).

So i still search for a better solution to soft delete an object with telerik.

Best regards
Wolfgang

[Ady]

Hi Wolfgang,

  There is no way to perform a 'soft delete'. The objects in the ObjectView are directly bound to an underlying ObjectScope. Any changes to these objects are tracked by this ObjectScope. You can use the 'PersistenceBehavior' property, as mentioned before, to control how 'Add/Delete' function but this is applicable for all objects.
 If you want to have 'soft delete' functionality then you will need to maintain your own list of objects that need to be updated on the database and then attach this list to the ObjectView so that these objects are tracked.

All the best,
Ady
the Telerik team

[Wolfgang]

Hi Ady,

I think the core problem is still that if i remove objects from objectscope, this change is not imm. pushed to the XtraGrid (funilly a test with an IBindingList does not show this problem).

So the hole thing is only a big workaround for the Telerik.OpenAccess.Exceptions.NoSuchObjectException. 

As the remove was so slow, we now copied the objects just from the objectscope to a list, filter the soft removed items and then add the list as source of objectscope again.
This design looks awful, but is faster then objectscope.list.Remove (which took for a 3000 object list ~20s). With this it looks from a customer view like an instant, fluent update of the grid.

Best regards
Wolfgang

[Ady]

Hi Wolfgang,

 From what I understand, you are deleting an object in 1 form and expect that the grid in another form is refreshed and the deleted object is no longer visible. If each form has it's own objectscope, the objectscope has no way to know that an object is deleted from another scope. You would need to manage this on your own; by starting a new transaction on the scope so that the objects are fetched again. If the scopes are obtained from a single database instance (same application) then you can use the 2nd Level cache to cache objects. If objects are deleted in 1 scope then they are evicted from the 2nd level cache also. 
I have not been able to reproduce the problem with deletion and the grid. If I delete an object it is immediately removed from the grid and the delete also is not slow.

Regards,
Ady
Telerik

[Wolfgang]

Hi Ady,

"From what I understand, you are deleting an object in 1 form and expect that the grid in another form is refreshed and the deleted object is no longer visible. If each form has it's own objectscope, the objectscope has no way to know that an object is deleted from another scope. "

Yes. Therefore we keep lists of objevtviews which are pointing to objects of same types (and also the conenction to the appropriate grids, to make sure we reach everything if we delete an object)
We push the changes to the other scopes, but do not see that the XtrGrid also gets the changes.

If we
- just delete it often the "redcross"-XtraGrid stall occured.
- delete it from objectscope.list it was slow (what you already could reproduce in the other post)
- update the complete list (manually go through each item, copy the non deleted to a new list) and put the updated list as source of the objectscope it i fast, but.. it seems like a very bad style to copy each object to another list, and reattach it to get an update of only one item...

The bad thing is that the DevExpress support is insisting that the Telerik.OpenAccess.Exceptions.NoSuchObjectException:
is a Telerik exception, and therefore they wouldn't have a problem (We tried with a nromal BindingList, where the problem also does not occur).

Best regards,
Wolfgang

[Ady]

Hi Wolfgang,

 The additional benefit that the ObjectView provides over the standard bindingsource is that you  add and delete operations are tracked buy the underlying scope. If you do not really want to use the delete functionality you should use the standard binding source and set the Data source property to point to a list of persistent objects that you obtain from the scope. Any changes to the objects are still tracked by the scope. Additionally you can decide which objects to delete and which not explicitly.
Regarding keeping multiple forms in sync, if 1 form deletes an object you can denote the rest of the forms (scopes) and begin a new transaction on them so that the changes are reflected. You should enable the 2nd level cache so that the objects are not fetched from the database again. Since the object is deleted by 1 scope if you try to delete it from the other you will get a 'NoSuchObjectException' and hence the red  cross.

Regards,
Ady
Telerik