I have a structure for storing item properties on SQL Server:
ItemId PropertyId Value 1 1 a 1 2 b 2 1 a 2 2 5
Currently there are over 130000 items and 10000 properties and the numbers are growing. Current row count is a little over 15M. If I created a pivot table for this data, it would have a little over 1.3 billion cells, 15 million of which are not null.
Users can form custom expressions on this data like:
X: P1 = 'a' (rule X selects items which have property 1 with value 'a') Y: P2 <> 'b' Z: P3 like '%c%' T: P4 > 5 (rule T selects items which have property 4 with a value greater than 5)
and they form filters by using expressions like:
(X AND T) (items that match both X and Y) (X AND Y) OR (Z AND T) (X OR Y) AND (Z OR NOT T) (X OR Y AND T) OR Z
I need to query the result of a few filters (generally 4 or 5) as a response of a single web request. How can I do this fast? Is there a storage method or a super efficient algorithm to get this filter results?
It'd be great if this is possible on SQL Server but I am also open to solutions like storing this portion of data on a no sql database.