Re: Query execution too long even after providing index
Hi Evgenii, Thank you for suggesting the query optimization. It worked perfectly fine. I unnecessarily complicated the sql. I really appreciate the efforts you guys are taking to help out the users. About the test data: Yes in production I will be having more than 100K records for single subscription and moduleId. The test data generated has 5 million entries against subscriptionId and moduleId. This is a worst case scenario but we do have such cases in production. Thanks, Prasad On Mon, Oct 22, 2018 at 6:22 PM Evgenii Zhuravlev wrote: > Well, looks like you trying to find the segments that intersects defined > segment, but you're complicating it. You don't need 3 conditions here - it > will be enough to have only one - ipStart <= MAX and ipEnd >= MIN. I've > checked it for your case and got absolutely the same results as you have > with a too complex query. > > Aditionally, you have the same subscriptionId AND moduleId in your test > data, which means that you will have a bad selectivity when you will filter > by these fields at first. Do you really have such data in your production? > > Also, when you will measure something again - do operation a lot of times > - that how benchmarks work. Ignite initialize internal structures at first > executions, so, it will not give an idea of overall latency if you will > measure it just once. > > Best Regards, > Evgenii > > пн, 22 окт. 2018 г. в 6:56, Prasad Bhalerao >: > >> Hi Evgenii, >> >> Did you get time to check the reproducer? >> >> Can you please suggest solution for this? >> >> >> Thanks, >> Prasad >> >> >> On Fri, Oct 19, 2018, 4:46 PM Prasad Bhalerao < >> prasadbhalerao1...@gmail.com> wrote: >> >>> Hi Evgenii, >>> >>> I have created a reproducer and uploaded it to GitHub. I have created 5 >>> cases to test the sql execution time. >>> >>> GitHub project: https://github.com/prasadbhalerao1983/IgniteTestPrj.git >>> >>> Please run IgniteQueryTester class. >>> >>> Thanks, >>> Prasad >>> >>> On Wed, Oct 17, 2018 at 7:46 PM ezhuravlev >>> wrote: >>> How much data do you have? What is the amount of heap and offheap memory? Can you share the reproducer with the community? Evgenii -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/ >>>
Re: Query execution too long even after providing index
Well, looks like you trying to find the segments that intersects defined segment, but you're complicating it. You don't need 3 conditions here - it will be enough to have only one - ipStart <= MAX and ipEnd >= MIN. I've checked it for your case and got absolutely the same results as you have with a too complex query. Aditionally, you have the same subscriptionId AND moduleId in your test data, which means that you will have a bad selectivity when you will filter by these fields at first. Do you really have such data in your production? Also, when you will measure something again - do operation a lot of times - that how benchmarks work. Ignite initialize internal structures at first executions, so, it will not give an idea of overall latency if you will measure it just once. Best Regards, Evgenii пн, 22 окт. 2018 г. в 6:56, Prasad Bhalerao : > Hi Evgenii, > > Did you get time to check the reproducer? > > Can you please suggest solution for this? > > > Thanks, > Prasad > > > On Fri, Oct 19, 2018, 4:46 PM Prasad Bhalerao < > prasadbhalerao1...@gmail.com> wrote: > >> Hi Evgenii, >> >> I have created a reproducer and uploaded it to GitHub. I have created 5 >> cases to test the sql execution time. >> >> GitHub project: https://github.com/prasadbhalerao1983/IgniteTestPrj.git >> >> Please run IgniteQueryTester class. >> >> Thanks, >> Prasad >> >> On Wed, Oct 17, 2018 at 7:46 PM ezhuravlev >> wrote: >> >>> How much data do you have? What is the amount of heap and offheap >>> memory? >>> Can you share the reproducer with the community? >>> >>> Evgenii >>> >>> >>> >>> -- >>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/ >>> >>
Re: Query execution too long even after providing index
Hi Evgenii, Did you get time to check the reproducer? Can you please suggest solution for this? Thanks, Prasad On Fri, Oct 19, 2018, 4:46 PM Prasad Bhalerao wrote: > Hi Evgenii, > > I have created a reproducer and uploaded it to GitHub. I have created 5 > cases to test the sql execution time. > > GitHub project: https://github.com/prasadbhalerao1983/IgniteTestPrj.git > > Please run IgniteQueryTester class. > > Thanks, > Prasad > > On Wed, Oct 17, 2018 at 7:46 PM ezhuravlev > wrote: > >> How much data do you have? What is the amount of heap and offheap memory? >> Can you share the reproducer with the community? >> >> Evgenii >> >> >> >> -- >> Sent from: http://apache-ignite-users.70518.x6.nabble.com/ >> >
Re: Query execution too long even after providing index
Hi Evgenii, I have created a reproducer and uploaded it to GitHub. I have created 5 cases to test the sql execution time. GitHub project: https://github.com/prasadbhalerao1983/IgniteTestPrj.git Please run IgniteQueryTester class. Thanks, Prasad On Wed, Oct 17, 2018 at 7:46 PM ezhuravlev wrote: > How much data do you have? What is the amount of heap and offheap memory? > Can you share the reproducer with the community? > > Evgenii > > > > -- > Sent from: http://apache-ignite-users.70518.x6.nabble.com/ >
Re: Query execution too long even after providing index
How much data do you have? What is the amount of heap and offheap memory? Can you share the reproducer with the community? Evgenii -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Query execution too long even after providing index
Hi, Evgenii, I tried to execute sql without UNION ALL operator. I mean I just executed the first part of the sql as shown below and it is taking 700-800 ms to complete. I have around 3 million records in my cache. and will be having around 30-40 million records in real scenario. SELECT id, moduleId,ipEnd, ipStart FROM IpContainerIpV4Data USE INDEX(ip_container_ipv4_idx1) WHERE subscriptionId = ? AND moduleId = ? AND (ipStart<= ? AND ipEnd >= ?) Does ignite executes 3 sqls independently and then does the union operation? Is this the reason it is taking time? Is there any solution to solve this problem? The sql shown below is the very basic sql which is needed everywhere in my application. Since ignite does not use index with OR clause I had to rewrite the same sql with UNION ALL operator. As mentioned in this email thread, this sql is using indexes. Now the question is does ignite brings all filtered data in heap space and then does union all operation? 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 <= ?) On Thu, Sep 13, 2018 at 9:44 PM ezhuravlev wrote: > Hi, > > What is the execution time of just a single query without UNION? > > Evgenii > > > > -- > Sent from: http://apache-ignite-users.70518.x6.nabble.com/ >
Re: Query execution too long even after providing index
Hi, What is the execution time of just a single query without UNION? Evgenii -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Query execution too long even after providing index
Hello guys,
I am also facing the similar problem. Does community users have any
solution for this?
This has become blocking issue for me. Can someone please help?
Thanks,
Akash
On Mon, Sep 10, 2018 at 8:33 AM Prasad Bhalerao <
prasadbhalerao1...@gmail.com> wrote:
> Guys, is there any solution for this?
> Can someone please respond?
>
> Thanks,
> Prasad
>
>
> -- Forwarded message -
> From: Prasad Bhalerao
> Date: Fri, Sep 7, 2018, 8:04 AM
> Subject: Fwd: Query execution too long even after providing index
> To:
>
>
> Can we have update on this?
>
> -- Forwarded message -
> From: Prasad Bhalerao
> Date: Wed, Sep 5, 2018, 11:34 AM
> Subject: Re: Query execution too long even after providing index
> To:
>
>
> Hi Andrey,
>
> Can you please help me with this? I
>
> Thanks,
> Prasad
>
> On Tue, Sep 4, 2018 at 2:08 PM Prasad Bhalerao <
> prasadbhalerao1...@gmail.com> wrote:
>
>>
>> I tried changing SqlIndexMaxInlineSize to 32 byte and 100 byte using
>> cache config.
>>
>> ipContainerIpV4CacheCfg.setSqlIndexMaxInlineSize(32/100);
>>
>> But it did not improve the sql execution time. Sql execution time
>> increases with increase in cache size.
>>
>> It is a simple range scan query. Which part of the execution process
>> might take time in this case?
>>
>> Can you please advise?
>>
>> Thanks,
>> PRasad
>>
>> On Mon, Sep 3, 2018 at 8:06 PM Andrey Mashenkov <
>> andrey.mashen...@gmail.com> wrote:
>>
>>> HI,
>>>
>>> Have you tried to increase index inlineSize? It is 10 bytes by default.
>>>
>>> Your indices uses simple value types (Java primitives) and all columns
>>> can be easily inlined.
>>> It should be enough to increase inlineSize up to 32 bytes (3 longs + 1
>>> int = 3*(8 /*long*/ + 1/*type code*/) + (4/*int*/ + 1/*type code*/)) to
>>> inline all columns for the idx1, and up to 27 (3 longs) for idx2.
>>>
>>> You can try to benchmark queries with different inline sizes to find
>>> optimal ratio between speedup and index size.
>>>
>>>
>>>
>>> On Mon, Sep 3, 2018 at 5:12 PM Prasad Bhalerao <
>>> prasadbhalerao1...@gmail.com> wrote:
>>>
>>>> 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,
>>>> UpdatableData {
>>>>
>>>> @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
RE: Query execution too long even after providing index
Guys, is there any solution for this?
Can someone please respond?
Thanks,
Prasad
-- Forwarded message -
From: Prasad Bhalerao
Date: Fri, Sep 7, 2018, 8:04 AM
Subject: Fwd: Query execution too long even after providing index
To:
Can we have update on this?
-- Forwarded message -
From: Prasad Bhalerao
Date: Wed, Sep 5, 2018, 11:34 AM
Subject: Re: Query execution too long even after providing index
To:
Hi Andrey,
Can you please help me with this? I
Thanks,
Prasad
On Tue, Sep 4, 2018 at 2:08 PM Prasad Bhalerao
wrote:
>
> I tried changing SqlIndexMaxInlineSize to 32 byte and 100 byte using cache
> config.
>
> ipContainerIpV4CacheCfg.setSqlIndexMaxInlineSize(32/100);
>
> But it did not improve the sql execution time. Sql execution time
> increases with increase in cache size.
>
> It is a simple range scan query. Which part of the execution process might
> take time in this case?
>
> Can you please advise?
>
> Thanks,
> PRasad
>
> On Mon, Sep 3, 2018 at 8:06 PM Andrey Mashenkov <
> andrey.mashen...@gmail.com> wrote:
>
>> HI,
>>
>> Have you tried to increase index inlineSize? It is 10 bytes by default.
>>
>> Your indices uses simple value types (Java primitives) and all columns
>> can be easily inlined.
>> It should be enough to increase inlineSize up to 32 bytes (3 longs + 1
>> int = 3*(8 /*long*/ + 1/*type code*/) + (4/*int*/ + 1/*type code*/)) to
>> inline all columns for the idx1, and up to 27 (3 longs) for idx2.
>>
>> You can try to benchmark queries with different inline sizes to find
>> optimal ratio between speedup and index size.
>>
>>
>>
>> On Mon, Sep 3, 2018 at 5:12 PM Prasad Bhalerao <
>> prasadbhalerao1...@gmail.com> wrote:
>>
>>> 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,
>>> UpdatableData {
>>>
>>> @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: SUBSCRIPTIO
Fwd: Query execution too long even after providing index
Can we have update on this?
-- Forwarded message -
From: Prasad Bhalerao
Date: Wed, Sep 5, 2018, 11:34 AM
Subject: Re: Query execution too long even after providing index
To:
Hi Andrey,
Can you please help me with this? I
Thanks,
Prasad
On Tue, Sep 4, 2018 at 2:08 PM Prasad Bhalerao
wrote:
>
> I tried changing SqlIndexMaxInlineSize to 32 byte and 100 byte using cache
> config.
>
> ipContainerIpV4CacheCfg.setSqlIndexMaxInlineSize(32/100);
>
> But it did not improve the sql execution time. Sql execution time
> increases with increase in cache size.
>
> It is a simple range scan query. Which part of the execution process might
> take time in this case?
>
> Can you please advise?
>
> Thanks,
> PRasad
>
> On Mon, Sep 3, 2018 at 8:06 PM Andrey Mashenkov <
> andrey.mashen...@gmail.com> wrote:
>
>> HI,
>>
>> Have you tried to increase index inlineSize? It is 10 bytes by default.
>>
>> Your indices uses simple value types (Java primitives) and all columns
>> can be easily inlined.
>> It should be enough to increase inlineSize up to 32 bytes (3 longs + 1
>> int = 3*(8 /*long*/ + 1/*type code*/) + (4/*int*/ + 1/*type code*/)) to
>> inline all columns for the idx1, and up to 27 (3 longs) for idx2.
>>
>> You can try to benchmark queries with different inline sizes to find
>> optimal ratio between speedup and index size.
>>
>>
>>
>> On Mon, Sep 3, 2018 at 5:12 PM Prasad Bhalerao <
>> prasadbhalerao1...@gmail.com> wrote:
>>
>>> 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,
>>> UpdatableData {
>>>
>>> @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))
&g
Re: Query execution too long even after providing index
Hi Andrey,
Can you please help me with this? I
Thanks,
Prasad
On Tue, Sep 4, 2018 at 2:08 PM Prasad Bhalerao
wrote:
>
> I tried changing SqlIndexMaxInlineSize to 32 byte and 100 byte using cache
> config.
>
> ipContainerIpV4CacheCfg.setSqlIndexMaxInlineSize(32/100);
>
> But it did not improve the sql execution time. Sql execution time
> increases with increase in cache size.
>
> It is a simple range scan query. Which part of the execution process might
> take time in this case?
>
> Can you please advise?
>
> Thanks,
> PRasad
>
> On Mon, Sep 3, 2018 at 8:06 PM Andrey Mashenkov <
> andrey.mashen...@gmail.com> wrote:
>
>> HI,
>>
>> Have you tried to increase index inlineSize? It is 10 bytes by default.
>>
>> Your indices uses simple value types (Java primitives) and all columns
>> can be easily inlined.
>> It should be enough to increase inlineSize up to 32 bytes (3 longs + 1
>> int = 3*(8 /*long*/ + 1/*type code*/) + (4/*int*/ + 1/*type code*/)) to
>> inline all columns for the idx1, and up to 27 (3 longs) for idx2.
>>
>> You can try to benchmark queries with different inline sizes to find
>> optimal ratio between speedup and index size.
>>
>>
>>
>> On Mon, Sep 3, 2018 at 5:12 PM Prasad Bhalerao <
>> prasadbhalerao1...@gmail.com> wrote:
>>
>>> 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,
>>> UpdatableData {
>>>
>>> @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:
Re: Query execution too long even after providing index
I tried changing SqlIndexMaxInlineSize to 32 byte and 100 byte using cache
config.
ipContainerIpV4CacheCfg.setSqlIndexMaxInlineSize(32/100);
But it did not improve the sql execution time. Sql execution time increases
with increase in cache size.
It is a simple range scan query. Which part of the execution process might
take time in this case?
Can you please advise?
Thanks,
PRasad
On Mon, Sep 3, 2018 at 8:06 PM Andrey Mashenkov
wrote:
> HI,
>
> Have you tried to increase index inlineSize? It is 10 bytes by default.
>
> Your indices uses simple value types (Java primitives) and all columns can
> be easily inlined.
> It should be enough to increase inlineSize up to 32 bytes (3 longs + 1 int
> = 3*(8 /*long*/ + 1/*type code*/) + (4/*int*/ + 1/*type code*/)) to inline
> all columns for the idx1, and up to 27 (3 longs) for idx2.
>
> You can try to benchmark queries with different inline sizes to find
> optimal ratio between speedup and index size.
>
>
>
> On Mon, Sep 3, 2018 at 5:12 PM Prasad Bhalerao <
> prasadbhalerao1...@gmail.com> wrote:
>
>> 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,
>> UpdatableData {
>>
>> @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
Re: Query execution too long even after providing index
HI,
Have you tried to increase index inlineSize? It is 10 bytes by default.
Your indices uses simple value types (Java primitives) and all columns can
be easily inlined.
It should be enough to increase inlineSize up to 32 bytes (3 longs + 1 int
= 3*(8 /*long*/ + 1/*type code*/) + (4/*int*/ + 1/*type code*/)) to inline
all columns for the idx1, and up to 27 (3 longs) for idx2.
You can try to benchmark queries with different inline sizes to find
optimal ratio between speedup and index size.
On Mon, Sep 3, 2018 at 5:12 PM Prasad Bhalerao
wrote:
> 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,
> UpdatableData {
>
> @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" */)
>
>
>
--
Best
Query execution too long even after providing index
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, UpdatableData {
@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" */)
