Session storage vs. frequent DB calls

[Rune]

Hi,

I'm developing web surveys where users logon to a web survey and answer up
to about 50 questions, one after the other, shown only one question at a
time. The answers are saved in a database.

A fellow developer and I are discussing when is the best time to save the
answers. We have two approaches in mind:

Approach 1:
After each question is answered, the answer is saved in the database.
Sessions are only used to store the User ID.

Approach 2:
After each question is answered, the answer is saved in a session variable.
When the user has finished the entire survey, all the answers are saved at
once in the database. Also, if the user logs out, or if the session times
out, the answers answered so far are all saved in the database at once.

The amount of actual data sent to the database is the same in both
approaches, but Approach 1 have much more frequent calls to the DB, thus
creating an overhead of opening and closing connections. I'm not sure how
significant that is. On the oter hand, Approach 2 store a lot of data in
sessions. I'm not sure how bad a thing that is.

Which approach is better and for what reasons? I'd be happy to supply more
information if needed.

If this question is better asked in a different newsgroup, please let me
know.

Best regards,
Rune

 

[CJM]

My answer would be that it doesnt matter.

The overhead for multiple DB calls is negligible, especially when connection
pooling is utilised. The overhead for storing so little information in a
Session variable is negligible, unless you have an VERY large number of
concurrent users.

You could avoid both if you really wanted to: pass the concatenated answers
to the next page (encrypted, of course) in a querystring. Store the
concatenated answers in a hidden form field, and POST to each page... I'm
sure there are many other weird & wonderful ways of doing it, but does it
really matter?

So I would concentrate on functionality. Do you want to abandon a survey if
not completed or do you want to salvage what you can? If you want to keep
the data from an unfinished survey, you can use the DB route or you could
store the answers in a cookie. If you want to abandon unfinished surveys,
then it doesnt matter what you do.

hth

Chris

 

[Rune]

My answer would be that it doesnt matter.

The overhead for multiple DB calls is negligible, especially when
connection pooling is utilised. The overhead for storing so little
information in a Session variable is negligible, unless you have an
VERY large number of concurrent users.

Some of out previous surveys (using multiple DB calls) slowed down the
server a lot when many users were connected at the same time, so it seems to
matter.

You could avoid both if you really wanted to: pass the concatenated
answers to the next page (encrypted, of course) in a querystring.

The querystring can only hold 1024 characters in total, which in this case
is not enough.

Store the concatenated answers in a hidden form field, and POST to
each page...

This would mean transfering the same data back and forth between server and
client again and again. This seems very ineffecient.

I'm sure there are many other weird & wonderful ways of
doing it, but does it really matter?

Yes, I would say so. See above.

So I would concentrate on functionality. Do you want to abandon a
survey if not completed or do you want to salvage what you can?

The idea is that the user can log off and return later and finish the rest
of the survey. Thus we save data from unfinished surveys.

If
you want to keep the data from an unfinished survey, you can use the
DB route or you could store the answers in a cookie.

We use session cookies to be able to use sessions, but we don't want to
force the user to turn on cookies for long term storage of information.
Apparently many people use high security and privacy settings.

Rune

 

[Aaron [SQL Server MVP]]

We use session cookies to be able to use sessions, but we don't want to

force the user to turn on cookies for long term storage of information.

So store it in a database and make them log in to retrieve half-finished
forms.

 

[CJM]

Some of out previous surveys (using multiple DB calls) slowed down the
server a lot when many users were connected at the same time, so it seems to
matter.

What database are you using? I'm not sure what kind of numbers you are
talking about but unless you really do have LOTS of concurrent users you
shouldnt have too many problems.

The querystring can only hold 1024 characters in total, which in this case
is not enough.

To be honest, I kind of made an assumption that it was a multiple choice
survey, in which case 1024 should be plenty.

This would mean transfering the same data back and forth between server and
client again and again. This seems very ineffecient.

It is, I guess. but it lowers the load on your DB, if that indeed is the
bottleneck.

The idea is that the user can log off and return later and finish the rest
of the survey. Thus we save data from unfinished surveys.

Fair enough... in which case, the DB solution seems the best solution,
doesnt it?

Obviously, only you know how much data and of what type it is... but it
seems clear from what you have said that the Db route is indeed the most
sensible route.

If you are using and industrial strength DB, e.g. SQL Server/MySQL etc, then
you shouldnt have too much of a problem. If you are, it might be worth
posting some code examples to see if we can streamlines your code a
little...

Chris

 

[Rune]

Actually, and I feel slightly stupid saying this, the surveys so far have
been running on an Access database (which explains the slow-down), while the
future planned surveys will probably run on mySQL - not Access anyway.

The thing is, while I have been an advocate of the frequent DB calls
approach (mainly due to convenience and ease of implementation), my fellow
developer was suggesting the "store in sessions, save all data in DB at the
end" approach, due to the lower frequency of DB calls. - Hearing you guys
say that the DB calls overhead really is neglible (when run on a proper
database) is very nice actually.

Thanks for the feedback.

Rune

 

[Mark Schupp]

One problem with the session-based approach is that you will not be able to
save the data on session-timeout as you mentioned earlier. I believe that
when the session_onend routine in global.asa is called the session data is
already gone.

 

[CJM]

Plus you can rely on Session_OnEnd actually firing...

 

[CJM]

Er...that was supposed to be a *can't*