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

First of all, change the HAVING clause to WHERE clause:

SELECT COUNT(MAID) AS ctCom FROM MedAnswers WHERE MedID = ? GROUP BY MedID 

Select all Open in new window

HAVING clause is used to filter the results based on a value of an aggregate function, i.e.

SELECT COUNT(MAID) AS ctCom FROM MedAnswers WHERE MedID = ? GROUP BY MedID HAVING MAX(Score) > 10

Select all Open in new window

Then, open the SQL Server Management Console and try to run this query in the Query window. Check how fast it is for different MedID. If you find it slow in the Query window you may need to create an index for the medID column. It should be faster after that

Hi Wayne,

A couple of things.  First, I think you are better off using do / loop over while/wend  https://msdn.microsoft.com/en-us/library/aa266320(v=vs.60).aspx  http://www.w3schools.com/vbscript/vbscript_looping.asp

Next, it is faster to put what you need in an array via getrows.

if not rsAnswer.eof then
   arryAnswer= rsAnswer.getrows()
end if

Select all Open in new window

Now you have a 2 dimensional array you can access multiple times throughout the same page without hitting the db. But it also runs through records much faster.

I think the real answer may be in your index.  Is MedAnswers indexed at all?  Also you can change the sql to put all of this on one row.

Your first sql, "SELECT medid WHERE MedID=? ORDER BY MedAnswers.MedID"  Where is the FROM?

Where is the FROM
Yep, that is what you get for removing un-needed stuff from the statement, to display here.
There is always something missing somewhere :)

@chaau
the records load fast, like they should within SQL Server, I have already tested that a while back, while designing my statements.
So, the issue is not really SQL Server, but the way that the records are looping through to gain the information that is needed to be displayed.
This I tested, by removing everything from the page, and then adding in bit-by-bit until the page started loading SLOW.
And this is where I found this one code here, that was causing the issue.
And that is within the WHILE Movenext LOOP.

Hey Scott, been skiing lately? Cool pic.

OK,
#1: the Array, I have never used before, so I am a little out-of-touch on how to implement it into the project.
#2: DO LOOP, I am uncertain on how many records will be returned per page.
It could be 1 or it could be 1,000.
How would I write the DO LOOP for the example code above. (WITH the FROM in the 1st statement )

I agree that moving everything to an array would speed things up, and switching over to a do while is simple:

if not rs.BOF and not rs.EOF then
    do while not rs.EOF
       '-- process recordset
        rs.MoveNext
     loop
end if

Select all Open in new window

it doesn't matter how many records you have, it'll loop through all of them until it reaches the end

one thing that may help that wasn't mentioned is to implement paging, especially if you have a large data set being returned. $Guys has a good thorough article detailing the steps needed to implement paging:

http://www.4guysfromrolla.com/webtech/121298-1.shtml

and here's a little cheat sheet on working with 2D arrays, which is what GetRows will give you:

http://webcheatsheet.com/asp/multidimensional_arrays.php

thanks Big Monty.
I remember now, seeing this code a while, while back.
I never used, and am not really sure as to why.
I have just always used the
While not rs.eof.

No one ever corrected me on it, and then I read from the link provided by Scott, that using it is not recommended.
But does not explain as to why? http://www.w3schools.com/vbscript/vbscript_looping.asp

OK. I replace my code, with what you supplied, and it still loads about the same.
45 records, at about 10 seconds for both Desktop and Mobile. (both Google Chrome)

One more thing.
I am hoping that they are not going to get HUGE, so, basically like what we have here on EE.
Where there is no paging, that is what I am doing on this particular site.
It is not going to be a paging site, just straight loaded content.
However, I am thinking about implementing a [Load More] JQuery script into the mix as well.
To help on loading larger pages.

OK. I replace my code, with what you supplied, and it still loads about the same.

is that changing over to using the GetRows function as well?

I don't know the technical reasons why using one type of loop is preferred, I always just used do...while

Just wondering if you have checked that it is not due to the slow Internet bandwidth
If you are using IE you can trace it using the F12 developer tools. You can select "network" and then activate the network traffic capturing. There, in the output window you will see the timing for each request. It maybe something different, i.e. a picture

What you want to do is recreate the query as just one query or if you have to, create a view linking these things, then run it.  

If you can provide sample data along with your layout, perhaps one of the sql experts can help you determine the best type of indexing to run.  15 seconds to display 100 records is very slow.  15 to 30 records should be almost instantly.  100 records a slight delay.  Where you get into trouble is pushing out several hundred or a thousand records to the browser....even if they are not displayed.  

The difference between using getrows or a do/loop is not going to be noticeable for 15 records, but it can be for 100 or more.

If you do have 1000 records, what you want to do is send maybe 50 or 100 to the browser, send them to some type of table (maybe data tables https://www.datatables.net/) and only load what you need. When it is time to get the 101st records, then send an ajax request to push out another 100 records.  Or when they get to the page prior to the last, push out the next hundred so it is in the background.

It is also very possible chaau is on to something and it could be your page.  Are you using a "blank" asp page with just data or does this include all your css and javascript?

@chaau.
This is local testing, not internet testing right now, that will be done with it is uploaded to the live hosting server.
I am running everything locally on a 64-bit windows 7 machine.
64-bit SQL Server 2005.
ASP Classic, under IIS7.5

However, at one point, when my pages where loading slowly, I noticed that it was during a time that the internet was crapped out, and I then downloaded ALL the JS files that the site uses, and now run them locally, instead of through the internet, so that took care of the SLOOW loading, DUE to Internet related issues.
So, right now, I have nothing on the site that is reaching out to the internet.
(Gotta love internet providers who do not give a crap about the little people with no business's around)


@Big Monty.
No, it is not using the getrows(). That has not been tested yet, as I am not certain how to use it. (keyword, yet, as in I will)

@Scott.
All the pages do their own thing.
All my asp pages, are a LOT of include files, everything has its own page.

The testing that I did, was to strip the content out of the "include file" page that was causing the slow-down.
So, running it, loading faster than a second.
So, at that point, I started adding in all the ASP Database coding.
Once I got to the LOOP code, it loading fast.
However, once I added in the select statements between the LOOP, that is what started to slow everything down.

Checking out the Task Manager, the only thing the spikes during the loading of a large page is
lsass.exe = Local Security Authority Process
It spikes to about 23% under CPU
And after the page loads, it drops back down to regular 0%

Any idea's on why that would be happening?
The page tested loaded 45 records.

As for sample data.
This is all in a test environment Scott, so all the data is simple mess like.

Hello test
This is a test
and another test
and then another test

So, each line is a different record.
Nothing huge, just simple test records to see what is going on, and to make sure that everything is loading and running like it is supposed to do and work.

You have not replied if you have tried to correct your query to remove the HAVING, as I suggested in my first response:

SELECT COUNT(MAID) AS ctCom FROM MedAnswers WHERE MedID = ? GROUP BY MedID 

Select all Open in new window

Sorry chaau
Yes, that was changed.
It really depends on how SQL Server Management studio produces the code, and on rather or not I catch the HAVING or not, and in this case, I did not catch it, until you stated it.
However, it still did not make a difference in the loading time.

OK, I am going to call it a night guys.
Tomorrow, I will put together a little example of the getrows, and see if I can tech myself how to use it.
For what I have read, it should (SHOULD) be a no-brainer.
Will have to wait and see.

Have a good one all.

One more thing, was thinking about this.
On 3 of my other sites, in the admin area's, I have all the records loading at one time.
each of the sites
#1: 55 records - about 1/2 a second
#2: 345 records - about 1/2 -to- 1 second
#3: 456 records - about a second or 2

And I am using the same
while not rs.eof
movenext

So, if they load fast, then why doesn't this site load them fast.
Then I got to thinking.

The record count.

sqlCtCom.commandtext="SELECT COUNT(MAID) AS ctCom FROM MedAnswers where MedID = ? GROUP BY MedID"

Select all Open in new window

This is where it is bogging down at.
So, why would this cause it to bog down.

This statement is used to check how many records are in the database for this ID#
Then it shots out the # to put beside the record.

Example
There are 20 records.
Beside each record displayed, it is going to show

record #1 (ID 57)
record #2 (ID 61)
record #3 (ID 62)

basically, it is letting you know which record it is, in the order it appears on the page.

is there a better way of doing this count? To display this information to the page?

have a good one.
going to bed now.

Wayne

Is the testing server an actual web server or a windows machine running iis?  Turns out there is a big difference.  Somebody had similar question and as it turned out that was the case.  

Run your page to render the html, then copy paste and throw on jsbin and lets see what it is all about.

I saved the page, and ran the URL to that page, with the 45 records, and it loaded quick, about 1/2 a second or less.
And yes, this is a Windows machine running IIS.
I had though that, that may be the issue as well.
Not unless it is the SQL Server that is the issue on the windows system.
The only way to truly find out, is to upload the site, to a testing environment on the live server
And see what happens.

I have a Server locally, it is a 32-bit windows 2003 server.
I could test it on that one, and see what it does, before I upload it to the live server.

Installed SQL Server on the 2003 Server.
ran the site, using the same link, and it loads about the same time frame.
So, not sure of the environment, is the issue here.
I am looking at maybe a new way of doing the count.
So, that is what I am going to try and focus on.

And yes, slept for a few hours, and back up again. Sucks really.

It sounds like it is not the actual page if the html is loading instantly.  It could still be the fact you are running on a windows desktop running iis vs a server but this is still a very  long time.

The  next thing I would look at is indexing your database and how many records you are outputing.    Please try adding "TOP 10" to your sql statement.

sqlAnswer.commandtext="SELECT TOP 10 medid  FROM sometable WHERE MedID=? ORDER BY MedAnswers.MedID"

Select all Open in new window

Are the two recordsets pulling from the same table?  

On the first query where it looks like you are gathering all of your MedID's, you should be able to look sql manager studio and figure out how many records you are dealing with.  If you are pushing out 1000 records at a time, that is too many.  I wouldn't push out more than 100 tops but 50 would be nice.  If you need  paging, page 10 at a time until you get to the last page, then push out another 50 records.

How are you getting at the MedID in the first place?  Is there another loop before the first?

I think you're on to something with the record count sql statement, especially if you have a LOT of data in that table. This would be another good reason to switch over to using GetRows(), you could eliminate that second query and use the following to get the number of records returned:

set rs = conn.Execute( sql )                            '-- execute first select statement
arrData = rs.GetRows()                                  '-- copy contents over to an array
numRecords = UBound( arrData ) - 1          '-- get the number of records returned. since UBound is 0 based, you subtract 1 from the total

Big Monty

I am trying your theory at the moment, however, I have run into an issue.
Could you let me know if this is written correctly?

Following error
ADODB.Recordset error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

/ask/Post/test.asp, line 55

<%if not rsAnswer.BOF and not rsAnswer.EOF then
do while not rsAnswer.eof
arrData = rsAnswer.GetRows()
numRecords=ubound(arrData)-1
%>
Is this the one record to show the record Number?
Basically, what I am wanting to show, is the
Record Number = 1
RecordID = 46

So if there is 45 records, then it needs to show
1-45
beside each record.
<div># <%=numRecords%></div>

<%
rsAnswer.movenext ' Line 55
loop
end if
%>

Select all Open in new window

scott, to answer your questions.

Yes, they are both from the same.
The Loop gets the records.
The count comes off from each loop, to get what number that it is in the cycle.

The main ID theMedID
Is coming from a URL Query.

ok I see the problem, you only call GetRows() once, not through each loop iteration.

using UBound will give you the total number of rows, if you want to have each record with it's own number, then you'll need to implement a counter, in this case we can switch over to a for-next loop

<%if not rsAnswer.BOF and not rsAnswer.EOF then
arrData = rsAnswer.GetRows()
numRecords=ubound(arrData) - 1

for counter = 0 to UBound( arrData, 1 )   %>

<div># <%=counter + 1%></div>

<% counter = counter + 1
next
Response.Write "<p>Total Number of Records: " & numRecords & "</p>"
end if
%>

Select all Open in new window

to list out the contents of the array, you need to reference the array by column and row:

Response.Write arrData( row, col )

Wayne, this is a good reference http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28109201.html#a39113720

Ok, if they are from the same table then you can simply  do this on one recordset.   Are you able to create a group by sql statement that shows the data you need and the count?  

Can you list the data you want output and your table layout and a few lines of sample data.

OK, Update.
I found this, and it works within the same statement, so I was able to remove the Count statement.

SELECT row_number() over (order by MedAnswers.MAID) as ctRows,

The page loaded 45 records in about 1-1/2 seconds on the Windows7 machine.

This is getting confusing.  Are you trying to show the total records?  Or row number?  You could have also just used the row number calculated on the page.  

If you need to show the total count, you could create a query that simply runs SELECT COUNT(column_name) FROM table_name WHERE something='something', then close that recordset and start a new with your looping.

Within the actual site, the time is equal for both desktop and Mobile device for loading.
The desktop is the same machine, and the Mobile is through WI-FI.
The time for the actual page, is about 6-8 seconds, loading 45 records.
(that is not single records, that is about 10 records per set, being loading,
It should be less time, but I am going to take into account, that it is loading on a Windows system.
It is slightly faster using the row_number() with the select statement.
And both pages (Desktop and Mobile), are using the DO WHILE
As well.

I do, however, want to test the getrows() as well, to see if that will speed things up a bit more.
However, I no longer need the record count, as it is coming from SQL now.
I will test the getrows() and get back with everyone in a few.

@Scott
In my post here http:Q_28646962.html#a40697600
I stated the following, as what I was needing to show.

record #1 (ID 57)
record #2 (ID 61)
record #3 (ID 62)

@Scott.
Using the Row Number calculator on the page, I had tried, and for some reason, it was breaking.
I do not know why, and could not figure it out at the time.
So I will with the select count statement, that I was using.
This is the reason for it being there.

The calculator is used in near about all my pages, except for this one.
I will have to dive into it, and see what was causing the issue, and making it not function properly at a later date.

Just wanted to mention that.

Knowing your schema and what you are linking is what will help others give you tips on making this faster.   See this example I created using just one query. http://sqlfiddle.com/#!6/0df62/9

CREATE TABLE MedAnswers 
    ([MedID] varchar(13))
;
    
INSERT INTO MedAnswers 
    ([MedID])
VALUES
    ('id1'),
    ('id2'),
    ('id2'),
    ('id2'),
    ('id1'),
    ('id3'),
     ('id1'),
    ('id2'),
    ('id2'),
    ('id2'),
    ('id1'),
    ('id3')
;
SELECT ROW_NUMBER() OVER(ORDER BY [MedID] ASC) AS Row,
    [MedID], 
    COUNT([MedID]) as Count 
    FROM MedAnswers 
    GROUP BY [MedID] 
    ORDER BY [MedID] ASC  

Select all Open in new window

OUTPUT

| Row | MedID | Count |
|-----|-------|-------|
|   1 |   id1 |     4 |
|   2 |   id2 |     6 |
|   3 |   id3 |     2 |

Select all Open in new window

Adding about 300 records the execution time is 30ms and sqlfiddle is typically slower than your production.  If you are getting noticeably slower results, there is probably an issue with a piece of the puzzle you are leaving out.

CREATE TABLE MedAnswers 
    ([MedID] varchar(13))
;
    
INSERT INTO MedAnswers 
    ([MedID])
VALUES
  ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
     ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
    ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
     ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
   ('id1'),('id2'),('id3'),
   ('id3'),('id1'),('id3'),
    ('id3')
;

Select all Open in new window

| Row | MedID | Count |
|-----|-------|-------|
|   1 |   id1 |   104 |
|   2 |   id2 |    52 |
|   3 |   id3 |   157 |
        

Select all Open in new window

I tried to run your script Scott, however, it is giving me an error on the , between each value.
If I run it with only a single value, it runs and inserts fine.
But not with all them values together.

Error received
Incorrect syntax near ','.

It seems to be related to the version of SQL Server that I am running, of which is 2005.
It will need to be individual insert statements to insert that many records.

I can create a temp Table, and load it with a bunch of stuff, and run the test that way.

Wayne,  Which script are  you running with what input?   Please try and create an sql fiddle so we can duplicate what you are experiencing.  

1)  go to http://sqlfiddle.com/
 
2) Top left, select SQL Server

3) In the box on the left, create your sample table and data as I have done above.  The left side of sqlfiddle will look something like below.

CREATE TABLE MedAnswers 
    ([MedID] varchar(13))
;
    
INSERT INTO MedAnswers 
    ([MedID])
VALUES
    ('id1'),
    ('id2'),
    ('id2'),
    ('id2'),
    ('id1'),
    ('id3'),
     ('id1'),
    ('id2'),
    ('id2'),
    ('id2'),
    ('id1'),
    ('id3')
;

Select all Open in new window

3) Click Build Schema and make sure you do not get any errors.

4) On the right side add your select statement

SELECT ROW_NUMBER() OVER(ORDER BY [MedID] ASC) AS Row,
    [MedID], 
    COUNT([MedID]) as Count 
    FROM MedAnswers 
    GROUP BY [MedID] 
    ORDER BY [MedID] ASC  

Select all Open in new window

Please do this with your own table structure and fake data that can båe posted to the public.  This is the best way to make sure we are all  on the same page.  If you don't want to use fiddle, then at least give us the same information posted above in your own data and structure so we can duplicated it on our own.

Tested 480 lines
Inside of management studio
fast, like not even a second.
Loading a page
Same, not even a second.

So, i then took the main sql query that does the LOOP.
And I ran it on its own page, and it loaded just about as fast as the other example did.

So.
What I am going to do now, is look at the rest of the code, that is loading in the actual page, and see where the other issue may be it, like I did with the COUNT statement.

Will post back later on with my findings.
Thanks everyone.

After a lot of thinking I believe I know what is the cause for the slowness. It is the creating and destroying of the ADO dataset object. Let's try this code and see if it makes difference:

<%
Set sqlCtCom = Server.CreateObject("ADODB.Command")
sqlCtCom.ActiveConnection=Conn
sqlCtCom.Prepared = true
sqlCtCom.commandtext="SELECT COUNT(MAID) AS ctCom FROM MedAnswers WHERE MedID = ?"
sqlCtCom.Parameters.Append sqlCtCom.CreateParameter("@MedID", adInteger, adParamInput,, 0)

Set sqlAnswer = Server.CreateObject("ADODB.Command")
sqlAnswer.ActiveConnection=Conn
sqlAnswer.Prepared = true
sqlAnswer.commandtext="SELECT medid FROM firstTable ORDER BY MedAnswers.MedID"
set rsAnswer = sqlAnswer.execute
while not rsAnswer.eof
if not rsAnswer.eof then
LoadMedID = rsAnswer("MedID")
sqlCtCom.Parameters.Item("@MedID").value = 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


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

Select all Open in new window

Please try the code above (BTW, you have not specified the table name for the firstTable) and see if it makes any difference. If it does then we know for sure that it is the creation/destruction of the object that causes it
Having said all that, I believe your code can be easily converted to a single SQL select, like this:

<%
Set sqlAnswer = Server.CreateObject("ADODB.Command")
sqlAnswer.ActiveConnection=Conn
sqlAnswer.Prepared = true
sqlAnswer.commandtext="SELECT f.medid, c.cnt FROM firstTable f LEFT JOIN (SELECT COUNT(MAID), medid AS cnt FROM MedAnswers GROUP BY medid) c ON f.medid = c.medid ORDER BY MedID"
set rsAnswer = sqlAnswer.execute
while not rsAnswer.eof
if not rsAnswer.eof then
response.Write rsAnswer("MedID") & "&nbsp;" & rsAnswer("cnt")
'response.End()
end if
rsAnswer.MOVENEXT
wend
rsAnswer.close
set rsAnswer = nothing
%>

Select all Open in new window

@chaau
In this post here http:Q_28646962.html#a40698543
I show that I added the count into the main SQL Statement, so the separate SQL statement has been removed.

OK, updated information
I have removed a LOT of stuff that was not needed.
The site was designed about 3 years ago, and I am updating it with some new structure as well as a new layout.

When loading a page that has up to 10 records, it loads pretty quick.
Loading a page with more, say over 20 records, then it bogs down.
So, I am thinking that maybe Scott was onto something, about it being the environment that I am working on.
I am going to finish fixing the site to work with Mobile devices, and once I am done with that, i am going to upload to the Live Server, and do a Beta Run on it, and make sure that everything loads and runs like it is supposed to.

I will post back in a few days, with my findings, on the Live Server.
Have a good one all.
Wayne

Scott,

The author is using SQL Server 2005.  The following code:

INSERT INTO MedAnswers 
    ([MedID])
VALUES
    ('id1'),
    ('id2'),
    ('id2'),
    ('id2'),
    ('id1'),
    ('id3'),
     ('id1'),
    ('id2'),
    ('id2'),
    ('id2'),
    ('id1'),
    ('id3')
;

Select all Open in new window

Has to be re-written as multiple INSERT statements or a single INSERT statement with a UNION ALL SELECT statement.

Hey Anthony.
yea, I found that one out, so I just inserted a lot of ones into the table column, and ran with it that way.

Thank you Anthony.   The main thing I was trying to do is demonstrate a different approach the double looping from the original question post.

Starting with:
SELECT medid WHERE MedID=? ORDER BY MedAnswers.MedID  

Then loop through the above recordset in asp (the FROM should be MedAnswers)

SELECT COUNT(MAID) AS ctCom FROM MedAnswers GROUP BY MedID HAVING MedID = ?

The MedID is from the first recordset.  

I think this means he is hitting the entire database, then finding the count for each row.  If there are 3 MedID's and 1000 rows of data, that means he is making 1000 loops and each loop recounting.

If all he wants to achieve is something like below, that should be done in one sql.  

| Row | MedID | Count |
|-----|-------|-------|
|   1 |   id1 |   104 |
|   2 |   id2 |    52 |
|   3 |   id3 |   157 |
        

Select all Open in new window

SELECT ROW_NUMBER() OVER(ORDER BY [MedID] ASC) AS Row,
    [MedID], 
    COUNT([MedID]) as Count 
    FROM MedAnswers 
    GROUP BY [MedID] 
    ORDER BY [MedID] ASC  

Select all Open in new window

My guess is there are some key items being left out and possibly some indexing that will help among other things.

@Scott
I read your last post, and got to looking in on Indexing, and watched a video on the topic.
https://www.youtube.com/watch?v=wAhr91FBlx4

She demonstrated 3 types of indexes used in coding, and I chose the one that forces SQL Server to use the index that I created.
So, I changed a few more things in my ASP SQL Coding, by removing all un-needed Tables and Rows from Queries, that did not need them. (not really sure why I create the sql the way that I did in the first place, a lot of stuff that was not needed)
I then created the Indexes (Non-Unique, Non-Clustered)
For 3 tables, as there are 3 queries in the center of the main LOOP.
I create the indexing for ONLY the records that was needed for each of the tables. As shown in the example code below, its index, only has the columns that are actually in the statement, plus the identifier ID as well.

This is one of the queries, within the loop that uses the index within the query.

sqlchCom.commandtext="SELECT AskID, MedID FROM MedAnswers as fs with (index=kb_MedAnswers_IDs) WHERE  MedID = ? AND AskID = ?"

Select all Open in new window

However, this still did not change the execution time.
It still took the same amount of time to load all records to the webpage, as it did previously.
This is loading 53 records, takes 12 seconds to load the page.

Any ideas?
Thanks for the bringing up of the Indexing, I have heard about them, however, I never messed with them, until now.

>not really sure why I create the sql the way that I did in the first place, a lot of stuff that was not needed

That's called learning. Each day you hopefully get a little bit smarter than yesterday. I look back at things I used to do and wish I had the time to change to how I do things now all the time.  

I was on your site the other week and noticed it was slow and a bit laggy.  There may be another issue not directly related?  If you render the page, copy and paste the html, then send that to the same server as an asp page, does it run a lot faster?

Can you paste the rendered code here or on your own try pasting it to jsbin.com and see how it runs compared to your own server.

If it runs fast as plan html then it is probably something with your sql server set up.  While your run the asp page, look at your memory and cpu usage, is it maxed out?  What kind of memory does sql server have?

I tried the copy and paste of the HTML from one of the loaded pages, and it loaded quick and fast.
(I think that I posted that information somewhere above)
Now, I have tested the page on my hosting providers servers as well, and it is slow and sluggish just like it is on my testing server and my laptop.
So, it has to be something.

When I run JUST the Loop, with NO SQL Statements in the middle of it.
It loads super fast, like it is supposed to.
But once I put in just 1 of the 3 statements in the middle, it slows everything down to a crawl.
Manly because it is looping through and checking every record.
But I never remember having this much of a lag before, in any of the other sites that I have designed.

And you was talking about being on one of my sites, and it being slow.
You must be talking about www.cffcs.com
See, the issue with the slowness, is that I am not 100% sure, that it is not part cause being my Hosting Provider.
It is on a shared server, so it is battling for memory with other sites and crap like that.
I was going to get a dedicated server with my hosting provider, but found out that they give only 500mb worth of memory. WHAT?? yep, so.
I want to host inhouse again so bad I can taste it.
But until I can get a better location with better internet, I am stuck where I am.

Just ran another test with the saved html.
It loaded the page with 53 records loaded, and it does it before you can blink.
About a second.

@Scott.
I created a demo, as we are really getting nowhere, unless you see the code first.
Please read the Readme.txt file to know what to do.
www.cffcs.com/test/EE/SpeedUp.zip
Thanks for all your help.

Wayne

Wayne, I think you said if you run the query in sql manager studio it runs fast.  What about trying to set up a stored proc or a view and that way you are running the query in sql server instead of the web server.

A long time ago I used to have many shared hosting accounts at what used to be called CrystalTech and now NewTek or thesba.   http://webservices.thesba.com/asp-net-web-hosting/  They used to have a set up where they had several hundred or more accounts per sql server and your shared webserver was separate.  Then they moved to a cloud set up for their shared sql server and I noticed things really slowed down.  A lot of it was probably my own lack of knowledge back then but it was still slow and I noticed things slowed down probably because they had more control of the allocated memory.

I tried out going to windows vps at liquidweb.com and at the time the 2 gigs of memory was the max.  I ran into issues using sql server because I had a handful off accounts on that service and kept maxing out the memory.  I went to a dedicated with 4 gigs and worked perfectly and I slowly moved people from newtek shared hosting to the dedicated server.  Now I have the xeon processor with 8gigs of ram with the web edition for sql server.   This is really the way to go. I wouldn't try hosting yourself.

I do think that traditional hosting is going to be less important and my next evolution for my clients is going to get out of a traditional hosting environment and move 100% to the cloud using the likes of azure, parse, amazon, app engine etc.  

On amazon, you can set up a traditional style hosting service and get a year for free.  That is probably a good deal and they also have an app platform as well to compete with parse, azure and Google's App engine.   I would suggest testing out amazon and azure.  One thing you can test out is keeping your webserver as is and using azure only as a db server and that will only cost $5/month https://azure.microsoft.com/en-us/pricing/details/sql-database/ 

You can test either for free, it is worth the effort because you really only need the horsepower for sql server and not for the www.

Thanks for the great information on the servers, I will look in on that.
However, until then, could you look at the project code, and put together a
stored proc or a view
Within that supplied database, with the code implemented within the main.asp page, to access everything.

If i can find a faster way of doing this, that is less resource and memory used, then I will go that route.
Which means a lot of work will have to be re-done on the site, but will get me up-to-speed for the next 2 projects that are getting under-way sometime mid-summer.

Wayne, here it is running on my own server.  Note that I udpated the acn.asp file to only one line and commented out your eeConn.close and eeConn.nothing

You can see as is works well mypadas.com/ee/wayne/main.asp.  On your own add the ?id=48

I am going to rewrite this for you, but for now you can see it works fast.  Try it on my server, then try it on yours with the modifications I made so far to both the ACN file and the Main.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>

<!--#include file="ACN.asp"-->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<%

' ACN.asp only has this one line below
'eeConn = "Provider=SQLOLEDB; datasource=localhost; Database=db_name; Uid=username; Pwd=password"


theMedID = request.QueryString("ID")
Set sqlAnswer = Server.CreateObject("ADODB.Command")
sqlAnswer.ActiveConnection=eeConn
sqlAnswer.Prepared = true
sqlAnswer.commandtext="SELECT row_number() over (order by Answers.MAID) as ctRows, Users.id, Users.Gender, Users.Username, Answers.CorAns, Answers.MyPost, Answers.MAID, Answers.MedID FROM Users INNER JOIN Answers ON Users.id = Answers.id WHERE Answers.MedID=? and not Answers.MyPost='==>New Post<==' ORDER BY Answers.MedID"
sqlAnswer.Parameters.Append sqlAnswer.CreateParameter("@MedID", adInteger, adParamInput,, theMedID) 
set rsAnswer = sqlAnswer.execute
do while not rsAnswer.eof


    if not rsAnswer.eof then
        ctRows = rsAnswer("ctRows")
        Username = rsAnswer("Username")
        bigString = rsAnswer("MyPost")
        id = rsAnswer("id")
        theMedID = rsAnswer("MedID")
        strMaid = rsAnswer("Maid")

        Set sqlchCom = Server.CreateObject("ADODB.Command")
        sqlchCom.ActiveConnection=eeConn
        sqlchCom.Prepared = true
        sqlchCom.commandtext="SELECT ID, MedID FROM Answers WHERE  MedID = ? AND ID = ?"
        sqlchCom.Parameters.Append sqlchCom.CreateParameter("@MedID", adInteger, adParamInput, , theMedID)
        sqlchCom.Parameters.Append sqlchCom.CreateParameter("@AskID", adInteger, adParamInput, , id)
        set rschCom = sqlchCom.execute


%>
        <ul>
            <li><span style="font-size:10pt; color:#666;"><%=ctRows%> [Post ID# <%=strMaid%>]</span></li>
            <li style="padding-bottom:10px; padding:5px;"><p id="Post"><%=bigString%></p></li>
        </ul>
<%
    end if
rsAnswer.movenext
loop
rsAnswer.close
set rsAnswer = nothing

'below does not need to be closed
'eeConn.close
'set eeConn = nothing
%>
</body>
</html>

Select all Open in new window

This bit needs to be removed as it is not used.  If you do use it, remember to close it.

Set sqlchCom = Server.CreateObject("ADODB.Command")
        sqlchCom.ActiveConnection=eeConn
        sqlchCom.Prepared = true
        sqlchCom.commandtext="SELECT ID, MedID FROM Answers WHERE  MedID = ? AND ID = ?"
        sqlchCom.Parameters.Append sqlchCom.CreateParameter("@MedID", adInteger, adParamInput, , theMedID)
        sqlchCom.Parameters.Append sqlchCom.CreateParameter("@AskID", adInteger, adParamInput, , id)
        set rschCom = sqlchCom.execute

Select all Open in new window

Sorry, I got side tracked.

mypadas.com/ee/wayne/main2.asp  same thing, add ?id=48 to the url

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include virtual="/ee/wayne/ACN.asp"-->
<%
theMedID =  request.QueryString("ID")

Set sqlAnswer = Server.CreateObject ("ADODB.Command")
sqlAnswer.ActiveConnection = eeConn
sqlAnswer.CommandText = "SELECT row_number() over (order by Answers.MAID) as ctRows, Users.id, Users.Gender, Users.Username, Answers.CorAns, Answers.MyPost, Answers.MAID, Answers.MedID FROM Users INNER JOIN Answers ON Users.id = Answers.id WHERE Answers.MedID=? and not Answers.MyPost='==>New Post<==' ORDER BY Answers.MedID"
sqlAnswer.Prepared = true
sqlAnswer.Parameters.Append sqlAnswer.CreateParameter("@MedID", 5, 1, -1, theMedID)
Set rsAnswer = sqlAnswer.Execute

if not rsAnswer.eof then
    arrAnswer = rsAnswer.getrows()
end if
rsAnswer.close
set rsAnswer = nothing

if isArray(arrAnswer) then

        For r = LBound(arrAnswer, 2) To UBound(arrAnswer, 2)
            ctRows 		    = arrAnswer(0, r)
            id 	            = arrAnswer(1, r)
            UsersGender 	= arrAnswer(2, r)
            Username        = arrAnswer(3, r)
            AnswersCorAns   = arrAnswer(4, r)
            bigString       = arrAnswer(5, r)
            strMaid         = arrAnswer(6, r)
            theMedID        = arrAnswer(7, r)

           ' you don't have to set variable names like this and can instead just use arrAnswer(7, r)
           ' I like this method to help keep track of what variable names I am using

            theHTML=""
            theHTML=theHTML&    "<ul>"
            theHTML=theHTML&        "<li><span style='font-size:10pt; color:#666;'>"&ctRows&" [Post ID# "&strMaid&"]</span></li>"
            theHTML=theHTML&        "<li style='padding-bottom:10px; padding:5px;'><p id='Post'>"&bigString&"</p></li>"
            theHTML=theHTML&    "</ul>"

            response.write theHTML ' only one response.write
        next


end if


%>

Select all Open in new window

OK, I like that method.
except that I would rather have the html code shown to page, during the design phase.
I have never been a big fan of wrapping the html like what you have demonstrated here.
As sometimes my pages are so huge, that wrapping them like that, is a nightmare when editing and big tracking.
Did you do anything in the database?

And as for the closing, yes, I was just throwing together the demo quickly to get it out to you, that i forgot to close it.
everything is closed in the actual site.

OK, to follow up on everything that I have tried so far, that you have Posted Scott.

#1: running the code supplied on http:#a40719220, loaded in 3 seconds

#2: The code supplied here, http:#40719222, that you stated to remove. This was added to show the lag, in the real project, it is being used within the project, so it cannot be removed.

#3: Running the code http:#40719249, loads in less than a second (My original runs the same speed without the above #2 code in it) So this was not a big change in the speed.

#4: http:#40719780, This the same as #3, loads as fast as my original without #2 in the code.
I am working right now, in implementing this (#4) into my site (Original created a backup of course) To see if I can see a change in the speed of the page load.

I will post back by the end of the day or tomorrow with my findings.
However, I was under the impression, that using indexing would help to really speed things up, so that is something that I am going to look into doing as well.

As for the built up some extra code that could be removed.
Are you referring to the code that is in this site?
(OR)
Are you referring to the way that I code in general?

OK.
Scott, you rock my friend.
Using your method, the page loads 53 records on the actual site, at about 1 second or less.
ALSO!!!!!!!!!
I found out that every time that I would load a page in Google Chrome, it was loading all sorts of crap in the background.
Stuff like Add-ons and no telling what else.
So I opened an Incognito window, and tested that way, and it loaded the page in less than a second.
So I tested the original code in Incognito, to make SURE that it was not chrome add-ons that was causing the issue, and it was not.
The original still took about 7 seconds to load the page, which was better than the original time.

So.
Your code works awesome.

One more question then I will close this one, as I am going to start using this code instead of the way I was before.
I like it.

My question is:

The code that you provided here http:l#a40719780
Has it in a loop.
How would it be written just to display records that are not going to loop?

With 50 some rows of data, some of these things are not going to be noticeable.  Some of the issues could be your network.  main.asp is without getrows. main2.asp is getrows with multiple response.writes and main3.asp is getrows with one response.write.  The main3 loads the fastest with about 73 to 80ms where a good portion of the time is dns.  main.asp takes 100 to 180ms.  I tested with http://tools.pingdom.com/fpt/#!/cOK2gn/www.cffcs.com and chose the server from NY.

Yea, cffcs.com needs to be re-designed from the ground-up.
I re-designed it May 02, 2013
So it is time for an update.
However, I am thinking a lot of the issues with the lag in loading, could be the provider.
As when the site starts to load, it hits the default.asp page, of which does a response.redirect to the Main.asp?Loc=Main
And it takes it forever for it to do that simple task.
I do not know, it is hard to say exactly what the issue is.
Until I can start testing locally, but from outside of the network, of which I am hoping this coming up week, they will be able to upgrade my bandwidth with my ISP. If that happens, then I will be able to start doing some in-house test from outside of the network.

Thanks for the ping site, that will come in handy.
One more thing.

The code that you provided here http:l#a40719780
Has it in a loop.
How would it be written just to display records that are not going to loop?

It's just using variables.

fontcolor = "red"
for x = 1 to 10
        y = x + 5
       response.write x&"+5="&<span style='color:"&fontcolor&"'>&y&""&fontcolor&"</span><hr>"
next

Select all Open in new window

Also, remember that the variables in the loop persist.  

 For r = LBound(arrAnswer, 2) To UBound(arrAnswer, 2)
            ctRows 		    = arrAnswer(0, r)
            id 	            = arrAnswer(1, r)
            UsersGender 	= arrAnswer(2, r)
            Username        = arrAnswer(3, r)
            AnswersCorAns   = arrAnswer(4, r)
            bigString       = arrAnswer(5, r)
            strMaid         = arrAnswer(6, r)
            theMedID        = arrAnswer(7, r)
next

response.write Username  ' this will be the value from the last row of data

Select all Open in new window

One of the advantages of putting your recordset to an array is you can run it over and over again without hitting the database.   Below runs the for/next multiple times but you only hit the db once.  I still wouldn't put 1000 rows into the array at one time though.

 
For r = LBound(arrAnswer, 2) To UBound(arrAnswer, 2)
            ctRows 		    = arrAnswer(0, r)
            id 	            = arrAnswer(1, r)
            UsersGender 	= arrAnswer(2, r)
            Username        = arrAnswer(3, r)
            AnswersCorAns   = arrAnswer(4, r)
            bigString       = arrAnswer(5, r)
            strMaid         = arrAnswer(6, r)
            theMedID        = arrAnswer(7, r)

          response.write Username&","
next

response.write "<br>"&Username  ' this will be the value from the last row of data
response.write "<hr>"
For r = LBound(arrAnswer, 2) To UBound(arrAnswer, 2)
            ctRows 		    = arrAnswer(0, r)
            id 	            = arrAnswer(1, r)
            UsersGender 	= arrAnswer(2, r)
            Username        = arrAnswer(3, r)
            AnswersCorAns   = arrAnswer(4, r)
            bigString       = arrAnswer(5, r)
            strMaid         = arrAnswer(6, r)
            theMedID        = arrAnswer(7, r)

          response.write Username&","
next

response.write "<br>"&Username  ' this will be the value from the last row of data
response.write "<hr>"
For r = LBound(arrAnswer, 2) To UBound(arrAnswer, 2)
            ctRows 		    = arrAnswer(0, r)
            id 	            = arrAnswer(1, r)
            UsersGender 	= arrAnswer(2, r)
            Username        = arrAnswer(3, r)
            AnswersCorAns   = arrAnswer(4, r)
            bigString       = arrAnswer(5, r)
            strMaid         = arrAnswer(6, r)
            theMedID        = arrAnswer(7, r)

          response.write Username&","
next

response.write "<br>"&Username  ' this will be the value from the last row of data
response.write "<hr>"
For r = LBound(arrAnswer, 2) To UBound(arrAnswer, 2)
            ctRows 		    = arrAnswer(0, r)
            id 	            = arrAnswer(1, r)
            UsersGender 	= arrAnswer(2, r)
            Username        = arrAnswer(3, r)
            AnswersCorAns   = arrAnswer(4, r)
            bigString       = arrAnswer(5, r)
            strMaid         = arrAnswer(6, r)
            theMedID        = arrAnswer(7, r)

          response.write Username&","
next

response.write "<br>"&Username  ' this will be the value from the last row of data
response.write "<hr>"

Select all Open in new window

This did exactly what I was needing, it sped of my page by 10x.
that is a HUGE difference in page load.

Thank you Scott!
You rock my friend.
Have an awesome rest of the weekend!

Wayne

>I found out that every time that I would load a page in Google Chrome, it was loading all sorts of crap in the background.

I would have to say out of all the browsers (I don't even count ie below 10 anymore), chrome seems to be the one that has the most issues.

Chrome is usually the one that I use for design, as if works in Chrome
In MOST cases, it will look good in other browsers.
I used to use IE exclusively, and I found out that all my pages would break in FF, Chrome, and other browsers.
So I went over to Chrome, and have been using it ever since.

About the site
I have found out one thing.
Going onto the site, the page loads in like a second of less. (For non-members)
For members that are logged in, the page loads really slow.
So, there is a LOT of stuff going on that members get that non-members do not get.

So, I am going to track down which one is causing this slow loading issue.
And see if I can speed things up, or remove it, if possible, if not needed.

Have a good one Scott

My main browser is chrome.  It just happens to be the one that gives me trouble with other users if there is trouble at all.

Check out my article on user log ins without using session variables. http://www.experts-exchange.com/Programming/Languages/Scripting/ASP/A_18259-User-Log-In-Using-A-Token.html

Hey Scott.
If you get a chance, could you check this out please. http:Q_28660475.html

Have a good one.
Carrzkiss

Scott.
What does the "2" stand for in this?

For r = LBound(arrAnswer, 2) To UBound(arrAnswer, 2)

It's the 2nd dimension.  http://www.w3schools.com/vbscript/func_lbound.asp