[jira] [Updated] (KYLIN-740) Slowness with many IN() values

["Luke Han (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/KYLIN-740?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Luke Han updated KYLIN-740:
---------------------------
    Sprint: Sprint 43, Sprint 44, Sprint 45  (was: Sprint 43, Sprint 44)

> Slowness with many IN() values
> ------------------------------
>
>                 Key: KYLIN-740
>                 URL: https://issues.apache.org/jira/browse/KYLIN-740
>             Project: Kylin
>          Issue Type: Bug
>            Reporter: liyang
>            Assignee: liyang
>
> [from dev mail list]
> Huang Hua <hu...@mininglamp.com>
> We were testing the query efficiency with some sql containing 'IN' keyword
> on a relatively big table which holds 22+ Million records.
> It seems to us that sql with a number of items in 'IN' show very poor
> performance in terms of running time.
> The fact table used has three columns: themonth, id, trans_at, and we
> created the cube with 2 dimensions and 1 measure:
> Dimensions: themonth, id
> Measure: sum(trans_at)
> Basically our queries are all similar to the one:
> select themonth, id, sum(trans_at) from the_table where id IN (id1, id2,
> id3, .) group by themonth, id limit 10
> When the number of ids reaches about 30-50, the response time becomes
> considerable long in terms of minutes. By the way, we are running kylin on
> 12 nodes with 2 name nodes and 10 data nodes.
> Besides, we tried to break the above sql with 'IN' keyword into several
> sub-sql and merge the results of each sub-sql to get the final result like
> the following:
> select themonth, id, sum(trans_at) from the_table where id = id1 group by
> themonth, id limit 10
> select themonth, id, sum(trans_at) from the_table where id = id2 group by
> themonth, id limit 10
> select themonth, id, sum(trans_at) from the_table where id = id3 group by
> themonth, id limit 10
> .
> Surprisedly, the total running time of those sub-sql is much less than the
> running time of the orginal sql with 'IN' keyword.
> Initially I thought the backend query engine should handle 'IN' keyword in
> the similar way as the individual sql with '=' keyword, but it seems not.
> Can anybody provide any thoughts regarding this? Any ideas on how to tune
> the queries containing 'IN' keyword?



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)