Slow code writing data to table

Discussion in 'ASP General' started by JP SIngh, Oct 1, 2004.

  1. JP SIngh

    JP SIngh Guest

    Hi All

    I am trying to plot the days off employees have taken in the selected month
    visually using the following code.

    The code below takes a long time to execute typically about 8-15 secs each
    time.

    Is there a away to optimise the code below

    thanks

    Set RS = CreateObject("ADODB.Recordset")
    dim arrDates(31)
    do while not rcado.eof
    html = html & "<TR>" & vbcrlf
    html = html & "<TD class=off>" & rcADO.Fields("Lastname").Value &
    ", " & rcADO.Fields ("Firstname").Value & "<TD>" & vbcrlf
    sql = "Select * from tempquery "
    sql = sql & " WHERE (((sMonStart)=" & selMonth & ") AND ((UserID)="
    & rcAdo ("EmployeeNumber") & ") AND ((EnYear)=" & selYear & ")) OR
    (((sMonEnd)=" & selMonth & ") AND ((UserID)=" & rcAdo("EmployeeNumber") & ")
    AND ((EnYear)=" & selYear & ")) order by Fdate "
    RS.Open SQL, conn,adOpenStatic
    arrcount = 0

    do while not rs.eof
    startDate = rs("fdate")
    endDate = rs("tdate")
    ' If both dates exists in current month
    if month(startdate) = selmonth and
    Month(endDate) = selmonth then
    startNum = day(startdate)
    endNum = day(enddate)
    end if
    ' If starting date is in previous month
    if month(startdate) = selmonth -1 then
    startnum = 1
    endnum = day(enddate)
    end if
    ' If ending date is in next month
    if month(enddate) = selmonth + 1 then

    startnum =
    day(startdate)
    endNum = lastday
    end if

    do while startnum <=endnum
    arrDates(arrcount) =
    startnum
    startnum = startnum + 1
    arrcount = arrcount + 1
    loop

    startnum = 0
    endnum = 0
    rs.movenext
    loop
    RS.Close

    for j = 1 to lastday
    found = flase
    for p = 0 to lastday
    if arrDates(p) = j then
    found = true
    end if
    next

    if found = true then
    html = html & "<TD align = center class=off>"& "X" & "<TD>" & vbcrlf
    else
    html = html & "<TD align = center class=off>" & "-" & "<TD>" & vbcrlf

    end if
    next

    html = html & "</TR>" & vbcrlf

    for p = 0 to lastday
    arrDates(p) = ""
    next
    rcado.movenext
    loop

    response.write html
     
    JP SIngh, Oct 1, 2004
    #1
    1. Advertising

  2. I suggest that using GetRows will speed this up considerably. See I suggest
    that using GetRows will speed this up considerably.

    Bob Barrows

    JP SIngh wrote:
    > Hi All
    >
    > I am trying to plot the days off employees have taken in the selected
    > month visually using the following code.
    >
    > The code below takes a long time to execute typically about 8-15 secs
    > each time.
    >
    > Is there a away to optimise the code below
    >
    > thanks
    >
    > Set RS = CreateObject("ADODB.Recordset")
    > dim arrDates(31)
    > do while not rcado.eof
    > html = html & "<TR>" & vbcrlf
    > html = html & "<TD class=off>" &
    > rcADO.Fields("Lastname").Value & ", " & rcADO.Fields
    > ("Firstname").Value & "<TD>" & vbcrlf sql = "Select * from
    > tempquery " sql = sql & " WHERE (((sMonStart)=" & selMonth &
    > ") AND ((UserID)=" & rcAdo ("EmployeeNumber") & ") AND
    > ((EnYear)=" & selYear & ")) OR (((sMonEnd)=" & selMonth & ") AND
    > ((UserID)=" & rcAdo("EmployeeNumber") & ") AND ((EnYear)=" & selYear
    > & ")) order by Fdate " RS.Open SQL, conn,adOpenStatic
    > arrcount = 0
    >
    > do while not rs.eof
    > startDate = rs("fdate")
    > endDate = rs("tdate")
    > ' If both dates exists in current month
    > if month(startdate) = selmonth and
    > Month(endDate) = selmonth then
    > startNum =
    > day(startdate)
    > endNum = day(enddate) end if
    > ' If starting date is in previous month
    > if month(startdate) = selmonth -1
    > then startnum = 1
    > endnum =
    > day(enddate) end if
    > ' If ending date is in next month
    > if month(enddate) = selmonth + 1
    > then
    >
    > startnum =
    > day(startdate)
    > endNum = lastday
    > end if
    >
    > do while startnum <=endnum
    > arrDates(arrcount) =
    > startnum
    > startnum = startnum
    > + 1 arrcount =
    > arrcount + 1 loop
    >
    > startnum = 0
    > endnum = 0
    > rs.movenext
    > loop
    > RS.Close
    >
    > for j = 1 to lastday
    > found = flase
    > for p = 0 to lastday
    > if arrDates(p) = j then
    > found = true
    > end if
    > next
    >
    > if found = true then
    > html = html & "<TD align = center class=off>"& "X" & "<TD>" &
    > vbcrlf else
    > html = html & "<TD align = center class=off>" & "-" & "<TD>" &
    > vbcrlf
    >
    > end if
    > next
    >
    > html = html & "</TR>" & vbcrlf
    >
    > for p = 0 to lastday
    > arrDates(p) = ""
    > next
    > rcado.movenext
    > loop
    >
    > response.write html


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Oct 1, 2004
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    3
    Views:
    3,076
  2. HK
    Replies:
    3
    Views:
    470
  3. mike
    Replies:
    3
    Views:
    409
    Virgil Green
    Jul 11, 2005
  4. Rio
    Replies:
    4
    Views:
    1,224
  5. sil
    Replies:
    4
    Views:
    294
    Thomas 'PointedEars' Lahn
    Feb 7, 2010
Loading...

Share This Page