[jira] [Commented] (IGNITE-4509) SQL: query with condition on affinity columns and without joins and subqueries can be replaced with GET
[
https://issues.apache.org/jira/browse/IGNITE-4509?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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
[
https://issues.apache.org/jira/browse/IGNITE-4509?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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
[
https://issues.apache.org/jira/browse/IGNITE-4509?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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)
