Solved

Storing Recordset to Session Variable?

Posted on 2001-07-18
16
424 Views
Last Modified: 2012-05-04
Should I attempt to do this? I have a query which produces thousands of records and i would like to span over a couple of pages. I know you all have seen it, a page which says "Page 1 of 5" and has next and previous buttons. I need to do something like this. So should I store the recordset to a session variable to use it among the pages or will there be a performance issue? should i store the recordset to an array, store the array to a session variable, and destroy the recordset? would that be quicker? i need some clean code that runs FAST. thanks guys.
0
Comment
Question by:Maxim10553
  • 5
  • 3
  • 2
  • +4
16 Comments
 
LVL 7

Expert Comment

by:John844
ID: 6295324
the best way to do this is to use stored procedures.  I assume that you are using SQL for this many records.  I have the code to do this very efficiently, but not for 25 points.  :)

you should build a temp table(in SP) and only select the page contents that you intend to show.  do not return thousands of records unless you need them all.

0
 
LVL 8

Expert Comment

by:drittich
ID: 6295359
Another option is to persist the recordset to disk, e.g.

    Dim cn, rs, sql
    Set cn = Server.CreateObject("ADODB.Connection")
    cn.Open YourConnectString
    sql = "select * from YourTable"
    Set rs = cn.Execute(sql)
    'persist to disk
    rs.Save Server.MapPath("YourResults.dat"), 0
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

Now you can open the recordset anytime you need it like this:

    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.CursorLocation = 3
    rs.Open Server.MapPath("YourResults.dat"),,,,adCmdFile




0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6295368
several ways to do this depending on how your search is structured.

Pass the search createria and page number and recs per page each time you move to a new page and only return from the server the necessary records
Slow as has to perform the search each time and problems if the data changes.

Have a table into which the search results are stored keyed on session id and populate on search then get back the relevant pages records on following searches.
Slower on first page but faster thereafter - needs method of purging old data - doesn't respond to changes of data (good and bad point).

Have a table into which the search criteria and results are stored and any searches thereafter with same criteria just get the data.
Slow on first search for criteria - fast thereafter, needs some way of detecting data changes unless data is static.

Pass all the data to middle layer and hold for session. Middle layer handles the paging.
Very slow on first page very fast thereafter, uses bandwidth to server, needs method making sure resources are released between calls.

There are many other ways of doing this but these are a few ideas.
0
 
LVL 5

Expert Comment

by:raizon
ID: 6295427
If I understand you correctly what you are wanting to do is to Page through the record set displaying n amount of records per page.  

http://www.asp101.com/samples/db_paging.asp

is a good tutorial on how to achieve this.
0
 
LVL 5

Accepted Solution

by:
raizon earned 25 total points
ID: 6295441
This is a better tutorial on Paging through a record set then the previous one I gave you.
http://www.stardeveloper.com/asp_paging_1.asp
0
 
LVL 3

Author Comment

by:Maxim10553
ID: 6295721
drritch-> i have a question about your comment. How will hundreds of users accessing the data simultaneosly affect saving the recordset to a file? Will there be more than 1 file saved? Will it be overwritten? How does ASP/ADO handle multiple concurrent users? Please explain.
0
 
LVL 3

Author Comment

by:Maxim10553
ID: 6295728
I guess what I am asking is if ASP supports multi-threading
0
 
LVL 2

Expert Comment

by:shirjeel
ID: 6296133
hey man ist of all i think wat u want to do is to make pages for the records that come up with ur query is it. then dont use session variable coz if u use session variable and ur hits are getting increase much then it will make ur server crash coz ur windows nt donot have very good memory management. So wat i preffer u to chose ist 20 or wat ever no of records u want then store the last one's primary key and pass it on to the next page and then display records next to that value. As i did in www.baycareers.com.

i preffer this now it depends upon u the approach u r thinking is not right in my sense.

ASP and every server side scripting gives support to multithreading oook
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 8

Expert Comment

by:drittich
ID: 6296283
What I do is, for slow queries, I persist the recordset to disk.  Then all of the users can share the recordset.  They can check the date of the file and if it is old (e.g. more than 10 minutes old) it can be recached.  This method works well for data that changes slowly, but is not good where people need up to the second accuracy.
0
 
LVL 2

Expert Comment

by:shirjeel
ID: 6297272
in my views it will not suitable coz in this way they have to search the file too much i dont think so thats a good way to do this. did u saw my answer.
0
 
LVL 3

Author Comment

by:Maxim10553
ID: 6298407
shirjeel-> i cant view your web page, is the URL correct?
0
 
LVL 10

Expert Comment

by:makerp
ID: 6298640
if you put dritches code in the Application_OnStart function in the global asa it will be run once.
0
 
LVL 10

Expert Comment

by:makerp
ID: 6298653
although thinking about it if the query is simple storing the results in a file does little as remeber the database stores tables in a file therefore reading from the database or file is little different. the only big advantage would be if the query is complex (lots of where's etc), if this is the case you avoid the database having to repeat the query processing if you use the file
0
 
LVL 3

Author Comment

by:Maxim10553
ID: 6299273
Okay, I am using recordset paging but i keep having a problem, it keeps saying the recordcount is -1 even when there is data coming back into the recordset and when i try to use the .absolutepage property of the recordset it tell me the provider doesnt allow it. I tried both ODBC and OLEDB. anybody know whats up?
0
 
LVL 10

Expert Comment

by:makerp
ID: 6299330
you will need to include adovbs.inc for the constants

Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, Connection, adOpenStatic
rs.PageSize = Application("pagesize")
rs.CacheSize = Application("pagesize")
0
 
LVL 3

Author Comment

by:Maxim10553
ID: 6299510
Since in the end i ended up using paging of recordsets i will award raizon the points. Thanks all for the help
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now