Re: Re: SQL query performance with JOIN and ORDER BY or WHERE
Wow, that seems to do the trick. When forcing the index use on the column that I order by the query returns within milliseconds instead of >40 seconds!I‘ll do more tests but so far it looks like you are right, this helps significantly:USE INDEX(my_index_on_order_by_column)ThanksThomas.On 13.04.21 at 13:10, Taras Ledkov wrote:
From: "Taras Ledkov" Date: 13. April 2021To: user@ignite.apache.orgCc: Subject: Re: SQL query performance with JOIN and ORDER BY or WHERE
Hi,
Please try to enforce use index for sorted select.
It make sense when filter produce a lot pf results.
So, full table will be scanned by ordered index, but result will
be not materialized on map nodes.
Using the example of the first message, it looks like this:
SELECT JQ._KEY
FROM "JobQueue".JOBQUEUE AS JQ USE INDEX(IDX_JQ_QUEUED)
INNER JOIN "Jobs".JOBS AS J ON JQ.jobid=J._key
WHERE JQ.STATUS = 2
ORDER BY JQ.QUEUED ASC
LIMIT 20
When IDX_JQ_QUEUED was created on JOBQUEUE (QUEUED).
I cannot guarantee he performance boost. Just to try.
On 12.04.2021 23:02, Thomas Kramer
wrote:
Hi Ilya,
unfortunately this also didn't help improving query
performance. Not sure what else I can try. Or maybe it is
expected? In my opinion it shouldn't take that long as the query
without the ORDER BY clause is super fast. Since there is a
index on the order field I would expect this should be fast.
Btw, I noticed that for some other queries the first call has
this long execution while on every following call with the same
SQL statement it returns within half a second. But I guess this
is caching related and not the issue I see here?
Best Regards,
Thomas.
On 12.04.21 13:15, Ilya Kasnacheev
wrote:
Hello!
I think you can try a (QUEUEID, STATUS) index.
Or maybe a (STATUS, QUEUEID), probably makes sense to try
both.
Regards,
--
Ilya Kasnacheev
сб, 10 апр. 2021 г. в 00:22,
<don.tequ...@gmx.de>:
The QUEUED
field is a BIGINT that contains timestamp from
System.currentTimeMillis(), so it should be pretty
easy to sort, shouldn’t it? Looks like the field
STATUS (used in where clause) and field QUEUED (used
in order clause) are not working optimal when used
together. Does this make sense? Do I need to create an
index on both together?
I will take
a look at UNION and WHERE EXISTS, I‘m not familiar
with these statements.
Thanks!
On 09.04.21 at 17:37, Ilya Kasnacheev wrote:
From:
"Ilya Kasnacheev" <ilya.kasnach...@gmail.com>
Date: 9. April 2021
To: user@ignite.apache.org
Cc:
Subject: Re: SQL query performance with JOIN and
ORDER BY or WHERE
Hello!
ORDER BY will have to sort the whole table.
I think that using index on QUEUED will be
optimal here. What is the selectivity of this
field? If it s boolean, you might as well use
UNION queries.
Have you tried joining JOBS
via WHERE EXISTS?
Regards,
--
Re: SQL query performance with JOIN and ORDER BY or WHERE
Hi,
Please try to enforce use index for sorted select.
It make sense when filter produce a lot pf results.
So, full table will be scanned by ordered index, but result will be not
materialized on map nodes.
Using the example of the first message, it looks like this:
SELECT JQ._KEY
FROM "JobQueue".JOBQUEUE AS JQ USE INDEX(IDX_JQ_QUEUED)
INNER JOIN "Jobs".JOBS AS J ON JQ.jobid=J._key
WHERE JQ.STATUS = 2
ORDER BY JQ.QUEUED ASC
LIMIT 20
When IDX_JQ_QUEUED was created on JOBQUEUE (QUEUED).
I cannot guarantee he performance boost. Just to try.
On 12.04.2021 23:02, Thomas Kramer wrote:
Hi Ilya,
unfortunately this also didn't help improving query performance. Not
sure what else I can try. Or maybe it is expected? In my opinion it
shouldn't take that long as the query without the ORDER BY clause is
super fast. Since there is a index on the order field I would expect
this should be fast.
Btw, I noticed that for some other queries the first call has this
long execution while on every following call with the same SQL
statement it returns within half a second. But I guess this is caching
related and not the issue I see here?
Best Regards,
Thomas.
On 12.04.21 13:15, Ilya Kasnacheev wrote:
Hello!
I think you can try a (QUEUEID, STATUS) index.
Or maybe a (STATUS, QUEUEID), probably makes sense to try both.
Regards,
--
Ilya Kasnacheev
сб, 10 апр. 2021 г. в 00:22, <mailto:don.tequ...@gmx.de>>:
The QUEUED field is a BIGINT that contains timestamp from
System.currentTimeMillis(), so it should be pretty easy to sort,
shouldn’t it? Looks like the field STATUS (used in where
clause) and field QUEUED (used in order clause) are not working
optimal when used together. Does this make sense? Do I need to
create an index on both together?
I will take a look at UNION and WHERE EXISTS, I‘m not familiar
with these statements.
Thanks!
On 09.04.21 at 17:37, Ilya Kasnacheev wrote:
From: "Ilya Kasnacheev" mailto:ilya.kasnach...@gmail.com>>
Date: 9. April 2021
To: user@ignite.apache.org <mailto:user@ignite.apache.org>
Cc:
Subject: Re: SQL query performance with JOIN and ORDER BY or WHERE
Hello!
ORDER BY will have to sort the whole table.
I think that using index on QUEUED will be optimal here. What is
the selectivity of this field? If it s boolean, you might as well
use UNION queries.
Have you tried joining JOBS via WHERE EXISTS?
Regards,
--
Ilya Kasnacheev
пт, 9 апр. 2021 г. в 01:03, DonTequila mailto:don.tequ...@gmx.de>>:
Hi,
I have a SQL performance issue. There are indexes on both
fields that are
used in the ORDER BY clause and the WHERE clause.
The following statement takes about 133941 ms with several
warnings from
IgniteH2Indexing:
SELECT JQ._KEY
FROM "JobQueue".JOBQUEUE AS JQ
INNER JOIN "Jobs".JOBS AS J ON JQ.jobid=J._key
WHERE JQ.STATUS = 2
ORDER BY JQ.QUEUED ASC
LIMIT 20
But when I remove the ORDER BY part or the WHERE part from
the statement it
returns in <10ms.
What may I do wrong?
Thanks,
Thomas.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
<http://apache-ignite-users.70518.x6.nabble.com/>
--
Taras Ledkov
Mail-To: tled...@gridgain.com
Re: SQL query performance with JOIN and ORDER BY or WHERE
Hi Ilya,
unfortunately this also didn't help improving query performance. Not
sure what else I can try. Or maybe it is expected? In my opinion it
shouldn't take that long as the query without the ORDER BY clause is
super fast. Since there is a index on the order field I would expect
this should be fast.
Btw, I noticed that for some other queries the first call has this long
execution while on every following call with the same SQL statement it
returns within half a second. But I guess this is caching related and
not the issue I see here?
Best Regards,
Thomas.
On 12.04.21 13:15, Ilya Kasnacheev wrote:
Hello!
I think you can try a (QUEUEID, STATUS) index.
Or maybe a (STATUS, QUEUEID), probably makes sense to try both.
Regards,
--
Ilya Kasnacheev
сб, 10 апр. 2021 г. в 00:22, mailto:don.tequ...@gmx.de>>:
The QUEUED field is a BIGINT that contains timestamp from
System.currentTimeMillis(), so it should be pretty easy to sort,
shouldn’t it? Looks like the field STATUS (used in where
clause) and field QUEUED (used in order clause) are not working
optimal when used together. Does this make sense? Do I need to
create an index on both together?
I will take a look at UNION and WHERE EXISTS, I‘m not familiar
with these statements.
Thanks!
On 09.04.21 at 17:37, Ilya Kasnacheev wrote:
From: "Ilya Kasnacheev" mailto:ilya.kasnach...@gmail.com>>
Date: 9. April 2021
To: user@ignite.apache.org <mailto:user@ignite.apache.org>
Cc:
Subject: Re: SQL query performance with JOIN and ORDER BY or WHERE
Hello!
ORDER BY will have to sort the whole table.
I think that using index on QUEUED will be optimal here. What is
the selectivity of this field? If it s boolean, you might as well
use UNION queries.
Have you tried joining JOBS via WHERE EXISTS?
Regards,
--
Ilya Kasnacheev
пт, 9 апр. 2021 г. в 01:03, DonTequila mailto:don.tequ...@gmx.de>>:
Hi,
I have a SQL performance issue. There are indexes on both
fields that are
used in the ORDER BY clause and the WHERE clause.
The following statement takes about 133941 ms with several
warnings from
IgniteH2Indexing:
SELECT JQ._KEY
FROM "JobQueue".JOBQUEUE AS JQ
INNER JOIN "Jobs".JOBS AS J ON JQ.jobid=J._key
WHERE JQ.STATUS = 2
ORDER BY JQ.QUEUED ASC
LIMIT 20
But when I remove the ORDER BY part or the WHERE part from the
statement it
returns in <10ms.
What may I do wrong?
Thanks,
Thomas.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
<http://apache-ignite-users.70518.x6.nabble.com/>
Re: Re: SQL query performance with JOIN and ORDER BY or WHERE
Hello! I think you can try a (QUEUEID, STATUS) index. Or maybe a (STATUS, QUEUEID), probably makes sense to try both. Regards, -- Ilya Kasnacheev сб, 10 апр. 2021 г. в 00:22, : > The QUEUED field is a BIGINT that contains timestamp from > System.currentTimeMillis(), so it should be pretty easy to sort, shouldn’t > it? Looks like the field STATUS (used in where clause) and field QUEUED > (used in order clause) are not working optimal when used together. Does > this make sense? Do I need to create an index on both together? > > I will take a look at UNION and WHERE EXISTS, I‘m not familiar with these > statements. > > Thanks! > > > On 09.04.21 at 17:37, Ilya Kasnacheev wrote: > > From: "Ilya Kasnacheev" > Date: 9. April 2021 > To: user@ignite.apache.org > Cc: > Subject: Re: SQL query performance with JOIN and ORDER BY or WHERE > Hello! > > ORDER BY will have to sort the whole table. > > I think that using index on QUEUED will be optimal here. What is the > selectivity of this field? If it s boolean, you might as well use UNION > queries. > > Have you tried joining JOBS via WHERE EXISTS? > > Regards, > -- > Ilya Kasnacheev > > > > пт, 9 апр. 2021 г. в 01:03, DonTequila : > >> Hi, >> >> I have a SQL performance issue. There are indexes on both fields that are >> used in the ORDER BY clause and the WHERE clause. >> >> The following statement takes about 133941 ms with several warnings from >> IgniteH2Indexing: >> >> SELECT JQ._KEY >> FROM "JobQueue".JOBQUEUE AS JQ >> INNER JOIN "Jobs".JOBS AS J ON JQ.jobid=J._key >> WHERE JQ.STATUS = 2 >> ORDER BY JQ.QUEUED ASC >> LIMIT 20 >> >> But when I remove the ORDER BY part or the WHERE part from the statement >> it >> returns in <10ms. >> >> What may I do wrong? >> >> Thanks, >> Thomas. >> >> >> >> >> -- >> Sent from: http://apache-ignite-users.70518.x6.nabble.com/ >> >
Re: Re: SQL query performance with JOIN and ORDER BY or WHERE
The QUEUED field is a BIGINT that contains timestamp from System.currentTimeMillis(), so it should be pretty easy to sort, shouldn’t it? Looks like the field STATUS (used in where clause) and field QUEUED (used in order clause) are not working optimal when used together. Does this make sense? Do I need to create an index on both together?I will take a look at UNION and WHERE EXISTS, I‘m not familiar with these statements.Thanks!On 09.04.21 at 17:37, Ilya Kasnacheev wrote: From: "Ilya Kasnacheev" Date: 9. April 2021To: user@ignite.apache.orgCc: Subject: Re: SQL query performance with JOIN and ORDER BY or WHERE Hello! ORDER BY will have to sort the whole table. I think that using index on QUEUED will be optimal here. What is the selectivity of this field? If it s boolean, you might as well use UNION queries. Have you tried joining JOBS via WHERE EXISTS? Regards, -- Ilya Kasnacheev пт, 9 апр. 2021 г. в 01:03, DonTequila <don.tequ...@gmx.de>: Hi, I have a SQL performance issue. There are indexes on both fields that are used in the ORDER BY clause and the WHERE clause. The following statement takes about 133941 ms with several warnings from IgniteH2Indexing: SELECT JQ._KEY FROM "JobQueue".JOBQUEUE AS JQ INNER JOIN "Jobs".JOBS AS J ON JQ.jobid=J._key WHERE JQ.STATUS = 2 ORDER BY JQ.QUEUED ASC LIMIT 20 But when I remove the ORDER BY part or the WHERE part from the statement it returns in <10ms. What may I do wrong? Thanks, Thomas. -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: SQL query performance with JOIN and ORDER BY or WHERE
Hello! ORDER BY will have to sort the whole table. I think that using index on QUEUED will be optimal here. What is the selectivity of this field? If it s boolean, you might as well use UNION queries. Have you tried joining JOBS via WHERE EXISTS? Regards, -- Ilya Kasnacheev пт, 9 апр. 2021 г. в 01:03, DonTequila : > Hi, > > I have a SQL performance issue. There are indexes on both fields that are > used in the ORDER BY clause and the WHERE clause. > > The following statement takes about 133941 ms with several warnings from > IgniteH2Indexing: > > SELECT JQ._KEY > FROM "JobQueue".JOBQUEUE AS JQ > INNER JOIN "Jobs".JOBS AS J ON JQ.jobid=J._key > WHERE JQ.STATUS = 2 > ORDER BY JQ.QUEUED ASC > LIMIT 20 > > But when I remove the ORDER BY part or the WHERE part from the statement it > returns in <10ms. > > What may I do wrong? > > Thanks, > Thomas. > > > > > -- > Sent from: http://apache-ignite-users.70518.x6.nabble.com/ >
