[Dbix-class] fate of mssql_limit_regression branch?

[Alan Humphrey]

Peter -

Sorry for the delay, I just now had time to get to this.  It looks good!  I've included the generated SQL below so you can confirm that we're getting what you're expecting.  FYI, I ran the sql through the query analyzer and the new SQL is *much* more efficient.

Many Thanks!

- Alan


my $items = $schema->resultset('Birds')->search(
{},
{order_by => {'-asc' => 'common_name'},
unsafe_subselect_ok => 1,
prefetch => [qw/genus species/],
page => 4,
rows => 10}
);

OLD:
SELECT me.id, me.genus_id, me.species_name_id, me.image_id, me.north_america_map_id, me.washington_map_id, me.default_sound_id, me.is_washington_bird, me.common_name, me.taxonomic_order, me.identification, me.voice, me.habitat, me.behavior, me.diet, me.nesting, me.migration, me.where_found, me.conservation_status, me.breeding, me.notes, genus.id, genus.family_id, genus.genus_scientific_name, genus.genus_common_name, genus.genus_description, species.id, species.species_scientific_name, species.species_name_notes FROM ( SELECT me.id, me.genus_id, me.species_name_id, me.image_id, me.north_america_map_id, me.washington_map_id, me.default_sound_id, me.is_washington_bird, me.common_name, me.taxonomic_order, me.identification, me.voice, me.habitat, me.behavior, me.diet, me.nesting, me.migration, me.where_found, me.conservation_status, me.breeding, me.notes FROM ( SELECT me.*, ROW_NUMBER() OVER( ORDER BY (SELECT(1)) ) AS rno__row__index FROM ( SELECT me.id, me.genus_id, me.species_name_id, me.image_id, me.north_america_map_id, me.washington_map_id, me.default_sound_id, me.is_washington_bird, me.common_name, me.taxonomic_order, me.identification, me.voice, me.habitat, me.behavior, me.diet, me.nesting, me.migration, me.where_found, me.conservation_status, me.breeding, me.notes FROM (SELECT TOP 4294967296 me.id, me.genus_id, me.species_name_id, me.image_id, me.north_america_map_id, me.washington_map_id, me.default_sound_id, me.is_washington_bird, me.common_name, me.taxonomic_order, me.identification, me.voice, me.habitat, me.behavior, me.diet, me.nesting, me.migration, me.where_found, me.conservation_status, me.breeding, me.notes FROM birds me JOIN genera genus ON genus.id = me.genus_id JOIN species_names species ON species.id = me.species_name_id ORDER BY common_name ASC) me ) me ) me WHERE rno__row__index BETWEEN 31 AND 40 ) me JOIN genera genus ON genus.id = me.genus_id JOIN species_names species ON species.id = me.species_name_id ORDER BY common_name ASC:


NEW:
SELECT id, genus_id, species_name_id, image_id, north_america_map_id, washington_map_id, default_sound_id, is_washington_bird, common_name, taxonomic_order, identification, voice, habitat, behavior, diet, nesting, migration, where_found, conservation_status, breeding, notes, genus__id, genus__family_id, genus__genus_scientific_name, genus__genus_common_name, genus__genus_description, species__id, species__species_scientific_name, species__species_name_notes FROM ( SELECT id, genus_id, species_name_id, image_id, north_america_map_id, washington_map_id, default_sound_id, is_washington_bird, common_name, taxonomic_order, identification, voice, habitat, behavior, diet, nesting, migration, where_found, conservation_status, breeding, notes, genus__id, genus__family_id, genus__genus_scientific_name, genus__genus_common_name, genus__genus_description, species__id, species__species_scientific_name, species__species_name_notes, ORDER__BY__1, ROW_NUMBER() OVER( ORDER BY ORDER__BY__1 ASC ) AS rno__row__index FROM ( SELECT me.id, me.genus_id, me.species_name_id, me.image_id, me.north_america_map_id, me.washington_map_id, me.default_sound_id, me.is_washington_bird, me.common_name, me.taxonomic_order, me.identification, me.voice, me.habitat, me.behavior, me.diet, me.nesting, me.migration, me.where_found, me.conservation_status, me.breeding, me.notes, genus.id AS genus__id, genus.family_id AS genus__family_id, genus.genus_scientific_name AS genus__genus_scientific_name, genus.genus_common_name AS genus__genus_common_name, genus.genus_description AS genus__genus_description, species.id AS species__id, species.species_scientific_name AS species__species_scientific_name, species.species_name_notes AS species__species_name_notes, common_name AS ORDER__BY__1 FROM birds me JOIN genera genus ON genus.id = me.genus_id JOIN species_names species ON species.id = me.species_name_id ) me ) me WHERE rno__row__index BETWEEN 31 AND 40 :

> -----Original Message-----
> From: Peter Rabbitson [mailto:rabbit+dbic at rabbit.us]
> Sent: Wednesday, May 05, 2010 3:35 AM
> To: DBIx::Class user and developer list
> Subject: Re: [Dbix-class] fate of mssql_limit_regression branch?
> 
> Alan Humphrey wrote:
> > I've installed the trunk code and, for my purposes, the
> unsafe_subselect_ok attribute is probably going to be sufficient.
> Thank you for your thorough documentation.
> >
> 
> Hi,
> 
> Waking up this old thread to get some of your testing input. A thorough
> rewrite[1] of the subselecting limit emulations has just landed in
> trunk[2] and *in theory* it should allow you to get rid of almost all
> unsafe_subselect_ok flags in your code (as the queries now changed to
> include much less subqueries to arrive at the same result). Please test
> if time permits, before this lands on CPAN. Your input is appreciated!
> 
> Cheers
> 
> [1] http://dev.catalystframework.org/svnweb/bast/revision?rev=9305
> [2] http://dev.catalyst.perl.org/repos/bast/DBIx-Class/0.08/trunk/
> 
> _______________________________________________
> 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 at lists.scsys.co.uk