You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Background:
Our scenario is to manage Ip range List. In each lists, it has IP Records which has IPStart and IPEnd as properties.
The IP Range Record is as below:
{
"Id":"{UniqueId}",
"ListId": 1,
"IpBegin": "Integer representation of beginning IP in range.",
"IpEnd": "Integer representation of beginning IP in range."
}
We support mainly 2 query patterns:
We select all IP Range Records with certain ListId in a time range.
Sample Query: SELECT * FROM c WHERE c.listId = @id AND c.lastModified > @ts ORDER BY c.lastModified ASC;
Traffic volume: The query run every 2 minutes for each IP range list and there're about 600K records in all lists.
By providing only IPBegin and IPEnd, search for all IP range Record that overlap with the provided IP range.
Sample Query: SELECT * FROM c WHERE c.IpRangeBegin <= @ipRangeEnd AND c.IpRangeEnd >= @ipRangeStart;
Traffic volume: The query triggered manually, ~10k/day, still in total 600K records in all lists.
Question:
How to partition the IP range Record? current we're trying to partition by list ID, but for use case 2, the query by IP range becomes cross partition query. If use hierarchy partitioning Level 1: listId, level 2: IP Begin, Level 3: IP End, will it help with use case 2 query? It will still be cross partition query though.
Any suggestions will be appreciated, Thanks in advance.
reacted with thumbs up emoji reacted with thumbs down emoji reacted with laugh emoji reacted with hooray emoji reacted with confused emoji reacted with heart emoji reacted with rocket emoji reacted with eyes emoji
-
Background:
Our scenario is to manage Ip range List. In each lists, it has IP Records which has IPStart and IPEnd as properties.
The IP Range Record is as below:
{
"Id":"{UniqueId}",
"ListId": 1,
"IpBegin": "Integer representation of beginning IP in range.",
"IpEnd": "Integer representation of beginning IP in range."
}
We support mainly 2 query patterns:
We select all IP Range Records with certain ListId in a time range.
Sample Query: SELECT * FROM c WHERE c.listId = @id AND c.lastModified > @ts ORDER BY c.lastModified ASC;
Traffic volume: The query run every 2 minutes for each IP range list and there're about 600K records in all lists.
By providing only IPBegin and IPEnd, search for all IP range Record that overlap with the provided IP range.
Sample Query: SELECT * FROM c WHERE c.IpRangeBegin <= @ipRangeEnd AND c.IpRangeEnd >= @ipRangeStart;
Traffic volume: The query triggered manually, ~10k/day, still in total 600K records in all lists.
Question:
How to partition the IP range Record? current we're trying to partition by list ID, but for use case 2, the query by IP range becomes cross partition query. If use hierarchy partitioning Level 1: listId, level 2: IP Begin, Level 3: IP End, will it help with use case 2 query? It will still be cross partition query though.
Any suggestions will be appreciated, Thanks in advance.
Beta Was this translation helpful? Give feedback.
All reactions