Mailing-List: contact user-help@cassandra.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cassandra.apache.org
Received-SPF: pass (athena.apache.org: local policy includes SPF record at
 spf.trusted-forwarder.org)
MIME-Version: 1.0
In-Reply-To: <527ABDFA.40607@chill.com>
References: <527ABDFA.40607@chill.com>
Date: Wed, 6 Nov 2013 17:05:53 -0600
Message-ID: 
 <CAKmMYa8oYM+vvihSw8SYyhXC-8DPJqjpJVX8eH--YYtSG6SZOQ@mail.gmail.com>
Subject: Re: CQL 'IN' predicate
From: Nate McCall <nate@thelastpickle.com>
To: Cassandra Users <user@cassandra.apache.org>
Content-Type: multipart/alternative; boundary=001a11c29ad2e3020704ea8a306b

--001a11c29ad2e3020704ea8a306b
Content-Type: text/plain; charset=ISO-8859-1

Unless you explicitly set a page size (i'm pretty sure the query is
converted to a paging query automatically under the hood) you will get
capped at the default of 10k which might get a little weird semantically.
That said, you should experiment with explicit page sizes and see where it
gets you (i've not tried this yet with an IN clause - would be real curious
to hear how it worked).

Another thing to consider is that it's a pretty big statement to parse
every time. You might want to go the (much) smaller batch route so these
can be stored procedures? (another thing I havent tried with IN clause -
don't see why it would not work though).


On Wed, Nov 6, 2013 at 4:08 PM, Dan Gould <dan@chill.com> wrote:

> I was wondering if anyone had a sense of performance/best practices
> around the 'IN' predicate.
>
> I have a list of up to potentially ~30k keys that I want to look up in a
> table (typically queries will have <500, but I worry about the long tail).
>  Most
> of them will not exist in the table, but, say, about 10-20% will.
>
> Would it be best to do:
>
> 1) SELECT fields FROM table WHERE id in (uuid1, uuid2, ...... uuid30000);
>
> 2) Split into smaller batches--
> for group_of_100 in all_30000:
>    // ** Issue in parallel or block after each one??
>    SELECT fields FROM table WHERE id in (group_of_100 uuids);
>
> 3) Something else?
>
> My guess is that (1) is fine and that the only worry is too much data
> returned (which won't be a problem in this case), but I wanted to check
> that it's not a C* anti-pattern before.
>
> [Conversely, is a batch insert with up to 30k items ok?]
>
> Thanks,
> Dan
>
>


-- 
-----------------
Nate McCall
Austin, TX
@zznate

Co-Founder & Sr. Technical Consultant
Apache Cassandra Consulting
http://www.thelastpickle.com

--001a11c29ad2e3020704ea8a306b
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Unless you explicitly set a page size (i&#39;m pretty sure=
 the query is converted to a paging query automatically under the hood) you=
 will get capped at the default of 10k which might get a little weird seman=
tically. That said, you should experiment with explicit page sizes and see =
where it gets you (i&#39;ve not tried this yet with an IN clause - would be=
 real curious to hear how it worked).=A0<div>
<br></div><div>Another thing to consider is that it&#39;s a pretty big stat=
ement to parse every time. You might want to go the (much) smaller batch ro=
ute so these can be stored procedures? (another thing I havent tried with I=
N clause - don&#39;t see why it would not work though).</div>
<div><div><br></div><div><br></div></div></div><div class=3D"gmail_extra"><=
br><br><div class=3D"gmail_quote">On Wed, Nov 6, 2013 at 4:08 PM, Dan Gould=
 <span dir=3D"ltr">&lt;<a href=3D"mailto:dan@chill.com" target=3D"_blank">d=
an@chill.com</a>&gt;</span> wrote:<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">I was wondering if anyone had a sense of per=
formance/best practices<br>
around the &#39;IN&#39; predicate.<br>
<br>
I have a list of up to potentially ~30k keys that I want to look up in a<br=
>
table (typically queries will have &lt;500, but I worry about the long tail=
). =A0Most<br>
of them will not exist in the table, but, say, about 10-20% will.<br>
<br>
Would it be best to do:<br>
<br>
1) SELECT fields FROM table WHERE id in (uuid1, uuid2, ...... uuid30000);<b=
r>
<br>
2) Split into smaller batches--<br>
for group_of_100 in all_30000:<br>
=A0 =A0// ** Issue in parallel or block after each one??<br>
=A0 =A0SELECT fields FROM table WHERE id in (group_of_100 uuids);<br>
<br>
3) Something else?<br>
<br>
My guess is that (1) is fine and that the only worry is too much data retur=
ned (which won&#39;t be a problem in this case), but I wanted to check that=
 it&#39;s not a C* anti-pattern before.<br>
<br>
[Conversely, is a batch insert with up to 30k items ok?]<br>
<br>
Thanks,<br>
Dan<br>
<br>
</blockquote></div><br><br clear=3D"all"><div><br></div>-- <br><div dir=3D"=
ltr">-----------------<br>Nate McCall<br>Austin, TX<br>@zznate<br><br>Co-Fo=
under &amp; Sr. Technical Consultant<br>Apache Cassandra Consulting<br><a h=
ref=3D"http://www.thelastpickle.com" target=3D"_blank">http://www.thelastpi=
ckle.com</a></div>

</div>

--001a11c29ad2e3020704ea8a306b--