locked
Seeking suggestion to efficiently perform a search on our Azure Storage Table RRS feed

  • Question

  • Hi Everyone,

    We are storing our activities log in the Azure table storage and the business requirement is to search the most recent activities performed for given date range, customer Id, and other attributes of the activities.

    Once the top 20 records loaded, we can load the remaining records using "Load More" button of each 20 records

    Table storage design

    partition key =YYYYMMDD_CustomerId_PropertyId

    Row Key = InverseTimeTicks_DeviceId_GUID

    Since we need the most recent activities, we are also planning to change our Date format (YYYYMMDD) in the partitionKey as inversed timestamp

    As of now, we identified two approaches

    a) Perform range scan on partition Key using Inversed_Start_Date>=Table.StartDate and Inversed_End_Date<=Table.EndDate, but we cannot include the Customer Id in the partition Key range because "20150328_2_X" is also included when I search the date range between '20150328_1' and 20150331_1'. So we included the Customer Id in the filters against an individual column of Customer Id.

    This approach is going to scan the wide range of partitions but we are going to fetch only top 20 records.

    Do we have any suggestion to fetch these entities much efficiently?

    Concern:

    • When customer Id is not matching, it has to scan the whole partition to eliminate it.
    • We cannot make more changes in the partition key, because it can impact other component which consuming the activities log.

    b) Use a background process to move the activities log (6 months of data) into the Azure SQL Database and perform the search.

    Concern: Effort and Maintenance

    What is your recommendation? or do we have any other better solutions?

    Thanks,

    Mohankumar


    • Edited by VRMohan Friday, April 3, 2015 1:20 PM
    Friday, April 3, 2015 1:19 PM

Answers

All replies