why does this comparison script run so slow??

[Shay]

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

 

[Shay]

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,adLockReadOnly)

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>"

 

[Ken Schaefer]

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:
www.adopenstatic.com/experiments/stringconcatenation.asp
under the heading "Why does VBScript concatenation take so long?"

Cheers
Ken

: 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,adLockReadOnly)
:
: 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
: >
: >: >: 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
: >
: >
: >.
: >

 

[Chris Hohmann]

Hey thanks for the link. That speeds it up a hell of a
lot, but I can't do the Select Count(*) as it is implied
data and I actually have to run through the RS and do
comparisons to get the counts.

But thanks again, will just have to do something else for
the HTML table.

Cheers

If you describe the summary logic in some more detail we could very
likely get this in one query/stored procedure. Here's some code.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>ACW</title>
<style type="text/css">
..ACW{
border-left : 1px solid #8A9AC6;
border-bottom : 1px solid #8A9AC6;
padding-left : 5px;
padding-right : 5px;
color : #000000;
font : 1 MS Serif;
}
</style>
</head>
<body>
<table>
<tr>
<td class='ACW'>
<%
Dim sSQL,sConn,cn,rs
sSQL = "EXEC spMyStoredProcedure" '<-- Insert your stored procedure/SQL
statement here
sConn = '<--- Insert your connection string here
Set cn = CreateObject("ADODB.Connection")
cn.Open sConn
Set rs = cn.Execute(sSQL,,&H1)
If Not rs.EOF Then Response.Write rs.GetString(2,,"</td><td
class='ACW'>","</td></tr><tr><td class='ACW'>")
rs.Close : Set rs = Nothing
%>
</td>
</tr>
</table>
</body>
</html>

 

[Chris Hohmann]

If you describe the summary logic in some more detail we could very
likely get this in one query/stored procedure. Here's some code.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>ACW</title>
<style type="text/css">
.ACW{
border-left : 1px solid #8A9AC6;
border-bottom : 1px solid #8A9AC6;
padding-left : 5px;
padding-right : 5px;
color : #000000;
font : 1 MS Serif;
}
</style>
</head>
<body>
<table>
<tr>
<td class='ACW'>
<%
Dim sSQL,sConn,cn,rs
sSQL = "EXEC spMyStoredProcedure" '<-- Insert your stored procedure/SQL
statement here
sConn = '<--- Insert your connection string here
Set cn = CreateObject("ADODB.Connection")
cn.Open sConn
Set rs = cn.Execute(sSQL,,&H1)
If Not rs.EOF Then Response.Write rs.GetString(2,,"</td><td
class='ACW'>","</td></tr><tr><td class='ACW'>")
rs.Close : Set rs = Nothing
%>
</td>
</tr>
</table>
</body>
</html>

Additional note: 5,000 records with four columns took 2.66 seconds on my
admittedly scrawny workstation (Dell OptiPlex GX110, 866mHz, 512M RAM).

 

[Chris Hohmann]

"Chris Hohmann" <hohmannATyahooDOTcom> wrote in message

Additional note: 5,000 records with four columns took 2.66 seconds on my
admittedly scrawny workstation (Dell OptiPlex GX110, 866mHz, 512M

RAM).

I was able to knock down that number to 0.25 seconds by omitting the
closing tags for <tr> and <td> and specifying a style for <td> instead
of creating a special class. Not too shabby. The GetString call looks
like this:

rs.GetString(2,,"<td>","<tr><td>")

HTH
-Chris, who loves carrying on a conversation all by himself. ;-)