Speeding up page load

I have a 2D array that is filled with data from a database which is then used within a while loop with another sql query. For each item in the while loop i'm checking for certain items within the 2d array using a for loop.

How can i speed this up because the items within the while loop are within 1000 - 2000 items and it takes 3 - 4 seconds to load the webpage.

Here is my 2d array -

'MultiDimensional Array
Dim permissionsArray()
Dim permissionsCount  
connectionstring = obj_ADO.getconnectionstring  
Increment = 1             

set c = CreateObject("ADODB.Connection")
set r = CreateObject("ADODB.R开发者_JS百科ecordset")
c.open connectionstring
SQL = "select Count(P_Name) as permissionsCount from l_objectpermission inner join A_Permission on op_permissionID = P_permissionID"
r.open SQL, c
permissionsCount = r("permissionsCount")

r.close
c.close


Set objCon = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
objCon.Open connectionstring

SQL = "select OP_ObjectID, P_Name from l_objectpermission inner join A_Permission on op_permissionID = P_permissionID order by P_Name"

objRS.open SQL, objCon
Redim permissionsArray(2, permissionsCount)
if not objRS.EOF then                                            
    objRS.MoveFirst
    while not objRS.EOF                   

        permissionsArray(0, increment) = objRS("OP_ObjectID")
        permissionsArray(1, increment) = objRS("P_Name")

        objRS.MoveNext
        Increment = Increment + 1
    wend

    objRS.close
    objCon.close

end if

and inside my while loop is -

        Page_ID = objRS("P_PageID")
        for i = 0 to (permissionsCount)
            if permissionsArray(0, i) = Page_ID then
                %>
                <li style="height: 2px;">
                <%=permissionsArray(1,i)%>
                </li>
                </br>
                <%
            end if                        
        next   
        %>
        </div></center></td>

---

Here is your bottle neck:

if not objRS.EOF then                                            
    objRS.MoveFirst
    while not objRS.EOF 

If all you want is a 2 dimensional array, then you would use Recordset.GetRows() instead

'MultiDimensional Array
Dim permissionsArray
Dim permissionsCount  
connectionstring = obj_ADO.getconnectionstring               

Set c = Server.CreateObject("ADODB.Connection")
Set r = Server.CreateObject("ADODB.Recordset")
r.CursorLocation = 2 'adUseServer
c.open connectionstring
c.CursorLocation = 2 'adUseServer

SQL = "select OP_ObjectID, P_Name from l_objectpermission inner join A_Permission on op_permissionID = P_permissionID order by P_Name"

Set r = Server.CreateObject("ADODB.Recordset")
r.CursorLocation = 2 'adUseServer
r.Open SQL, c, 0, 1 'adOpenForwardOnly, adLockReadOnly
If r.BOF or r.EOF Then
   r.close()
   Set r = Nothing
Else
   permissionsArray = r.GetRows()
   permissionsCount = UBound(permissionsArray, 2) + 1
   r.Close()
   Set r = Nothing
End If
c.Close()
Set c = Nothing

References:
http://www.learnasp.com/advice/whygetrows.asp
http://www.w3schools.com/ado/met_rs_getrows.asp
http://www.devguru.com/technologies/ado/quickref/recordset_getrows.html

---

Assuming the issue has to do with the ADODB side of things, and not the fact that you are trying to render 2000 <LI>'s...

It generally is a good practice to reuse your ADODB.Connection. Closing the connection and then reopening it is probably costing you a slight amount of performance.

You should also specify the RecordSet properties (before the RecordSet is opened):

1. CursorLocation: adUseServer[2] runs the cursor on the server; the server streams the recordset to the client in chunks, allowing the client to start processing sooner, rather than waiting until the entire set is received; also, the memory is managed on the server so you won't have memory issues on the client if lots of people are hitting the page at the same time; http://msdn.microsoft.com/en-us/library/ee252442(v=bts.10).aspx

2. LockType: adLockReadOnly[1] indicates read-only records where the data cannot be altered; if you are not altering the data, this should be the fastest; http://msdn.microsoft.com/en-us/library/ee252458(v=BTS.10).aspx

3. CursorType: adOpenForwardOnly[0] Specifying this value opens a forward-only-type cursor. This CursorType is identical to a static cursor, except that you can only scroll forward through records. This improves performance when only one pass through a Recordset is needed. http://msdn.microsoft.com/en-us/library/ee252445(v=BTS.10).aspx

You should also get rid of the "count" query...unless you need it for some other reason. Otherwise, you can set the value of permissionsCount = UBOUND(permissionsArray)...i think...it's been a long time since i've done any vbscript!

SQL = "select OP_ObjectID, P_Name from l_objectpermission inner join A_Permission on op_permissionID = P_permissionID order by P_Name"

Set rs = Server.CreateObject("ADODB.RecordSet")
rs.CursorLocation = 2 'adUseServer
rs.LockType = 1 'adLockReadOnly
rs.CursorType = 0 'adOpenForwardOnly
rs.ActiveConnection = c
rs.Open SQL

If NOT rs.EOF Then
   permissionsArray = rs.GetRows()
End If

rs.close()
Set rs = Nothing
c.Close()
Set c = Nothing

---