Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
1  
Is this what you need? en.wikipedia.org/wiki/Bitmap_index Consider doing this outside of the database and simply in memory. download.oracle.com/javase/1.4.2/docs/api/java/util/BitSet.html –  Job Jan 27 '11 at 23:20
    
I read a little and bitmap indexes seem promising. I will do a research on that, thanks. –  Serhat Özgel Jan 27 '11 at 23:29
    
stackoverflow.com/questions/2010132/… –  Job Jan 27 '11 at 23:47
    
This question is in wrong forum –  Amir Rezaei Jan 28 '11 at 5:53
add comment

3 Answers

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.

share|improve this answer
    
a, b, c and d are user provided values. I may try to drop the business requirement for "like" statement but I am not sure if I can do that. Other than that, this is a useful answer, thanks. –  Serhat Özgel Jan 27 '11 at 23:34
    
+1, this is typical SQL stuff. Proper indexes and query structure are all thats needed here. –  GrandmasterB Jan 28 '11 at 4:50
add comment

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.

share|improve this answer
add comment

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?

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.