Re: SQL query performance with JOIN and ORDER BY or WHERE

[Thomas Kramer]

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
<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" <ilya.kasnach...@gmail.com
    <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 <don.tequ...@gmx.de
    <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/>