I recently was building a search page which could return results to span multiplabe pages (i.e. Viewing Page 1 or 20). Instead of having to save my spot each time and get the Query each time the page loads, I decided to bookmark the query the first time it is opened (a bookmark for where I need to start on each page) and then save the recordset to a session variable so I will not need to requery each time.

After doing this I began to wonder if there were an performance issues or other problems with this method. I am not worried about the session timing out since it is an admin site and if it does they get booted anyway (they are authenticated w/ session variables). The site does have potential to be fairly busy during some periods but not constantly. Please let me know your thoughts and if you have any better ways of accomplishing this same result using different means (I did use a stored procedure before this).

Thanks

You were correct to worry!!

Never store recordsets (or objects) in session scope.

Its not threadsafe and can take up much of the servers resources.

Stick to making connections on every page, its not overly expensive at all, given the correct coding techniques.

Better yet - use Stored Procedures!!!! Get your results from the stored proc, transfer your results to an array using Getrows() or even Getsring, and quickly close the connection.

Multiple connections ACROSS pages are optimsed using Connection Pooling so you shouldn't worry about that.

Cheers,
Andrew

First of all thanks for your reply it was very helpful. I just had one more quick question. Why would saving the object to a session variable not be threadsafe. I knew it would use a lot of server resources, but I do not understand the other part. The way I think of it, since the server creates a new session for every user that hits the page, I do not see why it would not be threadsafe?

Thanks again,
Gabe

This article probably explains it better than I could:
http://www.yasd.com/devaspcomp2/bonus/sessobj.htm

Cheers,
Andrew

Thanks again for the help!

Gabe

The server resource hit should be enough. This solution would not be scaleable. Only use session variables when absolutely necessary. And they should only be used for small data types. Which database are you using - some of this data may be cached on the database side so multiple queries are already optimized ? Have you looked at asp.net ?

i knew asp.net would be a solution but the client i am working for did not want to take it that far...thanks for the post i have it taken care of ... just moved it to a stored procedure.

Good choice.