I'm experiencing some horrible performance with FireDAC in master/detail setup.
I'm experiencing some horrible performance with FireDAC in master/detail setup.
I got a fairly plain master/detail setup with schema adapter, and appending rows seems to be O(n^2) with respect to rows, which means it bogs down quickly.
I've narrowed it down to TFDDatSView.HandleNotification being called excessively often, but not sure if this is expected (ie FireDAC is useless in non-trivial master/detail setups), or I am doing something stupid and/or wrong (which I hope). edit: fortunately it turns out it's the latter!
Attached a project which reproduces it in Delphi 10.1.2. After hitting "Run" button the window caption shows the time taken per append, which in my case grows quickly.
https://www.dropbox.com/s/uxmzptipulodkl7/FireDACMasterDetailPerformance2.zip?dl=0
I got a fairly plain master/detail setup with schema adapter, and appending rows seems to be O(n^2) with respect to rows, which means it bogs down quickly.
I've narrowed it down to TFDDatSView.HandleNotification being called excessively often, but not sure if this is expected (
Attached a project which reproduces it in Delphi 10.1.2. After hitting "Run" button the window caption shows the time taken per append, which in my case grows quickly.
https://www.dropbox.com/s/uxmzptipulodkl7/FireDACMasterDetailPerformance2.zip?dl=0
/sub
ReplyDeleteI compiled your source on 10.2.1 it took not even 2 seconds to execute
ReplyDeletecaption=0;
shlomo abuisak Thanks so much for testing!
ReplyDeleteSeems I'm chasing a Heisenbug :( I recompiled it myself in a clean directory and now it's fast for me too... and this is after spending 2 hours trying to make this repro case project :(
Thanks for the confirmation shlomo abuisak, I now realized what I changed right before publishing, and I didn't think it mattered.
ReplyDeleteI removed an internal unit from the uses clause, and I had forgotten that it installed a workaround for a FireDAC issue I raised here not long ago. The workaround is what is causing the slowness. The workaround fixes unique constraints considering deleted rows by adding a row-state filter to the unique constraint, but it seems this state filter causes it to loop over all rows in the row-view held by the unique constraint :(
Thanks again for the help, always good to have someone else sanity check.
Found the issue. The cause was that for each append, a new unique constraint view was created, and building this loops over all rows of course.
ReplyDeleteThe reason a new unique constraint view was created was that TFDDatSViewList.FindSortedView(), used to get the existing unique constraint view if any, only considers views with exactly one "view mechanism".
My workaround as described above involve adding a state filter mechanism to the unique constraint view, thus TFDDatSViewList.FindSortedView() never finds this view, causing a new one to be created each time Post is called...
So the fix was to write my own variant of FindSortedView which considers the sorted and filtered view. I will add the source code for my workaround in the QP report I'll be filing during Easter.