Hi,
Danny Milosavljevic <dan...@scratchpost.org> skribis:
> On Sun, 11 Nov 2018 18:06:00 +0100
> l...@gnu.org (Ludovic Courtès) wrote:
>
>> I don’t really know what additional index to create (and I’d rather let
>> SQLite do it for me, if it were possible).
>
> Not exactly what you mean but there's this:
>
> https://www.sqlite.org/lang_analyze.html
>
> It does statistical analysis of queries that ran and will optimize for
> that case on subsequent connections.
That’s close to what I was hoping for. We could do “PRAGMA optimize”
before closing the database session as they suggest:
diff --git a/src/cuirass/database.scm b/src/cuirass/database.scm
index 8b83c18..fb037d1 100644
--- a/src/cuirass/database.scm
+++ b/src/cuirass/database.scm
@@ -403,7 +403,9 @@ a critical section that allows database operations to be serialized."
;; be costly and may defeat statement caching.
(parameterize ((%db-channel (make-critical-section db)))
body ...)
- (db-close db))))
+ (begin
+ (sqlite-exec db "PRAGMA optimize;")
+ (db-close db)))))
(define* (read-quoted-string #:optional (port (current-input-port)))
"Read all of the characters out of PORT and return them as a SQL quoted
… though I think we never really close it properly because Cuirass runs
“forever.”
I tried doing this in a Guile session on berlin:
--8<---------------cut here---------------start------------->8---
scheme@(guile-user)> ,use(cuirass database)
scheme@(guile-user)> (%package-database "/var/lib/cuirass/cuirass.db")
$6 =
"/gnu/store/g1q2lv75a2fibii4y52fndz5zpbmyl12-cuirass-0.0.1-21.0b40dca/var/lib/cuirass/cuirass.db"
scheme@(guile-user)> (with-database (db-get-builds `((nr . 200)(order .
finish-time)(status . done))) (with-db-critical-section db (sqlite-exec db
"PRAGMA optimize;")))
$7 = ()
scheme@(guile-user)> (with-database (db-get-builds `((nr . 200)(order .
finish-time)(status . done))) (with-db-critical-section db (sqlite-exec db
"PRAGMA optimize;")))
$8 = ()
scheme@(guile-user)> ,time (with-database (db-get-builds `((nr . 200)(order .
finish-time)(status . done))) (with-db-critical-section db (sqlite-exec db
"PRAGMA optimize;")))
$9 = ()
;; 13.215291s real time, 13.229189s run time. 0.016093s spent in GC.
scheme@(guile-user)> ,time (with-database (db-get-builds `((nr . 200)(order .
finish-time)(status . done))) (with-db-critical-section db (sqlite-exec db
"PRAGMA optimize;")))
$10 = ()
;; 13.204621s real time, 13.230655s run time. 0.029333s spent in GC.
--8<---------------cut here---------------end--------------->8---
It doesn’t seem to help much, perhaps because the query is too complex?
Thoughts?
Ludo’.
