Scenario: SSIS packages loading data. Deletes of sections of data prior to reload taking a very long time. Currently done manually with constraints disabled, data loads done, then constraints re-applied. This is deleting maybe 100k records from a table of about 400k.
Looking at the live query execution in SQL2016 on workstation, what seems to be the sticking point is the table at the top of the dependency tree. E.g. Org -> OrgLocation -> OrgLocationPeople etc. These tables are all referenced by about 20 schema-bound view indexes (a mix of clustered or not). Some of the views are non-trivial and involve some geographical function calculations.
When the delete is issued against the "org" table (DELETE FROM orgs FROM #tmpOrgs where orgs.id = #tmpOrgs.id) it seems that for every delete, all the constraints are checked as you would expect Now I'm not sure how SQLServer manages the views on the indexes, but from the look of the query plans, it seems to be calculating the outcome for each change for each view and index so that it can rebuild the indexes correctly. This kind of makes sense in that if the underlying data changes, the view output obviously changes and therefore the index attached to that view must change. This is obviously an insane amount of work for each delete.
SQLServer on Azure, SSD storage, single disk. Was ok on basic tin prior to move (apparently). Was ok for a while on Azure. Also very slow on SQL2016 on workstation (SSD drives, 7th Gen i7, lots of memory) (Support scenario, little chance for a full rewrite of existing code)
So, just looking for confirmation of what I am seeing and any suggestions of how to manage this situation. So far I have: a) disable constraints for load b) redesign system so that views are "materialised" - i.e. load the data into tables rather than use views as the data underneath the tables cannot change outside of these dataload processes.
(b) would be my preferred option, but I won't get the remit to change all that as it would require a massive overhaul and cooperation from too many people. (I need a bigger stick)
TIA for any info and help offered.
