Labels Milestones

New Issue

Migrate to a direct database interface. #8

Closed

opened 5 years ago by sander · 1 comments

sander commented 5 years ago

Owner

Experimentation with SQLite has yielded that when I increased the amount of entries in the database, song queries now take up to 60 seconds to complete. That is unacceptably slow, especially since the database will only grow more.

However, executing the same query directly in SQLite yielded much faster results.

Some Google research has shown that Sequelize is the bottleneck here, because it is de-duplicating results on the client side. Workarounds offered are not possible in this project because of nested sub-queries. More info:
https://github.com/sequelize/sequelize/issues/4868

This is not the only problem with sequelize: this project is also haunted by a problem with offsets and limits in the query handler.

Based on this experience it would be better to have a more direct database interface so that we have more control over these things.

For example: Knex.js (http://knexjs.org/), which allows abstraction from SQL dialects but without creating a relational model on top.

Experimentation with SQLite has yielded that when I increased the amount of entries in the database, song queries now take up to 60 seconds to complete. That is unacceptably slow, especially since the database will only grow more. However, executing the same query directly in SQLite yielded much faster results. Some Google research has shown that Sequelize is the bottleneck here, because it is de-duplicating results on the client side. Workarounds offered are not possible in this project because of nested sub-queries. More info: https://github.com/sequelize/sequelize/issues/4868 This is not the only problem with sequelize: this project is also haunted by a problem with offsets and limits in the query handler. Based on this experience it would be better to have a more direct database interface so that we have more control over these things. For example: Knex.js (http://knexjs.org/), which allows abstraction from SQL dialects but without creating a relational model on top.

sander added this to the Core project 5 years ago

sander referenced this issue 5 years ago

Database schema management #9

sander added a new dependency 5 years ago

#9 Database schema management

sander added a new dependency 5 years ago

#10 Move from Sequelize to Knex.js.

sander commented 5 years ago

Poster

Owner

Work has been merged.

Work has been merged.

sander closed this issue 5 years ago

sander removed this from the Core project 5 years ago

Sign in to join this conversation.

No Branch/Tag Specified

Branches Tags

cypress

editsong

expand_detailspages

manage_cleardb

master

statistics_page

ytm_integration

Labels

Apply labels

Clear labels

backend
Work on the back-end. bug
Something is not working duplicate
This issue or pull request already exists enhancement
New feature frontend
Work on the front-end. invalid
Something is wrong postgres question
More information is needed sqlite3 wontfix
This won't be fixed

No Label backend bug duplicate enhancement frontend invalid postgres question sqlite3 wontfix

Milestone

Set milestone

Clear milestone

No items

No Milestone

Projects

Clear projects

No project

Assignees

Assign users

Clear assignees

No Assignees

1 Participants

Notifications

Subscribe

Due Date

The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Blocks

#9 Database schema management

sander/MuDBase

Depends on

#10 Move from Sequelize to Knex.js.

sander/MuDBase

Reference: sander/MuDBase#8

Write Preview

Loading…

Cancel

Save

There is no content yet.