Add several indexes to share database, avoid using HasFlag() in other queries#1589
Merged
Conversation
Contributor
There was a problem hiding this comment.
Pull request overview
This PR optimizes database query performance in SQLite by eliminating bitwise operations (HasFlag) that prevent index utilization. It introduces TransferStateCategories to group transfer state combinations and updates pruning/removal logic to use bulk operations instead of individual updates.
- Introduces
TransferStateCategoriesstatic class with predefined HashSets of state combinations for optimized querying - Refactors
Prune()andRemove()methods to useExecuteUpdate()for bulk operations and accept multiple states - Adds indexes to the shares database for timestamp-based queries
Reviewed changes
Copilot reviewed 6 out of 6 changed files in this pull request and generated 8 comments.
Show a summary per file
| File | Description |
|---|---|
| src/slskd/Transfers/Types/TransferStateCategories.cs | New file defining HashSets of transfer state combinations to enable IN clause queries instead of bitwise operations |
| src/slskd/Transfers/Uploads/UploadService.cs | Updates Prune and Remove methods to accept state arrays, use ExecuteUpdate for bulk operations, and changes Remove to return bool/int |
| src/slskd/Transfers/Downloads/DownloadService.cs | Similar changes as UploadService, plus updates AddOrSupersede to use TransferStateCategories |
| src/slskd/Transfers/API/Controllers/TransfersController.cs | Updates clear completed endpoints to use new Remove overload with TransferStateCategories |
| src/slskd/Shares/SqliteShareRepository.cs | Adds three timestamp-based indexes for better query performance |
| src/slskd/Application.cs | Updates pruning calls to cast state combinations to int for new method signature |
💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.
Co-authored-by: Copilot <175728472+Copilot@users.noreply.github.com>
Co-authored-by: Copilot <175728472+Copilot@users.noreply.github.com>
This file contains hidden or 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
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
SQLite can't leverage indexes with bitwise operations, which is what
HasFlagdoes under the hood when Entity Framework translates it to SQL. This PR introducesTransferStateCategoriesthat group combinations ofTransferStatesflags into logical categories. These categories can then be used in place ofHasFlag, which will result in anINclause instead of a bitwise operation.This is mostly in preparation for some additional indexes on the transfers database.
I've also updated the transfer pruning logic so that only one
UPDATEstatement is issued, instead of one per transfer. Not sure why it was like that but it was very slow!