Azure search filtering performance over Edm.Int32 and Collection(Edm.String)

1
votes

I'm using Azure Search on my e-commerce site, and now want to implement filtering.

I've faced issue with performance. I have index with products. Each product belong to category. Each category can have nested subcategories. My business purpose is when customer is on category page i need to show products even from subcategories, so i have doubts about how to store this relation(products to categories) in azure products index. I'm considering two possibilities:

  1. I can store only products category id in field with type Edm.Int32. Then when customer goes to this category i query to my sql server to get all subcategory ids and then construct my query to index like this

categoryId eq 34 or categoryId eq 36 or categoryId eq 37 ...

  1. Other way is to create field with type Collection(Edm.String) and to store products category id and nested categories ids in this field and then my query to index would look like this

categoryIds/any(c: c eq '35')

So which way will be faster?

1
azurefaceted-searchazure-cognitive-search
You mention querying SQL Server in option #1. Would you have to do so for option #2 as well? – Bruce Johnston
Yes, but that's not the point. I wrote that just for example. I can store categories ids in local cache, so it wont be performance problem to fetch category id or category id with subcategories ids. – Volodymyr Gorodytskyi
Thanks. I just wanted to clarify this point before trying to answer your question. – Bruce Johnston

1 Answers

1
votes

Option #2 is likely faster since the number of documents in the index will be far fewer, but the only way to be sure is to run some experiments with your data and queries. Overall query performance is going to depend on other factors like whether you're doing full-text search, faceting, geo-spatial, etc.