Need help refining lengthy re-iterative (looping) query

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • talktozee
    New Member
    • Jan 2007
    • 10
    #1

    Need help refining lengthy re-iterative (looping) query

    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(sS QL)

    Dim j
    j = 0

    %>

    <html>
    <head>
    </head>
    <body>
    <div class="mc">
    <table border="0" width="100%">

    <%


    Do while not rsPositions.EOF

    nPositionID = rsPositions("po sition_id")
    sTitle = rsPositions("ti tle")
    sDescription = rsPositions("de scription")
    dTargetHireDate = FormatDateTime( rsPositions("ta rget_hire_date" ),2)

    // Recruit Link SQL

    if inStr(rsPositio ns("recruit_by_ security"),cSec urity) > 0 or _
    inStr(rsPositio ns("recruit_by_ member"),cUserI D) > 0 or _
    inStr(rsPositio ns("recruit_by_ state"),cState) > 0 or _
    inStr(rsPositio ns("recruit_by_ county"),cCount y) > 0 or _
    inStr(rsPositio ns("recruit_by_ wardprecinct"), cWardPrecinct) > 0 or _
    inStr(rsPositio ns("recruit_by_ zip"),cZip) > 0 then
    ShowRecruitLink = "yes"
    else
    ShowRecruitLink = ""
    end if


    // Controlled Enlist Link SQL

    Set rsEnlist = conn.execute("S ELECT enlist_by_membe r_control FROM action_recruitm ent_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(rsPositio ns("enlist_by_s ecurity"),cSecu rity) > 0 or _
    inStr(rsPositio ns("enlist_by_m ember"),cUserID ) > 0 or _
    rsPositions("en list_by_state") = cState or _
    rsPositions("en list_by_county" ) = cCounty or _
    rsPositions("en list_by_wardpre cinct") = cWardPrecinct or _
    inStr(rsPositio ns("enlist_by_z ip"),cZip) > 0 then
    ShowSelfEnlistL ink = "yes"
    else
    ShowSelfEnlistL ink = ""
    end if


    if ShowRecruitLink = "yes" or ShowEnlistLink = "yes" or ShowSelfEnlistL ink = "yes" then

    %>

    <tr>

    <td class="mc">
    <b><%=sTitle% ></b><br />
    <%=sDescription %><br />
    Target Hire Date: <%=dTargetHireD ate%> <br />
    </td>
    <td class="mc" align="right" valign="top" align="center">
    <%if ShowRecruitLink = "yes" then %>
    <a href="#" onclick="window .open('../action_recruitm ent_invite.asp? action=recruit& position_id=<%= nPositionID%>&i nvitee_id=<%=cU serID%>', 'recruit', 'toolbar=no, menubar=no, scrollbars=yes, resizable=yes, location=no, directories=no, status=no');">R ecruit</a>
    <%end if %>
    <%if ShowEnlistLink = "yes" then %>
    <br /><a href="#" onclick="window .open('../action_recruitm ent_invite.asp? action=enlist&p osition_id=<%=n PositionID%>&in vitee_id=<%=cUs erID%>', 'recruit', 'toolbar=no, menubar=no, scrollbars=yes, resizable=yes, location=no, directories=no, status=no');">E nlist</a>
    <%end if %>
    <%if (ShowSelfEnlist Link = "yes" or ShowSelfEnlistL inkByGeoPol = "yes") then
    j = j + 1
    %>
    <form name="form<%=j% >" action="availab le_positions.as p">
    <input type="hidden" name="position_ id" value="<%=nPosi tionID%>" />
    <input type="hidden" name="filled_da te" value="<%=date( )%>" />
    <input type="hidden" name="filled_by " value="<%=cUser ID%>" />
    <input type="hidden" name="action" value="takePosi tion" />
    <br /><a href="#" onclick="submit Status(document .form<%=j%>);"> Take</a>
    </form>
    <%end if %>
    </td>
    </tr>

    <%
    end if

    rsPositions.Mov eNext

    Loop

    %>
    </table>
    </div>
    </body>
    </html>

    <%
    rsPositions.Clo se
    set rsPositions = Nothing

    conn.Close
    set conn = Nothing
    %>

    =============== ======== CODE =============== ===========
    Tags: None
    • talktozee
      New Member
      • Jan 2007
      • 10
      #2
      I thought I might add some information to help explain how this system works:

      There are three rights in the recruitment system: Recruit, Enlist, or Take. When a user logs into the system, the system knows certain characteristics about the user: their security level, their member ID, state, county, ward/precinct, and zip code.

      So, a typical user might have the following settings:

      member_id = 482
      security_level = 5
      state = MI
      county = 81
      ward/precinct = 7880-5
      zip = 48108

      Each position has columns that correspond to each right (Recruit/Enlist/Take) and to each characteristic. So, position 555 has the following columns:

      recruit_by_secu rity = null
      recruit_by_memb er = null
      recruit_by_stat e = null
      recruit_by_coun ty = null
      recruit_by_ward precinct = 28880-1
      recruit_by_zip = 48108

      enlist_by_secur ity = 7
      enlist_by_membe r = 890,827,482
      enlist_by_state = null
      enlist_by_count y = null
      enlist_by_wardp recinct = 7880-5
      enlist_by_zip = null

      enlist_by_membe r_control = 482

      So, in comparing the member who's logged in and this position, the member has the following rights:

      recruit_by_zip (which would make the Recruit link appear on the webpage)
      enlist_by_wardp recinct (which would make the Take link appear)
      enlist_by_membe r_control (which would make the Enlist link appear)

      So, if a member has ANY of these rights, the position appears on their page with the appropriate link.

      I have given some thought to combining the columns and then delimiting the values, but would this really speed up the query? While I may not be looking at 15 columns (and only be looking at 3), isn't there a way I can keep my structure yet speed up the query?

        Comment

        • iburyak
          Recognized Expert Top Contributor
          • Nov 2006
          • 1016
          #3
          1. Try to create this procedure on a server and play with it first by passing different parameters.

          [PHP]Create proc GetPositions
          @member_id int,
          @security_level int,
          @state varchar(2),
          @county varchar(50),
          @wp varchar(50),
          @zip int
          as

          SELECT 'ShowRecruitLin ' Link, *

          FROM positions a
          left join action_recruitm ent_positions b on a.position_id = b.position_id
          WHERE active=1
          AND filled_date is NULL
          AND filled_by is NULL
          AND (recruit_by_sec urity = @security_level or recruit_by_memb er = @member_id
          or recruit_by_stat e = @state or recruit_by_coun ty = @county or recruit_by_ward precinct = @wp
          or recruit_by_zip = @zip)
          UNION
          SELECT 'ShowSelfEnlist Lin' Link, *
          left join action_recruitm ent_positions b on a.position_id = -1 -- just to have the same number of columns
          FROM positions a
          join action_recruitm ent_positions b on a.position_id = b.position_id
          WHERE active=1
          AND filled_date is NULL
          AND filled_by is NULL
          AND (enlist_by_secu rity = @security_level or enlist_by_membe r = @member_id
          or enlist_by_state = @state or enlist_by_count y = @county or enlist_by_wardp recinct = @wp
          or enlist_by_zip = @zip)[/PHP]

          2. to execute stored procedure you have to pass all available parameters to it

          [PHP]exec GetPositions 482, 5, 'MI', 81, 7880-5, 48108[/PHP]

          3. Play with result and figure out how to display it correctly on your page.


          4. To pass parameters from a webpage you should concatinate correct string using variable in place like

          [PHP]sSQL = "exec GetPositions " & cUserID & ", " & cSecurity & ", '" & cState & "', " & cCounty & ", " & cWardPrecinct & ", " & cZip[/PHP]

          Good luck.

            Comment

            • iburyak
              Recognized Expert Top Contributor
              • Nov 2006
              • 1016
              #4
              Correction to procedure

              [PHP]Create proc GetPositions
              @member_id int,
              @security_level int,
              @state varchar(2),
              @county varchar(50),
              @wp varchar(50),
              @zip int
              as

              SELECT 'ShowRecruitLin ' Link, *

              FROM positions a
              left join action_recruitm ent_positions b on a.position_id = b.position_id
              WHERE active=1
              AND filled_date is NULL
              AND filled_by is NULL
              AND (recruit_by_sec urity = @security_level or recruit_by_memb er = @member_id
              or recruit_by_stat e = @state or recruit_by_coun ty = @county or recruit_by_ward precinct = @wp
              or recruit_by_zip = @zip)
              UNION
              SELECT 'ShowSelfEnlist Lin' Link, *
              FROM positions a
              left join action_recruitm ent_positions b on a.position_id = -1 -- just to have the same number of columns
              WHERE active=1
              AND filled_date is NULL
              AND filled_by is NULL
              AND (enlist_by_secu rity = @security_level or enlist_by_membe r = @member_id
              or enlist_by_state = @state or enlist_by_count y = @county or enlist_by_wardp recinct = @wp
              or enlist_by_zip = @zip)[/PHP]

                Comment

                • talktozee
                  New Member
                  • Jan 2007
                  • 10
                  #5
                  Thanks, iburyak. I talked to a few friends, who pointed out the stupid "looping upon a query" error that I had made.

                  So what I ended up doing was querying the entire table with my WHEREs and ORs, and then running some IF-THEN statements mirroring those WHEREs and ORs, and if something was true then spit out the link.

                  Pretty simple, really, when I look back on it. Just a dumb looping mistake I didn't have to make.

                  Thanks!

                    Comment

                    • iburyak
                      Recognized Expert Top Contributor
                      • Nov 2006
                      • 1016
                      #6
                      I didn't wanted to be very hard on you seeing your struggle... :)

                      You are welcome.

                        Comment

                        Previous template Next
                        Working...