SitePoint Sponsor |
|
User Tag List
Results 1 to 13 of 13
Thread: ASP and SQL Temp Data
-
Jul 1, 2010, 06:51 #1
- Join Date
- May 2010
- Posts
- 12
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
ASP and SQL Temp Data
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.....
-
Jul 1, 2010, 13:10 #2
- Join Date
- Jul 2005
- Location
- West Springfield, Massachusetts
- Posts
- 17,290
- Mentioned
- 198 Post(s)
- Tagged
- 3 Thread(s)
Just a wild shot, is it a case sensitive thing? i.e.
Code ASP:aPkey(count)=rs("PKey") aFkey(count)=rs("FKey") ..... Session("pkey") = aPkey Session("fkey") = aFkey
Big Change Coming Soon - if you want your PMs save them now!
What you need to do to prepare for our migration to Discourse
A New SitePoint Forum Experience: Our Move to Discourse
-
Jul 1, 2010, 13:55 #3
- Join Date
- Oct 2002
- Location
- Scotland
- Posts
- 3,631
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
I don't think ASP is case-sensitive, but it's worth checking anyway.
-
Jul 1, 2010, 18:35 #4
- Join Date
- Jun 2007
- Posts
- 691
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
your code has :
Session("numrecs") = numrecs
but there is no value assigned to "numrecs"
did you mean :
Session("numrecs") = count
-
Jul 2, 2010, 21:40 #5
- Join Date
- Jul 2010
- Posts
- 1
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Jul 2, 2010, 23:47 #6
- Join Date
- Jan 2003
- Posts
- 781
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I don't think ASP is case-sensitive, but it's worth checking anyway. - siteguru
Session("numrecs") = numrecs
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.
Code: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
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-aspThe beauty of life is not dependent on how happy you are,
but on how happy others can be because of you...
-
Jul 8, 2010, 20:50 #7
- Join Date
- May 2010
- Posts
- 12
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jul 9, 2010, 08:25 #8
- Join Date
- Jun 2007
- Posts
- 691
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jul 9, 2010, 17:51 #9
- Join Date
- May 2010
- Posts
- 12
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
-
Jul 9, 2010, 17:53 #10
- Join Date
- May 2010
- Posts
- 12
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jul 10, 2010, 10:58 #11
- Join Date
- Jun 2007
- Posts
- 691
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jul 10, 2010, 12:43 #12
- Join Date
- May 2010
- Posts
- 12
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jul 10, 2010, 19:13 #13
- Join Date
- Jun 2007
- Posts
- 691
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
Bookmarks