SQL Azure vs Table Storage - queryig Entity parameters with "contains"

Question

I am playing with Azure Table Storage to get my head around it as a platform for a Big Data Project. As test data I am using the UK Companies House data feed to provide me with 3.5 million UK company records. I have successfully created an Entity and loaded a ton of the entries into my table "Companies" but think that this model won't work for me due to my "search" or query requirements being on more than just the PartitionKey and RowKey.

My Company class looks like this:

public class Company : TableEntity
{

    public Company() { }

    public Company(string companyNumnber)
    {
        CompanyNumber = companyNumnber;


        this.PartitionKey = "Company";
        this.RowKey = companyNumnber;

        RegAddress = new Address();
    }

    public String CompanyNumber { get; set; }
    public Address RegAddress { get; set; }
    public String CompanyName { get; set; }
    public String PhoneNumber { get; set; }
    public String CompanyCategory { get; set; }
    public String CompanyStatus { get; set; }
    public String CountryOfOrigin { get; set; }
    public String DissolutionDate { get; set; }
    public String IncorporationDate { get; set; }
    public String URI { get; set; }

}

public class Address 
{

    public Address() { }

    public String CareOf  { get; set; }
    public String POBox { get; set; }
    public String AddressLine1 { get; set; }
    public String AddressLine2 { get; set; }
    public String PostTown { get; set; }
    public String County { get; set; }
    public String Country { get; set; }
    public String PostCode { get; set; }
}

This is all fine and I can load the entities into Table Storage and I can query the table based on CompanyNumber, but I am going to need to also query the table on CompanyName and that is NOT going to be limited to a eq "equals" operator... I NEED to be able to do a "contains" on that property and it looks like that is not possible.

Am I right on that? If that is the case what is the work around? It seems to me that I am "stuck" with hosting these 3.5 million records in SQL and then use Table Storage to store "related" content with PartitionKey company number and then do a lookup that way.

I was hoping it would keep costs (of SQL Azure) down, but it seems like I might need a hybrid approach.

I would love to hear others comments on this.

How many unique company names do you have; 3.5M? And how fast do you need that query to run? — Herve Roggero
Well, companies house provides a data list of 4 CSV files with 850,000 company records in each. The initial load can take any amount of time, that doesn't matter, but there is no "changeset" so every month I need to look for "new" items and upload them. I can run a separate job to check for updates on each item once they are loaded in. The main problems are for end users, on my website, who will want to lookup the company details... they will want to enter part of a company name to find the details (and not necessarily the start of the name) — Mark Stokes
OK - then I agree that you should look into an indexing platform; SQL Azure is a good candidate. For your records, if you keep it simple, you shouldn't need a very large database, so you should be able to keep the costs down to a bare minimum. — Herve Roggero
Thanks Herve, yes, for this requirement I need to keep the structured content in the SQL database. I know it won't go much above 3.5million records and there's not a lot of data. I can use table storage for the big data element of the project. Thanks for your help. — Mark Stokes

bedane bedane · Accepted Answer · 2014-05-28T09:58:11

It looks like you'll have to use an indexing library featuring text search such as this one.

Here is a relevant discussion on MS forums.

In the end it all depends on your needs, if the data isn't that big, you might want to use SQL azure so you can use the text queries.

Another option is to organize your fields so that you can search the required strings using prefix matching, which tables queries support. (You can use a "StartsWith" query instead of "Contains")

SQL Azure vs Table Storage - queryig Entity parameters with "contains"

1 Answers