Mailing-List: contact user-help@ignite.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@ignite.apache.org
Date: Thu, 6 Jul 2017 05:34:18 -0700 (MST)
From: neerajbhatt <neerajbhatt2000@gmail.com>
To: user@ignite.apache.org
Message-ID: <1499344458667-14389.post@n6.nabble.com>
Subject: slow performance when using order by clause
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
archived-at: Thu, 06 Jul 2017 12:34:23 -0000

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.