[Dbix-class] SELECT from multiple tables

Matt S Trout dbix-class at trout.me.uk
Sat May 3 23:21:35 BST 2008


On Mon, Apr 28, 2008 at 03:15:42PM +0200, Emmanuel Quevillon wrote:
> Matt S Trout wrote:
> >On Fri, Apr 18, 2008 at 11:57:30AM +0200, Emmanuel Quevillon wrote:
> >>Hi,
> >>
> >>I am a big fan of DBIx::Class, I think that is a great tool.
> >>However, I did not find an easy way to query database using 
> >>multiple table in the FROM clause with DBIx::Class e.g:
> >>
> >>SELECT p.id, p.name, q.id, q.level FROM public p, group g 
> >>WHERE p.id=g.id AND p.name LIKE '%class%';
> >>
> >>I know you'll answer, "Why don't you use join?". Well when I 
> >>use join, my queries are very slow sometimes, and I realized 
> >>that querying this way is much faster (in my case at least).
> >
> >Then your database is broken.
> >
> >I'm going to go out on a limb and guess it's Oracle; it'd help if you'd
> >bothered giving us more information but I'll guess with what little you've
> >supplied.
> >
> >Assuming it -is- Oracle, you want to force usage of the Oracle::WhereJoins
> >module -
> >
> >__PACKAGE__->storage_type('::DBI::Oracle::WhereJoins');
> >
> >which is designed for old Oracles that don't support ANSI JOIN syntax, but
> >can sometimes be useful with ones that do but where the optimiser picks
> >better indexes if you put the join conditions in the WHERE clause [0]
> >
> >[0] No, I don't know why they did that. Even MySQL's excuse for an
> >optimiser is -reliably- retarded.
> Hi Matt,
> 
> I am running Sybase 12.5.1 sever entreprise. I heard, at 
> least for Pg, that not using join directive allow to 
> optimize much better the request by the optimizer, which 
> lead to a faster response from the sql server. I tested it 
> some times ago and discovered some gain from 10 times faster 
> not using join when querying 2 huge tables.

If you fancy having a go at making it possible to apply the WhereJoins
approach to any storage, send me an htpasswd line and make yourself a
branch.

-- 
      Matt S Trout       Need help with your Catalyst or DBIx::Class project?
   Technical Director                    http://www.shadowcat.co.uk/catalyst/
 Shadowcat Systems Ltd.  Want a managed development or deployment platform?
http://chainsawblues.vox.com/            http://www.shadowcat.co.uk/servers/



More information about the DBIx-Class mailing list