Please advise me to make my script more efficient and lean

Posted on 2014-11-22
Last Modified: 2014-11-23
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="""&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="""&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="""&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,

Question by:Steynsk
  • 3
  • 2
LVL 52

Accepted Solution

Scott Fell,  EE MVE earned 500 total points
ID: 40459411
What I would do in this case is throw all of your results into an array using getrows.  Or if you prefer, just output all of the data to the browser by looping through.   Then pivot on the client using javascript.  See   

You will need to get your data to json.   You could do this by hand but there are some gotchas and I have had great success with the asptojson project

The point is, using pivot.js opens up a lot of possibilities for you and you only have to output your data as a table.
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40459412
LVL 33

Expert Comment

by:Big Monty
ID: 40459806
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:
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Closing Comment

ID: 40460520
Thanks this was exactly the advise I was looking for.
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40460578
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.

Author Comment

ID: 40461131
Thanks Scott,

Good idea. I will make the first question tomorrow.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Randomize in Owl Carousel v1.3.2 6 104
rebind a grid after user clicks on node in treeview 1 48
VBScript on Html 15 58
ASP Error Handler 5 21
Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
JavaScript has plenty of pieces of code people often just copy/paste from somewhere but never quite fully understand. Self-Executing functions are just one good example that I'll try to demystify here.
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question