Mailing-List: contact user-help@ignite.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@ignite.apache.org
MIME-Version: 1.0
In-Reply-To: <113f01d2f599$98a1fd00$c9e5f700$@gridgain.com>
References: <1499257074525-14320.post@n6.nabble.com> <113f01d2f599$98a1fd00$c9e5f700$@gridgain.com>
From: Neeraj Bhatt <neerajbhatt2000@gmail.com>
Date: Wed, 5 Jul 2017 22:55:34 +0530
Message-ID: <CADAoojAqNgjoSqkuVzCeGXRbkC0rf0ToS7WosvRkU+PBJAyKBw@mail.gmail.com>
Subject: RE: Group index taking too much time
To: user@ignite.apache.org
Content-Type: multipart/alternative; boundary="94eb2c07720ec0d152055395490a"
archived-at: Wed, 05 Jul 2017 17:25:42 -0000

--94eb2c07720ec0d152055395490a
Content-Type: text/plain; charset="UTF-8"

Hi Mike

Yes, item has same index.
configured as item 2

We have a cluster of 3 nodes. Item is a partitioned cache with backup as
one.

We are executing queries from a single client.


On 05-Jul-2017 7:46 PM, "Michael Griggs" <michael.griggs@gridgain.com>
wrote:

Hi,

I see that you are referencing "Item" not "Item2" in your SQL query.  Does
"Item" have the same index configured?  Also, please describe your network
topology, how many server and client nodes are you running?

I built a reproducer based on your code, executed your query 5000 times, and
each query takes an average of 140 microseconds :

Executing query...
Query executed.
Execution time for 5000 queries = 700191368ns.  Avg Per Query: 140
microseconds

Kind regards
Mike


-----Original Message-----
From: neerajbhatt [mailto:neerajbhatt2000@gmail.com]
Sent: 05 July 2017 14:18
To: user@ignite.apache.org
Subject: Group index taking too much time

Hi All

We have pushed around 1.4 million item objects (with group index) and with
following query takes around 80 ms (on server), which is way ahead for our
use case. Please suggest

String rating="0";
String review="0";
String download="0";

StringBuilder buff = new StringBuilder("SELECT T._VAL FROM "
                                + "ITEMCACHE.Item as T "
                                + " WHERE  "
                                + " T.rating=? AND T.reviews=? AND
T.downloads=? "
                                + " limit 10");

SqlFieldsQuery qry = new SqlFieldsQuery(buff.toString());

List<List&lt;?>> res = cache.query(qry.setArgs(new
Object[]{rating,review,download})).getAll();

///Item object is

public class Item2 implements Serializable {

        private static final long serialVersionUID = 1L;

        @QuerySqlField(index = true, orderedGroups = {
@QuerySqlField.Group(name = "idx1", order = 2) })
        private int downloads;

        @QuerySqlField(index = true, orderedGroups = {
@QuerySqlField.Group(name = "idx1", order = 0) })
        private double rating;

        @QuerySqlField(index = true, orderedGroups = {
@QuerySqlField.Group(name = "idx1", order = 1) })
        private int reviews;
        @QuerySqlField(index = true)
        @AffinityKeyMapped
        private String id;
        public int getDownloads() {
                return downloads;
        }
        public void setDownloads(int downloads) {
                this.downloads = downloads;
        }
        public double getRating() {
                return rating;
        }
        public void setRating(double rating) {
                this.rating = rating;
        }
        public int getReviews() {
                return reviews;
        }
        public void setReviews(int reviews) {
                this.reviews = reviews;
        }
        public String getId() {
                return id;
        }
        public void setId(String id) {
                this.id = id;
        }
        public static long getSerialversionuid() {
                return serialVersionUID;
        }


--
View this message in context:
http://apache-ignite-users.70518.x6.nabble.com/Group-index-taking-too-much-t
ime-tp14320.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

--94eb2c07720ec0d152055395490a
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"auto"><div>Hi Mike<div dir=3D"auto"><br></div><div dir=3D"auto"=
>Yes, item has same index.</div><div dir=3D"auto">configured as item 2</div=
><div dir=3D"auto"><br></div><div dir=3D"auto">We have a cluster of 3 nodes=
. Item is a partitioned cache with backup as one.</div><div dir=3D"auto"><b=
r></div><div dir=3D"auto">We are executing queries from a single client.</d=
iv><div dir=3D"auto"><br></div><br><div class=3D"gmail_extra"><br><div clas=
s=3D"gmail_quote">On 05-Jul-2017 7:46 PM, &quot;Michael Griggs&quot; &lt;<a=
 href=3D"mailto:michael.griggs@gridgain.com">michael.griggs@gridgain.com</a=
>&gt; wrote:<br type=3D"attribution"><blockquote class=3D"quote" style=3D"m=
argin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hi,<br>
<br>
I see that you are referencing &quot;Item&quot; not &quot;Item2&quot; in yo=
ur SQL query.=C2=A0 Does<br>
&quot;Item&quot; have the same index configured?=C2=A0 Also, please describ=
e your network<br>
topology, how many server and client nodes are you running?<br>
<br>
I built a reproducer based on your code, executed your query 5000 times, an=
d<br>
each query takes an average of 140 microseconds :<br>
<br>
Executing query...<br>
Query executed.<br>
Execution time for 5000 queries =3D 700191368ns.=C2=A0 Avg Per Query: 140<b=
r>
microseconds<br>
<br>
Kind regards<br>
Mike<br>
<div class=3D"elided-text"><br>
<br>
-----Original Message-----<br>
From: neerajbhatt [mailto:<a href=3D"mailto:neerajbhatt2000@gmail.com">neer=
ajbhatt2000@gmail.<wbr>com</a>]<br>
Sent: 05 July 2017 14:18<br>
To: <a href=3D"mailto:user@ignite.apache.org">user@ignite.apache.org</a><br=
>
Subject: Group index taking too much time<br>
<br>
Hi All<br>
<br>
We have pushed around 1.4 million item objects (with group index) and with<=
br>
following query takes around 80 ms (on server), which is way ahead for our<=
br>
use case. Please suggest<br>
<br>
String rating=3D&quot;0&quot;;<br>
String review=3D&quot;0&quot;;<br>
String download=3D&quot;0&quot;;<br>
<br>
StringBuilder buff =3D new StringBuilder(&quot;SELECT T._VAL FROM &quot;<br=
>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 + &quot;ITEMCACHE.Item as T &quot;<b=
r>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 + &quot; WHERE=C2=A0 &quot;<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 + &quot; T.rating=3D? AND T.reviews=
=3D? AND<br>
T.downloads=3D? &quot;<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 + &quot; limit 10&quot;);<br>
<br>
SqlFieldsQuery qry =3D new SqlFieldsQuery(buff.toString()<wbr>);<br>
<br>
List&lt;List&amp;lt;?&gt;&gt; res =3D cache.query(qry.setArgs(new<br>
Object[]{rating,review,<wbr>download})).getAll();<br>
<br>
///Item object is<br>
<br>
public class Item2 implements Serializable {<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 private static final long serialVersionUID =3D =
1L;<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 @QuerySqlField(index =3D true, orderedGroups =
=3D {<br>
@QuerySqlField.Group(name =3D &quot;idx1&quot;, order =3D 2) })<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 private int downloads;<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 @QuerySqlField(index =3D true, orderedGroups =
=3D {<br>
@QuerySqlField.Group(name =3D &quot;idx1&quot;, order =3D 0) })<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 private double rating;<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 @QuerySqlField(index =3D true, orderedGroups =
=3D {<br>
@QuerySqlField.Group(name =3D &quot;idx1&quot;, order =3D 1) })<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 private int reviews;<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 @QuerySqlField(index =3D true)<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 @AffinityKeyMapped<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 private String id;<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 public int getDownloads() {<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 return downloads;<b=
r>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 }<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 public void setDownloads(int downloads) {<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 this.downloads =3D =
downloads;<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 }<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 public double getRating() {<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 return rating;<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 }<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 public void setRating(double rating) {<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 this.rating =3D rat=
ing;<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 }<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 public int getReviews() {<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 return reviews;<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 }<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 public void setReviews(int reviews) {<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 this.reviews =3D re=
views;<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 }<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 public String getId() {<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 return id;<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 }<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 public void setId(String id) {<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 <a href=3D"http://t=
his.id" rel=3D"noreferrer" target=3D"_blank">this.id</a> =3D id;<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 }<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 public static long getSerialversionuid() {<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 return serialVersio=
nUID;<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 }<br>
<br>
<br>
<br>
<br>
<br>
<br>
--<br>
View this message in context:<br>
<a href=3D"http://apache-ignite-users.70518.x6.nabble.com/Group-index-takin=
g-too-much-t
ime-tp14320.html" rel=3D"noreferrer" target=3D"_blank">http://apache-ignite=
-users.<wbr>70518.x6.nabble.com/Group-<wbr>index-taking-too-much-t<br>
ime-tp14320.html</a><br>
Sent from the Apache Ignite Users mailing list archive at Nabble.com.<br>
<br>
</div></blockquote></div><br></div></div></div>

--94eb2c07720ec0d152055395490a--