RE: Story->list() with 'story.category' parameter highly inefficient

This nested sub-select is indeed the culprit -- and since it
returns the
exact same result each time it is executed, I'm a little
surprised that
it's not being cached and / or running more quickly.  But
anyway, if
this expression can be pre-calculated and fed to the outer
query, the
performance problem will be solved.

		SELECT sc3.category__id 
		FROM story__category sc3, story s2, story_instance i2 
		WHERE  	i2.story__id = s2.id AND
			i2.version = s2.current_version AND 
			i2.checked_out = ( 
				SELECT checked_out 
				FROM story_instance 
				WHERE version = i2.version AND 
				story__id = s2.id AND 
				sc3.story_instance__id = i2.id AND 
				s2.id = $1 ORDER BY checked_out DESC
LIMIT 1 
			)

pg/plsql might be one way to go -- is it possible to specify
plsql
in-line without having to create a function or stored
procedure? 


-----Original Message-----
From: David E. Wheeler [mailto:davidkineticode.com] 
Sent: Friday, March 16, 2007 11:57 PM
To: userslists.bricolage.cc
Cc: devellists.bricolage.cc
Subject: Re: Story->list() with 'story.category'
parameter highly
inefficient

On Mar 16, 2007, at 11:06, Beaudet, David P. wrote:

> As far as a permanent fix, it might make sense to
either optimize the
> SQL generated by this parameter or change the code that
handles the
> 'story.category' parameter to loop through the list
like I'm doing  
> in my
> templates.

I'd be happy to see the query improved. Any ideas how it
should be  
rewritten?

Best,

David

On Mar 20, 2007, at 07:01, Beaudet, David P. wrote:

> This nested sub-select is indeed the culprit -- and
since it  
> returns the
> exact same result each time it is executed, I'm a
little surprised  
> that
> it's not being cached and / or running more quickly. 
But anyway, if
> this expression can be pre-calculated and fed to the
outer query, the
> performance problem will be solved.
>
> 		SELECT sc3.category__id
> 		FROM story__category sc3, story s2, story_instance
i2
> 		WHERE  	i2.story__id = s2.id AND
> 			i2.version = s2.current_version AND
> 			i2.checked_out = (
> 				SELECT checked_out
> 				FROM story_instance
> 				WHERE version = i2.version AND
> 				story__id = s2.id AND
> 				sc3.story_instance__id = i2.id AND
> 				s2.id = $1 ORDER BY checked_out DESC
> LIMIT 1
> 			)
>
> pg/plsql might be one way to go -- is it possible to
specify plsql
> in-line without having to create a function or stored
procedure?

No, it's not, but I'm sure that there's a way to calculate
it in an  
outer context to get it to work. I saw a presentation
recently on  
something similar, but that had to do with a PL/pgSQL
function  
itself, I think.

Anyone have any ideas?

Thanks,

David