You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by neerajbhatt <ne...@gmail.com> on 2017/07/06 12:34:18 UTC
slow performance when using order by clause
Hi All
We are running below query on two caches (item and rank)
SELECT T._VAL FROM ITEMCACHE.Item as T, IGPCACHE.Rank r WHERE T.uniqueSkuId
= r.uniqueSkuId AND T.rating=? AND T.reviews=? AND T.downloads=?+ " order by
rank desc limit 10
while we are getting good result (~10 ms in 1.4 million of item objects)
when order by is not used. In case of order by the performance is very bad
(200 ms in 1000 item objects)
Please suggest
//item class
public class Item implements Serializable {
private static final long serialVersionUID = 1L;
@QuerySqlField(index = true)
private int downloads;
@QuerySqlField(index = true)
private double rating;
@QuerySqlField(index = true)
private int reviews;
@QuerySqlField(index = true)
@AffinityKeyMapped
private String uniqueSkuId;
public int getDownloads() {
return downloads;
}
public void setDownloads(int downloads) {
this.downloads = downloads;
}
public String getUniqueSkuId() {
return uniqueSkuId;
}
public void setUniqueSkuId(String uniqueSkuId) {
this.uniqueSkuId = uniqueSkuId;
}
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 static long getSerialversionuid() {
return serialVersionUID;
}
// rank class
ublic class Rank {
@QuerySqlField(index = true)
private long id;
@QuerySqlField(index = true)
private String geoId;
@QuerySqlField(index = true)
@AffinityKeyMapped
private String uniqueSkuId;
@QuerySqlField(index = true,descending=true)
private long rank;
public long getRank() {
return rank;
}
public void setRank(long rank) {
this.rank = rank;
}
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getGeoId() {
return geoId;
}
public void setGeoId(String geoId) {
this.geoId = geoId;
}
public String getUniqueSkuId() {
return uniqueSkuId;
}
public void setUniqueSkuId(String uniqueSkuId) {
this.uniqueSkuId = uniqueSkuId;
}
//explain plan
SELECT
T__Z0._VAL AS __C0_0,
R__Z1.RANK AS __C0_1
FROM ITEMCACHE.ITEM T__Z0
/* ITEMCACHE.ITEM_RATING_IDX: RATING = ?1 */
/* WHERE (T__Z0.RATING = ?1)
AND ((T__Z0.DOWNLOADS = ?3)
AND (T__Z0.REVIEWS = ?2))
*/
INNER JOIN IGPCACHE.RANK R__Z1
/* IGPCACHE.RANK_UNIQUESKUID_IDX: UNIQUESKUID = T__Z0.UNIQUESKUID */
ON 1=1
WHERE (T__Z0.DOWNLOADS = ?3)
AND ((T__Z0.REVIEWS = ?2)
AND ((T__Z0.RATING = ?1)
AND (T__Z0.UNIQUESKUID = R__Z1.UNIQUESKUID)))
ORDER BY 2 DESC
LIMIT 10
SELECT
__C0_0 AS _VAL
FROM PUBLIC.__T0
/* IGPCACHE.merge_sorted */
ORDER BY =__C0_1 DESC
LIMIT 10
/* index sorted */
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/slow-performance-when-using-order-by-clause-tp14389.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: slow performance when using order by clause
Posted by neerajbhatt <ne...@gmail.com>.
Hi Andrew
Thanks a ton, by chaging order the performance increased signifacantly to
about 10 ms
Thanks
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/slow-performance-when-using-order-by-clause-tp14389p14573.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: slow performance when using order by clause
Posted by Andrey Mashenkov <an...@gmail.com>.
Hi Neeraj,
Try to change join order and enable option
SqlFieldsQuery.enforceJoinOrder(true).
Sometimes, changing join order can significantly improve performance.
On Fri, Jul 7, 2017 at 9:20 AM, neerajbhatt <ne...@gmail.com>
wrote:
> explain plan without order by
>
>
> FROM ITEMCACHE.ITEM T__Z0
> /* ITEMCACHE.ITEM_RATING_IDX: RATING = ?1 */
> /* WHERE (T__Z0.RATING = ?1)
> AND ((T__Z0.DOWNLOADS = ?3)
> AND (T__Z0.REVIEWS = ?2))
> */
> INNER JOIN IGPCACHE.RANK R__Z1
> /* IGPCACHE.RANK_UNIQUESKUID_IDX: UNIQUESKUID = T__Z0.UNIQUESKUID */
> ON 1=1
> WHERE (T__Z0.DOWNLOADS = ?3)
> AND ((T__Z0.REVIEWS = ?2)
> AND ((T__Z0.RATING = ?1)
> AND (T__Z0.UNIQUESKUID = R__Z1.UNIQUESKUID)))
> LIMIT 10
> SELECT
> __C0_0 AS _VAL
> FROM PUBLIC.__T0
> /* IGPCACHE.merge_scan */
> LIMIT 10
>
>
>
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/slow-performance-when-using-order-
> by-clause-tp14389p14470.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
--
Best regards,
Andrey V. Mashenkov
Re: slow performance when using order by clause
Posted by neerajbhatt <ne...@gmail.com>.
explain plan without order by
FROM ITEMCACHE.ITEM T__Z0
/* ITEMCACHE.ITEM_RATING_IDX: RATING = ?1 */
/* WHERE (T__Z0.RATING = ?1)
AND ((T__Z0.DOWNLOADS = ?3)
AND (T__Z0.REVIEWS = ?2))
*/
INNER JOIN IGPCACHE.RANK R__Z1
/* IGPCACHE.RANK_UNIQUESKUID_IDX: UNIQUESKUID = T__Z0.UNIQUESKUID */
ON 1=1
WHERE (T__Z0.DOWNLOADS = ?3)
AND ((T__Z0.REVIEWS = ?2)
AND ((T__Z0.RATING = ?1)
AND (T__Z0.UNIQUESKUID = R__Z1.UNIQUESKUID)))
LIMIT 10
SELECT
__C0_0 AS _VAL
FROM PUBLIC.__T0
/* IGPCACHE.merge_scan */
LIMIT 10
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/slow-performance-when-using-order-by-clause-tp14389p14470.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: slow performance when using order by clause
Posted by Andrey Mashenkov <an...@gmail.com>.
Hi Neeraj,
Does query plans differ 'with' and 'without' GroupBy ?
On Thu, Jul 6, 2017 at 3:34 PM, neerajbhatt <ne...@gmail.com>
wrote:
> Hi All
>
> We are running below query on two caches (item and rank)
>
> SELECT T._VAL FROM ITEMCACHE.Item as T, IGPCACHE.Rank r WHERE T.uniqueSkuId
> = r.uniqueSkuId AND T.rating=? AND T.reviews=? AND T.downloads=?+ " order
> by
> rank desc limit 10
>
> while we are getting good result (~10 ms in 1.4 million of item objects)
> when order by is not used. In case of order by the performance is very bad
> (200 ms in 1000 item objects)
>
> Please suggest
>
> //item class
> public class Item implements Serializable {
>
> private static final long serialVersionUID = 1L;
>
> @QuerySqlField(index = true)
> private int downloads;
>
> @QuerySqlField(index = true)
> private double rating;
>
> @QuerySqlField(index = true)
> private int reviews;
> @QuerySqlField(index = true)
> @AffinityKeyMapped
> private String uniqueSkuId;
> public int getDownloads() {
> return downloads;
> }
> public void setDownloads(int downloads) {
> this.downloads = downloads;
> }
> public String getUniqueSkuId() {
> return uniqueSkuId;
> }
> public void setUniqueSkuId(String uniqueSkuId) {
> this.uniqueSkuId = uniqueSkuId;
> }
> 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 static long getSerialversionuid() {
> return serialVersionUID;
> }
>
>
> // rank class
>
> ublic class Rank {
>
> @QuerySqlField(index = true)
> private long id;
> @QuerySqlField(index = true)
> private String geoId;
>
> @QuerySqlField(index = true)
> @AffinityKeyMapped
> private String uniqueSkuId;
>
> @QuerySqlField(index = true,descending=true)
> private long rank;
>
> public long getRank() {
> return rank;
> }
>
> public void setRank(long rank) {
> this.rank = rank;
> }
>
> public long getId() {
> return id;
> }
>
> public void setId(long id) {
> this.id = id;
> }
>
> public String getGeoId() {
> return geoId;
> }
>
> public void setGeoId(String geoId) {
> this.geoId = geoId;
> }
>
> public String getUniqueSkuId() {
> return uniqueSkuId;
> }
>
> public void setUniqueSkuId(String uniqueSkuId) {
> this.uniqueSkuId = uniqueSkuId;
> }
>
> //explain plan
>
> SELECT
> T__Z0._VAL AS __C0_0,
> R__Z1.RANK AS __C0_1
> FROM ITEMCACHE.ITEM T__Z0
> /* ITEMCACHE.ITEM_RATING_IDX: RATING = ?1 */
> /* WHERE (T__Z0.RATING = ?1)
> AND ((T__Z0.DOWNLOADS = ?3)
> AND (T__Z0.REVIEWS = ?2))
> */
> INNER JOIN IGPCACHE.RANK R__Z1
> /* IGPCACHE.RANK_UNIQUESKUID_IDX: UNIQUESKUID = T__Z0.UNIQUESKUID */
> ON 1=1
> WHERE (T__Z0.DOWNLOADS = ?3)
> AND ((T__Z0.REVIEWS = ?2)
> AND ((T__Z0.RATING = ?1)
>
> AND (T__Z0.UNIQUESKUID = R__Z1.UNIQUESKUID)))
> ORDER BY 2 DESC
> LIMIT 10
> SELECT
> __C0_0 AS _VAL
> FROM PUBLIC.__T0
> /* IGPCACHE.merge_sorted */
> ORDER BY =__C0_1 DESC
> LIMIT 10
> /* index sorted */
>
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/slow-performance-when-using-order-
> by-clause-tp14389.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
--
Best regards,
Andrey V. Mashenkov