10
Hello, everyone!
Here's are the basics:
1. The query looks at all positions that are active and haven't been filled.
2. It then has to look at every single position and determine three things:
Does the person running the query (via an ASP page) have certain rights?
a. Recruit rights
b. Enlist rights
c. Take rights
This works fine for 100 or less positions. But I recently added 5000 positions to the table, and of course the query takes FOREVER to run because it's looping upon itself (a big NO NO).
I've indexed the table and it helped a bit, but I think a view or stored procedure might be in order. Unfortunately, I know little about either, and as you can tell my querying skills are lacking in the sophistication department.
Also, none of the fields can be combined into a single field, hence the Ors in each section. A person could have Recruit/Enlist/Take rights based upon at least 5 fields per right.
Thoughts?
======================= CODE ==========================
sSQL = "SELECT * FROM positions"
sSQL = sSQL & " WHERE active=1"
sSQL = sSQL & " AND filled_date is NULL"
sSQL = sSQL & " AND filled_by is NULL"
Set conn = OpenConn()
Set rsPositions = conn.Execute(sSQL)
Dim j
j = 0
%>
<html>
<head>
</head>
<body>
<div class="mc">
<table border="0" width="100%">
<%
Do while not rsPositions.EOF
nPositionID = rsPositions("position_id")
sTitle = rsPositions("title")
sDescription = rsPositions("description")
dTargetHireDate = FormatDateTime(rsPositions("target_hire_date"),2)
// Recruit Link SQL
if inStr(rsPositions("recruit_by_security"),cSecurity ) > 0 or _
inStr(rsPositions("recruit_by_member"),cUserID) > 0 or _
inStr(rsPositions("recruit_by_state"),cState) > 0 or _
inStr(rsPositions("recruit_by_county"),cCounty) > 0 or _
inStr(rsPositions("recruit_by_wardprecinct"),cWard Precinct) > 0 or _
inStr(rsPositions("recruit_by_zip"),cZip) > 0 then
ShowRecruitLink = "yes"
else
ShowRecruitLink = ""
end if
// Controlled Enlist Link SQL
Set rsEnlist = conn.execute("SELECT enlist_by_member_control FROM action_recruitment_positions WHERE position_id = " & rsPositions(0))
if inStr(rsEnlist(0),cUserID) > 0 then
ShowEnlistLink = "yes"
else
ShowEnlistLink = ""
end if
// Self Enlist Link SQL
if inStr(rsPositions("enlist_by_security"),cSecurity) > 0 or _
inStr(rsPositions("enlist_by_member"),cUserID) > 0 or _
rsPositions("enlist_by_state") = cState or _
rsPositions("enlist_by_county") = cCounty or _
rsPositions("enlist_by_wardprecinct") = cWardPrecinct or _
inStr(rsPositions("enlist_by_zip"),cZip) > 0 then
ShowSelfEnlistLink = "yes"
else
ShowSelfEnlistLink = ""
end if
if ShowRecruitLink = "yes" or ShowEnlistLink = "yes" or ShowSelfEnlistLink = "yes" then
%>
<tr>
<td class="mc">
<b><%=sTitle%></b><br />
<%=sDescription%><br />
Target Hire Date: <%=dTargetHireDate%> <br />
</td>
<td class="mc" align="right" valign="top" align="center">
<%if ShowRecruitLink = "yes" then %>
<a href="#" onclick="window.open('../action_recruitment_invite.asp?action=recruit&posit ion_id=<%=nPositionID%>&invitee_id=<%=cUserID%>', 'recruit', 'toolbar=no, menubar=no, scrollbars=yes, resizable=yes, location=no, directories=no, status=no');">Recruit</a>
<%end if %>
<%if ShowEnlistLink = "yes" then %>
<br /><a href="#" onclick="window.open('../action_recruitment_invite.asp?action=enlist&positi on_id=<%=nPositionID%>&invitee_id=<%=cUserID%>', 'recruit', 'toolbar=no, menubar=no, scrollbars=yes, resizable=yes, location=no, directories=no, status=no');">Enlist</a>
<%end if %>
<%if (ShowSelfEnlistLink = "yes" or ShowSelfEnlistLinkByGeoPol = "yes") then
j = j + 1
%>
<form name="form<%=j%>" action="available_positions.asp">
<input type="hidden" name="position_id" value="<%=nPositionID%>" />
<input type="hidden" name="filled_date" value="<%=date()%>" />
<input type="hidden" name="filled_by" value="<%=cUserID%>" />
<input type="hidden" name="action" value="takePosition" />
<br /><a href="#" onclick="submitStatus(document.form<%=j%>);">Take</a>
</form>
<%end if %>
</td>
</tr>
<%
end if
rsPositions.MoveNext
Loop
%>
</table>
</div>
</body>
</html>
<%
rsPositions.Close
set rsPositions = Nothing
conn.Close
set conn = Nothing
%>
======================= CODE ==========================
