How to fetch latest below 1000 WADLogsTable entry?

Question

I've coded for fetching latest diagnostic log from wadlogstable in c# But it is traversing all over records and then giving latest entry ex. there 5000 records in table but i want only latest or last 1000 record but it is giving all records then after order by query it is giving last 1000 record, so it is very time consuming, it is taking almost 7-8 minutes to fecth 4000-5000 records

 CloudStorageAccount storageAccount = CloudStorageAccount.Parse(ATCommon.DiagnosticConfig);
        CloudTableClient cloudTableClient = storageAccount.CreateCloudTableClient();
        TableServiceContext serviceContext = cloudTableClient.GetDataServiceContext();
        IQueryable<WadLogEntity> traceLogsTable = serviceContext.CreateQuery<WadLogEntity>("WADLogsTable");
        var selection = from row in traceLogsTable where row.PartitionKey.CompareTo("0" + DateTime.UtcNow.AddHours(hours).Ticks) >= 0 select row;
        //var selection = from row in traceLogsTable where row.PartitionKey.CompareTo("0" + DateTime.UtcNow.AddMinutes(-5.0).Ticks) >= 0 select row;
        CloudTableQuery<WadLogEntity> query = selection.AsTableServiceQuery<WadLogEntity>();
        IEnumerable<WadLogEntity> output = query.Execute();
return output.OrderByDescending(s => s.Timestamp).ToList();

@Mahesh partition key is for filtering purpose, and whole code i've share over here. — Ashok Dhakhada, Mar 02 '16 at 06:57
Thats what i I am trying to say. azure table does not support order by and all so using partition key you need to write logic to get latest 100. as partition key works like index in your table so query will run fast. using partition key filter you need to write logic like first get only last 2 hours records if you get only 60 in this query try again and fill your list then order by it and take only 100 — Mahesh, Mar 02 '16 at 10:24
@Mahesh but, if you have 12000 records in storage table, first it will fetch all and then we can do any type of filtering, and that i don't want — Ashok Dhakhada, Mar 10 '16 at 12:48

score 1 · Answer 1 · answered Mar 04 '16 at 07:12

1

You can try achieve it by trick, use a RowKey value of DateTime.MaxValue.Ticks – DateTime.UtcNow.Ticks to allow you to sort the items by an offsetted time from newest items to older items. By doing this, you can retrieve the results in the correct order or most recently added items first and then limiting the results to the most recent 1000 rows by using the the .Take(1000) parameter. check details in this blog.

answered Mar 04 '16 at 07:12

forester123

1,549
10
10

i am already using ticks things, but, if you have 12000 records in storage table, first it will fetch all and then we can do any type of filtering, and that i don't want – Ashok Dhakhada Mar 10 '16 at 12:46
@AshokDhakhada: if you filter the table it will fetch only filtered data, not all data. just the thing is filter should be on partition key so it wont go threw full table to search for proper data. and it will be fast. i am posting my code bellow – Mahesh Mar 15 '16 at 10:36

score 1 · Accepted Answer · answered Mar 15 '16 at 10:41

I have more then 500 million of entries. and every second it is adding more..

string apiLogTableName = "yourtableName";
StorageTable apiLogsTable = new StorageTable(apiLogTableName);

 string filter1 = TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, date);   //hear you can check with ticks


 string filter2 = TableQuery.GenerateFilterConditionForInt("yourColumn", QueryComparisons.Equal, yourValue);

            string filter3 = TableQuery.GenerateFilterCondition("YourColumn2", QueryComparisons.NotEqual, "YourValue2");



            TableQuery<ApiCallLogEntity> findapiLogsRecord = new TableQuery<ApiCallLogEntity>().Where(TableQuery.CombineFilters(

                TableQuery.CombineFilters(
                            filter1,
                            TableOperators.And,
                            filter2),
                TableOperators.And, filter3));

//you can use
//var records= apiLogsTable._table.ExecuteQuery(findapiLogsRecord)


//bellow code will get one-one records // time consuming
            foreach (ApiCallLogEntity entity in apiLogsTable._table.ExecuteQuery(findapiLogsRecord))
            {
                Console.WriteLine("{0}, {1}\t{2}\t{3}", entity.PartitionKey, entity.RowKey,
                    entity.Action, entity.RequestBody);


                tw.WriteLine("{0}, {1}\t{2}\t{3}\t{4}", entity.PartitionKey, entity.RowKey,
                    entity.Action, entity.RequestBody, entity.ResponseBody);
            }

How to fetch latest below 1000 WADLogsTable entry?

2 Answers2