August 8, 2019 at 4:00 pm
Looking for Opinions on if this area of a C# application should be modified in how it hands things off to the DB.
The table in question has two columns (ListId,PersonId) there can be multiple PersonID with different list ID's
The UI of the application allows you to go in and view all these Lists and the PersonID's assigned to the list. You can then add and delete Personids as needed. I do not have access to the code in the UI so I do not know how it stores this change data before posting it, I can only see what it does on the DB side.
If person makes changes and posts the following fires against the DB.
Delete from Table where Listid = 1
Insert into Table (Listid,Personid) Values (1,1);
Insert into Table (Listid,Personid) Values (1,2);
Insert into Table (Listid,Personid) Values (1,3);
Insert into Table (Listid,Personid) Values (1,10);
Insert into Table (Listid,Personid) Values (1,20);
ETC..
At most the row count for any given list will be around 20k records.
I've done some testing around this method or only deleting those selected to delete or inserting new records and there was nothing overly conclusive. My guess without having access to the UI code is that it would be more intensive to send only the deletes or only the inserts because it will need to scan the whole table anyway, so the Delete and re-insert is the more effective way at this row count but looking for other opinions.
August 8, 2019 at 4:56 pm
Hello,.
I can't provide a direct answer but an answer to the following question might help others provide advice:
You said that you ran some tests. What were you using to measure the tests? Was it the execution plan in SSMS?
August 8, 2019 at 4:59 pm
As a pure guess, I am thinking the UI is issuing a full delete of a list as a way around trying to figure out how many PersonIDs had been deleted. It looks like something of a sledgehammer approach to maintaining the list. Not pretty, but sure. Individual deletes would not be a large tax on the system, as the table would (at least I hope it would) have a primary key on (ListID, PersonID).
August 8, 2019 at 9:29 pm
Where does the C# app get the list of people to be added to the list? If it's stored somehow in the DB and retrieved, I might actually try to do this in two statements (one delete, one insert) rather than a lot of singletons.
August 9, 2019 at 12:21 am
Is this an MVC application? Was the sql was generated by the entity framework?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 16, 2019 at 1:59 pm
The UI is probably using bound controls (eg. a datagrid) to update a DataTable. It sounds as if it is then clearing the table and posting each remaining row to the database. There is a flag called DataRowState* that could be used instead to post only those rows that changed to the database.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply