commit 52dfc51052fd43c9e08fc1977a9f76bca3f6fc7c
parent 8dd22711e7f8d2332bfc74cbac6d59efdc541f8c
Author: Silas Brack <silasbrack@gmail.com>
Date: Sat, 13 Jun 2026 12:05:02 +0200
perf: use prepare_cached for all queries, HashMap for enrichment
prepare_cached reuses compiled SQL statements across requests on
the same thread-local connection, avoiding re-parsing 19 queries
per request. HashMap/HashSet for story enrichment replaces O(n²)
linear scans with O(1) lookups.
Story page: 60k → 95k rps (+57%)
Homepage: 21k → 24k rps (+16%)
Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
Diffstat:
2 files changed, 27 insertions(+), 21 deletions(-)
diff --git a/src/database.rs b/src/database.rs
@@ -194,7 +194,7 @@ impl Database {
pub fn get_stories_new(&self, limit: usize, offset: usize) -> Result<Vec<Story>, AppError> {
let conn = self.read_conn()?;
- let mut stmt = conn.prepare(
+ let mut stmt = conn.prepare_cached(
"SELECT * FROM story ORDER BY published DESC LIMIT ?1 OFFSET ?2"
).map_err(|e| AppError::Database(e.to_string()))?;
@@ -208,7 +208,7 @@ impl Database {
pub fn get_stories_top(&self, limit: usize, offset: usize) -> Result<Vec<Story>, AppError> {
let conn = self.read_conn()?;
- let mut stmt = conn.prepare(
+ let mut stmt = conn.prepare_cached(
"SELECT * FROM story ORDER BY score DESC, published DESC LIMIT ?1 OFFSET ?2"
).map_err(|e| AppError::Database(e.to_string()))?;
@@ -223,7 +223,7 @@ impl Database {
pub fn get_stories_hot(&self, limit: usize, offset: usize) -> Result<Vec<Story>, AppError> {
let fetch_limit = (limit + offset) * 2;
let conn = self.read_conn()?;
- let mut stmt = conn.prepare(
+ let mut stmt = conn.prepare_cached(
"SELECT * FROM story ORDER BY published DESC LIMIT ?1"
).map_err(|e| AppError::Database(e.to_string()))?;
@@ -244,7 +244,7 @@ impl Database {
pub fn get_story(&self, id: i64) -> Result<Option<Story>, AppError> {
let conn = self.read_conn()?;
- let mut stmt = conn.prepare("SELECT * FROM story WHERE id = ?1")
+ let mut stmt = conn.prepare_cached("SELECT * FROM story WHERE id = ?1")
.map_err(|e| AppError::Database(e.to_string()))?;
let mut rows = stmt.query_map(params![id], Self::row_to_story)
@@ -298,7 +298,7 @@ impl Database {
pub fn get_stories_by_tag(&self, category_id: i64, limit: usize, offset: usize) -> Result<Vec<Story>, AppError> {
let conn = self.read_conn()?;
- let mut stmt = conn.prepare(
+ let mut stmt = conn.prepare_cached(
"SELECT s.* FROM story s
JOIN story_category sc ON sc.news_item_id = s.id
WHERE sc.category_id = ?1
@@ -316,7 +316,7 @@ impl Database {
pub fn get_stories_by_user(&self, user_id: i64, limit: usize, offset: usize) -> Result<Vec<Story>, AppError> {
let conn = self.read_conn()?;
- let mut stmt = conn.prepare(
+ let mut stmt = conn.prepare_cached(
"SELECT * FROM story WHERE created_by = ?1 ORDER BY published DESC LIMIT ?2 OFFSET ?3"
).map_err(|e| AppError::Database(e.to_string()))?;
@@ -330,7 +330,7 @@ impl Database {
pub fn search_stories(&self, query: &str, limit: usize) -> Result<Vec<SearchResult>, AppError> {
let conn = self.read_conn()?;
- let mut stmt = conn.prepare(
+ let mut stmt = conn.prepare_cached(
"SELECT s.id, s.title, s.text, s.score, s.comment_count
FROM story_fts fts
JOIN story s ON s.id = fts.rowid
@@ -376,7 +376,7 @@ impl Database {
pub fn get_comments_for_story(&self, story_id: i64) -> Result<Vec<Comment>, AppError> {
let conn = self.read_conn()?;
- let mut stmt = conn.prepare(
+ let mut stmt = conn.prepare_cached(
"SELECT * FROM comment WHERE story_id = ?1 ORDER BY path"
).map_err(|e| AppError::Database(e.to_string()))?;
@@ -451,7 +451,7 @@ impl Database {
pub fn get_comment(&self, id: i64) -> Result<Option<Comment>, AppError> {
let conn = self.read_conn()?;
- let mut stmt = conn.prepare("SELECT * FROM comment WHERE id = ?1")
+ let mut stmt = conn.prepare_cached("SELECT * FROM comment WHERE id = ?1")
.map_err(|e| AppError::Database(e.to_string()))?;
let mut rows = stmt.query_map(params![id], Self::row_to_comment)
@@ -466,7 +466,7 @@ impl Database {
pub fn get_comments_by_user(&self, user_id: i64, limit: usize, offset: usize) -> Result<Vec<Comment>, AppError> {
let conn = self.read_conn()?;
- let mut stmt = conn.prepare(
+ let mut stmt = conn.prepare_cached(
"SELECT * FROM comment WHERE created_by = ?1 ORDER BY created_at DESC LIMIT ?2 OFFSET ?3"
).map_err(|e| AppError::Database(e.to_string()))?;
@@ -522,7 +522,7 @@ impl Database {
placeholders.join(", ")
);
- let mut stmt = conn.prepare(&sql).map_err(|e| AppError::Database(e.to_string()))?;
+ let mut stmt = conn.prepare_cached(&sql).map_err(|e| AppError::Database(e.to_string()))?;
// Build params
let mut param_values: Vec<Box<dyn rusqlite::types::ToSql>> = Vec::new();
@@ -547,7 +547,7 @@ impl Database {
pub fn get_categories(&self, limit: usize) -> Result<Vec<Category>, AppError> {
let conn = self.read_conn()?;
- let mut stmt = conn.prepare("SELECT id, name FROM category LIMIT ?1")
+ let mut stmt = conn.prepare_cached("SELECT id, name FROM category LIMIT ?1")
.map_err(|e| AppError::Database(e.to_string()))?;
let categories = stmt.query_map(params![limit as i64], |row| {
@@ -565,7 +565,7 @@ impl Database {
pub fn get_categories_for_story(&self, story_id: i64) -> Result<Vec<Category>, AppError> {
let conn = self.read_conn()?;
- let mut stmt = conn.prepare(
+ let mut stmt = conn.prepare_cached(
"SELECT c.id, c.name FROM category c
JOIN story_category sc ON sc.category_id = c.id
WHERE sc.news_item_id = ?1"
@@ -623,7 +623,7 @@ impl Database {
pub fn get_user_by_id(&self, id: i64) -> Result<Option<User>, AppError> {
let conn = self.read_conn()?;
- let mut stmt = conn.prepare("SELECT * FROM user WHERE id = ?1")
+ let mut stmt = conn.prepare_cached("SELECT * FROM user WHERE id = ?1")
.map_err(|e| AppError::Database(e.to_string()))?;
let mut rows = stmt.query_map(params![id], Self::row_to_user)
@@ -638,7 +638,7 @@ impl Database {
pub fn get_user_by_email(&self, email: &str) -> Result<Option<User>, AppError> {
let conn = self.read_conn()?;
- let mut stmt = conn.prepare("SELECT * FROM user WHERE email = ?1")
+ let mut stmt = conn.prepare_cached("SELECT * FROM user WHERE email = ?1")
.map_err(|e| AppError::Database(e.to_string()))?;
let mut rows = stmt.query_map(params![email], Self::row_to_user)
@@ -653,7 +653,7 @@ impl Database {
pub fn get_user_by_username(&self, username: &str) -> Result<Option<User>, AppError> {
let conn = self.read_conn()?;
- let mut stmt = conn.prepare("SELECT * FROM user WHERE username = ?1")
+ let mut stmt = conn.prepare_cached("SELECT * FROM user WHERE username = ?1")
.map_err(|e| AppError::Database(e.to_string()))?;
let mut rows = stmt.query_map(params![username], Self::row_to_user)
@@ -716,7 +716,7 @@ impl Database {
placeholders.join(", ")
);
- let mut stmt = conn.prepare(&sql).map_err(|e| AppError::Database(e.to_string()))?;
+ let mut stmt = conn.prepare_cached(&sql).map_err(|e| AppError::Database(e.to_string()))?;
let mut param_values: Vec<Box<dyn rusqlite::types::ToSql>> = Vec::new();
for id in story_ids {
param_values.push(Box::new(*id));
@@ -739,7 +739,7 @@ impl Database {
placeholders.join(", ")
);
- let mut tag_stmt = conn.prepare(&tag_sql).map_err(|e| AppError::Database(e.to_string()))?;
+ let mut tag_stmt = conn.prepare_cached(&tag_sql).map_err(|e| AppError::Database(e.to_string()))?;
let mut tags_map: HashMap<i64, Vec<String>> = HashMap::new();
let tag_rows = tag_stmt
@@ -778,7 +778,7 @@ impl Database {
placeholders.join(", ")
);
- let mut stmt = conn.prepare(&sql).map_err(|e| AppError::Database(e.to_string()))?;
+ let mut stmt = conn.prepare_cached(&sql).map_err(|e| AppError::Database(e.to_string()))?;
let mut param_values: Vec<Box<dyn rusqlite::types::ToSql>> = Vec::new();
for id in user_ids {
param_values.push(Box::new(*id));
diff --git a/src/handlers/feed.rs b/src/handlers/feed.rs
@@ -75,18 +75,24 @@ async fn fetch_feed(
let reached_end = stories.len() <= PAGE_LIMIT;
+ // O(1) lookups instead of O(n) scans
+ let meta_map: std::collections::HashMap<i64, &database::StoryMeta> =
+ meta.iter().map(|m| (m.story_id, m)).collect();
+ let voted_set: std::collections::HashSet<i64> =
+ voted_ids.into_iter().collect();
+
let mut enriched_stories = Vec::with_capacity(stories.len());
let base_rank = page * PAGE_LIMIT;
for (idx, story) in stories.into_iter().enumerate() {
- let story_meta = meta.iter().find(|m| m.story_id == story.id);
+ let story_meta = meta_map.get(&story.id);
let tags: Vec<Category> = story_meta
.map(|m| m.tags.iter().map(|name| Category { id: 0, name: Some(name.clone()) }).collect())
.unwrap_or_default();
let author_username = story_meta.and_then(|m| m.author_username.clone());
let time_ago = database::time_ago(&story.published);
- let user_voted = voted_ids.contains(&story.id);
+ let user_voted = voted_set.contains(&story.id);
let rank = base_rank + idx + 1;
enriched_stories.push(StoryWithMeta::from_story(
