Query Execution is very slow (Can I create a Jira for this )

[Prasad Bhalerao]

Hi,

I am executing a SQL with TEMP table join for IN caluse query. Sql is as
show below.
This sql is taking 20-30 seconds to execute. My cache has only 1.39 million
entries but in real scenario I will be having around 40 million entries.

I have created a reproducer and uploaded it to GitHub. I have created 3
cases to test the sql execution time.

Please run *IgniteQueryTester_4* class to check the issue.

Can some please help me this case?
Can I create JIRA for this issue?

GitHub project: https://github.com/prasadbhalerao1983/IgniteTestPrj.git


  SELECT ipv4agd.id,
    ipv4agd.assetGroupId,
    ipv4agd.ipStart,
    ipv4agd.ipEnd
  FROM IpV4AssetGroupData ipv4agd
  JOIN TABLE (assetGroupId bigint = ? ) temp
  ON ipv4agd.assetGroupId         = temp.assetGroupId
  WHERE subscriptionId            = ?
  AND (ipStart                   <= ? AND ipEnd                      >= ?)
  ORDER BY ipv4agd.assetGroupId

I am also attaching a jprofiler snapshot for this query ran on 40 million
load.

[image: image.png]
Thanks,
Prasad