Re: [racket-users] Why are SQLITE queries so slow?
Le jeudi 26 mars 2015 19:37:19 UTC+1, Jens Axel Søgaard a écrit : > Did you subtract the startup cost of Racket to get the time of the queries? > > /Jens Axel I subtracted the two timings, which should get rid of everything but the query time, and gives about 30s for raket vs. about 5s for perl. -- You received this message because you are subscribed to the Google Groups "Racket Users" group. To unsubscribe from this group and stop receiving emails from it, send an email to racket-users+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [racket-users] Why are SQLITE queries so slow?
Le jeudi 26 mars 2015 19:21:11 UTC+1, Jens Axel Søgaard a écrit :
> How did you time the queries?
>
> /Jens Axel
>
>
> 2015-03-25 15:03 GMT+01:00 Renaud :
> > Hi,
> >
> > I'm new to Racket, and i would like to know why sqlite queries are so slow
> > in my test program.
> >
> > This program imports some data from a text file into a simple sqlite DB. It
> > takes 35s with the INSERT queries and 5-6s without them.
> >
> > I've done the same thing with other languages, all do far better. For
> > example with perl the same code runs in 8s with the queries, and 2-3s with
> > them.
> >
> > I know that startup and regex are (reasonably ?) slower in racket, but
> > queries seems to be very slow.
> >
> > Am i doing somethin wrong?
> >
> > Thanks for your help.
> >
> >
> > test done by swapping the commented line with the next. Code :
> > -
> > #!/usr/bin/env racket
> >
> > #lang racket/base
> > (require racket/list racket/string db/base db/sqlite3)
> >
> > (define DBFILE "database.rkt.sql")
> > (define rx #rx"[~^]+")
> > (define sdb #f)
> >
> > (define (openDatas)
> > (unless sdb (set! sdb (sqlite3-connect #:database DBFILE
> >
> > (define (importDatas)
> > (when (file-exists? DBFILE) (delete-file DBFILE))
> > (set! sdb (sqlite3-connect #:database DBFILE #:mode 'create))
> > (start-transaction sdb)
> > (query-exec sdb "CREATE TABLE groups (fam INTEGER, name STRING ,
> > PRIMARY KEY (fam))")
> > (query-exec sdb "CREATE TABLE foods (alim INTEGER, fam INTEGER, name
> > STRING , PRIMARY KEY (alim))")
> > (query-exec sdb "CREATE TABLE nutrients (nutr INTEGER, unit STRING,
> > short STRING, name STRING , PRIMARY KEY (nutr))")
> > (query-exec sdb "CREATE TABLE contents (alim INTEGER, nutr INTEGER, val
> > FLOAT , PRIMARY KEY (alim, nutr))")
> > (process-file "FD_GROUP.txt" "groups"2)
> > (process-file "FOOD_DES.txt" "foods" 3)
> > (process-file "NUTR_DEF.txt" "nutrients" 4)
> > (process-file "NUT_DATA.txt" "contents" 3)
> > (commit-transaction sdb))
> >
> > (define (process-file fname table nb)
> > (define Q (prepare sdb (string-append "INSERT INTO " table " VALUES ("
> > (string-join (for/list ([x nb]) "?") ",") ")")))
> > (call-with-input-file fname (lambda (file)
> > (for ([line (in-port read-line file)])
> > ;(bind-prepared-statement Q (take (regexp-split rx line 1)
> > nb))
> > (query-exec sdb (bind-prepared-statement Q (take (regexp-split
> > rx line 1) nb)))
> >
> >
> > (importDatas)
> > -
> --
> Jens Axel Søgaard
Hi Jens Axel,
I just used "time ./my-script.rkt" with the given code, then after swapping the
comment near the bottom :
;(bind-prepared-statement Q (take (regexp-split rx line 1) nb))
(query-exec sdb (bind-prepared-statement Q (take (regexp-split rx
line 1) nb)))
vs.
(bind-prepared-statement Q (take (regexp-split rx line 1) nb))
;(query-exec sdb (bind-prepared-statement Q (take (regexp-split rx
line 1) nb)))
Crude... i know...
--
You received this message because you are subscribed to the Google Groups
"Racket Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to racket-users+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: [racket-users] Re: Why are SQLITE queries so slow?
You're all forgiven since, 1/ there's nothing to forgive, and 2/ you're of great help with such precise and thorough answers. I still don't know how to speed up my code, but i've learnt a lot. Big thanks to you George, again. -- You received this message because you are subscribed to the Google Groups "Racket Users" group. To unsubscribe from this group and stop receiving emails from it, send an email to racket-users+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[racket-users] Re: Why are SQLITE queries so slow?
Thanks a lot George, for this insightful reply. Your idea about FFI explaining 95% of the overhead looks good... but i wonder: in this case, shouldn't we also see a high cost in bind-prepared-statement alone? (sorry for my sketchy english) Thanks again, Renaud -- You received this message because you are subscribed to the Google Groups "Racket Users" group. To unsubscribe from this group and stop receiving emails from it, send an email to racket-users+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[racket-users] Why are SQLITE queries so slow?
Hi,
I'm new to Racket, and i would like to know why sqlite queries are so slow in
my test program.
This program imports some data from a text file into a simple sqlite DB. It
takes 35s with the INSERT queries and 5-6s without them.
I've done the same thing with other languages, all do far better. For example
with perl the same code runs in 8s with the queries, and 2-3s with them.
I know that startup and regex are (reasonably ?) slower in racket, but queries
seems to be very slow.
Am i doing somethin wrong?
Thanks for your help.
test done by swapping the commented line with the next. Code :
-
#!/usr/bin/env racket
#lang racket/base
(require racket/list racket/string db/base db/sqlite3)
(define DBFILE "database.rkt.sql")
(define rx #rx"[~^]+")
(define sdb #f)
(define (openDatas)
(unless sdb (set! sdb (sqlite3-connect #:database DBFILE
(define (importDatas)
(when (file-exists? DBFILE) (delete-file DBFILE))
(set! sdb (sqlite3-connect #:database DBFILE #:mode 'create))
(start-transaction sdb)
(query-exec sdb "CREATE TABLE groups (fam INTEGER, name STRING , PRIMARY
KEY (fam))")
(query-exec sdb "CREATE TABLE foods (alim INTEGER, fam INTEGER, name STRING
, PRIMARY KEY (alim))")
(query-exec sdb "CREATE TABLE nutrients (nutr INTEGER, unit STRING, short
STRING, name STRING , PRIMARY KEY (nutr))")
(query-exec sdb "CREATE TABLE contents (alim INTEGER, nutr INTEGER, val
FLOAT , PRIMARY KEY (alim, nutr))")
(process-file "FD_GROUP.txt" "groups"2)
(process-file "FOOD_DES.txt" "foods" 3)
(process-file "NUTR_DEF.txt" "nutrients" 4)
(process-file "NUT_DATA.txt" "contents" 3)
(commit-transaction sdb))
(define (process-file fname table nb)
(define Q (prepare sdb (string-append "INSERT INTO " table " VALUES ("
(string-join (for/list ([x nb]) "?") ",") ")")))
(call-with-input-file fname (lambda (file)
(for ([line (in-port read-line file)])
;(bind-prepared-statement Q (take (regexp-split rx line 1) nb))
(query-exec sdb (bind-prepared-statement Q (take (regexp-split rx
line 1) nb)))
(importDatas)
-
--
You received this message because you are subscribed to the Google Groups
"Racket Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to racket-users+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
