Solved

Storing Recordset to Session Variable?

Posted on 2001-07-18
16
427 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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
 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

730 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