By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,849 Members | 2,169 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,849 IT Pros & Developers. It's quick & easy.

why does this comparison script run so slow??

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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
.

Jul 19 '05 #2

P: n/a
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
: >
: >
: >.
: >
Jul 19 '05 #3

P: n/a

"Shay" <sh*******@hotmail.com> wrote in message
news:02****************************@phx.gbl...
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
-----Original Message-----
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,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
: >
: >"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
: >
: >
: >.
: >
.

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>
Jul 19 '05 #4

P: n/a
"Chris Hohmann" <hohmannATyahooDOTcom> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...

"Shay" <sh*******@hotmail.com> wrote in message
news:02****************************@phx.gbl...
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
-----Original Message-----
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,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
: >
: >"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
: >
: >
: >.
: >
.

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).
Jul 19 '05 #5

P: n/a
"Chris Hohmann" <hohmannATyahooDOTcom> wrote in message
news:OW**************@tk2msftngp13.phx.gbl...
<<Beginning of message omitted for brevity>>
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. ;-)
Jul 19 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.