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.

Thanks,

Prasad