-result_as => 'firstrow' optimization

[Sergiy Zuban]

Hello Laurent, 

When we expect to get only one row, but query itself returns more then one currently DBIx::DataModel simply stops after fetching the first row.
It seems like we can optimize this by passing -limit => 1 implicitly, so database will always return 1 row. Sure there won't be any difference in cases when we look up by primary key or unique index, because DBMS knows that there will be maximum 1 record in resultset.

But in cases when when DBMS can't predict size of resultset it may choose not optimal query plan. For example, PostgreSQL always prefers NESTED LOOP join (over MERGE or HASH joins) when resultset is small. So passing -limit => 1 may help planner and speedup query execution.

Thanks.

[laurent dami]

Le 16.02.2013 00:21, Sergiy Zuban a �crit :

> Hello Laurent,
>
> When we expect to get only one row, but query itself returns more then
> one currently DBIx::DataModel simply stops after fetching the first row.
> It seems like we can optimize this by passing -limit => 1 implicitly,
> so database will always return 1 row. Sure there won't be any
> difference in cases when we look up by primary key or unique index,

Hi Sergiy,

Thanks for the suggestion. It makes sense, but I was afraid that putting
this as a default would create some unexpected side-effects in some
databases.

So I added a new option 'autolimit_firstrow' that you can toggle on your
schema:

$schema_class->singleton->autolimit_firstrow(1);

or

$schema->autolimit_firstrow(1);


Committed on https://github.com/damil/DBIx-DataModel

Waiting for your feedback before a CPAN release.

Cheers, Laurent D.

[Sergiy Zuban]

Hi Laurent,

This feature works fine for me as well. I've not even expected that you implement everything so fast :)

Will keep waiting for CPAN release.

--
Sergiy Zuban