Re: Query performance varying with LIMIT keyword

[nunob]

Hello,

Thanks for responding.

Well, it takes 50ms to fetch the first line.

What I don't get is why it takes 8 seconds to fetch the 50th line.

In both cases I'm only fetching ONE row.

I suppose query execution time should be roughly the same whether i want the
1st or the 50th line.

I mean, aren't OFFSET and LIMIT just discarding rows from the resulting
dataset? Isn't it a question of  looping through the cursor until it finds
the Nth row? Why does it look like it's responding with exponential time as
I increase the offset?

Looking at explain it does look like it's doing a full table scan. In the
following query I have indexes over the joining columns on both tables:

SELECT
SE__Z1.ISINCODE AS __C0_0,
M__Z0.SYMBOL AS __C0_1,
NULL AS __C0_2,
CURRENT_TIMESTAMP() AS __C0_3,
M__Z0.LASTTIME AS __C0_4,
M__Z0.OPENPRICE AS __C0_5,
M__Z0.HIGHPRICE AS __C0_6,
M__Z0.LOWPRICE AS __C0_7,
M__Z0.CLOSEPRICE AS __C0_8,
M__Z0.LASTPRICE AS __C0_9,
M__Z0.LASTQUANTITY AS __C0_10,
M__Z0.BIDPRICE AS __C0_11,
M__Z0.ASKPRICE AS __C0_12,
M__Z0.BIDSIZE AS __C0_13,
M__Z0.ASKSIZE AS __C0_14
FROM SERVERDEAL.MARKETDATA M__Z0
/* SERVERDEAL.MARKETDATA.__SCAN_ */
LEFT OUTER JOIN SIMS.SECURITYEXCHANGE SE__Z1
/* SIMS.SECURITYEXCHANGE.__SCAN_ */
ON (SE__Z1.SECURITYTYPE = M__Z0.MARKET)
AND ((SE__Z1.EXCHANGEID = M__Z0.EXCHANGE)
AND (SE__Z1.SYMBOL = M__Z0.SYMBOL))
LIMIT 11












--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Query performance varying with LIMIT keyword

[nunob]

Also if I fix the LIMIT and vary the OFFSET I get responses of this
magnitude:

  LIMIT 1 OFFSET 0: 50ms
  LIMIT 1 OFFSET 1: 200ms
  LIMIT 1 OFFSET 5: 700ms
  LIMIT 1 OFFSET 10: 1.3s
  LIMIT 1 OFFSET 50: 7.8s

Why is this?



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Query performance varying with LIMIT keyword

[nunob]

Hello,

I have table A with PK (col1, col2, col3) and table B with PK (col1, col2
col3)

Both tables have around 300k records.

This query executes in *2ms*:

/SELECT * FROM table_a a
LEFT JOIN table_b b ON  b.col1 = a.col1
 AND b.col2 = a.col2
 AND b.col3 = a.col3
*LIMIT 1 *OFFSET 0;/

This query executes in *1.4s*:

/SELECT * FROM table_a a
LEFT JOIN table_b b ON  b.col1 = a.col1
 AND b.col2 = a.col2
 AND b.col3 = a.col3
*LIMIT 10 *OFFSET 0;/

Shouldn't the execution time be the same for both queries or am I missing
something?



Regards,

Nuno





--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/