Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Check if it's worth using a single MERGE operation for some modes of update_database_table #7036

Open
radeusgd opened this issue Jun 15, 2023 · 0 comments
Assignees
Labels
-libs Libraries: New libraries to be implemented l-db-write Libraries: database writer p-lowest Should be completed at some point s-research-needed Status: the task will require heavy research to complete

Comments

@radeusgd
Copy link
Member

Some DB engines provide a MERGE operation (Transact-SQL, Postgres) that can be used to do INSERT / UPDATE / DELETE operations in a single pass. This may be more efficient than the current implementation which does every of these 3 steps separately (so Update_Or_Insert relies on 2 update queries and Align_Tables relies on 3 (not counting the read-only queries used for checking initial invariants)).

However, not all DBs support this, so we will need logic that can still rely on the current implementation. But we can at least try to use MERGE on backends where it is available.

As part of this task, it could be worth to first measure (in Enso or elsewhere, even local DB or DB-fiddle) how much of a difference does MERGE do and if its even worth implementing such an optimization. I suspect it may prove to be a bit faster, but probably not by orders of magnitude, since the major efficiency concern for these updates is whether there is an index available for the key in question. We should compare the 2 scenarios and analyze the difference. Then we can implement the optimization for supporting backends.

@radeusgd radeusgd added p-lowest Should be completed at some point s-research-needed Status: the task will require heavy research to complete -libs Libraries: New libraries to be implemented l-db-write Libraries: database writer labels Jun 15, 2023
@jdunkerley jdunkerley moved this from ❓New to 📤 Backlog in Issues Board Jun 27, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
-libs Libraries: New libraries to be implemented l-db-write Libraries: database writer p-lowest Should be completed at some point s-research-needed Status: the task will require heavy research to complete
Projects
Status: 📤 Backlog
Development

No branches or pull requests

1 participant