[Dbix-class] SELECT from multiple tables

[Emmanuel Quevillon]

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.

Cheers

-- 
-------------------------
Emmanuel Quevillon
Biological Software and Databases Group
Institut Pasteur
+33 1 44 38 95 98
tuco at_ pasteur dot fr
-------------------------