Hi there,
I was very surprised to see the SQL statement which is generated when trying to
delete some data with LIMIT clause.
My DBIC code:
my $rows = $rs->search(
{ state => 'D' },
{ rows => 10000 }
)->delete();
The SQL statement I get is this:
DELETE FROM cache WHERE ( id IN ( SELECT * FROM ( SELECT me.id FROM cache me
WHERE ( state = ? ) LIMIT ? ) `_forced_double_subquery` ) ): 'D', '10000'
Which is a bit different from expected simple:
DELETE FROM cache WHERE state = 'D' LIMIT 10000;
The problem with the generated SQL is performance of course, on a table with
just 100k records it's already 3 times slower than the second version.
If this is a known drawback and is it possible to get the normal simple query
with DBIC or my only option here is to fall back to DBI with
schema->storage->dbh ?
Would appreciate any suggestions,
Dmitry
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
