1

I'm trying to design the best way to index my data into Azure Search. Let's say my Azure SQL Database contains two tables:

  • products
  • orders

In my Azure Search index I want to have not only products (name, category, description etc.), but also count of orders for this product (to use this in the scoring profiles, to boost popular products in search results).

I think that the best way to do this is to create a view (indexed view?) which will contain columns from products and count of orders for each product, but I'm not sure if my view (indexed view?) can have its own rowversion column, which will change every time the count changes (orders may be withdrawn - DELETED - and placed - INSERTED).

Maybe there is some easier solution to my problem? Any hints are appreciated.

Regards, MJ

Maciej Jakubczyk
  • 137
  • 1
  • 9
  • One more thing which may be important. I'll be updating Azure Search index every 5 minutes and my aim is to reindex only those products, whose count of orders has changed. – Maciej Jakubczyk Mar 13 '15 at 16:58
  • 1
    A view, indexed or not, cannot have its "own" data. All data is computed. – usr Mar 13 '15 at 17:21

2 Answers2

0

Yes, I believe the way you are looking to do this is a good approach. Some other things that I have seen people do is to also includes types For example, you could have a Collection field (which is an Array of strings), perhaps called OrderTypes that you would load with all of the associated order types for that product. That way you can use the Azure Search $facets features to show you the total count of specific order types. Also, you can use this to drill into the specifics of those order. For example, you could then filter based on the selected order type they selected. Certainly if there are too many types of Orders, perhaps that might not be viable.

In any case, yes, I think this would work well and also don't forget, if you want to periodically update this count you could simply pass on just that value (rather than sending the whole product fields) to make it more efficient.

Liam Cavanagh - MSFT
  • 1,426
  • 7
  • 5
-1

A view cannot have its "own" rowversion column - that column should come from either products or orders table. If you make that column indexed, a high water mark change tracking policy will be able to capture new or updated (but not deleted) rows efficiently. If products are deleted, you should look into using a soft-delete approach as described in http://azure.microsoft.com/en-us/documentation/articles/search-howto-connecting-azure-sql-database-to-azure-search-using-indexers-2015-02-28/

HTH, Eugene

Eugene Shvets
  • 4,561
  • 13
  • 19
  • Thank you. I've came up with another idea. Let's say I have two tables: Products and Variants, and both have their "own" rowversion. In the view (which JOINs both tables) I compute a common rowversion column using such statement: `cast((cast(p.LastVersion as int) + cast(v.LastVersion as int)) as rowversion) AS CommonLastVersion` CommonLastVersion is not unique, so I can't index this column. Would it be a problem? It's recommended in the provided link, that the rowversion column is indexed. – Maciej Jakubczyk Mar 18 '15 at 13:32
  • Actually, I can have index but not unique index, so I think this attitude will work. – Maciej Jakubczyk Mar 18 '15 at 15:20
  • You can preserve the uniqueness by taking the maximum of two rowversion values. Rowversion counter is global for a database, so values in different tables are always unique – Eugene Shvets Mar 18 '15 at 18:06