CODEWITHSUNDEEP
azure-storageazure-table-storage
Jérôme Tarere
Jérôme Tarere

Reputation: 427

Is it possible to query a big Azure Table with specific search parameters but without PartitionKey?

Let say I have a table entity as it :

Partitionkey Rowkey table userid datecreated

Let's take a query which works great, with it I can retrieve all entries wich have a particular ID (which is used as the PartitionKey)

Dim azt_query As CloudTableQuery(Of adlog)
azt_query = azt_context.CreateQuery(Of adlog)("adlog").Where(Function(e) (e.PartitionKey = "myid" And e.table = "mytable" And e.userid = "myuserid" And e.datecreated >= dateStart And e.datecreated <= dateEnd)).AsTableServiceQuery()

Now I would like to query the table without knowing the PartitionKey but based on the USERID

It would be like that :

Dim azt_query As CloudTableQuery(Of adlog)
azt_query = azt_context.CreateQuery(Of adlog)("adlog").Where(Function(e) (e.table = "mytable" And e.userid = "myuserid" And e.datecreated >= dateStart And e.datecreated <= dateEnd)).AsTableServiceQuery()

But removing the e.PartitionKey = "myid" now it takes forever. I think the query want to retrieve all the rows of the table and search in it with the parameter.

But there is millions of rows. Querying with the partitionkey is actually pretty fast.

Is there a way to query this ? Can I retrieve all the rows of a particular USERID or I'm I stuck to the partitionkey level ?

Upvotes: 2

Views: 1413

Answers (2)

Brian Reischl
Brian Reischl

Reputation: 7356

You can query the table that way, but as you noticed it's not a great idea. Without the PartitionKey the storage server is doing exactly what you suspected: it's reading every single entity in the table. In Azure Tables parlance that's called a "table scan", which is very slow.

The only way around that is including the PartitionKey in the query. If you need to do queries like this a lot you might consider keeping a separate table with a PartitonKey/RowKey scheme that will make that query fast, or else using a SQL database with the appropriate indexes.

Upvotes: 3

Karell Ste-Marie
Karell Ste-Marie

Reputation: 1052

Both Partition and Row Keys are mandatory fields, while you can query without specifying them it will cause a table scan which may actually cause all results to be loaded from azure tables to your application where they will be checked row by row for validity.

Both Partition and Row Keys are indexed. The Partition key will actually be used to split up your table in between partitions to make access to some partitions (used more often) to be faster, within a partition, rows are all together.

We use the partition key here to break down the tenants of our multi-tenant application, then use rows to retrieve by specific ID. We find this method to be extremely fast but unfortunately like you observed, it's not very search friendly unless you are working with at least 1 of those two indexed IDs.

Hope this helps,

Upvotes: 0

Related Questions