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.

Reply via email to