Databases
StackOverflow2013
Postsdbo
POCorrect method of deleting over 2100 rows (by ID) with Dapper
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCorrect method of deleting over 2100 rows (by ID) with Dapper
    primarykey
    data
    text
    <p>I am trying to use <a href="http://code.google.com/p/dapper-dot-net/" rel="nofollow noreferrer">Dapper</a> support my data access for my server app.</p> <p>My server app has another application that drops records into my database at a rate of 400 per minute.</p> <p>My app pulls them out in batches, processes them, and then deletes them from the database.</p> <p>Since data continues to flow into the database while I am processing, I don't have a good way to say <code>delete from myTable where allProcessed = true</code>.</p> <p>However, I do know the PK value of the rows to delete. So I want to do a <code>delete from myTable where Id in @listToDelete</code></p> <p>Problem is that if my server goes down for even 6 mintues, then I have over 2100 rows to delete.</p> <p>Since Dapper takes my @listToDelete and turns each one into a parameter, my call to delete fails. (Causing my data purging to get even further behind.)</p> <p>What is the best way to deal with this in Dapper?</p> <p>NOTES: I have looked at Tabled Valued Parameters but from what I can see, they are not very <a href="https://stackoverflow.com/a/6246886/16241">performant</a>. This piece of my architecture is the bottle neck of my system and I need to be very <strong>very</strong> fast.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USVaccano
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCorrect method of deleting over 2100 rows (by ID) with Dapper
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POCorrect method of deleting over 2100 rows (by ID) with Dapper
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO@marc_s - I don't **need** to pass that many parameters... But I do need to delete that many rows by PK Id. However I do that is fine by me. I am telling dapper to delete every row in my `@list`. It is Dapper that is making parameters of every item in my list.
      singulars
      1. POCorrect method of deleting over 2100 rows (by ID) with Dapper
      1. USVaccano
    2. COHard to tell from this little information, but why can't you use teh selection criteria for batch as your criteria for a delete. Or have a processed flag in MyTable, set it in the "processing" and then use that. It's not brill, but it will be much quicker than deleting them one by one. It's isn't going to be shockingly bad even with 10,000 records.
      singulars
      1. POCorrect method of deleting over 2100 rows (by ID) with Dapper
      1. USTony Hopkinson
    3. COMy batch criteria is not very performant. So I would prefer not to use that. I could add a processed flag, but to do that I would need a way to call out the rows to add the processed flag to. If I can call them out, I may as well delete them. (I could add a flag to say, "batched" at select time. But I would rather not.)
      singulars
      1. POCorrect method of deleting over 2100 rows (by ID) with Dapper
      1. USVaccano
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload