[jira] [Commented] (IGNITE-4509) SQL: query with condition on affinity columns and without joins and subqueries can be replaced with GET

[Sergey Kalashnikov (JIRA)]

[ 
https://issues.apache.org/jira/browse/IGNITE-4509?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16026316#comment-16026316
 ] 

Sergey Kalashnikov commented on IGNITE-4509:


*Important note*

The implemented approach differs from the one proposed in description/title.
The new approach attempts to derive a set of cache partitions that the given 
query covers 
from the query itself and its arguments. 
The query will be sent to execution only to those cluster nodes which have the 
derived partitions.

Thus if there is a condition in the {{where}} clause of form {{"_key = ?" or 
"affinityKey = ?"}}
the parameter (after binding) or a constant to the right is used to calculate 
the cache partition.

Multiple such conditions may be present. 
Additional non-key filters can be used in query and still the query may be 
optimized this way. 
(f.ex. {{"_key = ? and field = ?"}} as opposed to {{"_key = ? or field = ?"}} 
which cannot be optimized).

The approach does not put any constraints on the {{select expression_list}} 
clause of the query.
However, in the scope of this ticket, the queries with joins are not optimized 
this way (will be done in the scope of 
[https://issues.apache.org/jira/browse/IGNITE-4510]).

The {{cache.get()}} approach was considered not feasible for the following 
reasons:
1) requires specific {{cache.get()}} implementation that would accept a set of 
fields to retrieve,
which is essentially some sort of sql-like implementation;

2) limited application. Only the simplest form of queries can be optimized that 
way, 
i.e. no expressions over columns, no functions, just simplest {{_key=?}};

3) that kind of functionality doesn't fit seemlessly to the flow of sql query 
execution.


> SQL: query with condition on affinity columns and without joins and 
> subqueries can be replaced with GET
> ---
>
> Key: IGNITE-4509
> URL: https://issues.apache.org/jira/browse/IGNITE-4509
> Project: Ignite
>  Issue Type: Task
>  Components: sql
>Affects Versions: 1.8
>Reporter: Vladimir Ozerov
>Assignee: Sergey Kalashnikov
>  Labels: important, performance, sql
> Fix For: 2.1
>
>
> Simple SQL queries usually demonstrate negative scalability when more nodes 
> are added. 
> Consider the following query:
> {code}
> SELECT * FROM Person p
> WHERE p.id = ?
> {code}
> If {{Person.id}} is affinity field, then query can be replaced with plain 
> {{IgniteCache.get}} or some specialized version of GET, which will return 
> only desired fields. This optimization will guarantee smooth scale with any 
> number of nodes.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (IGNITE-4509) SQL: query with condition on affinity columns and without joins and subqueries can be replaced with GET

[Sergey Kalashnikov (JIRA)]

[ 
https://issues.apache.org/jira/browse/IGNITE-4509?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16007807#comment-16007807
 ] 

Sergey Kalashnikov commented on IGNITE-4509:


[~sergi.vladykin], [~vozerov] Can you please review the changes?

> SQL: query with condition on affinity columns and without joins and 
> subqueries can be replaced with GET
> ---
>
> Key: IGNITE-4509
> URL: https://issues.apache.org/jira/browse/IGNITE-4509
> Project: Ignite
>  Issue Type: Task
>  Components: sql
>Affects Versions: 1.8
>Reporter: Vladimir Ozerov
>Assignee: Sergey Kalashnikov
>  Labels: performance, sql
> Fix For: 2.1
>
>
> Simple SQL queries usually demonstrate negative scalability when more nodes 
> are added. 
> Consider the following query:
> {code}
> SELECT * FROM Person p
> WHERE p.id = ?
> {code}
> If {{Person.id}} is affinity field, then query can be replaced with plain 
> {{IgniteCache.get}} or some specialized version of GET, which will return 
> only desired fields. This optimization will guarantee smooth scale with any 
> number of nodes.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (IGNITE-4509) SQL: query with condition on affinity columns and without joins and subqueries can be replaced with GET

[ASF GitHub Bot (JIRA)]

[ 
https://issues.apache.org/jira/browse/IGNITE-4509?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15998818#comment-15998818
 ] 

ASF GitHub Bot commented on IGNITE-4509:


GitHub user skalashnikov opened a pull request:

https://github.com/apache/ignite/pull/1916

IGNITE-4509: send unicast sql request when partition can be deduced f…

…rom the query

You can merge this pull request into a Git repository by running:

$ git pull https://github.com/gridgain/apache-ignite ignite-4509

Alternatively you can review and apply these changes as the patch at:

https://github.com/apache/ignite/pull/1916.patch

To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:

This closes #1916


commit 4a1768d28550286d69134ff62ee29994a0faa188
Author: skalashnikov 
Date:   2017-05-04T11:42:40Z

IGNITE-4509: send unicast sql request when partition can be deduced from 
the query




> SQL: query with condition on affinity columns and without joins and 
> subqueries can be replaced with GET
> ---
>
> Key: IGNITE-4509
> URL: https://issues.apache.org/jira/browse/IGNITE-4509
> Project: Ignite
>  Issue Type: Task
>  Components: SQL
>Affects Versions: 1.8
>Reporter: Vladimir Ozerov
>Assignee: Sergey Kalashnikov
>  Labels: performance, sql
> Fix For: 2.1
>
>
> Simple SQL queries usually demonstrate negative scalability when more nodes 
> are added. 
> Consider the following query:
> {code}
> SELECT * FROM Person p
> WHERE p.id = ?
> {code}
> If {{Person.id}} is affinity field, then query can be replaced with plain 
> {{IgniteCache.get}} or some specialized version of GET, which will return 
> only desired fields. This optimization will guarantee smooth scale with any 
> number of nodes.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)