What would be the best way to store the questions and responses for a survey where I need to keep the traffic on the database to a minimum?

Background

I am writing a survey that is going to a large audience. It contains 15 questions and there are five possible answers to each question along with potential comments.

The user can cycle through all 15 questions answering them in any order and is allowed to leave the survey at any point and return to answer the remaining questions.

Once an answer has been attempted on all 15 questions a submit button appears which allows them to submit the questions as final answers. Until that stage all answers are required to be retrievable whenever the user loads the survey page up.

The requirement is that the user only sees one question on a page and 'Previous' and 'Next' buttons allow the user to scroll through the questions.

Requir开发者_如何学Goement

I could request the question each time the user clicks a button and save the current response and so on but that would be a large number of hits to a database that is already heavily used. I don't have the time to procure a new server etc so I have to make do with what I have. Is there any way I can cache the questions on the user machine and/or responses? Obviously I need the response data to be secure and only known to the user so I feel a little bit stuck as for the best way of doing this. Any pointers?

I am prepared to offer a bounty of 100 points on this question if it means I get some good quality discussion and feedback going.

Unless there's a reason for using a database, you could always store the results in flat files on the server itself. It doesn't sound like the data you're storing is relational in any way. Worst comes to worst, you could always insert them back into a relational db as a batch job every night.

Another option would be the application cache. However, if your web server suddenly crashes on you, you risk losing information from there.

You could also store the values in the user's cookies.

Based on my personal experience (serving thousands of short survey pages per second) I suspect your fears are unfounded. Among other reasons, the DBMS will cache such small amounts of data far more efficiently that you can.

I've tested this, loading the questions and answers into an Application-scope collection at start up, and serving them from memory after that - often it made no difference at all.

Your alternative is to send everything at once to the browser, and write it as a javascript application, storing the data in (encrypted) cookies and only hitting the database when the whole thing is done. This is tedious but not difficult.

You have three requirements that need to be balanced:

• users must be able to return to their survey at any time
• answers entered by users must be saved with the least possible chance of data loss
• need to minimize database hits

Any solution that involves caching answers in a volatile place (cookies, session, etc) will increase the risk of data loss. The final solution depends on how you rank the three requirements in importance. If the db issue is at the top, then you will either need to risk data loss, or spend a lot of extra time coding a solution using some temporary storage scheme (like Kevin's flat file idea).

A couple of folks suggested that you may be optimizing prematurely. I suggest you consider that idea first - maybe this whole thing is moot.

However, assuming that your db situation is a real problem, I think your best balance of requirements will be a system that saves answer to the db immediately (to prevent data loss) but carefully manages when you actually have to hit the db.

1. When the app starts up (or when the first user requests the survey) load the survey and its questions into application cache. If any of the questions have a pick list of possible answers, load these also. You will only have to hit the db once during the application lifetime (or your cache duration) to load survey data.
2. When a user starts their survey, run a single query to load any existing answers (in case they are a returning user) into an object in session - could be as simple as a <List>string. (If you can somehow identify a new user without having to hit the db, then you can skip this step for new users.)
3. Use the session answer object along with the survey question object in app cache to populate each page without hitting the db again.
4. When the user submits an answer, compare it to the session answer object to see if it has changed (she may be just clicking 'next' on a page with a previously entered answer). If the answer is new, or has changed, the save it to the db and to the session answer object.
5. When the user leaves the survey, you don't need to do anything - everything is saved already.

With this scheme, you hit the db once to load the survey, once for each user when they start (or restart) the survey, and once for each new or modified answer. Probably not as much of a reduction as you were hoping for, but it gives you the best data protection.

If the database trips are a problem, you can cache them in the web server (or wherever your application resides) but it sounds like each answer needs to be recorded as the user goes to the next question.

If the questions and possible answers are identical for everyone, I would definitely cache them in the application layer - this can be stored in the Application object. In any case, you could certainly optimize the database calls to return the results as efficiently as possible - i.e. multiple result sets or a joined result set from a single stored proc. If you don't mind multiple copes for each session (or if there is variation), you can stored it in the Session object. Storing it on the client (i.e. a cookie) is not really secure and kind of pointless from a web server-client bandwidth saving persepective.

This sounds a lot like premature optimization to me, though.

Your scenario is a perfect candidate for Predictive Fetch Pattern. I would suggest that you cache all your questions. When the user signs in use the pattern to fetch the first 5 answers (if they have given any answers) and based on their navigation (where their current question is) get the information from the Response object or from the DB.

HTH

Not sure of the languages etc you are using, but most have an application cache. I would store the questions there, and retrieve them from the database and store them when they are not in the cache (when the application recycles).

As for the answers, are the users logging in some how? Is it feasible to save answers in a cookie until all questions are answered?

Edit: If cookies aren't reliable enough, you could store (in the application cache) a list of queries (inserts/updates) to be executed, they would not be executed until an a query limit was reached or under certain conditions (i.e. execute the query list when a user requests answers that are in the list, execute list when the application recycles, etc).

Pretty crude, but you get the idea:

if (function == "get question" && userQuestionIsInQueue) || function == "finish survey"
execute(Application["querylist"]);
continue as normal...

if function == "submit answer"
if Application["querylist"] == null
Application["querylist"] = newAnswerQuery;
else
Application["querylist"] += newAnswerQuery;

You'd also need to add execute(Application["querylist"]) to the recycle event, I believe you can hook it in the global.asax

Edit 2: I would also accumulate all database transactions for a request into 1, you if you did have to execute the list, then followed by getting the answer for the user, do them in the same transaction and save a trip. Common practice when optimizing.

This is a classic problem to do with maintaining state between pages in a browser based system. Im also assuming that we want this data to persist even if the user logs out and comes back later. Here are the options:

• With a high availability server we can keep a single collection of 15 answers in memory (not session) for this user (probably not a good idea and not easily load balanced)
• We denormalise the 15 answers into 1 row of a sql table
• We persist the data on the client using a cookie or localStorage (IE8).

My feeling is that the first two options are probably not what you are looking for, so lets explore the last option.

You could quite simply store the answers in a cookie. There is a small chance that this could get lost, and that the user may log in from another machine, but this may be an acceptable risk. With with latest browsers that support HTML5 (inc IE8 afaik) you get the benefit of localStorage which is not as easily deleted as a cookie. You could fall back to cookies if this wasnt available.

Cookies can be encrypted if required.

I would like to offer you the new feature of HTML5 which is called Dom Storage but since only the new browsers are supporting it, it could be a problem using it at this point.

With DOM Storage, you can store data on user browser. Since it can store up to 5MB per domain in Mozilla Firefox[3], Google Chrome, and Opera, 10MB per storage area in Internet Explorer, you can store answers and question ids in the DOM Storage.

Even with DOM Storage, let alone Database hit, you can reduce server hits as well.

Since we all know working with cookies is hassle sometimes and it can store 4kb, the easiest way is now to store key-value information in DOM Storage.

You can store key-value information specifically for sessions as well as locally. When session ends, the session based info will be wiped off from the browser but if you store local based values, even the user closes the tab, the key-value will remain for a while.

Example Code:

<p>
  You have viewed this page
  <span id="count">an untold number of</span>
  time(s).
</p>
<script>
  var storage = window.localStorage;
  if (!storage.pageLoadCount) storage.pageLoadCount = 0;
  storage.pageLoadCount = parseInt(storage.pageLoadCount, 10) + 1;
  document.getElementById('count').innerHTML = storage.pageLoadCount;
</script> 

You can learn more about DOM Storage from the links below :

• https://developer.mozilla.org/en/DOM/Storage
• http://en.wikipedia.org/wiki/Web_Storage
• http://msdn.microsoft.com/en-us/library/cc197062%28VS.85%29.aspx

do you mean...a cookie?