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 ).