We help IT Professionals succeed at work.
Get Started

asp classic and sql server loading records within a loop (Slow)

2,352 Views
Last Modified: 2015-04-26
Hello all;

Here is the code that I am using.
It loads very slow, taking up to 1/2 a minute depending on how many records are processed.
Example
1 record = Quick, less than a second
15 records = about (up-to 5 seconds computer, up-to 10 seconds Mobile device)
100 records = DAMN (up-to 15 seconds computer, up-to 1 minute on Mobile Device)

would would be causing it to be so damn slow?
<%
Set sqlAnswer = Server.CreateObject("ADODB.Command")
sqlAnswer.ActiveConnection=Conn
sqlAnswer.Prepared = true
sqlAnswer.commandtext="SELECT medid WHERE MedID=? ORDER BY MedAnswers.MedID"
sqlAnswer.Parameters.Append sqlAnswer.CreateParameter("@MedID", adInteger, adParamInput,, theMedID) 
set rsAnswer = sqlAnswer.execute
while not rsAnswer.eof
if not rsAnswer.eof then
LoadMedID = rsAnswer("MedID")

Set sqlCtCom = Server.CreateObject("ADODB.Command")
sqlCtCom.ActiveConnection=Conn
sqlCtCom.Prepared = true
sqlCtCom.commandtext="SELECT COUNT(MAID) AS ctCom FROM MedAnswers GROUP BY MedID HAVING MedID = ?"
sqlCtCom.Parameters.Append sqlCtCom.CreateParameter("@MedID", adInteger, adParamInput,, LoadMedID)
set rsCtCom = sqlCtCom.execute
if not rsCtCom.eof then
'response.Write rsCtCom("ctCom")
'response.End()
if rsCtCom("ctCom")=1 then
ctRows = ctRows+1
else
ctRows = ctRows+1
'ctRows-1
end if
end if
rsCtCom.close
set rsCtCom = nothing


rsAnswer.MOVENEXT
wend
rsAnswer.close
set rsAnswer = nothing
%>

Open in new window


Carrzkiss
Comment
Watch Question
Developer
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013
Commented:
This problem has been solved!
Unlock 1 Answer and 66 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE