RE: Re: Query performance varying with LIMIT keyword
Hello, Your query doesn’t use indices and performs a full scan on your tables.That requires more time in order to execute a join and get records. Please, consider adding the corresponding index:https://www.gridgain.com/docs/latest/sql-reference/ddl#create-index From: nunobSent: Tuesday, January 21, 2020 7:17 PMTo: user@ignite.apache.orgSubject: Re: Query performance varying with LIMIT keyword 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 the1st or the 50th line. I mean, aren't OFFSET and LIMIT just discarding rows from the resultingdataset? Isn't it a question of looping through the cursor until it findsthe Nth row? Why does it look like it's responding with exponential time asI increase the offset? Looking at explain it does look like it's doing a full table scan. In thefollowing 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_14FROM 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
Hello! 200 ms for 2 entries / 1.3s for 11 entries is just 20% increase per entry. Likewise, 7.8s for 51 entries / 1.3s for 11 entries is further 30% increase per entry. It does not sound exponential, at least for any reasonable number of entries, instead it just looks superlinear. I recommend adding index for this use case. Regards, -- Ilya Kasnacheev вт, 21 янв. 2020 г. в 19:17, 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
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
Hello! This would suggest that fetching a single row takes around 100 ms, in a slightly superlinear fashion. Why is it a problem? What indexes do you have? Can you provide EXPLAIN plans? Regards, -- Ilya Kasnacheev пн, 20 янв. 2020 г. в 22:23, 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/ >
Re: Query performance varying with LIMIT keyword
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/
