delete with dapper or other aproach with performance and fast

c# cascading-deletes dapper performance

Question

I want delete data from database that tables have relation i should query with tables for delete data from parent and child with dapper. this is very slow . i delete alot data(millions record) 45 second but delete Several rows perform in 2 minute how perform this with performance and fast??

 var deletequery = @"delete from DailyCodeDetail " +
                   @" from DailyCodeDetail  dcd inner join DailyCode" +
                   @" dc on dcd.DailyCodeId = dc.id inner join DailyResult  d on d.id=dc.DailyResultId " +
                    @"where d.PersonId =@Personid and dcd.DateAttendance >= @sDate and dcd.DateAttendance <= @eDate";
connection.Execute(deletequery, new { Personid = personId, sDate = sdate, eDate = edate }, commandTimeout: 1000);

Popular Answer

The performance of dapper (or any other client technology) only matters for the number of queries and the number of rows returned. Your query is executed infrequently (but does a lot of work). The performance of Dapper does not matter here. All Dapper does is relay the query to SQL Server.

You should now look to optimize your query. This cannot be done as part of this question because we have almost no information about the tables and data taking part. It might just be that the query cannot be made faster at all because it is doing that much DML work.

You should probably research a bit about "how to bulk delete in SQL Server" now. That will help you.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why