Parsing and controlling display getrows Array data....

OK, that output is like this:
Starting Data like this:
6087|195|25|000.96|COMMODITY INVENTORY|CAN|APPLE SLICES, Commodity 6/#10 Cans|

Your table output:
|---------------------|
|6087                   |
|---------------------|
|195                     |
|---------------------|
|25                       |
|---------------------|
|000.96                 |
|---------------------|
|Commodity          |
|Inventory             |
|---------------------|
|CAN                    |
|---------------------|
|APPLE SLICES,     |
|Commodity 6/#10|
|Cans                   |
|---------------------|

This continues all the way across the screen to the right.

Joe

Okay, good. Now we need to split the string:

Response.Write("<Table Border=1 cellpadding=2 cellspacing=2>" & VBCrLf)
for i=0 to UBound(arrRows,1)
      Response.Write("<TR>" & VBCrLf)
      for j=0 to UBound(arrRows,2)
            arrFields = Split(arrRows(i,j),"|")
            Response.write("<td>" & arrFields(2) & "</td>"
            Response.write("<td>" & arrFields(3) & "</td>"
            Response.write("<td>" & arrFields(3) & "</td>"
      next
      Response.Write("</TR>" & VBCrLf)
next
Response.Write("</Table>")

FtB

I'm getting:
Microsoft VBScript runtime error '800a0009'
Subscript out of range: '[number: 2]'

/getrows/getrows2.asp, line 27
 

Line 27: Response.write("<td>" & arrFields(2) & "</td>")

Joe

I wasn't very clear in the initial posting that the Pipe (|) is not part of the database.  When I was displaying the data in rows I put the | as part of the response.write to be able to visually differentiate between columns.  The actual output of a line without it looks like this:

608719525000.96COMMODITY INVENTORYCANAPPLE SLICES, Commodity 6/#10 Cans

Joe

I am not sure that I follow. Are you saying that you have seven fields and what you want to do is to group them by the 3rd, 4th, and 5th?

If so, then you need to add the GROUP BY clause in your SQL select.

FtB

You are correct in that they get grouped by the 3rd, 4th amd 5th elements.  They really only get grouped by the 3rd element (category ID in this case, ie "25, 26, 27, etc").  That's where I need to split them so that I can generate a header for the table that contains only that category (Element #3) and the header information (#'s 4 & 5), display all those results and then generate another header for the next one.  That needs to continue on until there are no more categories (element #3)

Does that make a little more sense?

I was doing this using a recordset.filter method and I would just .filter using a "categoryID = 25" or whatever one I was on.  This works well, except that this database is steadily kicking out over 600+ rows of items now with a steady 150 users and it's just too much data to be parsing in that inefficient manner.  By using getrows, it's significantly faster, the databases close much quicker and pages are served faster.  

Follow me?

jj

Not exactly, but I do something like this:

intCategory= ""
do while not objRS.EOF
     if objRS("intCategory") <> intCategorythen
          response.write("<tr bgcolor='gray'><td colspan =2>" & objRS("intCategory") & "</td></tr>" & VBCrLf)
          intCategory= objRS("intCategory")
     end if
     response.write("<tr><td>" & objRS("SomeOtherField1") & "</td><td>" & objRS("SomeOtherField2") & "</td></tr>" & VBCrLf)
     objRS.MoveNext
loop

This way, the main heading only gets printed out once. You can increase this to as many levels as you need.

FtB

That's still keeping the recordset open through the entire display of the page which is upwards of 12-20 seconds depending on the load on the server at that moment.  That's entirely TOO long.

Hmm.  What if I changed my SQL to only get the Category ID and not description of the category and all that; essentially dropping 2 fields (#4 & 5).  Loaded Elements #3, 4 & 5 into another array using a separate query, use an outer loop of the array with categories and the 3 values in that quuery and the inner loop would cycle the data using the first query.

The only problem then is how would I parse out the ones that only have element 3 out of the big array?

jj

The only real problem here is that you can't free the recordset with the above code because you're still calling use of the recordset by initializing intCategory with the "objRS("intCategory") value.  . . Or am I wrong here?

Here's another thought that just popped in my head...

What about creating a sub function that runs a query that limits the data and passes the array out based on the current category.  Now, this would be more connections to the database BUT they would be quick and only on each category.  Would this be a more negetive influence on the server load than keeping the recordset open?

jj

Actually, the above does free the recordset:

arrRows = objRS.GetRows()
objRS.Close
set objRS = Nothing

However, if your query takes 12-15 seconds, that is a different issue.

>>Now, this would be more connections to the database<<

Not at all, you can and should use the same connection object for all recordsets on the page.

>>that runs a query that limits the data<<

We need to look at that more. The more that you limit the data returned from your sql the better.

FtB

[==Quoted==]
Actually, the above does free the recordset:

arrRows = objRS.GetRows()
objRS.Close
set objRS = Nothing

[=====]

This is true, it does free the recordset.  However, it holds that recordset open and .movenext and other cursor type movements are are an issue with holding that recordset open that long (12-15 seconds on average).  From my testing, querying the database and outputting the rows to the screen using arraws is much faster.

Example: (Same Query, same # of rows returned)

Recordset movement: 14.20 seconds
Arrays Output: 1.06 seconds

The database connection is only open long enough to fill the array with data and then I'm done with the database and recordset in 1 second as opposed to keeping them open for that duration.

jj

How are you outputting the data directly as an array without the use of a recordset?

FtB

Hi Fritz --
Sorry for letting this one sit so long.  To answer your above question, the recordset is no longer needed after you fill the array with data from the recordset.  Freeing it releases that memory back to the application pool.

I did manage to get the desired results on my own.  I ended up using a 2 array system to accomplish this.  

I had one array filled with the various category types and one filled with the data from the tables I needed.  As I looked through the categories, I would display the header and then run a query to fill the items array with data delimited by categoryID in the WHERE clause.  This worked out much better in the long run looping through this subset of data than keeping the recordset open.  Apparently, rs.movenext is quite hard on the system processor (pegging my 4 processor system at 15-20% for the duration of the query) and by using an array method, the processor never hops over 2%.  MAJOR performance increase.

Thanks again for the assistance.

Joe Jenkins