StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Do you mean that you retrieve all entities in code to perform the query or that the Table service has to scan all entities to return the results? In either case you are probably using Talbe storage the wrong way.</p> <p>Azure Table Storage is not an RDBMS and you shouldn't treat it this way. It is designed to allow ultra-fast and scalable modifications, easy partitioning and indexed access. The only indexed access is by the partition and row key. It is definitely not suitable for ad-hoc queries and reports. In this respect it is not that different from most NoSQL databases. </p> <p>I suspect you are trying to retrieve query results using ad-hoc filters and display them in a datagrid. This is an ad-hoc query/reporting scenario for which the Table Storage is not suitable. </p> <p>That said, there are ways you can optimize reporting even with table storage. The most important thing is to understand that reporting data (let's consider datagrids as reports for this discussion) should be separate from transactional data - that's what <a href="http://www.udidahan.com/2009/12/09/clarified-cqrs/" rel="nofollow">Command-Query Responsibility Separation</a> (CQRS) is all about.</p> <ul> <li>You can use multiple tables to store the results for different reports and update them asynchronously while processing transactions. You can use partition keys that combine multiple field values in each table that make retrieving the report's rows easier. You still have to define your reports at the server level. In this case, the scenario is closer to retrieving report snapshots rather than querying.</li> <li>You can extract the reporting data to SQL Azure and use SQL for ad-hoc reporting. Updating will have to be done asynchronously as before, but you gain ad-hoc querying. SQL Azure is much more expensive though.</li> <li>An unusual solution, suited for fairly static reports (e.g. montly or weekly summaries) is to use BLOB storage to store the report data in JSon format (or whatever suits you). You still have to define the appropriate partition and row keys. This is similar to creating report snapshots in SQL Server reporting services.</li> </ul> <p>The most suitable solution depends on your specific scenario:</p> <ul> <li>If your application processes business transactions (eg. orders, phone calls etc) your main table should use keys that optimize TX processing and the report tables should use keys that optimize report retrieval.</li> <li>If you are building a forum, web site or CMS service, you probably can create one "Posts" table per forum for the posts, partitioned by threadid (just an idea) and separate "index" tables for tags, users etc that link to the posts table. </li> </ul> <p>That said, Table Storage does support the OData/WCF Data Service query operators (filter etc), skip <a href="http://www.odata.org/developers/protocols/uri-conventions#SkipSystemQueryOption" rel="nofollow">($skip)</a> and take (<a href="http://www.odata.org/developers/protocols/uri-conventions#TopSystemQueryOption" rel="nofollow">$top</a>). </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload