Azure search or full text index?

Question

Data in sql azure, we have an existing webapi expose the data with odata. Issue is client want to make a call with filters with substring query on a few columns, which making performance really slow. we are debating at this point whether to use full text search index or use the azure search service, thoughts please?

Indomitable

you can test both and see which offers better performance for your scenario,but i see Azure search more powerfull :learn.microsoft.com/en-us/azure/search/… – Skirt

thanks, would azure search support logical multi-column substring filter? e.g. substringof('Alfreds', CompanyName) or substringof('adam', Name) or substringof('manager', title)? – Indomitable

I never tried,but it seems to be more powerfull based on the docs – Skirt

Answer 1

Some of the considerations and tradeoffs between hosting search in Azure Search vs. using SQL Server FTS are captured here.

As pointed out above, Azure Search can index in-database data - see Connecting Azure SQL Database to Azure Search using indexers.

Malina

Answer 2

You can point Azure Search at your AzureSQL database and it will index it without you having to write code, but Azure Search is a service you have to pay for on hourly basis and you can learn more information about it here.

Azure Search is recommended for performing searches on various sources and application

Azure search can be used instead of Full-Text search, but If you need to join search results with other tables, then Full-Text Search is recommended.

Hope this helps.

Glaciate

thanks, would azure search support logical multi-column on same table substring filter? e.g. substringof('Alfreds', CompanyName) or substringof('adam', Name) or substringof('manager', title)? – Indomitable

To my knowledge, Azure Search does not support partial matching other than prefix matching. More about it on this URL: learn.microsoft.com/en-us/rest/api/searchservice/… – Glaciate

I thought it support contains, but with my 2 minute poke around it does appears search only support prefix, e.g. Home* work, but home returns nothing, or am I missing anything, this is just sad, is not full text search anyhow – Indomitable

As I mentioned, my experience it works as prefix matching only. One more thing I would like to suggest you since it seems you will choose -Full-Text Search. Scale up the SQL Azure tier before creating full-text indexes, once they have been created scale down. – Glaciate

Azure Search supports regex searches on multiple fields (using Lucene query syntax), as well running searches as part of a filter (OData $filter expression) - see search.ismatch function. For more general tradeoffs between SQL FTS and Azure Search, see my response. – Malina