2

I have Azure table called users which serveral fields related to user, I have PartitionKey as UserId and RowKey as CompanyId and I want to query the table via the Email field which is not an PartionKey or RowKey, Is it possible to query the table like this?

If yes then it is good to query without the PartitionKey and RowKey

Jenish Zinzuvadiya
  • 999
  • 2
  • 15
  • 29

2 Answers2

6

You can query without using PartitionKey or RowKey and table service will not throw any errors however please keep in mind that this will do a full table scan starting from 1st PartitionKey/RowKey combination till the time matching entities are found.

This may not be an issue if your table is small. However it will be an issue if your table becomes big. There would be a chance that you don't get any data back but just the continuation token and you would need to query your table again using that continuation token.

If you want to query on Email field, I would highly recommend duplicating your data so that the Email field becomes the PartitionKey.

Not sure if you have read this already, but I would highly recommend reading Azure Table Storage Design Guide: https://azure.microsoft.com/en-in/documentation/articles/storage-table-design-guide/.

Gaurav Mantri
  • 128,066
  • 12
  • 206
  • 241
  • I tried to do that, it returning the data but its too slow, now I was thinking to save the email field in `RegisteredUser Table`. – Jenish Zinzuvadiya Apr 06 '16 at 15:10
  • `it returning the data but its too slow` --> This is what I meant by doing table scan. If you monitor network traffic using a tool like Fiddler, you will notice that there are many calls made to Azure Table Storage Service. – Gaurav Mantri Apr 06 '16 at 15:12
  • Thanks sir, Now i am final about this to do via `RegisteredUserEmail` table . – Jenish Zinzuvadiya Apr 06 '16 at 15:14
  • @GauravMantri isn't it better to move the email field to the RowKey? The email field is unique, so if you'd use email as a partitionkey you'll end up with having partitions with only one record of data. A unique key is something you want to have in the RowKey if I'm not mistaken. BTW it's also possible to search on RowKey only. – Jorn.Beyers Nov 05 '16 at 11:09
  • @Saturation it can very well be. However the performance would depend on the choice of the `PartitionKey`. What do you propose for that? In one application (Have I been Pawned), I say that `PartitionKey` was chosen as email domain (e.g. hotmail.com) and the `RowKey` was chosen as the email name (e.g. something in case the email is something@hotmail.com). – Gaurav Mantri Nov 05 '16 at 11:23
  • In this case CompanyId could be used. But ofcourse this is all dependent on how one would like to use table storage. Email domain is not a unique key, so you won't end up with having a large number of partitions. – Jorn.Beyers Nov 07 '16 at 08:22
0

Performance by query

You can, but it will be full scan and it's the worst case of using table storage.

You should change your design and you should use at least one of the keys. You can create storage tables for your companies and make your email part of partion key.

Note: Image taken by this article

Erkan Demirel
  • 4,302
  • 1
  • 25
  • 43