[TriLUG] Any SQL experts out there?
Andrew Perrin
clists at perrin.socsci.unc.edu
Sat Feb 26 14:41:54 EST 2005
Thanks - I'll work on it. As it happens for thsi case I only need to run
the query a few times, so performance isn't a problem, but the point is
well taken.
ap
----------------------------------------------------------------------
Andrew J Perrin - http://www.unc.edu/~aperrin
Assistant Professor of Sociology, U of North Carolina, Chapel Hill
clists at perrin.socsci.unc.edu * andrew_perrin (at) unc.edu
On Fri, 25 Feb 2005, Tom Bryan wrote:
> On Friday 25 February 2005 07:46 pm, Andrew Perrin wrote:
> > Actually it does work slightly modified - here's the query that works for
> > my application:
> >
> > leted=# Select s.partid, s.zip, c.coder, c.ca_date, c.tone_angry
> > leted-# from participants s,code_assignments c
> > leted-# where c.ca_partid = s.partid
> > leted-# and c.ca_date = (select min(c2.ca_date) from code_assignments c2
> > where c.ca_partid=c2.ca_partid)
> > leted-# ;
>
> I just thought that I'd point out that your query has a nested subquery (the
> select on the right of that equals in your where clause). I'm not sure how
> postgreSQL actually handles this query, but I know that Oracle executes this
> type of query by running the correlated subquery for each row in the
> code_assignments (c) table. For large tables (millions of rows), performance
> of this type of query can really suck.
>
> I would normally try to replace the correlated subquery with a view or an
> inline view. So, for example (untested code follows)
>
> select
> s.partid, s.zip, c.coder, c.ca_date, c.tone_angry
> from
> participants s,
> code_assignments c,
> (select min(c2.ca_date) min_date, c2.ca_partid
> from
> code_assignments c2
> group by
> c2.ca_partid) d
> where d.ca_partid = c.ca_partid
> and c.ca_partid = s.partid
> and c.ca_date = d.min_date
>
> Since the nested select appears here in the from clause, it can be executed
> once instead of once per row in the other table. I'm pretty sure that
> postgreSQL supports this type of subquery. If not, you can create a view and
> use that view for the "d" table in this query. In either case, a query
> structured in this way is much easier for the RDBMS to optimize.
>
> ---Tom
>
More information about the TriLUG
mailing list