Skip to content
HeliosDB HeliosDB Full

Prepared Statement Cache - Quick Reference

Prepared Statement Cache - Quick Reference

Quick Start (30 seconds)

use heliosdb_pooling::{IntelligentConnectionPool, Parameters, ParameterValue};


// 1. Create pool
let pool = IntelligentConnectionPool::new(config, PoolMode::Transaction)?;
pool.initialize().await?;


// 2. Prepare statement
let stmt = pool.prepare("SELECT * FROM users WHERE id = $1")?;


// 3. Execute with parameters
let params = Parameters::Positional(vec![ParameterValue::Int64(123)]);
pool.execute(&stmt, params).await?;


// 4. Check performance
let stats = pool.statement_cache_stats();
println!("Hit rate: {:.2}%", stats.hit_rate * 100.0);

Common Parameter Types

// Integers
ParameterValue::Int64(123)


// Strings
ParameterValue::String("text".to_string())


// Booleans
ParameterValue::Bool(true)


// Null
ParameterValue::Null


// Timestamps
ParameterValue::Timestamp(Utc::now())


// JSON
ParameterValue::Json(r#"{"key": "value"}"#.to_string())

Positional vs Named Parameters

// Positional ($1, $2, ...)
let stmt = pool.prepare("SELECT * FROM users WHERE id = $1 AND name = $2")?;
let params = Parameters::Positional(vec![
    ParameterValue::Int64(123),
    ParameterValue::String("Alice".to_string()),
]);


// Named (:name)
let stmt = pool.prepare("SELECT * FROM users WHERE id = :id AND name = :name")?;
let mut map = HashMap::new();
map.insert("id".to_string(), ParameterValue::Int64(123));
map.insert("name".to_string(), ParameterValue::String("Alice".to_string()));
let params = Parameters::Named(map);

Best Practices

DO

// Prepare once, execute many
let stmt = pool.prepare(sql)?;
for item in items {
    pool.execute(&stmt, item.params()).await?;
}


// Use parameters, not string interpolation
pool.prepare("SELECT * FROM users WHERE id = $1")?;


// Monitor cache performance
let stats = pool.statement_cache_stats();

❌ DON’T

// Don't prepare in a loop
for item in items {
    let stmt = pool.prepare(sql)?; // BAD!
    pool.execute(&stmt, params).await?;
}


// Don't use string interpolation
let sql = format!("SELECT * FROM users WHERE id = {}", id); // BAD!


// Don't ignore cache stats
// Always monitor hit rate

Monitoring

let stats = pool.statement_cache_stats();


// Key metrics
stats.hit_rate          // Should be > 0.80
stats.total_memory_bytes // Should be < 100MB
stats.current_size      // Number of cached statements
stats.total_evictions   // Should be low

Performance Expectations

OperationTimeNotes
Cache Hit~5-10μsFast lookup
Cache Miss~50-100μsIncludes parsing
Binding~1-5μsPer statement

Expected Hit Rates:

  • OLTP: 85-95%
  • Batch: 90-99%
  • Analytics: 60-80%

API Reference

// Prepare statement (cached automatically)
fn prepare(&self, sql: &str) -> PoolResult<PreparedStatement>


// Execute prepared statement
async fn execute(
    &self,
    statement: &PreparedStatement,
    params: Parameters
) -> PoolResult<()>


// Get cache statistics
fn statement_cache_stats(&self) -> CacheStatistics


// Clear cache
fn clear_statement_cache(&self)

Troubleshooting

Low hit rate?

  • Use consistent SQL formatting
  • Avoid dynamic SQL
  • Use parameters instead of string interpolation

High memory usage?

  • Check stats.current_size
  • Clear cache if needed
  • Reduce unique queries

Slow performance?

  • Check hit rate (should be >70%)
  • Profile execution times
  • Monitor evictions

Configuration

// Default: 10,000 statements, 100MB max
let pool = IntelligentConnectionPool::new(config, PoolMode::Transaction)?;


// Cache configured automatically:
// - Max size: 10,000 statements
// - Max memory: 100MB
// - LRU eviction when full

Common Patterns

Batch Insert

let stmt = pool.prepare("INSERT INTO logs VALUES ($1, $2)")?;
for log in logs {
    pool.execute(&stmt, log.params()).await?;
}

Update with Multiple Parameters

let stmt = pool.prepare(
    "UPDATE users SET name = $1, email = $2 WHERE id = $3"
)?;
let params = Parameters::Positional(vec![
    ParameterValue::String("Alice".to_string()),
    ParameterValue::String("alice@example.com".to_string()),
    ParameterValue::Int64(123),
]);
pool.execute(&stmt, params).await?;

Complex Query

let stmt = pool.prepare(r#"
    SELECT u.*, COUNT(o.id) as order_count
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE u.created_at > $1
    GROUP BY u.id
"#)?;

Performance Tips

  1. Pre-warm cache with common queries
  2. Prepare once at startup
  3. Monitor hit rate regularly
  4. Use parameters for all variable data
  5. Normalize SQL (consistent formatting)