Fastest method to check for key existence?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Fastest method to check for key existence?

Ekki Plicht (DF4OR)-2
Hi,
I need to check a list of values if they exist in a database. The
value is a unique key of that table. A stupid 'find' works of course,
but returns the whole row data, where I only need a simple binary
yes/no information if a row was found or not.

I am thinking of setting up a special resultset which contains only
the key as a returned value by SELECT, but I am wondering if there is
a faster method.

TIA,
Ekki

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fastest method to check for key existence?

Charlie Garrison
Good morning,

On 11/2/15 at 10:58 PM +0100, Ekki Plicht (DF4OR) <[hidden email]> wrote:

>I am thinking of setting up a special resultset which contains only
>the key as a returned value by SELECT, but I am wondering if there is
>a faster method.

Put a method in your ResultSet class, eg:

sub key_exists {
    my ($self, $key) = @_;
    my $row = $self->find( $key );
    return $row ? 1 : 0;
}


Elsewhere:

my $got_row = $schema->resultset('RSClass')->key_exists($key);


Charlie

--
   Charlie Garrison  <[hidden email]>
   github.com/cngarrison   metacpan.org/author/CNG

O< ascii ribbon campaign - stop html mail
http://www.ietf.org/rfc/rfc1855.txt


_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fastest method to check for key existence?

QE :: Felix Ostmann
We are using this function in our ResultSet.pm:

sub exists {
    my ($self, $query) = @_;

    return $self->search($query, { rows => 1, select => [\1] })->single;
}


Have a nice day
Felix


2015-02-12 1:05 GMT+01:00 Charlie Garrison <[hidden email]>:
Good morning,

On 11/2/15 at 10:58 PM +0100, Ekki Plicht (DF4OR) <[hidden email]> wrote:

>I am thinking of setting up a special resultset which contains only
>the key as a returned value by SELECT, but I am wondering if there is
>a faster method.

Put a method in your ResultSet class, eg:

sub key_exists {
    my ($self, $key) = @_;
    my $row = $self->find( $key );
    return $row ? 1 : 0;
}


Elsewhere:

my $got_row = $schema->resultset('RSClass')->key_exists($key);


Charlie

--
   Charlie Garrison  <[hidden email]>
   github.com/cngarrison   metacpan.org/author/CNG

O< ascii ribbon campaign - stop html mail
http://www.ietf.org/rfc/rfc1855.txt


_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fastest method to check for key existence?

Alexander Hartmaier
I'd additinally restrict the columns returned to the pk columns to save bytes going over the wire and using HRI to not instantiate a result object.

Looks like a nice addition to DBIC::Helpers!

On 2015-02-12 08:22, QE :: Felix Ostmann wrote:
We are using this function in our ResultSet.pm:

sub exists {
    my ($self, $query) = @_;

    return $self->search($query, { rows => 1, select => [\1] })->single;
}


Have a nice day
Felix


2015-02-12 1:05 GMT+01:00 Charlie Garrison <[hidden email]>:
Good morning,

On 11/2/15 at 10:58 PM +0100, Ekki Plicht (DF4OR) <[hidden email]> wrote:

>I am thinking of setting up a special resultset which contains only
>the key as a returned value by SELECT, but I am wondering if there is
>a faster method.

Put a method in your ResultSet class, eg:

sub key_exists {
    my ($self, $key) = @_;
    my $row = $self->find( $key );
    return $row ? 1 : 0;
}


Elsewhere:

my $got_row = $schema->resultset('RSClass')->key_exists($key);


Charlie

--
   Charlie Garrison  <[hidden email]>
   github.com/cngarrison   metacpan.org/author/CNG

O< ascii ribbon campaign - stop html mail
http://www.ietf.org/rfc/rfc1855.txt



_______________________________________________
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@...



*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
T-Systems Austria GesmbH Rennweg 97-99, 1030 Wien
Handelsgericht Wien, FN 79340b
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
Notice: This e-mail contains information that is confidential and may be privileged.
If you are not the intended recipient, please notify the sender and then
delete this e-mail immediately.
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fastest method to check for key existence?

Dmitry Latin
On 12 February 2015 at 14:27, Hartmaier Alexander
<[hidden email]> wrote:
> I'd additinally restrict the columns returned to the pk columns to save
> bytes going over the wire and using HRI to not instantiate a result object.
>
> Looks like a nice addition to DBIC::Helpers!
>
Looks like already there: DBIx::Class::Helper::ResultSet::Shortcut::HasRows


>
> On 2015-02-12 08:22, QE :: Felix Ostmann wrote:
>
> We are using this function in our ResultSet.pm:
>
> sub exists {
>     my ($self, $query) = @_;
>
>     return $self->search($query, { rows => 1, select => [\1] })->single;
> }
>
>
> Have a nice day
> Felix
>
>
> 2015-02-12 1:05 GMT+01:00 Charlie Garrison <[hidden email]>:
>>
>> Good morning,
>>
>> On 11/2/15 at 10:58 PM +0100, Ekki Plicht (DF4OR) <[hidden email]> wrote:
>>
>> >I am thinking of setting up a special resultset which contains only
>> >the key as a returned value by SELECT, but I am wondering if there is
>> >a faster method.
>>
>> Put a method in your ResultSet class, eg:
>>
>> sub key_exists {
>>     my ($self, $key) = @_;
>>     my $row = $self->find( $key );
>>     return $row ? 1 : 0;
>> }
>>
>>
>> Elsewhere:
>>
>> my $got_row = $schema->resultset('RSClass')->key_exists($key);
>>
>>
>> Charlie
>>
>> --
>>    Charlie Garrison  <[hidden email]>
>>    github.com/cngarrison   metacpan.org/author/CNG
>>
>> O< ascii ribbon campaign - stop html mail
>> http://www.ietf.org/rfc/rfc1855.txt
>>
>>
>> _______________________________________________
>> 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@...
>
>
>
>
> _______________________________________________
> 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@...
>
>
>
>
> *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
> T-Systems Austria GesmbH Rennweg 97-99, 1030 Wien
> Handelsgericht Wien, FN 79340b
> *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
> Notice: This e-mail contains information that is confidential and may be
> privileged.
> If you are not the intended recipient, please notify the sender and then
> delete this e-mail immediately.
> *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
>
> _______________________________________________
> 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@...



--
//wbr, Dmitry L.

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fastest method to check for key existence?

Lianna Eeftinck
Or just use ->count, which doesn't need to retrieve and instantiate the objects.

On 12 February 2015 at 11:33, Dmitry L. <[hidden email]> wrote:
On 12 February 2015 at 14:27, Hartmaier Alexander
<[hidden email]> wrote:
> I'd additinally restrict the columns returned to the pk columns to save
> bytes going over the wire and using HRI to not instantiate a result object.
>
> Looks like a nice addition to DBIC::Helpers!
>
Looks like already there: DBIx::Class::Helper::ResultSet::Shortcut::HasRows


>
> On 2015-02-12 08:22, QE :: Felix Ostmann wrote:
>
> We are using this function in our ResultSet.pm:
>
> sub exists {
>     my ($self, $query) = @_;
>
>     return $self->search($query, { rows => 1, select => [\1] })->single;
> }
>
>
> Have a nice day
> Felix
>
>
> 2015-02-12 1:05 GMT+01:00 Charlie Garrison <[hidden email]>:
>>
>> Good morning,
>>
>> On 11/2/15 at 10:58 PM +0100, Ekki Plicht (DF4OR) <[hidden email]> wrote:
>>
>> >I am thinking of setting up a special resultset which contains only
>> >the key as a returned value by SELECT, but I am wondering if there is
>> >a faster method.
>>
>> Put a method in your ResultSet class, eg:
>>
>> sub key_exists {
>>     my ($self, $key) = @_;
>>     my $row = $self->find( $key );
>>     return $row ? 1 : 0;
>> }
>>
>>
>> Elsewhere:
>>
>> my $got_row = $schema->resultset('RSClass')->key_exists($key);
>>
>>
>> Charlie
>>
>> --
>>    Charlie Garrison  <[hidden email]>
>>    github.com/cngarrison   metacpan.org/author/CNG
>>
>> O< ascii ribbon campaign - stop html mail
>> http://www.ietf.org/rfc/rfc1855.txt
>>
>>
>> _______________________________________________
>> 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@...
>
>
>
>
> _______________________________________________
> 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@...
>
>
>
>
> *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
> T-Systems Austria GesmbH Rennweg 97-99, 1030 Wien
> Handelsgericht Wien, FN 79340b
> *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
> Notice: This e-mail contains information that is confidential and may be
> privileged.
> If you are not the intended recipient, please notify the sender and then
> delete this e-mail immediately.
> *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
>
> _______________________________________________
> 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@...



--
//wbr, Dmitry L.


_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fastest method to check for key existence?

David Cantrell
On Thu, Feb 12, 2015 at 12:25:55PM +0000, Lianna Eeftinck wrote:

> Or just use ->count, which doesn't need to retrieve and instantiate the
> objects.

Something like this:

  SELECT COUNT(*) FROM foo WHERE bar IN ('ant', 'bat', cat', 'dog');

might tell you that two of them exist, but I got the impression that the
important information which *which* two exist.

--
David Cantrell | Minister for Arbitrary Justice

Eye have a spelling chequer / It came with my pea sea
It planely marques four my revue / Miss Steaks eye kin knot sea.
Eye strike a quay and type a word / And weight for it to say
Weather eye am wrong oar write / It shows me strait a weigh.

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fastest method to check for key existence?

Will Crawford
In reply to this post by Ekki Plicht (DF4OR)-2
On 11 February 2015 at 21:58, Ekki Plicht (DF4OR) <[hidden email]> wrote:
> Hi,
> I need to check a list of values if they exist in a database. The
> value is a unique key of that table. A stupid 'find' works of course,
> but returns the whole row data, where I only need a simple binary
> yes/no information if a row was found or not.

Presuming you mean that you need to get a list of them all, there's a
few answers, depending on your needs. A couple of the answers already
given are pretty useful and I'll be filing the "exists" rs method away
for future use.

Something like

    my %found = map +( $_ => 1 ),
        $rs->search_rs( { key => { -in => [ ... ] } } )->get_column('key')->all;

might do the trick (not tested for typos).

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fastest method to check for key existence?

Ekki Plicht (DF4OR)-2
Ooookay... that's a lot of very helpful answers. Many thanks for all
the suggestions, I guess I will go with the helper or with count().

Thanks,
Ekki


2015-02-13 11:32 GMT+01:00 Will Crawford <[hidden email]>:

> On 11 February 2015 at 21:58, Ekki Plicht (DF4OR) <[hidden email]> wrote:
>> Hi,
>> I need to check a list of values if they exist in a database. The
>> value is a unique key of that table. A stupid 'find' works of course,
>> but returns the whole row data, where I only need a simple binary
>> yes/no information if a row was found or not.
>
> Presuming you mean that you need to get a list of them all, there's a
> few answers, depending on your needs. A couple of the answers already
> given are pretty useful and I'll be filing the "exists" rs method away
> for future use.
>
> Something like
>
>     my %found = map +( $_ => 1 ),
>         $rs->search_rs( { key => { -in => [ ... ] } } )->get_column('key')->all;
>
> might do the trick (not tested for typos).
>
> _______________________________________________
> 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@...

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fastest method to check for key existence?

QE :: Felix Ostmann
I often build a map and use this also for an exist()-check (not for bigdata)

my %email_for =
    map { $@_ }
    $rs
        ->search(
            undef,
            {
                select => qw[ user_id email ],
            },
         )
        ->cursor
        ->all
    ;

only important is to use exact 2 fields in the select.



Mit freundlichem Gruß
Felix Ostmann


2015-02-13 12:13 GMT+01:00 Ekki Plicht (DF4OR) <[hidden email]>:
Ooookay... that's a lot of very helpful answers. Many thanks for all
the suggestions, I guess I will go with the helper or with count().

Thanks,
Ekki


2015-02-13 11:32 GMT+01:00 Will Crawford <[hidden email]>:
> On 11 February 2015 at 21:58, Ekki Plicht (DF4OR) <[hidden email]> wrote:
>> Hi,
>> I need to check a list of values if they exist in a database. The
>> value is a unique key of that table. A stupid 'find' works of course,
>> but returns the whole row data, where I only need a simple binary
>> yes/no information if a row was found or not.
>
> Presuming you mean that you need to get a list of them all, there's a
> few answers, depending on your needs. A couple of the answers already
> given are pretty useful and I'll be filing the "exists" rs method away
> for future use.
>
> Something like
>
>     my %found = map +( $_ => 1 ),
>         $rs->search_rs( { key => { -in => [ ... ] } } )->get_column('key')->all;
>
> might do the trick (not tested for typos).
>
> _______________________________________________
> 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@...

_______________________________________________
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@...


_______________________________________________
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@...
Loading...