Super Slow DeleteAllOnSubmit
-
Hi, I am trying to delete my SQL Compact FIXMsgs Table upon application startup. DevelopmentDB db = GetNewDataContext(); PerformanceTimer timer = new PerformanceTimer(); int nbFIXMsg = 0; if (_log.IsDebugEnabled) { nbFIXMsg = db.FIXMsgs.Count(); timer.Start(); } //db.ExecuteCommand("delete from FIXMsgs"); db.FIXMsgs.DeleteAllOnSubmit(db.FIXMsgs); db.SubmitChanges(); if (_log.IsDebugEnabled) { timer.Stop(); _log.Debug("ClearDataBase - " + nbFIXMsg + " messages deleted in " + timer.MilliSeconds + " ms"); } Using db.FIXMsgs.DeleteAllOnSubmit(db.FIXMsgs); -> 4000 items deleted in 40,000 ms! Using db.ExecuteCommand("delete from FIXMsgs"); -> 4000 items deleted 250ms it's about 200 times faster! I clearly am missing something obvious as such a performance difference is ridiculous. Can you please try an help me to figure out what I'm doing wrong? Thanks, Regis PS: a few more information - I am using sqlMetal to create the dbml file - This table has a primary key
-
Hi, I am trying to delete my SQL Compact FIXMsgs Table upon application startup. DevelopmentDB db = GetNewDataContext(); PerformanceTimer timer = new PerformanceTimer(); int nbFIXMsg = 0; if (_log.IsDebugEnabled) { nbFIXMsg = db.FIXMsgs.Count(); timer.Start(); } //db.ExecuteCommand("delete from FIXMsgs"); db.FIXMsgs.DeleteAllOnSubmit(db.FIXMsgs); db.SubmitChanges(); if (_log.IsDebugEnabled) { timer.Stop(); _log.Debug("ClearDataBase - " + nbFIXMsg + " messages deleted in " + timer.MilliSeconds + " ms"); } Using db.FIXMsgs.DeleteAllOnSubmit(db.FIXMsgs); -> 4000 items deleted in 40,000 ms! Using db.ExecuteCommand("delete from FIXMsgs"); -> 4000 items deleted 250ms it's about 200 times faster! I clearly am missing something obvious as such a performance difference is ridiculous. Can you please try an help me to figure out what I'm doing wrong? Thanks, Regis PS: a few more information - I am using sqlMetal to create the dbml file - This table has a primary key
You are not doing it wrong. Linq is generating a delete statement for each row in the table and matches the row to delete on all columns. This is why it takes a lot of time. the ExecuteCommand is the prefered way in this scenario
Andreas Johansson
IT Professional at Office IT Partner i Norrbotten Sweden
What we don't know. We learn.
What you don't know. We teach