ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Prasad Bhalerao <prasadbhalerao1...@gmail.com>
Subject Query execution too slow
Date Tue, 21 Aug 2018 11:28:34 GMT
Hi,
Following SQL is taking too long to execute. It seems that highlighted
condition is not using indexes.

I have created the indexes as follows. What should be done to makes use of
index created on value column/property?

Can someone please advise?

public class DnsNetBiosAssetGroupData implements
Data<DefaultDataAffinityKey>,UpdatableData<DefaultDataAffinityKey> {

  @QuerySqlField
  private long id;
  @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"dns_nb_asset_group_data_idx1", order = 1)})
  private long assetGroupId;
  @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"dns_nb_asset_group_data_idx1", order = 2)})
  private int assetTypeInd;
  private int partitionId;
  @QuerySqlField
  private long subscriptionId;
  @QuerySqlField
  private long updatedDate;
  @QuerySqlField (index = true)
  private String value;


2018-08-21 16:53:37,765 143847 [pub-#72%springDataNode%] WARN
o.a.i.i.p.query.h2.IgniteH2Indexing - Query execution is too long
[time=30638 ms,
sql='SELECT
DNSNB__Z0.VALUE __C0_0,
DNSNB__Z0.ID __C0_1
FROM DNS_NB_ASSET_GROUP_DETAIL_CACHE.DNSNETBIOSASSETGROUPDATA DNSNB__Z0
 INNER JOIN TABLE(VALUE VARCHAR=?1) TEMP__Z1
 ON TRUE
WHERE (DNSNB__Z0.VALUE = TEMP__Z1.VALUE) AND ((DNSNB__Z0.ASSETTYPEIND = ?4)
AND ((DNSNB__Z0.SUBSCRIPTIONID = ?2) AND (DNSNB__Z0.ASSETGROUPID = ?3)))',

plan=
SELECT
    DNSNB__Z0.VALUE AS __C0_0,
    DNSNB__Z0.ID AS __C0_1
FROM DNS_NB_ASSET_GROUP_DETAIL_CACHE.DNSNETBIOSASSETGROUPDATA DNSNB__Z0
    /* DNS_NB_ASSET_GROUP_DETAIL_CACHE.DNS_NB_ASSET_GROUP_DATA_IDX1:
ASSETTYPEIND = ?4
        AND ASSETGROUPID = ?3
     */
    /* WHERE (DNSNB__Z0.ASSETGROUPID = ?3)
        AND ((DNSNB__Z0.ASSETTYPEIND = ?4)
        AND (DNSNB__Z0.SUBSCRIPTIONID = ?2))
    */
INNER JOIN TABLE(VALUE VARCHAR=?1) TEMP__Z1
    /* function: VALUE = DNSNB__Z0.VALUE */
    ON 1=1
WHERE (DNSNB__Z0.VALUE = TEMP__Z1.VALUE)
    AND ((DNSNB__Z0.ASSETTYPEIND = ?4)
    AND ((DNSNB__Z0.SUBSCRIPTIONID = ?2)
    AND (DNSNB__Z0.ASSETGROUPID = ?3)))

Thanks,
Prasad

Mime
View raw message