[Cl-perec-devel] Optimization question

[Mihai Bazon]

You hard-coded the type, which is what I want to avoid.  That works,
but consider the following:

        (defvar tmp 'foo)  ;*** the type is in a variable
        (with-transaction
           (execute-query
            (make-query '(select-instances
                          (o) (where (typep o tmp)))))))

It results in:

; BEGIN
; SELECT _o._oid FROM _foo_ai _o
; COMMIT

(so it doesn't select all data, only the OID; the result is a list of
objects, but individual SELECT-s will run for each of them to retrieve
the value of 's when it's needed).

BTW: I'm setting compiled-query-cache like this:

   (let ((prc::*compiled-query-cache* (site-query-cache ,site)) ...

where site-query-cache is an accessor for the following slot:

   (query-cache :accessor site-query-cache
                :initform (prc::make-compiled-query-cache))

It seems to work, and definitely improves performance (about 2x
faster).  Hope it's a clean way...

Tomorrow I'll try to understand how to use the macro that Attila
suggested--again, sorry for I'm trying to learn too much in too little
time...

Cheers,
-Mihai

Levente Mészáros <levente.meszaros at gmail.com> wrote:
> 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
> >