Radio: Slow Playlist Loading #33

New Issue

codey · 2025-07-01T09:42:16-04:00

codey commented

2025-07-01 09:42:16 -04:00

The time to process/load a playlist from SQLite currently increases exponentially with each additional row returned.

The artist/genre pairings and other track data are contained in separate databases.

Presently, get_genre is being called for each individual SQLite row returned by the playlist query.

Because the API does not maintain a persistent connection to the database for most/all operations, it opens the "file"/connects to the DB on each call.

An additional method (name TBD) is to be created for batch genre lookups. The method will accept as input a list of artists, and will return a dictionary comprised of the artist/genre pairs.

The time to process/load a playlist from SQLite currently increases exponentially with each additional row returned. The artist/genre pairings and other track data are contained in separate databases. Presently, `get_genre` is being called for each individual SQLite row returned by the playlist query. Because the API does not maintain a persistent connection to the database for most/all operations, it opens the "file"/connects to the DB on each call. An additional method (name TBD) is to be created for batch genre lookups. The method will accept as input a list of artists, and will return a dictionary comprised of the artist/genre pairs.

codey added the

Task

label 2025-07-01 09:42:16 -04:00

codey self-assigned this 2025-07-01 09:42:16 -04:00

codey added the due date 2025-07-07

2025-07-01 09:42:32 -04:00

codey commented

2025-07-01 09:46:19 -04:00

Offending code:

                self.active_playlist = [
                    {
                        "uuid": str(uuid().hex),
                        "id": r["id"],
                        "artist": double_space.sub(" ", r["artist"]).strip(),
                        "song": double_space.sub(" ", r["song"]).strip(),
                        "album": double_space.sub(" ", r["album"]).strip(),
                        "genre": self.get_genre(
                            double_space.sub(" ", r["artist"]).strip()
                        ),
                        "artistsong": double_space.sub(
                            " ", r["artistdashsong"]
                        ).strip(),
                        "file_path": r["file_path"],
                        "duration": r["duration"],
                    }
                    for r in results
                    if r not in self.active_playlist
                ]

def get_genre(self, artist: str) -> str:
        """
        Retrieve Genre for given Artist
        Args:
            artist (str): The artist to query
        Returns:
            str
        """
        try:
            artist = artist.strip()
            query: str = (
                "SELECT genre FROM artist_genre WHERE artist LIKE ? COLLATE NOCASE"
            )
            params: tuple[str] = (f"%%{artist}%%",)
            with sqlite3.connect(self.artist_genre_db_path, timeout=2) as _db:
                _db.row_factory = sqlite3.Row
                _cursor = _db.execute(query, params)
                res = _cursor.fetchone()
                if not res:
                    return "Not Found"  # Exception suppressed
                    # raise RadioException(
                    #     f"Could not locate {artist} in artist_genre_map db."
                    # )
                return res["genre"]
        except Exception as e:
            logging.info("Failed to look up genre for artist: %s (%s)", artist, str(e))
            traceback.print_exc()
            return "Not Found"

Offending code: ```python self.active_playlist = [ { "uuid": str(uuid().hex), "id": r["id"], "artist": double_space.sub(" ", r["artist"]).strip(), "song": double_space.sub(" ", r["song"]).strip(), "album": double_space.sub(" ", r["album"]).strip(), "genre": self.get_genre( double_space.sub(" ", r["artist"]).strip() ), "artistsong": double_space.sub( " ", r["artistdashsong"] ).strip(), "file_path": r["file_path"], "duration": r["duration"], } for r in results if r not in self.active_playlist ] ``` ```python def get_genre(self, artist: str) -> str: """ Retrieve Genre for given Artist Args: artist (str): The artist to query Returns: str """ try: artist = artist.strip() query: str = ( "SELECT genre FROM artist_genre WHERE artist LIKE ? COLLATE NOCASE" ) params: tuple[str] = (f"%%{artist}%%",) with sqlite3.connect(self.artist_genre_db_path, timeout=2) as _db: _db.row_factory = sqlite3.Row _cursor = _db.execute(query, params) res = _cursor.fetchone() if not res: return "Not Found" # Exception suppressed # raise RadioException( # f"Could not locate {artist} in artist_genre_map db." # ) return res["genre"] except Exception as e: logging.info("Failed to look up genre for artist: %s (%s)", artist, str(e)) traceback.print_exc() return "Not Found" ```

codey referenced this issue from a commit

2025-07-01 10:34:21 -04:00

small improvements re: #33

codey referenced this issue from a commit

2025-07-01 13:03:04 -04:00

misc/formatting ++ resolve #33 (restructured radio DBs, combined genre into track_file_map, revised query w/ INNER JOIN)

codey closed this issue

2025-07-01 13:03:04 -04:00

codey commented

2025-07-01 13:06:07 -04:00

Related: New query

self.RADIO_DB_QUERY: str = (
                'SELECT LOWER(TRIM(tracks_deduped.artist)) || " - " || LOWER(TRIM(tracks_deduped.song))'
                'AS artistdashsong, tracks_deduped.id, tracks_deduped.artist, tracks_deduped.song, tracks_deduped.album,'
                'tracks_deduped.file_path, tracks_deduped.duration, genre FROM tracks_deduped '
                'INNER JOIN artist_genre ON artist_genre.artist = tracks_deduped.artist'
            )

Related: New query ```sql self.RADIO_DB_QUERY: str = ( 'SELECT LOWER(TRIM(tracks_deduped.artist)) || " - " || LOWER(TRIM(tracks_deduped.song))' 'AS artistdashsong, tracks_deduped.id, tracks_deduped.artist, tracks_deduped.song, tracks_deduped.album,' 'tracks_deduped.file_path, tracks_deduped.duration, genre FROM tracks_deduped ' 'INNER JOIN artist_genre ON artist_genre.artist = tracks_deduped.artist' ) ```

Sign in to join this conversation.

1 Participants

Notifications

Due Date

2025-07-07

Dependencies

No dependencies set.

Reference: codey/api#33