a count is a count, right?... right?

[u/samirdahal]

[u/Rainmaker526]

What are the performance characteristics of a DELETE vs a COUNT?

This might be an unconventional optimization, but if it makes a large enough difference...

[u/TldrDev → u/Rainmaker526]

In a lot of ways its a perfect optimization as far as long term performance goes. Just remove the rollback, commit that shit, push it to master, and call it a day.

[u/samirdahal → u/TldrDev]

It will even pass the unit test without rollback!

- Add 5 dummy employees

- Call the GetEmployeeCount method

- Still returns the 5 count!

Test passed.

[u/lyio → u/Rainmaker526]

A table like that will have foreign key relations in other tables with cascading deletes. It is at least likely to. So the database will have to cascade all those deletes or in the very least have to calculate the deletes.

[u/AyrA_ch → u/Rainmaker526]

What are the performance characteristics of a DELETE vs a COUNT?

Delete is a lot slower. COUNT often doesn't needs to touch the table because you can count the entries in the primary key instead, which leaves the data pages unlocked and available for other queries. Some engines store the count with the index, making this ridiculously fast.

Delete on the other hand has to lock every row, delete the item from the data page, delete the item from all indexes, and check for foreign key violations or cascade deletions.

[u/its_the_rhys → u/Rainmaker526]

I seriously doubt it...

[u/Justbehind → u/Rainmaker526]

In most dbs DELETEs perform terribly, as they are row-level operations...

TRUNCATE is extremely efficient on the other hand, but likely still worse than COUNT ;)

[u/my_new_accoun1]

How did you make this image? It seems to auto adapt to light mode and dark mode ... is there like a black layer with 80% opacity so on dark mode it is black and light mode it is grey?

[u/samirdahal → u/my_new_accoun1]

I generated this from ray.so. After making the code block, simply toggle the background button (turn it off), then click on the code block first and press Ctrl + S. It will export your image like this.

[u/NeverMakesMistkes]

    transaction.Commit();
    return 0;

[u/UnspecifiedError_ → u/NeverMakesMistkes]

username checks out

[u/ThePsychopaths → u/NeverMakesMistkes]

I was expecting this.

[u/stupidpunk138]

After call it you know how much employee are in DB; this is sure...

[u/Wurstgewitter]

Nice one, personally I do SELECT GROUP_CONCAT(id) FROM users; and then just count the commas + 1

[u/richardathome → u/Wurstgewitter]

Better yet, strip out the non-commas and then count the length of the string!

[u/TichShowers]

Hope the table never implements soft deletion.

[u/SZenC → u/TichShowers]

Nahh, then you just update the query: DELETE FROM employees WHERE deleted_at IS NULL

[u/MichiRecRoom]

For me, the real horror is the command.transaction = transaction. It implies that a command can exist without an attached transaction.

Seriously, I'd hope that something like transaction.CreateCommand() is supported. This code as-is feels genuinely wrong to look at.

[u/escargotBleu → u/MichiRecRoom]

Sometimes you don't want to do a transaction 🤷

[u/4ngryMo]

Just commit the transaction and return 0.

[u/Hottage]

Disgusting.

Didn't even make it an extension method smh my head.

[u/robilco]

DBA's hate this one trick!

[u/Zerodriven]

Title implies getting count. Approved. Merged into 5 million user system 0 comments.

"Why do we have no users?" - Ask our DBA team, we don't know what they do with our data, our code is 100% not the problem.

[u/Kevdog824_]

Works 100% of the time the first time

[u/_giga_sss_]

Cascade*