delete with dapper or other aproach with performance and fast

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);

What about indexes on the columns used in the where clause? – Steve Aug 31 '13 at 10:15 — Steve, Aug 31 '13 at 10:15
How about setting cascaded deletes? – Robert Koritnik Sep 02 '13 at 04:19 — Robert Koritnik, Sep 02 '13 at 04:19

score 3 · Answer 1 · answered Aug 31 '13 at 12:07

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.

delete with dapper or other aproach with performance and fast

1 Answers1