SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Member
    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.....
    Attached Files Attached Files

  2. #2
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,191
    Mentioned
    191 Post(s)
    Tagged
    2 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

  3. #3
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,629
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I don't think ASP is case-sensitive, but it's worth checking anyway.
    Ian Anderson
    www.siteguru.co.uk

  4. #4
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    690
    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

  5. #5
    SitePoint Member
    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

  6. #6
    Learning...
    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
    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.

    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
    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
    The beauty of life is not dependent on how happy you are,
    but on how happy others can be because of you...

  7. #7
    SitePoint Member
    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.

  8. #8
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    690
    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.

  9. #9
    SitePoint Member
    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?

  10. #10
    SitePoint Member
    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.

  11. #11
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    690
    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.

  12. #12
    SitePoint Member
    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.

  13. #13
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    690
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •