Recursive Function on Demand

[ReportingAnalyst]

Is it possible to call a recursive program on demand?

For instance, I have a function -

getSubordinates(ManagerID, Counter)

within which I call the above function to get the nested subordinates.

If a subordinate is a manager, I add a hyperlink to the manager name other just display his name. That hyperlink has a onclick event to drill down to see the further nested hierarchy.

My question here is can I call the function which in turn builds the SQL query to get the nested subordinates recordset only when I were to click on the link...

My GetSubordinates(ManagerID, Counter) function is:

Function GetSubordinates(managerID, Counter)

			'Creating a log file to detect errors:
			'If Not(IsObject(objFSO)) Then
			'	Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
			'End If

			'Dim objFSO, objLogFile, strLogFilePath
			'Const LOG_FILE_NAME="GetSubordinate.txt"
			'strLogFilePath = Server.MapPath(LOG_FILE_NAME)
			'Set objFile = objFSO.OpenTextFile(strLogFilePath, 8) 'ForAppending
			'objFile.WriteLine("manager: " & managerID  & " " & Now())
			'objFile.Close

			sub_Emp_Source = "SELECT AMP_AllMySubordinates_Postings.Member_Manager, " & _
			                  "AMP_AllMySubordinates_Postings.Member_Subordinate,   " & _
			                  "AMP_AllMySubordinates_Postings.Owner_DateOfPost, " & _
			                  "Employee.EmpID, " & _
			                  "Employee.FirstName + ' ' + Employee.LastName AS Emp_Name, " & _
			                  "Employee.LastName " & _
			                  "FROM AMP_AllMySubordinates_Postings " & _
			                  "INNER JOIN Employee ON " & _
			                  "AMP_AllMySubordinates_Postings.Member_Subordinate = Employee.UniqueIdentifier " & _
			                  "WHERE AMP_AllMySubordinates_Postings.Member_Manager = " & managerID & " " & _
			                  "AND AMP_AllMySubordinates_Postings.Owner_Payee = " & strUI & " " & _
			                  "AND AMP_AllMySubordinates_Postings.Owner_DateOfPost In " & _
			                  "(Select MAX(AMP_AllMySubordinates_Postings.Owner_DateOfPos  t) FROM Amp_AllMySubordinates_Postings WHERE owner_payee = " & strUI & ") " & _
			                  "ORDER BY Employee.LastName; "

			Dim rs2
			set rs2 = Server.CreateObject("ADODB.Recordset")
			rs2.open sub_Emp_Source, Conn
			Dim aSubordinates

			If not rs2.EOF then

				aSubordinates = rs2.GetRows()

				'Close Recordset to use the new array with the 2 columns data
				rs2.Close()
				set rs2 = Nothing

				'Declare Constants for the above SQL columns for better readability
				'Use these Constants instead of referring to the array numeric indexes
				Const c_Manager = 0
				Const c_Subordinate = 1
				Const c_PostDate = 2
				Const c_EmpID = 3
				Const c_EmpName = 4

				'Ubound(MyArray,1) 'Returns the Number of Columns
				'Ubound(MyArray,2) 'Returns the Number of Rows

				Dim iRowLoop

				For iRowLoop = 0 to UBound(aSubordinates, 2)

					Dim uniqueID
					'Changed this from managerid to subordinate id because we want to see the subordinates for the member_subordinate
					uniqueID  = aSubordinates(c_Subordinate, iRowLoop)

					Dim ManagerOrNot
					ManagerOrNot = IsManager(uniqueID)

					Dim indent
					indent = "**"

					If ManagerOrNot = True then
						'Is a manager
						%>
							<li>
								<input type="radio" name="empid" id="empid<%=aSubordinates(c_EmpID,iRowLoop)%>" value="<%=aSubordinates(c_EmpID,iRowLoop)%>" onClick="showReports(this.value)">
								<a href="#" class="a_style" onclick="s_Hide('<%=aSubordinates(c_Subordinate, iRowLoop)%>'); return false;"><%=aSubordinates(c_EmpName,iRowLoop)%></a><font size=1>*(<%=aSubordinates(c_EmpID,iRowLoop)%>)</font>
								<ul id="<%=aSubordinates(c_Subordinate, iRowLoop)%>" style="display: none">
								<%
								Counter = Counter + 1
	[b]	'can i call this on demand when there the users click on the link	
		Call GetSubordinates(uniqueID, Counter)
[/b]
                         	Counter = Counter - 1
								%>
								</ul>
							</li>
								<%
				    Else
								%>
							<li>
								<input type="radio" name="empid" id="empid<%=aSubordinates(c_EmpID,iRowLoop)%>" value="<%=aSubordinates(c_EmpID,iRowLoop)%>" onClick="showReports(this.value)">
								<%=aSubordinates(c_EmpName,iRowLoop)%>*<font size=1>(<%=aSubordinates(c_EmpID,iRowLoop)%>)</font>
							</li>
							<%
					End if 'ManagerOrNot = true or false
				Next 'iRowLoop
				%>
				<%
			End If 'rs2.EOF
	End Function

Code takes 33 seconds for the top most guy. So wondering what other ways I can use to get this faster. I have optimized my SQL queries and it is down from 54 to 33 seconds. But would like to improve the speed more.

Secondly saw in another post that if I were to to Recordset.Filter, it could make my execution faster. How can I apply the filter in the recordset other than the where clause in the SQL query?

Thanks.

 

[xwb]

Have you tried just executing the query and returning without displaying the data? It would sort out whether it is the query that takes a long time or going through the recordset.

 

[ReportingAnalyst]

Query in the db takes less than 1 milesecond.

I would like to add that if I comment out the recursive function, it takes only 1 second.

If I uncomment it, then it takes that 32 seconds.

I think it is not the query - but going through the array which is taking time.

I also close the recordset and dump the results into an array just to make it quicker.

Any ideas.

Thanks.

 

[Tarwn]

Or the time delay could be that you are recursively calling the query AND executing a loop through the returning dataset for pretty much every single user in the system that is flagged as a manager.

 

[ReportingAnalyst]

So could be there any other way to approach this. My isManager(UniqueID) function is this:

'Determine if the employee is a manager
Function IsManager(uniqueID)

		'Declare returnValue as boolean
		Dim returnValue
		returnValue = False

		'Execute Query and if Query returns non-empty recordset then set the value of returnValue to False - not a manager
		Dim rs3, sqlEmp
		Set rs3 = Server.CreateObject("ADODB.Recordset")

		sqlEmp = "Select AMP_AllMySubordinates_Postings.Member_Manager " & _
		         "FROM AMP_AllMySubordinates_Postings " & _
		         "WHERE Member_Manager = " & uniqueID & " " & _
		         "AND Owner_Payee = " & strUI & " " & _
		         "AND AMP_AllMySubordinates_Postings.Owner_DateOfPost = " & _
		         "(Select MAX(AMP_AllMySubordinates_Postings.Owner_DateOfPost) FROM Amp_AllMySubordinates_Postings WHERE owner_payee = " & strUI & ")"
		rs3.open sqlEmp, Conn
		If rs3.EOF Then
			'this person is not a manager
			returnValue = False
		Else
			'this person is a manager
			returnValue = True
		End If
		rs3.Close
		Set rs3 = Nothing
		IsManager = returnValue
End Function

Since the IsManager(UniqueID) is also checking against a recordset (Which in turn is being created from the same table), is it possible to check against the array which I get within the getSubordinates(ManagerID, Counter) function?

If yes, how can I do that?

THanks.

 

[ReportingAnalyst]

Ok, given the hint that the IsManager(Unique) is being called for every employee, I moved the functionality to within GetSubordinates(ManagerID, Counter) function. It has downed my time from 34 to 26 seconds.

However, even last of the subordinates who are not managers appear with a hyperlink.

What could I be missing?

Thanks.

Function GetSubordinates(managerID, Counter)

		returnValue = False

		sub_Emp_Source = "SELECT AMP_AllMySubordinates_Postings.Member_Manager, " & _
                         "AMP_AllMySubordinates_Postings.Member_Subordinate, " & _
                         "AMP_AllMySubordinates_Postings.Owner_DateOfPost, " & _
                         "Employee.EmpID, " & _
                         "Employee.FirstName + ' ' + dbo.Employee.LastName AS Emp_Name, " & _
                         "Employee.LastName " & _
                         "FROM AMP_AllMySubordinates_Postings INNER JOIN " & _
                         "Employee ON " & _
                         "AMP_AllMySubordinates_Postings.Member_Subordinate = dbo.Employee.UniqueIdentifier " & _
                         "WHERE  (AMP_AllMySubordinates_Postings.Member_Manager = " & managerID & ") " & _
                         "AND " & _
                         "AMP_AllMySubordinates_Postings.Owner_Payee = " & strUI & " " & _
                         "AND " & _
                         "AMP_AllMySubordinates_Postings.Owner_DateOfPost = " & _
	                     "(Select MAX(AMP_AllMySubordinates_Postings.Owner_DateOfPost) FROM Amp_AllMySubordinates_Postings WHERE owner_payee = " & strUI & ") " & _
                         "ORDER BY Employee.LastName; "

		Dim rs2
		set rs2 = Server.CreateObject("ADODB.Recordset")
		rs2.open sub_Emp_Source, Conn

		If rs2.EOF Then
			'this person is not a manager
			returnValue = False
		Else
			'this person is a manager
			returnValue = True
		End If

		Dim aSubordinates

		If not rs2.EOF then

			aSubordinates = rs2.GetRows()

			'Close Recordset to use the new array with the 2 columns data
			rs2.Close()
			set rs2 = Nothing

			'Declare Constants for the above SQL columns for better readability
			'Use these Constants instead of referring to the array numeric indexes
			Const c_Subordinate = 1
			Const c_EmpID = 3
			Const c_EmpName = 4

			'Ubound(MyArray,1) 'Returns the Number of Columns
			'Ubound(MyArray,2) 'Returns the Number of Rows

			Dim iRowLoop

			For iRowLoop = 0 to UBound(aSubordinates, 2)

				Dim uniqueID
				'Changed this from managerid to subordinate id because we want to see the subordinates for the member_subordinate
				uniqueID  = aSubordinates(c_Subordinate, iRowLoop)

				If returnValue = True then
					%>
						<li>
							<input type="radio" name="empid" id="empid<%=aSubordinates(c_EmpID,iRowLoop)%>" value="<%=aSubordinates(c_EmpID,iRowLoop)%>">
							<a href="#" class="a_style" onclick="s_Hide('<%=aSubordinates(c_Subordinate, iRowLoop)%>'); return false;"><%=aSubordinates(c_EmpName,iRowLoop)%></a><font size=1>&nbsp;(<%=aSubordinates(c_EmpID,iRowLoop)%>)</font>
							<ul id="UI_<%=aSubordinates(c_Subordinate, iRowLoop)%>" style="display: none">
							<%
							Counter = Counter + 1
							Call GetSubordinates(uniqueID, Counter)
							Counter = Counter - 1
							%>
							</ul>
						</li>
							<%
			    Else
							%>
							<li>
								<input type="radio" name="empid" id="empid<%=aSubordinates(c_EmpID,iRowLoop)%>" value="<%=aSubordinates(c_EmpID,iRowLoop)%>">
								<%=aSubordinates(c_EmpName,iRowLoop)%>&nbsp;<font size=1>(<%=aSubordinates(c_EmpID,iRowLoop)%>)</font>
							</li>
							<%
				End if 'returnvalue = true or false
			Next 'iRowLoop
			%>
			<%
		End If 'rs2.EOF
End Function

 

[Tarwn]

I think you need to look at changing the design of your database a little. if you were to add in the capability for nested set or materialized path hierarchies I think it would reduce your execution time into the milliseconds and removeany need for the recursion.

 

[ReportingAnalyst]

I cannot change the database now. I did look into this. But this is like a short fix to implement soon, so I need to do something else.

If I am move the logic IsManager(UniqueID) function to within GetSubordinates, then a simple thing I am missing how can I determine not to put a hyperlink for the subordinates who are not managers.

I already noticed that by doing so, my improvement by 10 seconds.

 

[Tarwn]

The fault in the logic above is that your setting the variable returnValue based on whether there are results in the recordset or not. When you are later looping through the recordset you look at this variable to determine whether to output a link or not.
There is no way, according to your current logic, that you could not output a link for a persons name. As the returnValue can only be false if theer are no records in the recordset, which in turn would mean that you would enter the output loop.

I think the easiest way to solve this would be to add a field into your SQL query that does a COUNT to return the number of subordinates for each person. You could then output a link if this field is greater than 0, or normal text if it is 0.

-T

 

[ReportingAnalyst]

I modified the SQL query and added a count of the subordinates. Whenever I see a subordinate for a manager, the count column shows a value of 1 for all rows of the subordinates.

However if I pass a managerID to the query who does not have any subordinates, then I see no recordset. I do not see a value of 0

So my guess is that I do the same logic of seeing if the recordset is empty, i.e, if rs.EOF, then do not output the link, otherwise output the link.

Is that approach OK.

Thanks.

 

[ReportingAnalyst]

Since you gave the idea of nested sets model in the table structure, just wanted to make sure if this is a adjancy model or nested sets model.

Would this be a nested sets model?

My table structure is:

UniqueIdentifier - Primary Key
Owner_Payee
Member_Manager
Member_Subordinate
Owner_DateOfPost

If I want to see the top most manager, I pass a value to the owner_payee, for
instance, 4655.

For the owner_payee 4655, I see 6 rows.
Member_Manager Member_Subordinate
4655 2109776
4655 2330918
4655 2109843
4655 2109886
4655 4294
4655 2271274

If I want to see the subordinates for the 1st subordinate in the above recordset 2109776, then I run the query again in order to see the subordinates for the person 2109776

This is definitely a adjacency list model from what I have read in several DB forums.

Well, data is never perfect.

 

[ReportingAnalyst]

I modified the code, but I still see the subordinates with a link. What am I doing wrong? I added a variable to hold the count_sub value and see if it is one, then returnvalue is true else it is false. Is that a faulty thinking?

Please let me know.

Dim rs2
        set rs2 = Server.CreateObject("ADODB.Recordset")
        rs2.open sub_Emp_Source, Conn


        [b]If Not rs2.EOF then
        	sub_Value = rs2("Count_Sub")
        	If sub_Value = 1 Then
        		'this person is a manager
        		returnValue = True
        	Else
        		'this person is not a manager
        		returnValue = False
        	End If
        End If[/b]

        Dim aSubordinates

        If not rs2.EOF then

            aSubordinates = rs2.GetRows()

            'Close Recordset to use the new array with the 2 columns data
            rs2.Close()
            set rs2 = Nothing

            'Declare Constants for the above SQL columns for better readability
            'Use these Constants instead of referring to the array numeric indexes
            Const c_Subordinate = 1
            Const c_EmpID = 3
            Const c_EmpName = 4

            'Ubound(MyArray,1) 'Returns the Number of Columns
            'Ubound(MyArray,2) 'Returns the Number of Rows

            Dim iRowLoop

            For iRowLoop = 0 to UBound(aSubordinates, 2)

                Dim uniqueID
                'Changed this from managerid to subordinate id because we want to see the subordinates for the member_subordinate
                uniqueID  = aSubordinates(c_Subordinate, iRowLoop)

                If returnValue = True then
                    %>
                        <li>
                            <input type="radio" name="empid" id="empid<%=aSubordinates(c_EmpID,iRowLoop)%>" value="<%=aSubordinates(c_EmpID,iRowLoop)%>">
                            <a href="#" class="a_style" onclick="s_Hide('<%=aSubordinates(c_Subordinate, iRowLoop)%>'); return false;"><%=aSubordinates(c_EmpName,iRowLoop)%></a><font size=1>&nbsp;(<%=aSubordinates(c_EmpID,iRowLoop)%>)</font>
                            <ul id="UI_<%=aSubordinates(c_Subordinate, iRowLoop)%>" style="display: none">
                            <%
                            Counter = Counter + 1
                            Call GetSubordinates(uniqueID, Counter)
                            Counter = Counter - 1
                            %>
                            </ul>
                        </li>
                            <%
                Else
                            %>
                            <li>
                                <input type="radio" name="empid" id="empid<%=aSubordinates(c_EmpID,iRowLoop)%>" value="<%=aSubordinates(c_EmpID,iRowLoop)%>">
                                <%=aSubordinates(c_EmpName,iRowLoop)%>&nbsp;<font size=1>(<%=aSubordinates(c_EmpID,iRowLoop)%>)</font>
                            </li>
                            <%
                End if 'returnvalue = true or false
            Next 'iRowLoop
            %>
            <%
        End If 'rs2.EOF

 

[Tarwn]

Your still using an adjacency model. A nested sets model generally has a left and right value and all children under that node have numbers between that left and right. (It's a bit more complicated, but thats the shortest way I can explain it).


I think the problem is still in your logic. If you have added the subordinate count tot he SELECT clause of your statement, then you should use that inside your loop to determine whether the user your outputting is a manager or not. You can't do this logic outside the loop because that is on the manager level, inside the loop is the subordinate level.