[Cl-perec-devel] Optimization question

[Levente Mészáros]

I think I wasn't clear enough, obviously you can use `, in place of '

(let ((class-name 'foo)) ;; it's not hardcoded
  (execute-query (make-query `(select-instances (o) (where (typep o
,class-name)))))

Also if you look at the generated SQL it does select prefetched slots
too. This code fragment calls the query compiler runtime unless the
exact same query is already compiled. In that case it looks up the
resulting lambda from the cache.

Cheers,
levy

2009/10/9 Mihai Bazon <mihai at bazon.net>:
> 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
>> >



-- 
There's no perfectoin