[Cl-perec-devel] Optimization question

[Levente Mészáros]

I suggest to build a query form dynamically and execute that. Query
cache will prevent calling the query compiler each time and you can
build free queries with this.

TEST> (defpclass* foo ()
        ((s :type boolean)))
#<PERSISTENT-CLASS FOO>

TEST> (with-transaction
        (make-instance 'foo :s t))

; BEGIN
; SELECT NEXTVAL('_instance_id')
; $1 = 475775 as BIGINT, $2 = TRUE as BOOL
; INSERT INTO _foo (_oid, _s) VALUES ($1::BIGINT, $2::BOOL)
; COMMIT
#<FOO :persistent #t 7>

TEST> (with-transaction (execute-query (make-query '(select-instances
(o) (where (typep o 'foo))))))
; BEGIN
; SELECT _o._oid, _o._s FROM _foo_ap _o
; COMMIT
(#<FOO :persistent #t 4> #<FOO :persistent #t 5> #<FOO :persistent #t 6>
 #<FOO :persistent #t 7>)

Be sure to bind this variable high enough:

TEST> hu.dwim.perec::*compiled-query-cache*
#<HASH-TABLE :TEST EQUAL :COUNT 2 {10084C4BB1}>

levy

-- 
There's no perfectoin

On Fri, Oct 9, 2009 at 3:00 PM, Mihai Bazon <mihai at bazon.net> wrote:
> Hi folks,
>
> Here are two variants, I'd say equivalent, to select some objects:
>
>   (select-instances (o) (where (typep o 'db-group)))
>
>   (defvar tmp 'db-user)
>   (select-instances (o) (where (typep o tmp)))
>
> Both work the same, but the first one is better because it results in a
> single select that fetches the data as well.  Here's what I see using
> start-sql-recording:
>
> 1st version:
>
> ; SELECT _o._oid, _o._edition, _o._name, _o._description FROM _db_group_ap _o
>
> 2nd version:
>
> ; SELECT _o._oid FROM _db_group_ai _o
> ; $1 = 69012 as BIGINT
> ; SELECT _edition, _name, _description FROM _db_group_dd WHERE (_oid = $1::BIGINT)
> ; $1 = 134548 as BIGINT
> ; SELECT _edition, _name, _description FROM _db_group_dd WHERE (_oid = $1::BIGINT)
> ; $1 = 200084 as BIGINT
> ; SELECT _edition, _name, _description FROM _db_group_dd WHERE (_oid = $1::BIGINT)
>
> I presume there's some compile-time magic that happens in case 1 and not
> in case 2.  I can't hard-code the type so I'd like to use the second
> variant, but is it possible to optimize it so that it results in a
> single select like the first one?
>
> Thanks for any hints!
> -Mihai
> _______________________________________________
> Cl-perec-devel mailing list
> Cl-perec-devel at common-lisp.net
> http://common-lisp.net/cgi-bin/mailman/listinfo/cl-perec-devel
>