Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Stored procedure fast on backend, but slow to display on web page. 2

Status
Not open for further replies.
BG12424

BG12424

Programmer
Jun 4, 2002
717
US
I have a stored procedure that I am executing from within an ASP page. The number of results being returned from the database are 20,000 rows. It takes about 1 second to run in Query Analyzer, but takes a bit over 30 minutes to display those results to an ASP page front-end. For approximately 2000 records, it takes about 2 to 3 minutes to display in ASP.

I need help determining how I can improve the performance of my web pages.

Here is my function which calls the stored procedures and my formatting routine, which is used for re-use.

function BI_Addr1_Norm(proc,retval)

dim cmd, rs, dbConn

Set dbConn = Server.CreateObject("ADODB.Connection")
Set cmd = Server.CreateObject("ADODB.Command")
Set rs = Server.CreateObject("ADODB.Recordset")

dbConn.Open strConn

with cmd
.ActiveConnection = dbConn
.CommandText = proc
.CommandType = adCmdStoredProc
.CommandTimeout = 7200
.Parameters.Append .CreateParameter("Return", adInteger,adParamReturnValue)
Set rs = .Execute
end with

if rs.State = adStateOpen then
FORMAT_ADDR1_NORMALIZATION rs,proc
CloseRS(rs)
else
retval = cmd(0)
end if

CloseDBConn(dbConn)

end function

sub FlushResponse(row)
if row mod 2 = 0 then
Response.Flush
end if
end sub

sub FORMAT_ADDR1_NORMALIZATION(rs,proc)

'THE FOLLOWING FIELDS ARE RETRIEVED:
'EPS_ID,
'Vendor_Name,
'New_Vendor_Name,
'New_Vendor_Id,
'New_Federal_Id,
'New_Remittance_Address_1,
'Remittance_City,
'Remittance_State,
'New_Remittance_Zip

dim i,buildROWS,buildCOLS,prev,curr,desc

select case proc
case "BI_Addr1_Norm_X"
desc = "(CHECK " & CHR(34) & "X" & CHR(34) & " ADDR1)"
case "BI_Addr1_Norm_No_X"
desc = "(NO CHECK " & CHR(34) & "X" & CHR(34) & " ADDR1)"
case "BI_Addr1_Norm_All"
desc = "(BOTH CHECK " & CHR(34) & "X" & CHR(34) & " AND UNCHECK ADDR1)"
end select

'build the table header row
buildCOLS = &quot;<TR><td colspan=9 align=middle class=bodyText><b><font size=3>Remittance Address 1 Normalization Table<br><center><font size=2>&quot; & desc & &quot;</font></center></font></b></td></TR>&quot;
buildCOLS = buildCOLS & makeTH(&quot;UPDATE&quot;) & endline
buildCOLS = buildCOLS & makeTH(&quot;Old Name&quot;) & endline
buildCOLS = buildCOLS & makeTH(&quot;New Name&quot;) & endline
buildCOLS = buildCOLS & makeTH(&quot;VId&quot;) & endline
buildCOLS = buildCOLS & makeTH(&quot;FedId&quot;) & endline
buildCOLS = buildCOLS & makeTH(&quot;Addr1&quot;) & endline
buildCOLS = buildCOLS & makeTH(&quot;City&quot;) & endline
buildCOLS = buildCOLS & makeTH(&quot;State&quot;) & endline
buildCOLS = buildCOLS & makeTH(&quot;Zip&quot;) & endline
buildROWS = makeTR(buildCOLS,0,1) & endline
buildCOLS = &quot;&quot; 'clear out header values

i=0
do while not rs.EOF
if i mod 2 = 0 then
prev = rs(&quot;NEW_REMITTANCE_ADDRESS_1&quot;)
else
curr = rs(&quot;NEW_REMITTANCE_ADDRESS_1&quot;)
end if
buildCOLS = buildCOLS & makeTD(&quot;<INPUT TYPE=CHECKBOX NAME=CKBOX VALUE=&quot; & rs(&quot;NEW_REMITTANCE_ADDRESS_1&quot;) & &quot;~&quot; & rs(&quot;EPS_ID&quot;) & &quot;>&quot;,&quot;bodyText&quot;,&quot;CENTER&quot;) & endline
buildCOLS = buildCOLS & makeTD(rs(&quot;VENDOR_NAME&quot;),&quot;bodyText&quot;,&quot;LEFT&quot;) & endline
buildCOLS = buildCOLS & makeTD(&quot;<INPUT TYPE=TEXT NAME=TXT&quot; & rs(&quot;EPS_ID&quot;) & &quot; VALUE=&quot; & qt & rs(&quot;NEW_VENDOR_NAME&quot;) & qt & &quot;>&quot;,&quot;bodyText&quot;,&quot;LEFT&quot;) & endline
buildCOLS = buildCOLS & makeTD(rs(&quot;NEW_VENDOR_ID&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline
buildCOLS = buildCOLS & makeTD(rs(&quot;NEW_FEDERAL_ID&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline
buildCOLS = buildCOLS & makeTD(rs(&quot;NEW_REMITTANCE_ADDRESS_1&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline
buildCOLS = buildCOLS & makeTD(rs(&quot;REMITTANCE_CITY&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline
buildCOLS = buildCOLS & makeTD(rs(&quot;REMITTANCE_STATE&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline
buildCOLS = buildCOLS & makeTD(rs(&quot;NEW_REMITTANCE_ZIP&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline
buildROWS = buildROWS & makeTR(buildCOLS,prev,curr) & endline 'build the row set
buildCOLS = &quot;&quot; 'clear out column values and start over
i=i+1
FlushResponse(i)
rs.movenext
loop
Response.Write(makeTABLE(buildROWS,&quot;#FFFFFF&quot;,0,2,1)) & endline

end sub regards,
Brian
 
Sort by date Sort by votes
I used getrows for dataaccess. click on classic asp and search for getrows. It returns data in a array. we saw a huge increase in speed. Pages went from taking 1-2 mins to a few seconds.

Also avoid string concatination, response.write each piece is faster.

hth
 
Upvote 0 Downvote
Doesn't getrows() only get 2 fields at a time for all records? regards,
Brian
 
Upvote 0 Downvote
This is aload of recordsets for ADO. The best thing to do is to implement paging. By doing so you could limit the number of the recordsets to display. The paging could be done on the front end or on the SQL server( by a stored proc). Have a look at &quot; &quot; and &quot; &quot;. There are some excellent articles on the subject.

Good Luck!
 
Upvote 0 Downvote
ayladilara,

It sounds like you think this is common of ASP. Is this true? Is there any settings that I can verify to make this paint to the web page any faster? thanks

brian regards,
Brian
 
Upvote 0 Downvote
Is there a setting within IIS, SQL Server, Command Object, Recordset object, or something where we can make this process run much faster? Thanks

Brian regards,
Brian
 
Upvote 0 Downvote
Ahh! Now, you are asking a very qomprehensive question , which is optimization(maximization the performance). The answer to that problem involves a lot of details from opening the recordset properly to designing and maintaining database etc.Well, I am afraid you have to do some research on it. Sorry...I have to go to bed now...Thanks.

Good Luck.
 
Upvote 0 Downvote
I was using the getRows() method to return a resultset as prescribed above, and that was working great when viewing plain text, but as soon as I added HTML formatting to the data, ASP was taking an extra long time to render the page, resulting in my dllhost.exe process to spike to 100% utilization. Any ideas on this? regards,
Brian
 
Upvote 0 Downvote
It sounds like I need to remove the makeTABLE routine and response.write the makeTR. I see, I am concatenating until it's all done, then writing it. I need to write it as I go. I think this is part of the problem. Thanks for the eyes on my code above. regards,
Brian
 
Upvote 0 Downvote
I had a similar problem. I was concatenting an HTML string to output. The length of the string was over 1 million characters. Addin 50 characters to a string that is already over 1 million long takes a very long time - you drag the server down exponentially as you add to the string. The solution: create a class to handle the string separately (parallel process = very fast) I got a page that took over 15 minutes to display down to about 3 seconds....
<%
Class strCat
Private IntCntr
Private strArray()
Private intLength
Public Property Get Length
Length = intLength
End Property
Public Property Let Length( ByVal intLen)
intLength = intLen
IntCntr = 0
Redim strArray(1)
strArray(0) = &quot;&quot;
Redim strArray(intLength)
End Property
Public Property Get Value
Value = Join(strArray,&quot;&quot;)
End Property
Private Sub Class_Initialize()
IntCntr = 0
Length = 100000
End Sub
Public function Add( strToAdd)
strArray(IntCntr) = strToAdd
IntCntr = IntCntr + 1
End function
End Class

dim strOut
set strOut = new strCat
My stored procedure returns HTML code
do while not objrs.eof
if not isNull(objrs(&quot;detail&quot;)) then strOut.add(objrs(&quot;detail&quot;))
if not isNull(objrs(&quot;value&quot;)) then strOut.add(objrs(&quot;value&quot;)&vbcrlf) else strOut.add(vbcrlf)
objrs.movenext
loop

%>

<%=strOut.value%>
-----------------------------------------------------------------
[pc] Be nice. It's only doing what you tell it to do.
mikewolf@tst-us.com
 
Upvote 0 Downvote
If I tried your method mwolf00, would I add all my html and data from database to this add method of the class and not write to the page until all data is retrieved, or can I write out after each row is retrieved, then clear the array and start over? If I can write out as I go from your array that is being created and added to, how would I clear it out and start over for each row? Thanks regards,
Brian
 
Upvote 0 Downvote
It's always faster to use fewer response.write statements. Just make one. Everywhere where you'd normally write:
response.write &quot;<tr><td>&quot; & objrs(&quot;myField&quot;) & &quot;</td>&quot;

Now write
strOut.add(&quot;<tr><td>&quot; & objrs(&quot;myField&quot;) & &quot;</td>&quot;)

Or in your case:
strOut.add((makeTABLE(buildROWS,&quot;#FFFFFF&quot;,0,2,1)) & endline) -----------------------------------------------------------------
[pc] Be nice. It's only doing what you tell it to do.
mikewolf@tst-us.com
 
Upvote 0 Downvote
I tried the class above and it seemed to be way slower than writing out after each row is received from the database. See my code below:

The class is the same as what is listed above. I have it in my same asp file as this code below.

sub FORMAT_ADDR2_TO_1_COMPARE(rs,addr)

dim i,prev,curr,strOut
set strOut = new strCat

'build the table header row
strOut.add(makeTH(&quot;1_ID&quot;) & endline)
strOut.add(makeTH(&quot;1_Old Name&quot;) & endline)
strOut.add(makeTH(&quot;1_New Name&quot;) & endline)
strOut.add(makeTH(&quot;1_Addr1&quot;) & endline)
strOut.add(makeTH(&quot;1_Addr2&quot;) & endline)
strOut.add(makeTH(&quot;1_City&quot;) & endline)
strOut.add(makeTH(&quot;1_State&quot;) & endline)
strOut.add(makeTH(&quot;1_Zip&quot;) & endline)
strOut.add(makeTH(&quot;2_UPDATE&quot;) & endline)
strOut.add(makeTH(&quot;2_Old Name&quot;) & endline)
strOut.add(makeTH(&quot;2_New Name&quot;) & endline)
strOut.add(makeTH(&quot;2_ID&quot;) & endline)
strOut.add(makeTH(&quot;2_Addr1&quot;) & endline)
strOut.add(makeTH(&quot;2_Addr2&quot;) & endline)
strOut.add(makeTH(&quot;2_City&quot;) & endline)
strOut.add(makeTH(&quot;2_State&quot;) & endline)
strOut.add(makeTH(&quot;2_Zip&quot;) & endline)
strOut.add(makeTR(buildCOLS,0,1) & endline)
' Response.Write(strOut.value)

i=0
do while not rs.EOF
if i mod 2 = 0 then
prev = rs(&quot;2Addr2&quot;)
else
curr = rs(&quot;2Addr2&quot;)
end if
strOut.add(makeTD(rs(&quot;ID1&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline)
strOut.add(makeTD(rs(&quot;OldName1&quot;),&quot;bodyText&quot;,&quot;LEFT&quot;) & endline)
strOut.add(makeTD(rs(&quot;NewName2&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline)
strOut.add(makeTD(rs(&quot;1Addr1&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline)
strOut.add(makeTD(rs(&quot;1Addr2&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline)
strOut.add(makeTD(rs(&quot;State1&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline)
strOut.add(makeTD(rs(&quot;City1&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline)
strOut.add(makeTD(rs(&quot;Zip1&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline)
strOut.add(makeTD(&quot;<INPUT TYPE=CHECKBOX NAME=CKBOX VALUE=&quot; & rs(&quot;ID2&quot;) & &quot;>&quot;,&quot;bodyText&quot;,&quot;CENTER&quot;) & endline)
strOut.add(makeTD(rs(&quot;OldName2&quot;),&quot;bodyText&quot;,&quot;LEFT&quot;) & endline)
strOut.add(makeTD(&quot;<INPUT TYPE=TEXT NAME=TXT&quot; & rs(&quot;ID2&quot;) & &quot; VALUE=&quot; & qt & rs(&quot;NewName2&quot;) & qt & &quot;>&quot;,&quot;bodyText&quot;,&quot;LEFT&quot;) & endline)
strOut.add(makeTD(rs(&quot;ID2&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline)
strOut.add(makeTD(rs(&quot;2Addr1&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline)
strOut.add(makeTD(rs(&quot;2Addr2&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline)
strOut.add(makeTD(rs(&quot;State2&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline)
strOut.add(makeTD(rs(&quot;City2&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline)
strOut.add(makeTD(rs(&quot;Zip2&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline)
strOut.add(makeTR(buildCOLS,prev,curr) & endline)
Response.Write(strOut.value)
i=i+1
FlushResponse(i)
rs.movenext
loop

end sub regards,
Brian
 
Upvote 0 Downvote
I found my own problem with the performance issue described above in my last post. I was writing out the strOut.value within the loop instead of outside. Yeah, it's pretty quick, but I am losing my row color formatting when using this routine.

Here is my updated code block:

sub FORMAT_ADDR2_TO_1_COMPARE(rs,addr)

dim i,prev,curr,strOut
set strOut = new strCat

'build the table header row
strOut.add(makeTH(&quot;1_ID&quot;) & endline)
strOut.add(makeTH(&quot;1_Old Name&quot;) & endline)
strOut.add(makeTH(&quot;1_New Name&quot;) & endline)
strOut.add(makeTH(&quot;1_Addr1&quot;) & endline)
strOut.add(makeTH(&quot;1_Addr2&quot;) & endline)
strOut.add(makeTH(&quot;1_City&quot;) & endline)
strOut.add(makeTH(&quot;1_State&quot;) & endline)
strOut.add(makeTH(&quot;1_Zip&quot;) & endline)
strOut.add(makeTH(&quot;2_UPDATE&quot;) & endline)
strOut.add(makeTH(&quot;2_Old Name&quot;) & endline)
strOut.add(makeTH(&quot;2_New Name&quot;) & endline)
strOut.add(makeTH(&quot;2_ID&quot;) & endline)
strOut.add(makeTH(&quot;2_Addr1&quot;) & endline)
strOut.add(makeTH(&quot;2_Addr2&quot;) & endline)
strOut.add(makeTH(&quot;2_City&quot;) & endline)
strOut.add(makeTH(&quot;2_State&quot;) & endline)
strOut.add(makeTH(&quot;2_Zip&quot;) & endline)
strOut.add(makeTR(buildCOLS,0,1) & endline)

i=0
do while not rs.EOF
if i mod 2 = 0 then
prev = rs(&quot;2Addr2&quot;)
else
curr = rs(&quot;2Addr2&quot;)
end if
strOut.add(makeTD(rs(&quot;ID1&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline)
strOut.add(makeTD(rs(&quot;OldName1&quot;),&quot;bodyText&quot;,&quot;LEFT&quot;) & endline)
strOut.add(makeTD(rs(&quot;NewName2&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline)
strOut.add(makeTD(rs(&quot;1Addr1&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline)
strOut.add(makeTD(rs(&quot;1Addr2&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline)
strOut.add(makeTD(rs(&quot;State1&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline)
strOut.add(makeTD(rs(&quot;City1&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline)
strOut.add(makeTD(rs(&quot;Zip1&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline)
strOut.add(makeTD(&quot;<INPUT TYPE=CHECKBOX NAME=CKBOX VALUE=&quot; & rs(&quot;ID2&quot;) & &quot;>&quot;,&quot;bodyText&quot;,&quot;CENTER&quot;) & endline)
strOut.add(makeTD(rs(&quot;OldName2&quot;),&quot;bodyText&quot;,&quot;LEFT&quot;) & endline)
strOut.add(makeTD(&quot;<INPUT TYPE=TEXT NAME=TXT&quot; & rs(&quot;ID2&quot;) & &quot; VALUE=&quot; & qt & rs(&quot;NewName2&quot;) & qt & &quot;>&quot;,&quot;bodyText&quot;,&quot;LEFT&quot;) & endline)
strOut.add(makeTD(rs(&quot;ID2&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline)
strOut.add(makeTD(rs(&quot;2Addr1&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline)
strOut.add(makeTD(rs(&quot;2Addr2&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline)
strOut.add(makeTD(rs(&quot;State2&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline)
strOut.add(makeTD(rs(&quot;City2&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline)
strOut.add(makeTD(rs(&quot;Zip2&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline)
strOut.add(makeTR(buildCOLS,prev,curr) & endline)
i=i+1
rs.movenext
loop
Response.Write(strOut.value)

end sub
regards,
Brian
 
Upvote 0 Downvote
Looking at your original post, I was thinking more along the lines....

sub FORMAT_ADDR1_NORMALIZATION(rs,proc)

'THE FOLLOWING FIELDS ARE RETRIEVED:
'EPS_ID,
'Vendor_Name,
'New_Vendor_Name,
'New_Vendor_Id,
'New_Federal_Id,
'New_Remittance_Address_1,
'Remittance_City,
'Remittance_State,
'New_Remittance_Zip

dim i,buildROWS,buildCOLS,prev,curr,desc

select case proc
case &quot;BI_Addr1_Norm_X&quot;
desc = &quot;(CHECK &quot; & CHR(34) & &quot;X&quot; & CHR(34) & &quot; ADDR1)&quot;
case &quot;BI_Addr1_Norm_No_X&quot;
desc = &quot;(NO CHECK &quot; & CHR(34) & &quot;X&quot; & CHR(34) & &quot; ADDR1)&quot;
case &quot;BI_Addr1_Norm_All&quot;
desc = &quot;(BOTH CHECK &quot; & CHR(34) & &quot;X&quot; & CHR(34) & &quot; AND UNCHECK ADDR1)&quot;
end select

'build the table header row
buildCOLS = &quot;<TR><td colspan=9 align=middle class=bodyText><b><font size=3>Remittance Address 1 Normalization Table<br><center><font size=2>&quot; & desc & &quot;</font></center></font></b></td></TR>&quot;
buildCOLS = buildCOLS & makeTH(&quot;UPDATE&quot;) & endline
buildCOLS = buildCOLS & makeTH(&quot;Old Name&quot;) & endline
buildCOLS = buildCOLS & makeTH(&quot;New Name&quot;) & endline
buildCOLS = buildCOLS & makeTH(&quot;VId&quot;) & endline
buildCOLS = buildCOLS & makeTH(&quot;FedId&quot;) & endline
buildCOLS = buildCOLS & makeTH(&quot;Addr1&quot;) & endline
buildCOLS = buildCOLS & makeTH(&quot;City&quot;) & endline
buildCOLS = buildCOLS & makeTH(&quot;State&quot;) & endline
buildCOLS = buildCOLS & makeTH(&quot;Zip&quot;) & endline
buildROWS = makeTR(buildCOLS,0,1) & endline
buildCOLS = &quot;&quot; 'clear out header values

i=0
do while not rs.EOF
if i mod 2 = 0 then
prev = rs(&quot;NEW_REMITTANCE_ADDRESS_1&quot;)
else
curr = rs(&quot;NEW_REMITTANCE_ADDRESS_1&quot;)
end if
buildCOLS = buildCOLS & makeTD(&quot;<INPUT TYPE=CHECKBOX NAME=CKBOX VALUE=&quot; & rs(&quot;NEW_REMITTANCE_ADDRESS_1&quot;) & &quot;~&quot; & rs(&quot;EPS_ID&quot;) & &quot;>&quot;,&quot;bodyText&quot;,&quot;CENTER&quot;) & endline
buildCOLS = buildCOLS & makeTD(rs(&quot;VENDOR_NAME&quot;),&quot;bodyText&quot;,&quot;LEFT&quot;) & endline
buildCOLS = buildCOLS & makeTD(&quot;<INPUT TYPE=TEXT NAME=TXT&quot; & rs(&quot;EPS_ID&quot;) & &quot; VALUE=&quot; & qt & rs(&quot;NEW_VENDOR_NAME&quot;) & qt & &quot;>&quot;,&quot;bodyText&quot;,&quot;LEFT&quot;) & endline
buildCOLS = buildCOLS & makeTD(rs(&quot;NEW_VENDOR_ID&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline
buildCOLS = buildCOLS & makeTD(rs(&quot;NEW_FEDERAL_ID&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline
buildCOLS = buildCOLS & makeTD(rs(&quot;NEW_REMITTANCE_ADDRESS_1&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline
buildCOLS = buildCOLS & makeTD(rs(&quot;REMITTANCE_CITY&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline
buildCOLS = buildCOLS & makeTD(rs(&quot;REMITTANCE_STATE&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline
buildCOLS = buildCOLS & makeTD(rs(&quot;NEW_REMITTANCE_ZIP&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline
buildROWS = buildROWS & makeTR(buildCOLS,prev,curr) & endline 'build the row set
buildCOLS = &quot;&quot; 'clear out column values and start over
i=i+1
rs.movenext
loop
strOut.add(makeTABLE(buildROWS,&quot;#FFFFFF&quot;,0,2,1)) & endline

end sub -----------------------------------------------------------------
[pc] Be nice. It's only doing what you tell it to do.
mikewolf@tst-us.com
 
Upvote 0 Downvote
I tried what you just posted and it still runs very slow. I think the problem is the concatenation of buildCOLS and buildROWS variables until we build up the entire table content, then writing it to the page. This method is time intensive. I had better luck with your routine if I don't have formatting issues involved. In order for my formatting to occur, I need to build up my column list then write the row with the row color. Without this concatenation of my column values, I don't get my color coding. Unless, I know how to re-dimensionalize the array back to nothing while I am looping through my resultset, I will not use the function (but does work great).

So, how can I re-dimension the array during my loop of each row? See the example below, which is similar to the first posting.

sub FORMAT_ADDR2_NORMALIZATION(rs,proc)

dim i,buildCOLS,prev,curr,desc

select case proc
case &quot;BI_Addr2_Norm_X&quot;
desc = &quot;(DUPLICATES - CHECK &quot; & CHR(34) & &quot;X&quot; & CHR(34) & &quot; ADDR2)&quot;
case &quot;BI_Addr2_Norm_No_X&quot;
desc = &quot;(NO DUPLICATES - NO CHECK &quot; & CHR(34) & &quot;X&quot; & CHR(34) & &quot; ADDR2)&quot;
case &quot;BI_Addr2_Norm_All&quot;
desc = &quot;(ALL RECORDS - BOTH CHECK &quot; & CHR(34) & &quot;X&quot; & CHR(34) & &quot; AND UNCHECK ADDR2)&quot;
end select

'build the table header row
buildCOLS = &quot;<TR><td colspan=9 align=middle class=bodyText><b><font size=3>Remittance Address 2 Normalization Table<br><center><font size=2>&quot; & desc & &quot;</font></center></font></b></td></TR>&quot;
buildCOLS = buildCOLS & makeTH(&quot;UPDATE&quot;) & endline
buildCOLS = buildCOLS & makeTH(&quot;Old Name&quot;) & endline
buildCOLS = buildCOLS & makeTH(&quot;New Name&quot;) & endline
buildCOLS = buildCOLS & makeTH(&quot;VId&quot;) & endline
buildCOLS = buildCOLS & makeTH(&quot;FedId&quot;) & endline
buildCOLS = buildCOLS & makeTH(&quot;Addr2&quot;) & endline
buildCOLS = buildCOLS & makeTH(&quot;City&quot;) & endline
buildCOLS = buildCOLS & makeTH(&quot;State&quot;) & endline
buildCOLS = buildCOLS & makeTH(&quot;Zip&quot;) & endline
Response.Write(makeTR(buildCOLS,0,1) & endline)
buildCOLS = &quot;&quot; 'clear out header values

i=0
do while not rs.EOF
if i mod 2 = 0 then
prev = rs(&quot;NEW_REMITTANCE_ADDRESS_2&quot;)
else
curr = rs(&quot;NEW_REMITTANCE_ADDRESS_2&quot;)
end if
buildCOLS = buildCOLS & makeTD(&quot;<INPUT TYPE=CHECKBOX NAME=CKBOX VALUE=&quot; & rs(&quot;NEW_REMITTANCE_ADDRESS_2&quot;) & &quot;~&quot; & rs(&quot;EPS_ID&quot;) & &quot;>&quot;,&quot;bodyText&quot;,&quot;CENTER&quot;) & endline
buildCOLS = buildCOLS & makeTD(rs(&quot;VENDOR_NAME&quot;),&quot;bodyText&quot;,&quot;LEFT&quot;) & endline
buildCOLS = buildCOLS & makeTD(&quot;<INPUT TYPE=TEXT NAME=TXT&quot; & rs(&quot;EPS_ID&quot;) & &quot; VALUE=&quot; & qt & rs(&quot;NEW_VENDOR_NAME&quot;) & qt & &quot;>&quot;,&quot;bodyText&quot;,&quot;LEFT&quot;) & endline
buildCOLS = buildCOLS & makeTD(rs(&quot;NEW_VENDOR_ID&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline
buildCOLS = buildCOLS & makeTD(rs(&quot;NEW_FEDERAL_ID&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline
buildCOLS = buildCOLS & makeTD(rs(&quot;NEW_REMITTANCE_ADDRESS_2&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline
buildCOLS = buildCOLS & makeTD(rs(&quot;REMITTANCE_CITY&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline
buildCOLS = buildCOLS & makeTD(rs(&quot;REMITTANCE_STATE&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline
buildCOLS = buildCOLS & makeTD(rs(&quot;NEW_REMITTANCE_ZIP&quot;),&quot;bodyText&quot;,&quot;CENTER&quot;) & endline
Response.Write(makeTR(buildCOLS,prev,curr) & endline)
buildCOLS = &quot;&quot; 'clear out column values and start over
i=i+1
FlushResponse(i)
rs.movenext
loop

end sub regards,
Brian
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
201
penguinspeaks
penguinspeaks
penguinspeaks
  • Locked
  • Question
READ TEXT FILE BASED ON VARIABLE 1
Replies
2
Views
188
guitarzan
guitarzan
JScannell
  • Locked
  • Question
Border issue with Google Chrome on an Android phone
Replies
0
Views
180
JScannell
JScannell
leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
249
leifoet
leifoet
leifoet
  • Locked
  • Question
First execute an input command and only then proceed with displaying the next record of the table
Replies
0
Views
140
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top