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 = "<TR><td colspan=9 align=middle class=bodyText><b><font size=3>Remittance Address 1 Normalization Table<br><center><font size=2>" & desc & "</font></center></font></b></td></TR>"
buildCOLS = buildCOLS & makeTH("UPDATE" & endline
buildCOLS = buildCOLS & makeTH("Old Name" & endline
buildCOLS = buildCOLS & makeTH("New Name" & endline
buildCOLS = buildCOLS & makeTH("VId" & endline
buildCOLS = buildCOLS & makeTH("FedId" & endline
buildCOLS = buildCOLS & makeTH("Addr1" & endline
buildCOLS = buildCOLS & makeTH("City" & endline
buildCOLS = buildCOLS & makeTH("State" & endline
buildCOLS = buildCOLS & makeTH("Zip" & endline
buildROWS = makeTR(buildCOLS,0,1) & endline
buildCOLS = "" 'clear out header values
i=0
do while not rs.EOF
if i mod 2 = 0 then
prev = rs("NEW_REMITTANCE_ADDRESS_1"
else
curr = rs("NEW_REMITTANCE_ADDRESS_1"
end if
buildCOLS = buildCOLS & makeTD("<INPUT TYPE=CHECKBOX NAME=CKBOX VALUE=" & rs("NEW_REMITTANCE_ADDRESS_1" & "~" & rs("EPS_ID" & ">","bodyText","CENTER" & endline
buildCOLS = buildCOLS & makeTD(rs("VENDOR_NAME","bodyText","LEFT" & endline
buildCOLS = buildCOLS & makeTD("<INPUT TYPE=TEXT NAME=TXT" & rs("EPS_ID" & " VALUE=" & qt & rs("NEW_VENDOR_NAME" & qt & ">","bodyText","LEFT" & endline
buildCOLS = buildCOLS & makeTD(rs("NEW_VENDOR_ID","bodyText","CENTER" & endline
buildCOLS = buildCOLS & makeTD(rs("NEW_FEDERAL_ID","bodyText","CENTER" & endline
buildCOLS = buildCOLS & makeTD(rs("NEW_REMITTANCE_ADDRESS_1","bodyText","CENTER" & endline
buildCOLS = buildCOLS & makeTD(rs("REMITTANCE_CITY","bodyText","CENTER" & endline
buildCOLS = buildCOLS & makeTD(rs("REMITTANCE_STATE","bodyText","CENTER" & endline
buildCOLS = buildCOLS & makeTD(rs("NEW_REMITTANCE_ZIP","bodyText","CENTER" & endline
buildROWS = buildROWS & makeTR(buildCOLS,prev,curr) & endline 'build the row set
buildCOLS = "" 'clear out column values and start over
i=i+1
FlushResponse(i)
rs.movenext
loop
Response.Write(makeTABLE(buildROWS,"#FFFFFF",0,2,1)) & endline
end sub regards,
Brian
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 = "<TR><td colspan=9 align=middle class=bodyText><b><font size=3>Remittance Address 1 Normalization Table<br><center><font size=2>" & desc & "</font></center></font></b></td></TR>"
buildCOLS = buildCOLS & makeTH("UPDATE" & endline
buildCOLS = buildCOLS & makeTH("Old Name" & endline
buildCOLS = buildCOLS & makeTH("New Name" & endline
buildCOLS = buildCOLS & makeTH("VId" & endline
buildCOLS = buildCOLS & makeTH("FedId" & endline
buildCOLS = buildCOLS & makeTH("Addr1" & endline
buildCOLS = buildCOLS & makeTH("City" & endline
buildCOLS = buildCOLS & makeTH("State" & endline
buildCOLS = buildCOLS & makeTH("Zip" & endline
buildROWS = makeTR(buildCOLS,0,1) & endline
buildCOLS = "" 'clear out header values
i=0
do while not rs.EOF
if i mod 2 = 0 then
prev = rs("NEW_REMITTANCE_ADDRESS_1"
else
curr = rs("NEW_REMITTANCE_ADDRESS_1"
end if
buildCOLS = buildCOLS & makeTD("<INPUT TYPE=CHECKBOX NAME=CKBOX VALUE=" & rs("NEW_REMITTANCE_ADDRESS_1" & "~" & rs("EPS_ID" & ">","bodyText","CENTER" & endline
buildCOLS = buildCOLS & makeTD(rs("VENDOR_NAME","bodyText","LEFT" & endline
buildCOLS = buildCOLS & makeTD("<INPUT TYPE=TEXT NAME=TXT" & rs("EPS_ID" & " VALUE=" & qt & rs("NEW_VENDOR_NAME" & qt & ">","bodyText","LEFT" & endline
buildCOLS = buildCOLS & makeTD(rs("NEW_VENDOR_ID","bodyText","CENTER" & endline
buildCOLS = buildCOLS & makeTD(rs("NEW_FEDERAL_ID","bodyText","CENTER" & endline
buildCOLS = buildCOLS & makeTD(rs("NEW_REMITTANCE_ADDRESS_1","bodyText","CENTER" & endline
buildCOLS = buildCOLS & makeTD(rs("REMITTANCE_CITY","bodyText","CENTER" & endline
buildCOLS = buildCOLS & makeTD(rs("REMITTANCE_STATE","bodyText","CENTER" & endline
buildCOLS = buildCOLS & makeTD(rs("NEW_REMITTANCE_ZIP","bodyText","CENTER" & endline
buildROWS = buildROWS & makeTR(buildCOLS,prev,curr) & endline 'build the row set
buildCOLS = "" 'clear out column values and start over
i=i+1
FlushResponse(i)
rs.movenext
loop
Response.Write(makeTABLE(buildROWS,"#FFFFFF",0,2,1)) & endline
end sub regards,
Brian