OK, a couple of things will make this faster:
a) Use "SELECT COUNT()" SQL statements to get your counts, rather than
scrolling through a whole recordset incrementing counters based on comparing
values.
b) The main reason everything is slow is the string concatenation. This is
*very* slow in VBScript for large amounts of data and/or large numbers of
concatenations. There is an explanation here:
http://www.adopenstatic.com/experime...catenation.asp
under the heading "Why does VBScript concatenation take so long?"
Cheers
Ken
"Shay" <sh*******@hotmail.com> wrote in message
news:06****************************@phx.gbl...
: Well I was just doing a
:
: set lRS = moConn.Execute
:
: So whatever the default is, but I changed it to :
:
: call lRS.Open(sSQL,moConn,adOpenForwardOnly,adLockReadO nly)
:
: and while it seems to run a little faster, still very slow
: for only 3 thousand records. It is not the SQL query as
: that takes about 1 second in Query Analyser.
:
: In the 'do something section, all I do is increment a 3
: counts based on whether the comparisons evaluate to true
: and each comparison can only can only be true once so
: if...elseif....else if
:
: iCnt = iCnt + 1
:
: and also builting a HTML table which I will then write out
: at the end, but I limit that to 250 rows, but something
: like below...any help would be great as I can't believe
: how slow it runs through the loop.
:
: Cheers
:
: sACW_HTML = sACW_HTML + "<tr
: onmouseover='javascript
:fnMouseOver(this)'
: onmouseout='javascript
:fnMouseOut(this)'>"
:
: sACW_HTML = sACW_HTML + "<td style='border-
: bottom: 1 solid #8A9AC6;padding-left:5px;padding-
: right:5px'><font color='#000000' size='1' face='MS Sans
: Serif'>" & CStr(lRS.Fields("Contact Date/Time"))
: & "</font></td>"
:
: sACW_HTML = sACW_HTML + "<td style='border-
: left: 1 solid #8A9AC6; border-bottom: 1 solid
: #8A9AC6;padding-left:5px;padding-right:5px'><font
: color='#000000' size='1' face='MS Sans Serif'>" & CStr
: (lRS.Fields("Internal Card No")) & "</font></td>"
:
: sACW_HTML = sACW_HTML + "<td style='border-
: left: 1 solid #8A9AC6; border-bottom: 1 solid
: #8A9AC6;padding-left:5px;padding-right:5px'><font
: color='#000000' size='1' face='MS Sans Serif'>" & CStr
: (lRS.Fields("Supp No")) & "</font></td>"
:
: sACW_HTML = sACW_HTML + "<td style='border-
: left: 1 solid #8A9AC6; border-bottom: 1 solid
: #8A9AC6;padding-left:5px;padding-right:5px'><font
: color='#000000' size='1' face='MS Sans Serif'>" & CStr
: (lRS.Fields("User Id")) & "</font></td>"
:
: sACW_HTML = sACW_HTML + "</tr>"
:
:
:
: >-----Original Message-----
: >What is in the bit that is marked "do something"?
: >
: >Also, what type of cursor/lock do you have on the
: recordset?
: >
: >Cheers
: >Ken
: >
: >"Shay" <sh*******@hotmail.com> wrote in message
: >news:3d****************************@phx.gbl...
: >: essentially I am trying to do some counts based on some
: >: assumptions in the recordset. So I get the RS back, put
: >: the values into a variable, move to the next record in
: the
: >: RS and compare what is in the variable to the value in
: the
: >: next record in the recordset and do a count. Then
: >: overwrite the value in the variables and do the same for
: >: the next record and so.
: >:
: >: But this runs extremly slow. 5000 records takes about 10
: >: minutes in IE6 and I can only guess it is the stepping
: >: through the RS that is the problem?? There are only 4
: >: fields in the RS...but the comparisons seem to take
: >: forever and wondering is there something I am doing
: wrong
: >: or anything I can do to speed it up???
: >:
: >: Thanks in advance
: >:
: >:
: >: set lRS = moConn.Execute(sSQL)
: >:
: >: if not lRS.EOF then
: >: iCnt = 0
: >: iTransfers = 0
: >: iRepeatCalls = 0
: >: iRealACWs = 0
: >:
: >: Do While Not lRS.EOF
: >: if iCnt <> 0 then
: >:
: >:
: >: if (sInternal_No = Trim(CStr(lRS("Internal Card
: >: No")))) and (sSupp_No = Trim(CStr(lRS("Supp No")))) and
: >: (sUser_Id = Trim(CStr(lRS("User Id")))) then
: >:
: >: 'do something
: >:
: >:
: >: end if
: >:
: >:
: >: sInternal_No = Trim(CStr(lRS("Internal Card No")))
: >: sSupp_No = Trim(CStr(lRS("Supp No")))
: >: sContact_Time = lRS("Contact Date/Time")
: >: sUser_Id = Trim(CStr(lRS("UserId")))
: >: lRS.MoveNext
: >: Loop
: >
: >
: >.
: >
