ASP and SQL Temp Data

[tribalsimba]

I have a Classic ASP Application the Queries an SQL DB (actually a Read-Only Table View to a FoxPro DB through a Linked Server in SQL). Anyway, it pulls a bunch of records; and then does some computational summary of the data that is pulled. The Queried information changes very frequently. I want to find a way to minimize the computational routine, yet still run it at least once for every session, and present that summary data across multiple pages in the application. This application is used by over 1000 users FREQENTLY, and each source record equals a page of data; and the summary data is what creates the personalized 'menu' so to speak. I don't want to run the computational routine each time they page through the data.

Currently I am using Session() to store arrays of the subject summary data; but for some reason if the page is refreshed that session data is lost. I need the data to remains 'cached' and able to be enumerated through the session; or until the user clicks on my internal refresh data button to get the latest snapshot.

Should I be using cookies, SQL Temp tables, or something else to store a 5 cells x 24 cells array of data temporarily?

Thanks.....

[Mittineague]

Just a wild shot, is it a case sensitive thing? i.e.

aPkey(count)=rs("PKey")
aFkey(count)=rs("FKey")
.....
Session("pkey") = aPkey
Session("fkey") = aFkey

[siteguru]

I don't think ASP is case-sensitive, but it's worth checking anyway.

[webber123456]

your code has :

Session("numrecs") = numrecs

but there is no value assigned to "numrecs"

did you mean :

Session("numrecs") = count

[david_hill]

I suspect that the issue might be that you're bringing in remote data from a linked server, so when you refresh the page, it requeries and you lose the current set.

I would try sticking the data into a local holding table and building a refresh routine hooked to a user action.


Dave

[tahirjadoon]

I don't think ASP is case-sensitive, but it's worth checking anyway. - siteguru

JScript is case sensitive where as VBScript is not. Sample code is VBScript, variable names can be upper or lower or both. But one should pick a format and then stick with it.

Session("numrecs") = numrecs

This should be either count or use RS.RecordCount

Before executing your query you need to check if the session has data or not. If it empty only then pull data from the DB.

If Not IsNumeric(Session("numrecs")) Or CInt(Session("numrecs")) <= 0 Then 
    'code to execute the query, build recordset and assign to session goes here

End If

Session can expire due to:

1. By default the session expires after 20 minutes. You can overwrite this setting via IIS. If this is the case then the alternative would be to use cookies, but if the user has these turned off then these are of no use.

2. If you are using load balancing then if the first request is to server one and the next request goes to server 2, you'll loose your session. We got ourselves in this situation when going from http to https. The network guy fixed it by altering the setting on the load balancer.

Take a look at the following link, this describes the session in detail.

http://stackoverflow.com/questions/1...or-classic-asp

[tribalsimba]

The code does not have syntax issues (ie caps, or count references).

It works; it just doesn't remain when you hit refresh. It's as if clicking on refresh in the browser resets the session ID in cookies or something.

It's a single server, and I am hitting it directly.

[webber123456]

you'll have to debug.

Try setting another session value - something small like session("test") = "test"

see if it is retained after refresh. if yes, then you can know the session is working and focus on other possible sources of the problem.

[tribalsimba]

It looks like it "may" have been partially related to a setting in the IIS Application Pool recycle settings that were set to recycle at x Memory usage; which is wierd to me because I thought this Memory setting was pretty high.

Anyway; all this has me thinking for the 800 users this application is for; where 300 concurrent connections is very plausible; is there a better way to do this? Should I be creating temp tables in in SQL or something?

[tribalsimba]

Webber,

I did a few things to pull out static data from the Session Data to test if it is a session issue; or an issue with the array's. It was definately session data getting lost; not an issue with the arrays.

[webber123456]

You might want to rethink how much data you are sending to the user.

Each person can't absorb too much data at any one time so perhaps a smaller amount of data per retrieval, then they can just hit the database on each request.

This should not bother a good database.

Otherwise, you will need to work with the server administrator to resolve pooling/load balancing issues.

[tribalsimba]

Smaller Queries would be GREAT!....
So I'll divulge more here...
This is for an online student gradebook app for a highschool. The backend is foxpro.
The data that I am trying to store for the user is a rather large query that pulls all of their classes and grades; and then 'cumulates'. I want to store the end-result of the cumulation calculations; but 'CANNOT' write back to the FoxPro DB my results. I would have to create separate tables in SQL; but that might not be a bad idea anyway.
Currently i have been playing around with the Session() function with arrays of data; and then I am able to keep that to produce links on various pages where the student can access their individual class data. It basically creates a Grade 'wiki' module on the Student portal that has ALOT of other information all over it.

[webber123456]

Pulling classes and grades doesn't sound all that large...

I ran a golf course website that used to keep track of hundreds of kids playing tournament events throughout ther year and they were constantly getting their scores, rankings and overall comparative standings in the events to date. This also required heavy computational calculations.

The queries and formulas were simply run each time the request was made - and that was all done using only an Access database.

Not sure what else to offer.