Avatar of Steynsk
Flag for Netherlands asked on

Please advise me to make my script more efficient and lean

Hi Experts,

I’ve got this large classic ASP script that creates a pivot view on all ADS rights of a selected group of users. The script was, until now, a proof of concept showing it is possible to do this web enabled.  Below are the steps the script currently takes:

1. Selecting the scope

It lets the user select a group from their active directory.

2. Retrieval of members in scope

Then the script retrieves from all the members of that group the fields sAMAccountName,  department and memberof. Where memberof holds all groups that AD user a member of besides the initially selected group.

3. Processing retrieved data

The scripts splits the sub values stored in the memberof array and writes them separately in into my MS SQL table where . The table layout is included in this post.

4. Storing data

Then when all data is stored the script queries a distinct on all usernames and loads those values on to a variable.

5. Generate Pivot view

Then the script sends a pivot query using variable holding the usernames.

6. Presenting data

At the end the pivot view on the data gets presented as output and result.  Both query's are included in this post.As you can imagine the script is slow and consumes a lot of process power from my webserver. And for that reason I’d like to make it more efficient and quicker.
I’ve learned myself classic ASP (with the excellent help of this forum) but my knowledge does not reach very far yet. And besides that I'm not an English native speaker so sometimes it will take a little longer for me to understand a sollution.

What I would like to know is what steps you guys and girls would advise me to take to make the script more efficient and lean.
Important is to maintain the pivot view on the data in your suggested method . But please do not include in your advise the use of another programming language or the use of (third party) components.

Retrieval of users in scope (step2)
SELECT sAMAccountName,department,memberof FROM 'LDAP://" & SDomain &"' WHERE memberof = '" & Group & "' ORDER by name 

Open in new window

Table layout
CREATE TABLE membership(
	[memberID] [int] NOT NULL,
        [auteur] [nvarchar](50) NULL,
	[username] [nvarchar](50) NULL,
	[groupname] [nvarchar](50) NULL

Open in new window

Pivot query
SELECT * FROM (SELECT memberID, groupname, username FROM ADS_membership where auteur='"& Request.ServerVariables("AUTH_USER")&"') t PIVOT (MIN(memberID) FOR username IN ("&users&")) p

Open in new window

Complete script
<!--#INCLUDE FILE="_header.asp"-->
'Set the server locale
Session.LCID = 1043
dim strToday, opteldagen
strToday = date
select case WeekDay( strToday )
        case 1     '-- sunday
        opteldagen = 8
        case 2     '-- monday
        opteldagen = 8
        case 3     
        opteldagen = 8
        case 4     
        opteldagen = 8
        case 5    
        opteldagen = 8
        case 6    
        opteldagen = 10
        case 7     
        opteldagen = 9
end select
zesdagen = DateAdd( "d", opteldagen, strToday )
response.write("<form name=""sending"" method=""POST"" action=""GroupPivot.asp"">")
Set CS = Server.CreateObject("ADODB.Connection")
CS.Open "Provider=SQLOLEDB;Data Source=BedrijfsbureauAppSectCP01.db.umcg.intra;Initial Catalog=BedrijfsbureauAppSectCP01; User ID=BBAPuser; Password=tU4abrat;"
Phrase = Request.Form("Phrase")
Group = Request.QueryString("Group") ' Friendly name of group
GroupName = Request.QueryString("GroupName") ' DistinguishedName of group
FuncADuser = "mydomain\myADSviewAccount"
FuncADpassword = "password"
' This approach to finding the domain does not have a dpenendency on authenticated user
Set objRootDSE = GetObject("LDAP://RootDSE")
sDomain = objRootDSE.Get("defaultNamingContext")
Set oCmd = Server.CreateObject("ADODB.Command")
' First step, collecting search term
if Phrase = "" and Group = "" Then
        oStatus = "Search"
        oButtonVal = "Search"
        response.write "<p align=""center"">Enter a ads groupname or a part of a groupname:<br><br><input name=""Phrase"" autofocus size=""22"">"
        ' Second step, displaying groups to choose from
ElseIf Phrase <> "" and Group = "" Then
        oStatus = "Select"
        response.write "<p align=""center"">Select ont of the found groups</p>"
        oCmd.CommandText = "SELECT Name,DistinguishedName FROM 'LDAP://" & SDomain &"' WHERE objectCategory = 'group' AND name ='*" & Phrase & "*' order by name"
        ' Final step, storing the found info into the database and Pivot it.
ElseIf Phrase = "" and Group <> "" Then
        oStatus = "Finish"
        oButtonVal = "Diffrent group?"
        response.write "<p align=""center"">This are the members of the selected group <strong>"& GroupName &"</strong>:</p>"
        oCmd.CommandText = "SELECT sAMAccountName,department,memberof FROM 'LDAP://" & SDomain &"' WHERE memberof = '" & Group & "' ORDER by name "
        ' Just in case something weird happens
        response.write "<p>Invalid Page Access</p>"
        oButtonVal = "Different group?"
        oStatus = "Search"
End If
' If this isn't the first step, process
If oStatus <> "Search" Then
        Set Conn = Server.CreateObject("ADODB.Connection")
        Set RS = Server.CreateObject("ADODB.Recordset")
        Conn.Provider = "ADsDSOObject"
        Conn.Properties("User ID") = FuncADuser
        Conn.Properties("Password") = FuncADpassword
        Conn.Properties("Encrypt Password") = True
        strConn = "Active Directory Provider"
        Conn.Open strConn , FuncADuser, FuncADpassword
        Set oCmd.ActiveConnection = Conn
        Set oRS = oCmd.Execute
        dim sql
        sql3="delete from ADS_membership where auteur='"&Request.ServerVariables("AUTH_USER")&"';"
        Set RS3 = Server.CreateObject("ADODB.Connection")
        RS3.Open CS
        RS3.Execute sql3
        response.write "<table class=""breed"" align=""center"" border=0>"
        if not oRS.EOF Then
                  count =0
                  Do Until oRS.EOF
                          If oStatus = "Select" Then
                                        response.write "<tr><td><a href=""GroupPivot.asp?Group=" & oRS.Fields("DistinguishedName") & "&GroupName=" & oRS.Fields("Name") &""">" & oRS.Fields("Name") & "</td></tr>"
                                        URL = "GroupPivot.asp?Group=" & oRS.Fields("DistinguishedName") & "&GroupName=" & oRS.Fields("Name")
                                        For each group in membership
                                                groupname= left(newgroup(1), len(newgroup(1))-3)
                                                groupname = Replace (groupname, "\", "\\'")
                                            groupname = Replace (groupname, "'", "''")
                                                sql2= sql2 & "insert INTO ADS_membership (auteur, username, groupname) VALUES ('" & Request.ServerVariables("AUTH_USER") & "','" & oRS.Fields("sAMAccountName") & "','" & groupname & "');"
                                        sql2 = left(sql2,Len(sql2)-1)
                                        Set RS1 = Server.CreateObject("ADODB.Connection")
                                        RS1.Open CS
                                        RS1.Execute sql2
                          End If
                  if count=1 then
                  end if
        If oStatus <> "Select" Then
                SQL4 = "SELECT DISTINCT username FROM ADS_membership where auteur='"& Request.ServerVariables("AUTH_USER")& "'"
                Set RS4 = Server.CreateObject("ADODB.Recordset")
                RS4.Open SQL4, cs
                Do While Not RS4.EOF
                        users= users & "["&RS4("username") & "],"
                users = left(users,Len(users)-1)
                SQL5 = "SELECT * FROM (SELECT memberID, groupname, username FROM ADS_membership where auteur='"& Request.ServerVariables("AUTH_USER")&"') t PIVOT (MIN(memberID) FOR username IN ("&users&")) p"
                Set RS5 = Server.CreateObject("ADODB.Recordset")
                RS5.Open SQL5, cs
                Response.Write "<table class=""breed"" align=""center"" border=1><tr>"
                For Each objField in rs5.Fields
                        if i =0 then
                                Response.Write "<TH colspan=3 align=""right"">Edit -></TH>"
                                Response.Write "<td><a href=""user_edit.asp?username="&objField.Name&"&maingroup="&GroupName&""" target=""_blank""><img src='images/edit.png'style=""border-style: none""'></a></td>"
                        end if
                Response.Write "</tr><tr>"
                For Each objField in rs5.Fields
                        if i =0 then
                                Response.Write "<th></th><th class=""vertical""><- Edit</th><th>" & objField.Name & "</th>"
                                Response.Write "<th class=""vertical"">"&objField.Name&"</th></td>"
                        end if
                Response.Write "</tr>"
                Do While Not RS5.EOF
                        strExceptions = "APSWEBProxyGG, usersPST01, users01, users02, users03, users04, users05, users06, users07, users08, users09, users10, users11, users12, users13, users14, users15, users16, users17, users18, users19, users20, users21, users22, users23, users24, users25, users26, users27, users28, users29, users30, users31, users32, users33, users34, users35, users36, users37, users38, users39, users40, users41, users42, users43, users44, users45, users46, users47, users48, users49, users50, users51, users52, users53, users54, users55, users56, users57, users58, users59, users60, users61, users62, users63, users64, users65, users66, users67, users68, users69, users70, users71, users72, users73, users74, users75, users76, users77, users78, users79, users80, users81, users82, users83, users84, users85, users86, users87, users88, users89, users90, users91, users92, users93, users94, users95, users96, users97, users98, users99, DWSusersGG"
                        if InStr(strExceptions, RS5("groupname")) = 0 then
                        Response.Write "<tr>"
                        For Each objField in rs5.Fields
                                if i =0 then
                                        if left(rs5(objField.Name),3)="APW" OR left(rs5(objField.Name),3)="Res" OR left(groupname,3)="RES" OR left(rs5(objField.Name),3)="APS" OR left(rs5(objField.Name),3)="PF " then
                                                Response.Write "<td>"&count&"</td><td><a href=""http://bbato.sectorc.umcg.nl/ads/group_edit.asp?groupname="&rs5(objField.Name)&""" target=""_new""><img src='images/edit.png'style=""border-style: none""></a></td>"
                                                edit = "True"
                                                Response.Write "<td>"&count&"</td><td></td>"
                                                edit = "False"
                                        end if
                                        Response.Write "<td><a href=""http://bbato.sectorc.umcg.nl/ads/group_members.asp?Phrase="&rs5(objField.Name)&""" target=""_new""><b>"&rs5(objField.Name)&"</b></a></td>"
                                        if rs5(objField.Name)<>"" then
                                                if edit= "True" then
                                                Response.Write "<td align=""center""><a href=""http://bbato.sectorc.umcg.nl/ads/incidenteel_edit.asp?id="&rs5(objField.Name)&""" target=""_new""><b><font color=""red"">X</font></b></a></td>"
                                                Response.Write "<td align=""center""><b><font color=""red"">X</font></b></td>"
                                                end if
                                                Response.Write "<td></td>"                                     
                                        end if
                                end if
                        Response.Write "</tr>"
                        end if
        end if
        oButtonVal = "Diffrent group?"
        oStatus = "Once more?"
End If
Set rs = Nothing
End If
' Only display the button when it is relevant
If oStatus <> "Select" Then
  response.write "<p align=""center""><input type=""submit"" value=""" & oButtonVal & """ name=""Send""></p>"
End If
set oCmd = Nothing

Open in new window

Kind regards,

ASPWeb Languages and Standards

Avatar of undefined
Last Comment

8/22/2022 - Mon
Scott Fell

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Scott Fell

Big Monty

if the data doesn't change too often, and/or you don't need to provide real time data, you should look into caching techniques. Then you could run a vbs script every x amount of days to pull the latest data and build that data into a pre-existing page that your asp app could link to.

if that's not an option, you may want to look into paging the data, meaning only grab a specified size chunk of the data and outputting it to the screen, 4Guys has a great article on how to do that:


Thanks this was exactly the advise I was looking for.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Scott Fell

Thank you Steynsk, do you want to provide some sample output and let's try to make this work for you.  Or take it in steps, perhaps make one new question to output your data using asptojson, then a 2nd question for taking the json code to pivot.js in the javascript threads.

Thanks Scott,

Good idea. I will make the first question tomorrow.