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.