Hi,
My cache has 1 million rows and the sql is as follows.
This sql is taking around 1.836 seconds to execute and this time increases
as I go on adding the data to this cache. Some time it takes more than 4
seconds.
Is there any way to improve the execution time?
*SQL:*
SELECT id, moduleId,ipEnd, ipStart
FROM IpContainerIpV4Data USE INDEX(ip_container_ipv4_idx1)
WHERE subscriptionId = ? AND moduleId = ? AND (ipStart <= ?
AND ipEnd >= ?)
UNION ALL
SELECT id, moduleId,ipEnd, ipStart
FROM IpContainerIpV4Data USE INDEX(ip_container_ipv4_idx1)
WHERE subscriptionId = ? AND moduleId = ? AND (ipStart <= ?
AND ipEnd >= ?)
UNION ALL
SELECT id, moduleId,ipEnd, ipStart
FROM IpContainerIpV4Data USE INDEX(ip_container_ipv4_idx1)
WHERE subscriptionId = ? AND moduleId = ? AND (ipStart >= ?
AND ipEnd <= ?)
*Indexes are as follows:*
public class IpContainerIpV4Data implements
Data<DefaultDataAffinityKey>, UpdatableData<DefaultDataAffinityKey> {
@QuerySqlField
private long id;
@QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"ip_container_ipv4_idx1", order = 1)})
private int moduleId;
@QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"ip_container_ipv4_idx1", order = 0),
@QuerySqlField.Group(name = "ip_container_ipv4_idx2", order = 0)})
private long subscriptionId;
@QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"ip_container_ipv4_idx1", order = 3, descending = true),
@QuerySqlField.Group(name = "ip_container_ipv4_idx2", order = 2,
descending = true)})
private long ipEnd;
@QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"ip_container_ipv4_idx1", order = 2),
@QuerySqlField.Group(name = "ip_container_ipv4_idx2", order = 1)})
private long ipStart;
}
*Execution Plan:*
2018-09-03 19:32:03,098 232176 [pub-#78%springDataNode%] INFO
c.q.a.g.d.IpContainerIpV4DataGridDaoImpl - SELECT
__Z0.ID AS __C0_0,
__Z0.MODULEID AS __C0_1,
__Z0.IPEND AS __C0_2,
__Z0.IPSTART AS __C0_3
FROM IP_CONTAINER_IPV4_CACHE.IPCONTAINERIPV4DATA __Z0 USE INDEX
(IP_CONTAINER_IPV4_IDX1)
/* IP_CONTAINER_IPV4_CACHE.IP_CONTAINER_IPV4_IDX1: SUBSCRIPTIONID = ?1
AND MODULEID = ?2
AND IPSTART <= ?3
AND IPEND >= ?4
*/
WHERE ((__Z0.SUBSCRIPTIONID = ?1)
AND (__Z0.MODULEID = ?2))
AND ((__Z0.IPSTART <= ?3)
AND (__Z0.IPEND >= ?4))
2018-09-03 19:32:03,098 232176 [pub-#78%springDataNode%] INFO
c.q.a.g.d.IpContainerIpV4DataGridDaoImpl - SELECT
__Z1.ID AS __C1_0,
__Z1.MODULEID AS __C1_1,
__Z1.IPEND AS __C1_2,
__Z1.IPSTART AS __C1_3
FROM IP_CONTAINER_IPV4_CACHE.IPCONTAINERIPV4DATA __Z1 USE INDEX
(IP_CONTAINER_IPV4_IDX1)
/* IP_CONTAINER_IPV4_CACHE.IP_CONTAINER_IPV4_IDX1: SUBSCRIPTIONID = ?5
AND MODULEID = ?6
AND IPSTART <= ?7
AND IPEND >= ?8
*/
WHERE ((__Z1.SUBSCRIPTIONID = ?5)
AND (__Z1.MODULEID = ?6))
AND ((__Z1.IPSTART <= ?7)
AND (__Z1.IPEND >= ?8))
2018-09-03 19:32:03,098 232176 [pub-#78%springDataNode%] INFO
c.q.a.g.d.IpContainerIpV4DataGridDaoImpl - SELECT
__Z2.ID AS __C2_0,
__Z2.MODULEID AS __C2_1,
__Z2.IPEND AS __C2_2,
__Z2.IPSTART AS __C2_3
FROM IP_CONTAINER_IPV4_CACHE.IPCONTAINERIPV4DATA __Z2 USE INDEX
(IP_CONTAINER_IPV4_IDX1)
/* IP_CONTAINER_IPV4_CACHE.IP_CONTAINER_IPV4_IDX1: SUBSCRIPTIONID = ?9
AND MODULEID = ?10
AND IPSTART >= ?11
AND IPEND <= ?12
*/
WHERE ((__Z2.SUBSCRIPTIONID = ?9)
AND (__Z2.MODULEID = ?10))
AND ((__Z2.IPSTART >= ?11)
AND (__Z2.IPEND <= ?12))
2018-09-03 19:32:03,098 232176 [pub-#78%springDataNode%] INFO
c.q.a.g.d.IpContainerIpV4DataGridDaoImpl - ((SELECT
__C0_0 AS ID,
__C0_1 AS MODULEID,
__C0_2 AS IPEND,
__C0_3 AS IPSTART
FROM PUBLIC.__T0
/* IP_CONTAINER_IPV4_CACHE."merge_scan" */)
UNION ALL
(SELECT
__C1_0 AS ID,
__C1_1 AS MODULEID,
__C1_2 AS IPEND,
__C1_3 AS IPSTART
FROM PUBLIC.__T1
/* IP_CONTAINER_IPV4_CACHE."merge_scan" */))
UNION ALL
(SELECT
__C2_0 AS ID,
__C2_1 AS MODULEID,
__C2_2 AS IPEND,
__C2_3 AS IPSTART
FROM PUBLIC.__T2
/* IP_CONTAINER_IPV4_CACHE."merge_scan" */)
