locationrest.blogg.se - Tableplus add rows

TABLEPLUS ADD ROWS FULL
TABLEPLUS ADD ROWS PLUS

That way you can find the sweet spot for your own deletes based on your server’s horsepower, concurrency demands from other queries (some of which might be trying to take table locks themselves), the amount of data you need to delete, etc.

The number of rows in the view (say, 1K, 5K, 10K, etc, keeping in mind the lock escalation threshold).

If you need to do this regularly, tune it. Still way better than 25,022,799 though, but it brings up a good point…. If I change it to TOP 10,000, then the reads jump to 209,163.

TABLEPLUS ADD ROWS PLUS

Fast Ordered Delete logical reads: 24,732 on the Comments table, plus 2K on the worktables – but that’s with me using TOP 1,000 in the view.

Plain DELETE logical reads: 25,022,799 on the Comments table (plus another 4.1M on the worktables).

The grants are lower because we’re handling less data, which is also evidenced by the STATISTICS IO output:

Fast Ordered Delete memory grant: 1.8MB (only 472KB of which got used).

TABLEPLUS ADD ROWS FULL

Plain DELETE memory grant: 118MB (only 64MB of which gets used, but it spills to disk anyway because not every operator can leverage the full grant – you can learn more about grant fractions from Joe Obbish).Similarly, the memory grant on this query is way lower: There’s no yellow bangs because there’s fewer sort operators and they’re not spilling to disk.

It runs nearly instantly (because we’ve got an index to support it), and here’s the plan: Fast ordered deletes planĪt first, it looks the same as the plain DELETE plan, but look closer, and there’s something missing: Just like me with the tequila – no spills The Comments table has a CreationDate field, and let’s say I need to delete the oldest comments – we’re going to delete all the ones from 20: Comments by yearĢ008 & 2009 had a total of 1,387,218 comments – but that’s only about 2.3% of the table’s overall rows. I’ve created 5 nonclustered indexes that total about 5GB of space (to make the deletes a little tougher and more like real-world tables).The dbo.Comments table – which has 60M rows, 20GB in the clustered index.

The Stack Overflow public database as of 2017-Aug.An 8-core, 60GB RAM VM with the data & log files on ephemeral (fast) SSD.To demo this technique, I’m going to use the cloud setup for our Mastering Query Tuning classes: Wanna see it in action? No? Then just copy/paste my code, put it straight into production like you always do, and get back to work. It won’t necessarily be faster overall than just taking one lock and calling it a day, but it’ll be much more concurrency-friendly. Just keep running the DELETE statement until no rows are left that match. This lets you nibble off deletes in faster, smaller chunks, all while avoiding ugly table locks.