Hi Neeraj, Does query plans differ 'with' and 'without' GroupBy ?
On Thu, Jul 6, 2017 at 3:34 PM, neerajbhatt <[email protected]> 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
