Re: trouble with using 'select' in a search clause

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? :)

-Toby



-- 
Turning and turning in the widening gyre/The falcon cannot hear the falconer;
Things fall apart, the centre cannot hold/Mere anarchy is loosed upon the world
(gpg --keyserver www.co.uk.pgp.net --recv-key B1CCF88E)