You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@kylin.apache.org by dong wang <el...@gmail.com> on 2015/05/06 11:30:06 UTC
about the IN syntax
as we know, several users mentioned about the performance of IN syntax such
as
city_id in
(151,261,299,3,51,66,156,174,254,325,105,332,21,55,89,92,115,118,134,168,170,172,220,256,282,324,19,337,8,9,73,99,100,101,107,133,163,178,233,302,333,239,184,102,5,11,31,76,227,328,75,331,20,48,49,50,120,123,129,140,153,197,219,225,275,285,289,290,305,320,340,176,245,64,24,33,141,186,259,338,23,203,268,67,30,44,137,182,258,288,309,329,341,10,135,2,22,83,98,247,250,112,235,240,263,274,276,314,322,15,61,342,12,47,114,242,243,262,269,300,152,164,175,226,224,68,93,94,143,147,228,307,318,326,124,185,255,32,80,116,251,334,28,74,127,131,234,237,279,130,272,81,104,177,218,253,317,144,213,303,306,78,248,60,110,113,157,159,180,208,222,283,162,291,294,1,6,72,199,206,278,292,214,17,58,95,136,150,189,201,212,229,14,25,241,265,271,202,13,53,85,103,142,160,217,231,304,335,91,190,293,194,57,69,70,71,106,122,125,139,183,238,252,277,207,35,62,138,166,232,249,284,287,45,79,86,128,132,215,221,244,281,158,84,97,29,46,148,204,310,312,315,27,321,0,311,343,155,63,82,193,211,216,246,316,165,43,87,169,171,191,192,200,210,230,236,257,330,205,313,109,16,154,54,56,111,126,196,209,319,344,34,88,117,119,149,161,181,187,286,339,41,336,4,26,36,39,96,108,188,264,267,273,295,301,308,37,18,38,195,223,296,323,52,145,146,198,7,40,42,59,90,121,173,260,270,297,298,327,77,167,179,266,280,65)
so far, do we have some workaround ways to solve it right now? such as
city_id=151 or city_id=261 or city_id=299 or city_id=..., do anyone know
about the performance about this way?
Re: 答复: about the IN syntax
Posted by Li Yang <li...@apache.org>.
> For problem in 1., if we split the 'IN' clause into many small ranges,
would this increase the number of hbase rpc calls that might hurt the
performance?
Yes, too many small scans will hurt performance too. HBase is not super
good at random accesses. That's why we attempted to merge scans in the
first place.
On Thu, May 7, 2015 at 4:34 PM, Huang Hua <hu...@mininglamp.com> wrote:
> Would like to share my two cents worth on this topic:
> 1. for 'IN' values, the current strategy of Kylin storage engine just
> picks the min and max values from them to come up with the hbase scan
> range. This is usually bad, as you can imagine that in many cases you could
> have ‘IN' clause containing some random values, let's say a small value(e.g
> 1) and a large number(e.g. 1000000), and eventually the hbase scan range
> becomes [1, 1000000] meaning it would scan a pretty large range in the
> hbase table.
> This could be solved by divide the 'IN' values into many small ranges, for
> example, the 'IN' clause is (a, b, c, d, e), initially Kylin would use [a,
> e] as scan range, but it's better to divide it into [a, c] and [d, e]
> because scan_count([a, c]) + scan_count([d, e]) < scan_count([a, e]) as we
> ignore the [c, d] scan.
> And for cases that the number of 'IN' values is relatively small, we could
> even break the 'IN' clause into ranges where each range is just a single
> value scan, such as [a, a], [b, b], [c, c], [d, d] and [e, e] in the above
> example.
>
> 2. and also, for 'IN' values that are not in the corresponding dictionary,
> Kylin by default will use either '\x00' or '\xFF' to get thier scan values.
> If in some cases that the not-in-dictionary values happen to the min or the
> max value among the 'IN' values, Kylin would generate scan ranges like:
> ['\x00\x00\x00...', actual byte value of the max 'IN' value] - the
> not-in-dictionary value happen to be the min
> [actual byte value of the min 'IN' value, '\xFF\xFF\xFF..'] - the
> not-in-dictionary value happen to be the max
> ['\x00\x00\x00...', '\xFF\xFF\xFF..'] - the not-in-dictionary value happen
> to be the min as well as the max
> The last case is the worst because it's essentially a full table scan!!
> To avoid such issue, a quick fix can be made to drop the not-in-dictionary
> values before generating hbase scan range. And based on it, we can make a
> further optimization that as long as there is at least one andFilter is
> empty(after we drop the not-in-dictionary values), we can simply assert
> that the whole andFilters is empty to avoid an unnecessary hbase scan.
> However, this strategy only works for 'EQ', 'IN'.
> For 'LT', 'LTE', 'GT', 'GTE', you can't simply drop the not-in-dictionary
> values, instead you have to replace them with the some sort of closest
> smaller or bigger values by calling "final public int getIdFromValue(T
> value, int roundingFlag)" of "Dictionary" class. We've implemented a patch
> for this issue, and it works very well.
>
> For problem in 1., if we split the 'IN' clause into many small ranges,
> would this increase the number of hbase rpc calls that might hurt the
> performance?
>
> Best Regards,
> Hua
> > -----邮件原件-----
> > 发件人: dev-return-1529-
> > huanghua=mininglamp.com@kylin.incubator.apache.org [mailto:dev-return-
> > 1529-huanghua=mininglamp.com@kylin.incubator.apache.org] 代表 Li Yang
> > 发送时间: 2015年5月7日 15:13
> > 收件人: dev@kylin.incubator.apache.org
> > 主题: Re: about the IN syntax
> >
> > We shall add this to next sprint plan. It has become a common issue.
> >
> > As I mentioned, the right solution should be merge small gaps of scan
> ranges.
> > And the merge threshold should be configurable.
> >
> > On Wed, May 6, 2015 at 5:58 PM, dong wang <el...@gmail.com>
> > wrote:
> >
> > > thanks, I will directly skip the try.
> > >
> > > 2015-05-06 17:51 GMT+08:00 hongbin ma <ma...@apache.org>:
> > >
> > > > i guess the above way won't help, because internally Kylin
> > > > translates IN clause to ORs
> > > >
> > > > On Wed, May 6, 2015 at 5:50 PM, dong wang <el...@gmail.com>
> > > wrote:
> > > >
> > > > > if enough time, I will try to test the way mentioned above~
> > > > >
> > > > > 2015-05-06 17:43 GMT+08:00 hongbin ma <ma...@apache.org>:
> > > > >
> > > > > > So far we do not have a workaround for this.
> > > > > > but as you said, several user have reported the perf issue of IN
> > > > clause,
> > > > > > I've created a ticket for this
> > > > > > https://issues.apache.org/jira/browse/KYLIN-752
> > > > > >
> > > > > > On Wed, May 6, 2015 at 5:30 PM, dong wang
> > > > > > <el...@gmail.com>
> > > > > wrote:
> > > > > >
> > > > > > > as we know, several users mentioned about the performance of
> > > > > > > IN
> > > > syntax
> > > > > > such
> > > > > > > as
> > > > > > > city_id in
> > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > > (151,261,299,3,51,66,156,174,254,325,105,332,21,55,89,92,115,118,134,1
> > > 68,170,172,220,256,282,324,19,337,8,9,73,99,100,101,107,133,163,178,23
> > > 3,302,333,239,184,102,5,11,31,76,227,328,75,331,20,48,49,50,120,123,12
> > > 9,140,153,197,219,225,275,285,289,290,305,320,340,176,245,64,24,33,141
> > > ,186,259,338,23,203,268,67,30,44,137,182,258,288,309,329,341,10,135,2,
> > > 22,83,98,247,250,112,235,240,263,274,276,314,322,15,61,342,12,47,114,2
> > > 42,243,262,269,300,152,164,175,226,224,68,93,94,143,147,228,307,318,32
> > > 6,124,185,255,32,80,116,251,334,28,74,127,131,234,237,279,130,272,81,1
> > > 04,177,218,253,317,144,213,303,306,78,248,60,110,113,157,159,180,208,2
> > > 22,283,162,291,294,1,6,72,199,206,278,292,214,17,58,95,136,150,189,201
> > > ,212,229,14,25,241,265,271,202,13,53,85,103,142,160,217,231,304,335,91
> > > ,190,293,194,57,69,70,71,106,122,125,139,183,238,252,277,207,35,62,138
> > > ,166,232,249,284,287,45,79,86,128,132,215,221,244,281,158,84,97,29,46,
> > > 148,204,310,312,315,27,321,0,311,343,155,63,82,193,211,216,246,316,165
> > > ,43,87,169,171,191,192,200,210,230,236,257,330,205,313,109,16,154,54,5
> > > 6,111,126,196,209,319,344,34,88,117,119,149,161,181,187,286,339,41,336
> > > ,4,26,36,39,96,108,188,264,267,273,295,301,308,37,18,38,195,223,296,32
> > > 3,52,145,146,198,7,40,42,59,90,121,173,260,270,297,298,327,77,167,179,
> > > 266,280,65)
> > > > > > >
> > > > > > >
> > > > > > > so far, do we have some workaround ways to solve it right now?
> > > > > > > such
> > > > as
> > > > > > > city_id=151 or city_id=261 or city_id=299 or city_id=..., do
> > > anyone
> > > > > know
> > > > > > > about the performance about this way?
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Regards,
> > > > > >
> > > > > > *Bin Mahone | 马洪宾*
> > > > > > Apache Kylin: http://kylin.io
> > > > > > Github: https://github.com/binmahone
> > > > > >
> > > > >
> > > >
> > > >
> > > >
> > > > --
> > > > Regards,
> > > >
> > > > *Bin Mahone | 马洪宾*
> > > > Apache Kylin: http://kylin.io
> > > > Github: https://github.com/binmahone
> > > >
> > >
>
>
>
答复: about the IN syntax
Posted by Huang Hua <hu...@mininglamp.com>.
Would like to share my two cents worth on this topic:
1. for 'IN' values, the current strategy of Kylin storage engine just picks the min and max values from them to come up with the hbase scan range. This is usually bad, as you can imagine that in many cases you could have ‘IN' clause containing some random values, let's say a small value(e.g 1) and a large number(e.g. 1000000), and eventually the hbase scan range becomes [1, 1000000] meaning it would scan a pretty large range in the hbase table.
This could be solved by divide the 'IN' values into many small ranges, for example, the 'IN' clause is (a, b, c, d, e), initially Kylin would use [a, e] as scan range, but it's better to divide it into [a, c] and [d, e] because scan_count([a, c]) + scan_count([d, e]) < scan_count([a, e]) as we ignore the [c, d] scan.
And for cases that the number of 'IN' values is relatively small, we could even break the 'IN' clause into ranges where each range is just a single value scan, such as [a, a], [b, b], [c, c], [d, d] and [e, e] in the above example.
2. and also, for 'IN' values that are not in the corresponding dictionary, Kylin by default will use either '\x00' or '\xFF' to get thier scan values. If in some cases that the not-in-dictionary values happen to the min or the max value among the 'IN' values, Kylin would generate scan ranges like:
['\x00\x00\x00...', actual byte value of the max 'IN' value] - the not-in-dictionary value happen to be the min
[actual byte value of the min 'IN' value, '\xFF\xFF\xFF..'] - the not-in-dictionary value happen to be the max
['\x00\x00\x00...', '\xFF\xFF\xFF..'] - the not-in-dictionary value happen to be the min as well as the max
The last case is the worst because it's essentially a full table scan!!
To avoid such issue, a quick fix can be made to drop the not-in-dictionary values before generating hbase scan range. And based on it, we can make a further optimization that as long as there is at least one andFilter is empty(after we drop the not-in-dictionary values), we can simply assert that the whole andFilters is empty to avoid an unnecessary hbase scan. However, this strategy only works for 'EQ', 'IN'.
For 'LT', 'LTE', 'GT', 'GTE', you can't simply drop the not-in-dictionary values, instead you have to replace them with the some sort of closest smaller or bigger values by calling "final public int getIdFromValue(T value, int roundingFlag)" of "Dictionary" class. We've implemented a patch for this issue, and it works very well.
For problem in 1., if we split the 'IN' clause into many small ranges, would this increase the number of hbase rpc calls that might hurt the performance?
Best Regards,
Hua
> -----邮件原件-----
> 发件人: dev-return-1529-
> huanghua=mininglamp.com@kylin.incubator.apache.org [mailto:dev-return-
> 1529-huanghua=mininglamp.com@kylin.incubator.apache.org] 代表 Li Yang
> 发送时间: 2015年5月7日 15:13
> 收件人: dev@kylin.incubator.apache.org
> 主题: Re: about the IN syntax
>
> We shall add this to next sprint plan. It has become a common issue.
>
> As I mentioned, the right solution should be merge small gaps of scan ranges.
> And the merge threshold should be configurable.
>
> On Wed, May 6, 2015 at 5:58 PM, dong wang <el...@gmail.com>
> wrote:
>
> > thanks, I will directly skip the try.
> >
> > 2015-05-06 17:51 GMT+08:00 hongbin ma <ma...@apache.org>:
> >
> > > i guess the above way won't help, because internally Kylin
> > > translates IN clause to ORs
> > >
> > > On Wed, May 6, 2015 at 5:50 PM, dong wang <el...@gmail.com>
> > wrote:
> > >
> > > > if enough time, I will try to test the way mentioned above~
> > > >
> > > > 2015-05-06 17:43 GMT+08:00 hongbin ma <ma...@apache.org>:
> > > >
> > > > > So far we do not have a workaround for this.
> > > > > but as you said, several user have reported the perf issue of IN
> > > clause,
> > > > > I've created a ticket for this
> > > > > https://issues.apache.org/jira/browse/KYLIN-752
> > > > >
> > > > > On Wed, May 6, 2015 at 5:30 PM, dong wang
> > > > > <el...@gmail.com>
> > > > wrote:
> > > > >
> > > > > > as we know, several users mentioned about the performance of
> > > > > > IN
> > > syntax
> > > > > such
> > > > > > as
> > > > > > city_id in
> > > > > >
> > > > > >
> > > > >
> > > >
> > >
> > (151,261,299,3,51,66,156,174,254,325,105,332,21,55,89,92,115,118,134,1
> > 68,170,172,220,256,282,324,19,337,8,9,73,99,100,101,107,133,163,178,23
> > 3,302,333,239,184,102,5,11,31,76,227,328,75,331,20,48,49,50,120,123,12
> > 9,140,153,197,219,225,275,285,289,290,305,320,340,176,245,64,24,33,141
> > ,186,259,338,23,203,268,67,30,44,137,182,258,288,309,329,341,10,135,2,
> > 22,83,98,247,250,112,235,240,263,274,276,314,322,15,61,342,12,47,114,2
> > 42,243,262,269,300,152,164,175,226,224,68,93,94,143,147,228,307,318,32
> > 6,124,185,255,32,80,116,251,334,28,74,127,131,234,237,279,130,272,81,1
> > 04,177,218,253,317,144,213,303,306,78,248,60,110,113,157,159,180,208,2
> > 22,283,162,291,294,1,6,72,199,206,278,292,214,17,58,95,136,150,189,201
> > ,212,229,14,25,241,265,271,202,13,53,85,103,142,160,217,231,304,335,91
> > ,190,293,194,57,69,70,71,106,122,125,139,183,238,252,277,207,35,62,138
> > ,166,232,249,284,287,45,79,86,128,132,215,221,244,281,158,84,97,29,46,
> > 148,204,310,312,315,27,321,0,311,343,155,63,82,193,211,216,246,316,165
> > ,43,87,169,171,191,192,200,210,230,236,257,330,205,313,109,16,154,54,5
> > 6,111,126,196,209,319,344,34,88,117,119,149,161,181,187,286,339,41,336
> > ,4,26,36,39,96,108,188,264,267,273,295,301,308,37,18,38,195,223,296,32
> > 3,52,145,146,198,7,40,42,59,90,121,173,260,270,297,298,327,77,167,179,
> > 266,280,65)
> > > > > >
> > > > > >
> > > > > > so far, do we have some workaround ways to solve it right now?
> > > > > > such
> > > as
> > > > > > city_id=151 or city_id=261 or city_id=299 or city_id=..., do
> > anyone
> > > > know
> > > > > > about the performance about this way?
> > > > > >
> > > > >
> > > > >
> > > > >
> > > > > --
> > > > > Regards,
> > > > >
> > > > > *Bin Mahone | 马洪宾*
> > > > > Apache Kylin: http://kylin.io
> > > > > Github: https://github.com/binmahone
> > > > >
> > > >
> > >
> > >
> > >
> > > --
> > > Regards,
> > >
> > > *Bin Mahone | 马洪宾*
> > > Apache Kylin: http://kylin.io
> > > Github: https://github.com/binmahone
> > >
> >
Re: about the IN syntax
Posted by Li Yang <li...@apache.org>.
We shall add this to next sprint plan. It has become a common issue.
As I mentioned, the right solution should be merge small gaps of scan
ranges. And the merge threshold should be configurable.
On Wed, May 6, 2015 at 5:58 PM, dong wang <el...@gmail.com> wrote:
> thanks, I will directly skip the try.
>
> 2015-05-06 17:51 GMT+08:00 hongbin ma <ma...@apache.org>:
>
> > i guess the above way won't help, because internally Kylin translates IN
> > clause to ORs
> >
> > On Wed, May 6, 2015 at 5:50 PM, dong wang <el...@gmail.com>
> wrote:
> >
> > > if enough time, I will try to test the way mentioned above~
> > >
> > > 2015-05-06 17:43 GMT+08:00 hongbin ma <ma...@apache.org>:
> > >
> > > > So far we do not have a workaround for this.
> > > > but as you said, several user have reported the perf issue of IN
> > clause,
> > > > I've created a ticket for this
> > > > https://issues.apache.org/jira/browse/KYLIN-752
> > > >
> > > > On Wed, May 6, 2015 at 5:30 PM, dong wang <el...@gmail.com>
> > > wrote:
> > > >
> > > > > as we know, several users mentioned about the performance of IN
> > syntax
> > > > such
> > > > > as
> > > > > city_id in
> > > > >
> > > > >
> > > >
> > >
> >
> (151,261,299,3,51,66,156,174,254,325,105,332,21,55,89,92,115,118,134,168,170,172,220,256,282,324,19,337,8,9,73,99,100,101,107,133,163,178,233,302,333,239,184,102,5,11,31,76,227,328,75,331,20,48,49,50,120,123,129,140,153,197,219,225,275,285,289,290,305,320,340,176,245,64,24,33,141,186,259,338,23,203,268,67,30,44,137,182,258,288,309,329,341,10,135,2,22,83,98,247,250,112,235,240,263,274,276,314,322,15,61,342,12,47,114,242,243,262,269,300,152,164,175,226,224,68,93,94,143,147,228,307,318,326,124,185,255,32,80,116,251,334,28,74,127,131,234,237,279,130,272,81,104,177,218,253,317,144,213,303,306,78,248,60,110,113,157,159,180,208,222,283,162,291,294,1,6,72,199,206,278,292,214,17,58,95,136,150,189,201,212,229,14,25,241,265,271,202,13,53,85,103,142,160,217,231,304,335,91,190,293,194,57,69,70,71,106,122,125,139,183,238,252,277,207,35,62,138,166,232,249,284,287,45,79,86,128,132,215,221,244,281,158,84,97,29,46,148,204,310,312,315,27,321,0,311,343,155,63,82,193,211,216,246,316,165,43,87,169,171,191,192,200,210,230,236,257,330,205,313,109,16,154,54,56,111,126,196,209,319,344,34,88,117,119,149,161,181,187,286,339,41,336,4,26,36,39,96,108,188,264,267,273,295,301,308,37,18,38,195,223,296,323,52,145,146,198,7,40,42,59,90,121,173,260,270,297,298,327,77,167,179,266,280,65)
> > > > >
> > > > >
> > > > > so far, do we have some workaround ways to solve it right now? such
> > as
> > > > > city_id=151 or city_id=261 or city_id=299 or city_id=..., do
> anyone
> > > know
> > > > > about the performance about this way?
> > > > >
> > > >
> > > >
> > > >
> > > > --
> > > > Regards,
> > > >
> > > > *Bin Mahone | 马洪宾*
> > > > Apache Kylin: http://kylin.io
> > > > Github: https://github.com/binmahone
> > > >
> > >
> >
> >
> >
> > --
> > Regards,
> >
> > *Bin Mahone | 马洪宾*
> > Apache Kylin: http://kylin.io
> > Github: https://github.com/binmahone
> >
>
Re: about the IN syntax
Posted by dong wang <el...@gmail.com>.
thanks, I will directly skip the try.
2015-05-06 17:51 GMT+08:00 hongbin ma <ma...@apache.org>:
> i guess the above way won't help, because internally Kylin translates IN
> clause to ORs
>
> On Wed, May 6, 2015 at 5:50 PM, dong wang <el...@gmail.com> wrote:
>
> > if enough time, I will try to test the way mentioned above~
> >
> > 2015-05-06 17:43 GMT+08:00 hongbin ma <ma...@apache.org>:
> >
> > > So far we do not have a workaround for this.
> > > but as you said, several user have reported the perf issue of IN
> clause,
> > > I've created a ticket for this
> > > https://issues.apache.org/jira/browse/KYLIN-752
> > >
> > > On Wed, May 6, 2015 at 5:30 PM, dong wang <el...@gmail.com>
> > wrote:
> > >
> > > > as we know, several users mentioned about the performance of IN
> syntax
> > > such
> > > > as
> > > > city_id in
> > > >
> > > >
> > >
> >
> (151,261,299,3,51,66,156,174,254,325,105,332,21,55,89,92,115,118,134,168,170,172,220,256,282,324,19,337,8,9,73,99,100,101,107,133,163,178,233,302,333,239,184,102,5,11,31,76,227,328,75,331,20,48,49,50,120,123,129,140,153,197,219,225,275,285,289,290,305,320,340,176,245,64,24,33,141,186,259,338,23,203,268,67,30,44,137,182,258,288,309,329,341,10,135,2,22,83,98,247,250,112,235,240,263,274,276,314,322,15,61,342,12,47,114,242,243,262,269,300,152,164,175,226,224,68,93,94,143,147,228,307,318,326,124,185,255,32,80,116,251,334,28,74,127,131,234,237,279,130,272,81,104,177,218,253,317,144,213,303,306,78,248,60,110,113,157,159,180,208,222,283,162,291,294,1,6,72,199,206,278,292,214,17,58,95,136,150,189,201,212,229,14,25,241,265,271,202,13,53,85,103,142,160,217,231,304,335,91,190,293,194,57,69,70,71,106,122,125,139,183,238,252,277,207,35,62,138,166,232,249,284,287,45,79,86,128,132,215,221,244,281,158,84,97,29,46,148,204,310,312,315,27,321,0,311,343,155,63,82,193,211,216,246,316,165,43,87,169,171,191,192,200,210,230,236,257,330,205,313,109,16,154,54,56,111,126,196,209,319,344,34,88,117,119,149,161,181,187,286,339,41,336,4,26,36,39,96,108,188,264,267,273,295,301,308,37,18,38,195,223,296,323,52,145,146,198,7,40,42,59,90,121,173,260,270,297,298,327,77,167,179,266,280,65)
> > > >
> > > >
> > > > so far, do we have some workaround ways to solve it right now? such
> as
> > > > city_id=151 or city_id=261 or city_id=299 or city_id=..., do anyone
> > know
> > > > about the performance about this way?
> > > >
> > >
> > >
> > >
> > > --
> > > Regards,
> > >
> > > *Bin Mahone | 马洪宾*
> > > Apache Kylin: http://kylin.io
> > > Github: https://github.com/binmahone
> > >
> >
>
>
>
> --
> Regards,
>
> *Bin Mahone | 马洪宾*
> Apache Kylin: http://kylin.io
> Github: https://github.com/binmahone
>
Re: about the IN syntax
Posted by hongbin ma <ma...@apache.org>.
i guess the above way won't help, because internally Kylin translates IN
clause to ORs
On Wed, May 6, 2015 at 5:50 PM, dong wang <el...@gmail.com> wrote:
> if enough time, I will try to test the way mentioned above~
>
> 2015-05-06 17:43 GMT+08:00 hongbin ma <ma...@apache.org>:
>
> > So far we do not have a workaround for this.
> > but as you said, several user have reported the perf issue of IN clause,
> > I've created a ticket for this
> > https://issues.apache.org/jira/browse/KYLIN-752
> >
> > On Wed, May 6, 2015 at 5:30 PM, dong wang <el...@gmail.com>
> wrote:
> >
> > > as we know, several users mentioned about the performance of IN syntax
> > such
> > > as
> > > city_id in
> > >
> > >
> >
> (151,261,299,3,51,66,156,174,254,325,105,332,21,55,89,92,115,118,134,168,170,172,220,256,282,324,19,337,8,9,73,99,100,101,107,133,163,178,233,302,333,239,184,102,5,11,31,76,227,328,75,331,20,48,49,50,120,123,129,140,153,197,219,225,275,285,289,290,305,320,340,176,245,64,24,33,141,186,259,338,23,203,268,67,30,44,137,182,258,288,309,329,341,10,135,2,22,83,98,247,250,112,235,240,263,274,276,314,322,15,61,342,12,47,114,242,243,262,269,300,152,164,175,226,224,68,93,94,143,147,228,307,318,326,124,185,255,32,80,116,251,334,28,74,127,131,234,237,279,130,272,81,104,177,218,253,317,144,213,303,306,78,248,60,110,113,157,159,180,208,222,283,162,291,294,1,6,72,199,206,278,292,214,17,58,95,136,150,189,201,212,229,14,25,241,265,271,202,13,53,85,103,142,160,217,231,304,335,91,190,293,194,57,69,70,71,106,122,125,139,183,238,252,277,207,35,62,138,166,232,249,284,287,45,79,86,128,132,215,221,244,281,158,84,97,29,46,148,204,310,312,315,27,321,0,311,343,155,63,82,193,211,216,246,316,165,43,87,169,171,191,192,200,210,230,236,257,330,205,313,109,16,154,54,56,111,126,196,209,319,344,34,88,117,119,149,161,181,187,286,339,41,336,4,26,36,39,96,108,188,264,267,273,295,301,308,37,18,38,195,223,296,323,52,145,146,198,7,40,42,59,90,121,173,260,270,297,298,327,77,167,179,266,280,65)
> > >
> > >
> > > so far, do we have some workaround ways to solve it right now? such as
> > > city_id=151 or city_id=261 or city_id=299 or city_id=..., do anyone
> know
> > > about the performance about this way?
> > >
> >
> >
> >
> > --
> > Regards,
> >
> > *Bin Mahone | 马洪宾*
> > Apache Kylin: http://kylin.io
> > Github: https://github.com/binmahone
> >
>
--
Regards,
*Bin Mahone | 马洪宾*
Apache Kylin: http://kylin.io
Github: https://github.com/binmahone
Re: about the IN syntax
Posted by dong wang <el...@gmail.com>.
if enough time, I will try to test the way mentioned above~
2015-05-06 17:43 GMT+08:00 hongbin ma <ma...@apache.org>:
> So far we do not have a workaround for this.
> but as you said, several user have reported the perf issue of IN clause,
> I've created a ticket for this
> https://issues.apache.org/jira/browse/KYLIN-752
>
> On Wed, May 6, 2015 at 5:30 PM, dong wang <el...@gmail.com> wrote:
>
> > as we know, several users mentioned about the performance of IN syntax
> such
> > as
> > city_id in
> >
> >
> (151,261,299,3,51,66,156,174,254,325,105,332,21,55,89,92,115,118,134,168,170,172,220,256,282,324,19,337,8,9,73,99,100,101,107,133,163,178,233,302,333,239,184,102,5,11,31,76,227,328,75,331,20,48,49,50,120,123,129,140,153,197,219,225,275,285,289,290,305,320,340,176,245,64,24,33,141,186,259,338,23,203,268,67,30,44,137,182,258,288,309,329,341,10,135,2,22,83,98,247,250,112,235,240,263,274,276,314,322,15,61,342,12,47,114,242,243,262,269,300,152,164,175,226,224,68,93,94,143,147,228,307,318,326,124,185,255,32,80,116,251,334,28,74,127,131,234,237,279,130,272,81,104,177,218,253,317,144,213,303,306,78,248,60,110,113,157,159,180,208,222,283,162,291,294,1,6,72,199,206,278,292,214,17,58,95,136,150,189,201,212,229,14,25,241,265,271,202,13,53,85,103,142,160,217,231,304,335,91,190,293,194,57,69,70,71,106,122,125,139,183,238,252,277,207,35,62,138,166,232,249,284,287,45,79,86,128,132,215,221,244,281,158,84,97,29,46,148,204,310,312,315,27,321,0,311,343,155,63,82,193,211,216,246,316,165,43,87,169,171,191,192,200,210,230,236,257,330,205,313,109,16,154,54,56,111,126,196,209,319,344,34,88,117,119,149,161,181,187,286,339,41,336,4,26,36,39,96,108,188,264,267,273,295,301,308,37,18,38,195,223,296,323,52,145,146,198,7,40,42,59,90,121,173,260,270,297,298,327,77,167,179,266,280,65)
> >
> >
> > so far, do we have some workaround ways to solve it right now? such as
> > city_id=151 or city_id=261 or city_id=299 or city_id=..., do anyone know
> > about the performance about this way?
> >
>
>
>
> --
> Regards,
>
> *Bin Mahone | 马洪宾*
> Apache Kylin: http://kylin.io
> Github: https://github.com/binmahone
>
Re: about the IN syntax
Posted by hongbin ma <ma...@apache.org>.
So far we do not have a workaround for this.
but as you said, several user have reported the perf issue of IN clause,
I've created a ticket for this
https://issues.apache.org/jira/browse/KYLIN-752
On Wed, May 6, 2015 at 5:30 PM, dong wang <el...@gmail.com> wrote:
> as we know, several users mentioned about the performance of IN syntax such
> as
> city_id in
>
> (151,261,299,3,51,66,156,174,254,325,105,332,21,55,89,92,115,118,134,168,170,172,220,256,282,324,19,337,8,9,73,99,100,101,107,133,163,178,233,302,333,239,184,102,5,11,31,76,227,328,75,331,20,48,49,50,120,123,129,140,153,197,219,225,275,285,289,290,305,320,340,176,245,64,24,33,141,186,259,338,23,203,268,67,30,44,137,182,258,288,309,329,341,10,135,2,22,83,98,247,250,112,235,240,263,274,276,314,322,15,61,342,12,47,114,242,243,262,269,300,152,164,175,226,224,68,93,94,143,147,228,307,318,326,124,185,255,32,80,116,251,334,28,74,127,131,234,237,279,130,272,81,104,177,218,253,317,144,213,303,306,78,248,60,110,113,157,159,180,208,222,283,162,291,294,1,6,72,199,206,278,292,214,17,58,95,136,150,189,201,212,229,14,25,241,265,271,202,13,53,85,103,142,160,217,231,304,335,91,190,293,194,57,69,70,71,106,122,125,139,183,238,252,277,207,35,62,138,166,232,249,284,287,45,79,86,128,132,215,221,244,281,158,84,97,29,46,148,204,310,312,315,27,321,0,311,343,155,63,82,193,211,216,246,316,165,43,87,169,171,191,192,200,210,230,236,257,330,205,313,109,16,154,54,56,111,126,196,209,319,344,34,88,117,119,149,161,181,187,286,339,41,336,4,26,36,39,96,108,188,264,267,273,295,301,308,37,18,38,195,223,296,323,52,145,146,198,7,40,42,59,90,121,173,260,270,297,298,327,77,167,179,266,280,65)
>
>
> so far, do we have some workaround ways to solve it right now? such as
> city_id=151 or city_id=261 or city_id=299 or city_id=..., do anyone know
> about the performance about this way?
>
--
Regards,
*Bin Mahone | 马洪宾*
Apache Kylin: http://kylin.io
Github: https://github.com/binmahone