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

[BG12424]

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&quot
Set cmd = Server.CreateObject("ADODB.Command&quot
Set rs = Server.CreateObject("ADODB.Recordset&quot

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

 

[mwolf00]

Brian, Go back to you code when you said &quot;Yeah, it's pretty quick, but I am losing my row color formatting when using this routine.&quot; and look at the HTML that is output. Can you determine where the formatting is messing up? It should work just fine. Tweak it a bit and see if you can figure out what is happening to your formatting.... -----------------------------------------------------------------
Be nice. It's only doing what you tell it to do.
mikewolf@tst-us.com

 

[BG12424]

I know what is happening with the formatting in that post. It is not defining buildCOLS in my subroutine, because I replaced the buildCOLS with the strOut.Add method. I suppose I could do something like:

strOut.Add(makeTR(makeTD(rs(&quot;ID1&quot,&quot;bodyText&quot;,&quot;CENTER&quot & endline & makeTD(rs(&quot;OldName1&quot,&quot;bodyText&quot;,&quot;LEFT&quot & endline & makeTD(rs(&quot;NewName2&quot,&quot;bodyText&quot;,&quot;CENTER&quot & endline & etc.....)

Could you please tell me again how this is faster than my last post? Thank you, you've been a big help.

regards,
Brian

 

[mwolf00]

When the processor is trying to a large variable, the processor waits until the process is complete before going to the next line of code. When you create the class, the process of adding to the long variable is done separately from the main processing, which uses your system's resources more effectively. When I found this answer, it honestly sped up my page by over 100 times! Now I'll never go back.

Like I said in my first post, I actually have SQL Server spit out code with the HTML already written which makes it even faster..

SELECT '<tr><td>' + field1 + '</td><td>' + field2 + '</td></tr>' FROM myTable -----------------------------------------------------------------
Be nice. It's only doing what you tell it to do.
mikewolf@tst-us.com

 

[BG12424]

Thanks, the only downfall I see with you storing the html in the database is that you can separate the content from the presentation. On some systems, you need to be flexible enough to do this.

regards,
Brian

 

[mwolf00]

So, did it work? Is you page loading a lot faster? -----------------------------------------------------------------
Be nice. It's only doing what you tell it to do.
mikewolf@tst-us.com

 

[BG12424]

I did use it and tested and it didn't seem to be a vast improvement compared to what I had already. They both ran about the same amount of time. Thanks for the follow up. Why could this be? regards,
Brian

 

[mwolf00]

Have you tried to have your asp page tell you how long each loop is taking? Add a response.write &quot;<br>&quot; & timer

Timer returns the number fo seconds since midnight.
You could also do something like:

dim startTime
startTime = timer

execute your query/before looking through the recordset write:
&quot;<br>Query Execution - &quot; & timer - startTime & &quot; seconds.&quot;

And use the same format though your code. See if every loop is taking 30 seconds or if maybe they take longer after your output is getting large... -----------------------------------------------------------------
DIM bulb
SET bulb = NEW brain

mikewolf@tst-us.com

 

[BG12424]

Thanks, I will take a look at that. Here is my code before I do that though.

sub FORMAT_ADDR1_NORMALIZATION_classref(rs,proc)

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

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

'build the table header row
strOut.Add(&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; & _
makeTR(makeTH(&quot;UPDATE&quot & endline & _
makeTH(&quot;Old Name&quot & endline & _
makeTH(&quot;New Name&quot & endline & _
makeTH(&quot;VId&quot & endline & _
makeTH(&quot;FedId&quot & endline & _
makeTH(&quot;Addr1&quot & endline & _
makeTH(&quot;City&quot & endline & _
makeTH(&quot;State&quot & endline & _
makeTH(&quot;Zip&quot & endline,0,1))

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
strOut.Add(makeTR(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 & _
makeTD(rs(&quot;VENDOR_NAME&quot,&quot;bodyText&quot;,&quot;LEFT&quot & endline & _
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 & _
makeTD(rs(&quot;NEW_VENDOR_ID&quot,&quot;bodyText&quot;,&quot;CENTER&quot & endline & _
makeTD(rs(&quot;NEW_FEDERAL_ID&quot,&quot;bodyText&quot;,&quot;CENTER&quot & endline & _
makeTD(rs(&quot;NEW_REMITTANCE_ADDRESS_1&quot,&quot;bodyText&quot;,&quot;CENTER&quot & endline & _
makeTD(rs(&quot;REMITTANCE_CITY&quot,&quot;bodyText&quot;,&quot;CENTER&quot & endline & _
makeTD(rs(&quot;REMITTANCE_STATE&quot,&quot;bodyText&quot;,&quot;CENTER&quot & endline & _
makeTD(rs(&quot;NEW_REMITTANCE_ZIP&quot,&quot;bodyText&quot;,&quot;CENTER&quot & endline,prev,curr))
i=i+1
rs.movenext
loop
Response.Write(strOut.value)

end sub regards,
Brian

 

[Zequel]

The problem is concatention, this is why MS created a new class called StringBuilder in .net. Unlike a integer value whereas when you change it you merely change the value of the memory address where it is stored, a string is an object and is IMMUTABLE. If you change the object's value by contcatenating(sic?) it, the original object is destroyed and a new one is created. This is SLOW. In your example, it appears you are rebuilding a huge string. This is much more intensive than any other operation. My advice, keep it simple, cut down the number of function calls, spit out the HTML ASAP and, if you can, and avoid contecatenation as much as possible (especially 20,000x as in your example).

 

[BG12424]

Thanks, I am at the point where I am concatenating the columns, then spitting out the row after the columns are all together. I appreciate your feedback. regards,
Brian