locked
Slow query Java API Table RRS feed

  • Question

  • I've stored a number of time-stamped events in a table on azure. Now I'm querying the events by timestamp to retrieve them. THe query is not very complicated (is the timestamp after a start date and before a finish date), but the query is taking a very long time to run (as high as 72 seconds, to return 0 items)

    How do I optimize this query?

    private static void getLogForTrip(Entity t) {
    String startTimeFilter = TableQuery.generateFilterCondition(TableConstants.ROW_KEY, QueryComparisons.GREATER_THAN, t.getTimeString());
    String endTimeFilter = TableQuery.generateFilterCondition(TableConstants.ROW_KEY, QueryComparisons.LESS_THAN, TSEntity.formatDate(t.getEndDate()));
    String combo = TableQuery.combineFilters(startTimeFilter, Operators.AND, endTimeFilter);
    TableQuery<GeoEventEntity> partitionQuery = TableQuery.from(logsTableName, GeoEventEntity.class).where(combo);

    for ( EventEntity e: tableClient.execute(partitionQuery)) {
    // process the returned information and do something with it
    }
    }

    Friday, June 14, 2013 12:03 AM

Answers

All replies

  • One thing I noticed is that you're querying on RowKey only. Please note that querying on RowKey will result in full table scan. Depending on how many entities you have in a table, this may take a while to execute. For best performance, always try to include PartitionKey in your query.

    You may find this blog post useful (esp. querying section): http://blogs.msdn.com/b/windowsazurestorage/archive/2010/11/06/how-to-get-most-out-of-windows-azure-tables.aspx

    Hope this helps.

    • Marked as answer by dw1000 Friday, June 14, 2013 5:25 AM
    Friday, June 14, 2013 2:15 AM
  • Thanks for pointing out the lack of PartitionKey. 

    I found the TableQuery.generateFilterQuery approach a little cumbersome, so I just constructed the combo query by hand:

    String comboQuery = "(PartitionKey eq '"+t.getPartitionKey()+"') and ((RowKey gt '"+t.getTimeString()+"') and (RowKey lt '"+TSEntity.formatDate(t.getEndDate())+"'))";

    This now returns results in around 1000ms (sometimes less sometimes more) so thanks again

    Friday, June 14, 2013 5:25 AM