ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sergi Vladykin <sergi.vlady...@gmail.com>
Subject Re: Correlated Subquery Performance
Date Sun, 26 Jun 2016 10:13:29 GMT
Barrett,

For this exact query you will achieve the best results if you will have two
group indexes: (internalRobotName, internalTimeStamp DESC) and (
internalRobotName, internalMissionId). Also it will make sense to change
ordering to

ORDER BY internalRobotName, internalTimeStamp DESC

This way you will have the same query semantics but use index sort.

Sergi

On Sun, Jun 26, 2016 at 12:43 PM, Sergi Vladykin <sergi.vladykin@gmail.com>
wrote:

> Andrey,
>
> Did you try these "tricks" with Ignite? I think not, because they will not
> work.
> Please stop suggesting to our users anything that you did not prove
> yourself to be a correct solution.
>
> Sergi
>
> On Sun, Jun 26, 2016 at 11:14 AM, AndreyVel <andrey4vel@gmail.com> wrote:
>
>> H2 engine chooses index internalRobotName_idx because you have expression
>> WHERE (S1.INTERNALROBOTNAME = 'gp1')
>> In current H2 version  it's not possible to specify index hints. You can
>> use
>> same tricks.
>>
>> For complex queries more convenient to use temp tables.
>>
>>     CREATE TEMP TABLE IF NOT EXISTS TEMP(ID INT PRIMARY KEY, NAME
>> VARCHAR);
>>     CREATE INDEX IF NOT EXISTS IDX_TEMP_NAME ON TEMP(NAME);
>>
>>     INSERT INTO TEMP(ID, NAME)
>>     SELECT P1.ID, P1.FIRST_NAME FROM PERSON P1 WHERE ...
>>
>>
>> H2 SQL Grammar http://www.h2database.com/html/grammar.html#create_table
>>
>>
>>
>> --
>> View this message in context:
>> http://apache-ignite-users.70518.x6.nabble.com/Correlated-Subquery-Performance-tp5877p5898.html
>> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>
>
>

Mime
View raw message