Solved

Storing Recordset to Session Variable?

Posted on 2001-07-18
16
425 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

864 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

23 Experts available now in Live!

Get 1:1 Help Now