Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004

    Unanswered: One recordset instead of two


    I am using classic ASP.
    Records are grouped together by a GroupUnique number. Some groups are small with about 10 records, othere are larger at about 160.

    For each record, I have about 50 columns of data that I need to display on a webpage. Because the 50 columns don't easily fit on the one page, I create two tables, each displaying 26 columns, the first columnn being an ID column. Due to the size of groups, sometimes the tables are very large - and when they get too big it overloads the server.

    I think the main problem is the two tables. I use two recordsets (one of them is shown below - although instead of a SELECT * I do in fact name the columns needed for each table). I have to use two because the Recordsets don't like me using the ID column again - once it is used it is gone.

    Is there a better way to store all of this information so that I can just use the one recordset? Possibly in an array? Is there a more efficient way of getting the data?

    Dim Recordset4__MMColParam1
    Recordset4__MMColParam1 = "1"
    If (Scramble.Fields.Item("GU").Value <> "") Then
    Recordset4__MMColParam1 = Scramble.Fields.Item("GU").Value
    End If

    Dim Recordset4
    Dim Recordset4_numRows

    Set Recordset4 = Server.CreateObject("ADODB.Recordset")
    Recordset4.ActiveConnection = conn
    Recordset4.Source = "SELECT * FROM Table1 WHERE GroupUnique = " + Replace(Recordset4__MMColParam1, "'", "''") + ""
    Recordset4.CursorType = 0
    Recordset4.CursorLocation = 2
    Recordset4.LockType = 1

    Recordset4_numRows = 0

    Dim Repeat4__numRows
    Dim Repeat4__index

    Repeat4__numRows = -1

    Repeat4__index = 0
    Recordset4_numRows = Recordset4_numRows + Repeat4__numRows

  2. #2
    Join Date
    Aug 2006

    SQL Server 2000 and back don't have array function. However, I think SQL Server 2005 implemented some sort of array function, but don't quote me on that. Anyway about your two recordsets....

    You can create a stored procedure which is just a sql file with your queries in it. The cool thing about the stored procs is you can write queries and store the results in variables and reuse the variable throughout the store proc. Also, you can execute multiple queries like your doing and print them out into one recordset which eliminates manaually inserting SQL into your website, and opening up double connections, which maybe your bandwidth issue.. (Its good for security purposes too.)

    I'm not a pro at this, actually just graduated college and started my entry level software engineer position. So I may be missing some things. Your best bet is to go onto google and search SQL stored procedures.

    Hope this is some help.

  3. #3
    Join Date
    Jan 2004
    Hmm - I converted the SQL queries to stored procedures today. It works on groups when small amounts of data is retrieved and displayed as before, but not when over 150 records are displayed.

    If I just show the one table from one recordset, then it does work on the groups with 150 records, but of course I'm only showing half the data. I need to show the rest of it.

    I therefore think that I am exceeding my memory. Is there anyway I can monitor how much stress I am placing on the server? Or any fixes?

    Should I change the order at which I open and close connections? Currently I open all recordsets at the top of the page and close them at the bottom. Should I be opening one recordset, displaying that table and then closing it BEFORE I open another recordset up?

  4. #4
    Join Date
    Aug 2002
    You can take help of PROFILER in this case to see what are running on SQL Server side and also use SYSMON (PERFMON) to cpature CPU, Physical disk & memory related counters. Refer to for help on counters & performance related issues.
    --Satya SKJ
    Microsoft SQL Server MVP

  5. #5
    Join Date
    Feb 2007
    You've got a nice SQL injection thing going on there too. You should parameterise your query.

Posting Permissions

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