Last active
October 8, 2019 15:28
-
-
Save zxdcm/ead998e79602082b4a0a55c03ed86ddf to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
GUIDs hurt performance of inserts. | |
Relational databases use B+ tree structure to store data. | |
If data coming in is not ordered, then a lot of IO work is required to rearrange the leafs. | |
GUIDs are random, so they may lead to heavy performance drawbacks, especially with large tables. | |
Database generated integers may hurt performance of inserts as well but for another reason. | |
When a database generates a new auto-incremented value for a primary key, it takes a lock to avoid race conditions. | |
It causes a performance issue that shows up in scenarios with multithread or bulk inserts. | |
IF EXISTS (SELECT * FROM dbo.Ids WHERE EntityId = @EntityId) | |
BEGIN | |
BEGIN TRAN | |
COMMIT TRAN | |
END | |
Heavy load systems leads to deadlock. | |
When a transaction selects, it obtains a shared lock. | |
After that it tries to upgrade the lock to exclusive to update the record. | |
If another transaction selects with the same entity Id after the first transaction ends selecting, | |
but before it starts the update, the deadlock will occur. | |
Two transactions will acquire the shared lock, and both of them will also try to acquire an exclusive lock, | |
waiting for each other to release the shared lock. | |
https://enterprisecraftsmanship.com/2014/11/15/cqs-with-database-generated-ids/ | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment