Implement search in Azure SQL

问题:

I'm moving an .NET application to Azure since the amount of data stored is getting bigger and the concurrent users is increasing. To be able to search I need a robust solution to search the SQL which scales.

I need to return matching orders when searching (in one single field, google style). The data is stored in a few columns in these related tables: Orders, Customers and Addresses.

I've read that fulltext search isn't available in Azure so I've looked att Lucene but got a bit worried about storing the index in a blob. It doesn't seem right to hit the blob storage at every search.

Does anyone have any suggestions on how to accomplish this i a robust way?

回答1:

We use Solr/Lucene for best full-text search results. Indexed data is stored into local storage (up to 2 TB).

Local storage is like c: disk drive, but you must declare it in the definition file and get reference to it by calling RoleEnvironment.GetLocalResource.

If you want to speed up startup time (initial full index) you can save/download index data to persistent blob storage.

Another option is to use Azure Drive, but keep in mind that ony one instance can write to it.

回答2:

One solution would be to build a "key" table.

Have one column that contains the search strings you are after, and a second column that is the key back to your orders table.

You would need to create appropriate triggers on the CRUD functions of you Order, Customer and Address tables.

By flattening the searchable fields from all three into one key table, you can then do indexed searches very quickly for a minimal space trade off.

One caveat, queries in SQL Azure are single threaded, so table-scans will be ugly.

example: where key like 'abc%' will use the index example2: where key like '%abc% will do a table scan and on a large set will be slow.

I maintain an 11,000,000 row catalog table and server 3 million page views per day on sql azure in < 200ms, using these types of techniques.

回答3:

Azure has also rolled out a search as service api that works quite well.

I have it working against large data and it performs quite well.