The Suggested Shopping List Problem...

Question

question

Gustavo asked Oct 20, '09

The Suggested Shopping List Problem...

Imagine a web-based shop where users define product filters like: "its red", "its in the kitchen" or "has the word 'auto' on the name"; and every criteria has a weight that contributes to the final order of the suggested products.

After every criteria added/removed, the system should recalculate and report the top products to any online participant of that list.

This app is usefull when friends dont know what gift to give, and based on general tips the system try to guess the most suited gifts.

Right now i am saving a copy of the product key into a table ( that cant be #temporary or @variable ) and for each new criteria i am adding a new column, which is filled according to the matches it find. ( Upon removal of the criteria, i just update those column with zeros ).

Criterias can be added by multiple users on several days, and that shopping "suggestion" list stays on the database until its finalized ( or after 90 days of inactivity ).

The product list can grow up to millions of products ( slow insert/update statements ), and we have several Full Text Search enabled ( slow select statements ). Scale this search for up to a hundred concurrent users per list and thousands of lists enabled in paralel.

Unfortunatelly this approach is high cpu/io intensive and requires a LOT of disk space. I have spent nights trying to find ways to implement a better solution. Then i find some material about "Long Transactions" and become curious.

A "Long Transaction" can be defined as a standard transaction that can be "paused" and continued after a long period of time, even if that user disconnected and reconnected. It also enables someone to start a transaction and let someone else to complete it.

This concept is interesting in several aspects, but basically it can transform any web-based application into a client-server one, thus avoiding tricks like cookies, "fat" session variables, lots of temporary tables and several others.

Does SQL Server ( or any other database ) implements "Long Transactions" ? If not, what could be done to implement anything similar to this in the most automatic way.
What other solutions could you provide to solve The Suggested Shopping List Problem ?

Thanks. ( and sorry for my english, i am not a native speaker ).

t-sql sql-server performance transaction

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-10-20T16:33:52Z

Tom Staab answered Oct 20, '09

Edit: added weight to suggestion list criteria cross table and provided report example

First, the long transaction concept sounds scary. Transactions are used to isolate a batch of changes by performing all necessary locks, making the changes and then deciding to commit or roll back the entire batch. Keeping the transaction open for a long period of time or allowing a different user to continue a transaction both sound like they would cause much bigger problems than they would solve.

What about this database design:

CREATE TABLE products
(
  product_id int, name varchar(100), description varchar(max)
)

CREATE TABLE criteria
(
  criteria_id int, criteria_text varchar(100)
)

CREATE TABLE suggestion_list
(
  suggestion_list_id int, list_owner_id int, name varchar(100), description varchar(max)
)

CREATE TABLE product_X_criteria
(
  product_id int, criteria_id int
)

CREATE TABLE suggestion_list_X_criteria
(
  suggestion_list_id int, criteria_id int, weight decimal(4,2)
)

The last 2 tables are cross-tables for many-to-many relationships. A criteria administrator would be responsible for putting together the list of valid criteria. A product administrator UI would allow them to add or remove criteria that a product meets. An administrator for a given suggestion list (determined by the list owner id referencing back to a users table) would be able to add or remove criteria for that list.

Here's an example of a report:

SELECT TOP(10) p.name AS [Product Name], SUM(slxc.weight) AS [Weighted Score]
FROM products p
    INNER JOIN product_X_criteria pxc
    ON pxc.product_id = p.product_id
    INNER JOIN suggestion_list_X_criteria slxc
    ON slxc.criteria_id = pxc.criteria_id
    INNER JOIN suggestion_list sl
    ON sl.suggestion_list_id = slxc.suggestion_list_id
GROUP BY p.name
ORDER BY SUM(slxc.weight) DESC

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question