How to query a very long list of properties fast

Question

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.

Answer 1

You will need carefully constructed indexes on your table, based on an iterative session with the SQL server to ensure that the engine selects your indexes and avoid full table scans.

I suppose that a, b, c and d are user provided values. If so, I would expect X, Y and T to be easy to create indexes for, but that the "like" clause of Z will be a killer since generic text search is very space requiring and you still risk needing full table searches. I do not know if SQL Server supports full text search directly without doing full table searches.

So - you need to learn how the SQL Server planner works deciding how to evaluate your SQL and put in indexes to avoid full table scans.

Answer 2

From what you mention I think a quick full load and in-memory search would probably be the best option to start with. 15mln items shouldn't take too long. Loading the data quickly enough will most likely be your bottleneck in that case. Check if SQL server is quick enough, or if can you keep the data in-memory, or if you can/should use NoSQL solutions.

If you know more specifics about the type of filters used you can optimize from there. So log the queries.

Answer 3

Take into consideration what the requirements are for data modification before planning your indexing. And you're going to need indexing.

Having everything in memory in nice if you have enough (Is there ever enough.).

Depending on your version of SQL server (Enterprise offers the most features in a production environment.) you may be able to take advantage of table and index partitioning.

You can create indexed views, but having a lot of data modifications transactions may not make this ideal (Otherwise, we'd just index everything.).

Are you only dealing with one table that has no other related data tables?