Effiziente Pagination mit Sqlite
10.06.2023
Sqlite ist eine gigantisch praktische und häufig unterschätzte Datenbanklösung für die allermeisten Projekte, die keine "Millionen" Besucher haben.
Laut Webseite der Entwickler kommt die eingebettete Datenbank (natürlich immer abhängig von den Einstellungen mit WAL-Mode, Sync.., busy_timeout usw. und der Umsetzung im eigenen Code) mit mindestens 100.000 Besuchern pro Tag klar. Bei verschiedenen Projekten habe ich in letzter Zeit daher mal auf diese Lösung gesetzt und muss sagen: Bis jetzt mehr als einwandfrei...
Es gibt natürlich bei jeder Datenbank Eigenheiten, die zu beachten sind. Eine Sache, die für viele Datenbanken nicht "ohne" ist, ist effiziente Pagination. Der oft gesehene Versuch, einfach per ORDER BY mit LIMIT und OFFSET zu arbeiten funktioniert bei Tabellen mit vielen Zeilen nicht mehr performant.
Für einen Canvas basierten Timeline Kalender, den ich als Teil einer Vermietungslösung programmiert habe (der "fast-original" Stand ist auf github zu finden, ein paar Anpassungen macht man ja "immer" je nach Projekt: Timeline Kalender), brauchte ich heute in einem Golang Projekt eine effiziente Pagination mit Sqlite
Hier ein paar Code Schnipsel, wie ich das zunächst einmal gelöst habe, hier und da wird es noch optimiert werden:
Merker Variablen im Frontend, die den jeweils letzten "unteren" und letzten "oberen" id Wert speichern werden bei jedem Aufruf ans Backend geliefert, um Seiten nach oben und unten blättern zu können.
Nicht vergessen: Die entsprechenden indexes anlegen, sonst nützt die beste Strategie nicht viel. FÜr die bookings lohnt sich z.B. "CREATE INDEX if not exists "ix_from_to_bk" ON bookings (fr_dt, to_dt, prop_id);"
EXPLAIN QUERY PLAN
SELECT id, prop_id as 'resource_id', fr_dt as 'start', to_dt as 'end',
first_name || ' ' || family_name as 'name', checkout_time
FROM bookings WHERE
fr_dt <= '2023-06-27 11:00:00'
AND to_dt >= '2023-05-05 15:00:00'
AND confirmed > 0
AND prop_id >= 1
AND prop_id <= 25
ORDER BY fr_dt ASC;
Ergibt: "SEARCH TABLE bookings USING INDEX ix_from_to_bk". Manchmal kann man mit Reihenfolge und Klammern noch bessere Ergebnisse erzielen. Da ich nicht 8h täglich Datenbankabfragen schreibe, ist der Schritt dann nochmal trial and error...
Die "Resources", einige Spalten mit Kategorien für die Zuordnung im Timeline Kalender, werden hier seitenweise geladen. Die Funktion gibt an das Frontend ebenfalls zurück, ob es "darunter" und "darüber" noch Einträge in der Tabelle gibt. Damit kann sehr einfach ohne komplett alle Zeilen zählen zu müssen um ein Total zu bekommen, dem Benutzer eine entsprechende Rückmeldung über disable/enable von "Seite-vor" und "Seite-zurück" Buttons gegeben werden. Der Canvas wird nicht "überfordert" mit vielen Resourcen - er hat ja in allen Browsern eine maximale Größe und ein künstlicher Scrollbalken ist deutlich komplizierter umzusetzen, als eine Seitenweise Darstellung.
func (ps *PropertyService) GetResources(ctx context.Context, dir string, lastLower, lastUpper, lim int64) ([]*Resource, *PaginationResult, error) {
conn := ps.Db.Get(ctx)
if conn == nil {
return nil, nil, errors.New("GetResources: cannot get db connection")
}
defer ps.Db.Put(conn)
var props []*Resource
var from int64
var dbDir, sortStr string
if dir == "up" {
from = lastUpper
dbDir = ">"
sortStr = "ASC"
} else if dir == "down" {
from = lastLower
dbDir = "<"
sortStr = "DESC"
} else {
from = lastLower
dbDir = ">="
sortStr = "ASC"
}
// TODO: WHERE add active field, too - maybe user choice
err := sqlitex.ExecuteTransient(conn, fmt.Sprintf(`SELECT id, title as 'name', ref_id, status from properties
WHERE tp = 1 AND id %s ? ORDER BY id %s limit ?`, dbDir, sortStr), &sqlitex.ExecOptions{
Args: []interface{}{from, lim},
ResultFunc: func(stmt *sqlite.Stmt) error {
p := new(Resource)
p.Id = stmt.GetInt64("id")
p.Name = stmt.GetText("name")
p.RefId = stmt.GetText("ref_id")
p.Status = stmt.GetText("status")
props = append(props, p)
return nil
},
})
if err != nil {
return nil, nil, err
}
if dir == "down" {
sort.Slice(props, func(i, j int) bool {
return props[i].Id < props[j].Id
})
}
if len(props) > 0 {
lastLower = props[0].Id
lastUpper = props[len(props)-1].Id
}
hasLower := int64(0)
hasHigher := int64(0)
err = sqlitex.ExecuteTransient(conn, `SELECT (SELECT id FROM properties WHERE id < ? AND tp = 1 LIMIT 1) as 'low',
(SELECT id FROM properties WHERE id > ? AND tp = 1 LIMIT 1) as 'high'`, &sqlitex.ExecOptions{
Args: []interface{}{lastLower, lastUpper},
ResultFunc: func(stmt *sqlite.Stmt) error {
hasLower = stmt.GetInt64("low")
hasHigher = stmt.GetInt64("high")
return nil
},
})
if err != nil {
log.Println(err.Error())
}
return props, &PaginationResult{
HasLower: hasLower > 0,
HasHigher: hasHigher > 0,
LastLower: lastLower,
LastUpper: lastUpper,
}, nil
}
Die jeweils in jeder "Abfragerunde" gespeicherten low und high Werte werden auch benutzt, um die Daten zu laden, die dann den Resourcen zugeordnet im Kalender angezeigt werden.
func (bs *BookingService) Bookings(ctx context.Context, from, to string, lastLower, lastUpper int64) ([]*BookingForCalendar, error) {
conn := bs.Db.Get(ctx)
if conn == nil {
return nil, errors.New("BookingService Bookings: cannot get db connection")
}
defer bs.Db.Put(conn)
stmt := conn.Prep(`SELECT id, prop_id as 'resource_id', fr_dt as 'start', to_dt as 'end',
first_name || ' ' || family_name as 'name', checkout_time FROM bookings WHERE
fr_dt <= $to AND to_dt >= $from AND confirmed > 0 AND prop_id >= $min AND prop_id <= $max ORDER BY fr_dt ASC`)
stmt.SetText("$from", from+" 00:00:00")
stmt.SetText("$to", to+" 23:59:59")
stmt.SetInt64("$min", lastLower)
stmt.SetInt64("$max", lastUpper)
defer stmt.Reset()
// not very efficient. getting num rows though is potentially a scan all operation.
var x = make([]*BookingForCalendar, 0)
for {
hasRow, err := stmt.Step()
if err != nil {
return nil, err
} else if !hasRow {
return x, nil
} else {
x = append(x, &BookingForCalendar{
Id: stmt.GetInt64("id"),
Name: stmt.GetText("name"),
Start: stmt.GetText("start"),
End: stmt.GetText("end"),
ResourceId: stmt.GetInt64("resource_id"),
Checkout: stmt.GetText("checkout_time"),
})
}
}
}
Was ich in Zukunft noch überlege - wenn der Benutzer gerne Textbasiert sortieren möchte z.B. für die Referenz ID die nicht unbedingt eine Zahl sein muss, wäre ein ORDER BY mit CAST(order_column_mit_index_alphanum AS INTEGER), (oder_column_mit_index_alphanum) angebracht statt "nur" per id auto_increment Spalte.
Außerdem gibt es noch ein, zwei Stellen mit "Potential" - bei vielen Zeilen muss der Rückgabe Slice mit den BookingForCalendar structs oft erweitert werden. Möglicherweise besser wenn man viele Einträge erwartet, dort die Kapazität schon mitzugeben, damit möglichst nur 1x Memory Allocation statfindet. Ausserdem könnte ich natürlich statt der Transient Execution ohne "Merken" der Queries überall auf Prepared Statements umsatteln, dann entfällt die (kleine) Zeit des Parsens für Sqlite.