[Dbix-class] trouble with using 'select' in a search clause

[Matt S Trout]

Toby Corkindale wrote:
> On Thu, May 18, 2006 at 01:31:26PM +0100, Matt S Trout wrote:
>> Toby Corkindale wrote:
>>> On Thu, May 18, 2006 at 01:11:46AM -0700, John Napiorkowski wrote:
>>>> To be honest I am sure part of my problem is ignorance
>>>> about the best way to use DBIx.  So your suggestions
>>>> of any type are useful.  I've been using SQL for quite
>>>> a long time and writing a statement like the one I
>>>> show below is a very normal way for me to think.  My
>>>> instinct is to try and get all the data I want in a
>>>> single statement, since my training in SQL pushed me
>>>> to consider performance issue.
>>> I've entered the DBIx::Class arena from the same background as you..
>>> I've used to hand-crafting large SQL queries, with the emphasis being on
>>> performance, data normalisation, and avoiding long locks due to transaction
>>> serialisation.
>>>
>>> I don't believe DBIx::Class will ever have the performance of pure
>>> hand-crafted SQL, when used in complex situations. However, that's like
>>> saying that C++ won't have the performance of hand-written assembler - ie.
>>> It's true, but there isn't a huge amount in it, and no-one is going to
>>> write large programs in pure assembler, and you can always insert small
>>> pieces here and there if needs be anyway.
>>>
>>> DBIx::Class encourages you to think of your data as the conceptual related
>>> objects they really are.
>> ... but if you can't persuade it to run something that's at least *almost* 
>> equivalent to the pure hand-crafted SQL you would have done anyway, I want to 
>> know what the difference is so I can hack it in as a supported feature.
> 
> Hell, you probably already have :)
> 
> Here's an example:
> Say you want to increment a column in a bunch of records, eg. here's a
> synthetic example in SQL:
> CREATE TABLE example ( id SERIAL PRIMARY KEY, ..., foo INTEGER );
> UPDATE example SET foo = foo + 1 WHERE id < 1000;
> 
> With my current (incomplete, ungodlike) understanding of D::C, I'd implement
> that with a ->search (to get all the records with id < 1000), and then loop
> through them like:
> while (my $i = $results->next) { $i->foo($i->foo + 1); $i->update; }
> 
> Which is going to generate a couple of thousand queries!
> I could see that it's probably possible to get it down to just over 1000
> queries if you fiddle with the prefetching or something.. but still..
> 
> Also, you might want to be doing this operation inside a transaction - in which
> case, you'd be blocking other queries for quite a long time, whereas the
> original SQL example would allow the database to continue to operate on the
>> 1000 part of the table.
> 
> I think... Tell me I'm wrong? :)

Eek.

my $rs = $schema->resultset('Example')->search({ id => { '<', 1000 });
            # remember this doesn't actually run a query

$schema->txn_do(
   sub { $rs->update({ foo => \"foo + 1" }); }
);

-- 
      Matt S Trout       Offering custom development, consultancy and support
   Technical Director    contracts for Catalyst, DBIx::Class and BAST. Contact
Shadowcat Systems Ltd.  mst (at) shadowcatsystems.co.uk for more information

+ Help us build a better perl ORM: http://dbix-class.shadowcatsystems.co.uk/ +