|
|
The previous performance article was about how to get recordset from database with minimum of consumed processor time.
This article tels about how to get a HTML page from the recordset with the same limitations - minimum of consumed time.
Performance test configuration
- P200, 128MB RAM
- Win NT 4.0 SP5
- SQL 6.5 SP5
- IIS 4
- MDAC 2.1 SP2
- SQL server on the same machine as IIS
Procedure
First of all we have to create test data. The test data was created in SQL 6.5 as a single table with mixed char/int/money columns and 50 rows :
CREATE TABLE RSTest (
IntCol int,
CharCol char(32),
VarCharCol varchar(200),
MoneyCol money
)
go
DECLARE @Row int SELECT @Row = 0
WHILE @Row<50
BEGIN
INSERT INTO RSTest VALUES(
RAND()*5000,
REPLICATE(char(RAND()*25+65), RAND()*32),
REPLICATE(char(RAND()*25+65), RAND()*200),
RAND()*50000000
)
SELECT @Row = @Row + 1
END
The second, we have to open recordset from database :
Function GetSQLConn()
Dim Conn
Set Conn = CreateObject("ADODB.Connection")
Conn.Provider = "SQLOLEDB"
Conn.Open "Data Source=(local);App=PerfTests", "SomeUser", "SomePwd"
Set GetSQLConn = Conn
End Function
Dim SQL, RS
SQL = "set rowcount 20 Select IntCol,CharCol,VarCharCol,MoneyCol from RSTest Set rowcount 0"
Set RS = GetSQLConn.Execute(SQL)
|
Create HTML page from Recordset
| 1. And how to get a HTML page from the recordset ? We can use a first begginer idea : |
%>
<table CellPadding=0 CellSpacing=0>
<%
Do While Not rs.eof
%>
<TR><TD><%=RS("IntCol")%>
</TD><TD><%=RS("CharCol")%>
</TD><TD><%=RS("VarCharCol")%>
</TD><TD><%=RS("MoneyCol")%>
</TD></TR>
<%
rs.movenext
Loop
%>
</table>
<%
|
|
| 2. Good idea, but very bad look. We can arrange the code to real ASP VBScript : |
Response.Write "<table CellPadding=0 CellSpacing=0>" & vbCrLf
Do While Not rs.eof
Response.Write "<TR>"
Response.Write "<TD>" & RS("IntCol") & "</TD>"
Response.Write "<TD>" & RS("CharCol") & "</TD>"
Response.Write "<TD>" & RS("VarCharCol") & "</TD>"
Response.Write "<TD>" & RS("MoneyCol") & "</TD>"
Response.Write "</TR>" & vbCrLf
rs.movenext
Loop
Response.Write "</table>" & vbCrLf
|
| | As you can see, the code has as same performace as beginner code. The beginner code has a little better performance beacause the HTML code is not so splitted as this code (joined </TD> and <TD> tags). |
| 3. I'ts great. The appearance of the code is up to one class high.
But we are programmers :-), we can first create HTML page in some variable and then write the variable as whole page : |
Dim HTML
HTML = "<table CellPadding=0 CellSpacing=0>" & vbCrLf
Do While Not RS.EOF
HTML = HTML & "<TR>"
HTML = HTML & "<TD>" & RS("IntCol") & "</TD>"
HTML = HTML & "<TD>" & RS("CharCol") & "</TD>"
HTML = HTML & "<TD>" & RS("VarCharCol") & "</TD>"
HTML = HTML & "<TD>" & RS("MoneyCol") & "</TD>"
HTML = HTML & "</TR>" & vbCrLf
RS.MoveNext
Loop
HTML = HTML & "</table>" & vbCrLf
Response.Write HTML
|
| | This code looks very good. But it hides one big problem - VBS have to allocate more and more memory for the HTML variable. Each of the '&' operator means alocation of a new block of the memory. The size of allocated memory depends lineary on the number of rows, but consumed time depends by sqare. |
| 4. Optimalization of the previous code. We can create temporary variable to store HTML for each row and global variable to store whole HTML page. The consumed time will grow lineary on number of rows. |
Dim HTML, pHTML
HTML = "<table CellPadding=0 CellSpacing=0>" & vbCrLf
Do While Not RS.EOF
pHTML = "<TR>"
pHTML = pHTML & "<TD>" & RS("IntCol") & "</TD>"
pHTML = pHTML & "<TD>" & RS("CharCol") & "</TD>"
pHTML = pHTML & "<TD>" & RS("VarCharCol") & "</TD>"
pHTML = pHTML & "<TD>" & RS("MoneyCol") & "</TD>"
pHTML = pHTML & "</TR>" & vbCrLf
HTML = HTML & pHTML
RS.MoveNext
Loop
HTML = HTML & "</table>" & vbCrLf
Response.Write HTML
|
|
| 5. What we can do with the code ? The next optimalization is based on one of bad feature of VBS - VBS is not typed. So all of the variables are variants and VBS must resolve its object interface at the run-time. Recordset is also object, we can prepare the resoluton before the recordset row loop, the resolution is done only one, not for each row. |
Dim HTML, pHTML
Dim IntCol, CharCol, VarCharCol, MoneyCol
Set IntCol = RS("IntCol")
Set CharCol = RS("CharCol")
Set VarCharCol = RS("VarCharCol")
Set MoneyCol = RS("MoneyCol")
HTML = "<table CellPadding=0 CellSpacing=0>" & vbCrLf
Do While Not RS.EOF
pHTML = "<TR>"
pHTML = pHTML & "<TD>" & IntCol & "</TD>"
pHTML = pHTML & "<TD>" & CharCol & "</TD>"
pHTML = pHTML & "<TD>" & VarCharCol & "</TD>"
pHTML = pHTML & "<TD>" & MoneyCol & "</TD>"
pHTML = pHTML & "</TR>" & vbCrLf
HTML = HTML & pHTML
RS.MoveNext
Loop
HTML = HTML & "</table>" & vbCrLf
Response.Write HTML
|
|
| 6. And now one special : last version of ADO recordset has GetString method. The method enables create HTML table from recordset with one row of code. Of course, time results of the method are the best. |
Dim RowsString
Dim RowSeparator, ColSeparator, BeginTable, EndTable
RowSeparator = "</TD></TR>" & vbCrLf & "<TR><TD>"
ColSeparator = "</TD><TD>"
BeginTable = "<table CellPadding=0 CellSpacing=0>" & vbCrLf & "<TR><TD>"
EndTable = "</TD></TR>" & vbCrLf & "</table>" & vbCrLf
RowsString = RS.GetString(, , ColSeparator, RowSeparator, "")
'Remove redundant row separator
RowsString = Left(RowsString, Len(RowsString) - Len(RowSeparator))
Response.Write = BeginTable & RowsString & EndTable
|
|
| 7. At the least, we must measure net time consumed by recordset to retrieve data from database. |
Do While Not rs.eof
rs.movenext
Loop
|
|
Script time and consumed times
| Results for 20 rows |
|---|
| Without buffer | With buffer |
|---|
Code
number | Script
Time [ms] 4) | Kernel+User
Time [ms] 5) | Script
Time [ms] 4) | Kernel+User
Time [ms] 5) |
|---|
1. Hide
%>
<table CellPadding=0 CellSpacing=0>
<%
Do While Not rs.eof
%>
<TR><TD><%=RS("IntCol")%>
</TD><TD><%=RS("CharCol")%>
</TD><TD><%=RS("VarCharCol")%>
</TD><TD><%=RS("MoneyCol")%>
</TD></TR>
<%
rs.movenext
Loop
%>
</table>
<%
|
|
Beginner code | 46.46 | 36.45 | 23.93 | 21.23 | 2. Hide
Response.Write "<table CellPadding=0 CellSpacing=0>" & vbCrLf
Do While Not rs.eof
Response.Write "<TR>"
Response.Write "<TD>" & RS("IntCol") & "</TD>"
Response.Write "<TD>" & RS("CharCol") & "</TD>"
Response.Write "<TD>" & RS("VarCharCol") & "</TD>"
Response.Write "<TD>" & RS("MoneyCol") & "</TD>"
Response.Write "</TR>" & vbCrLf
rs.movenext
Loop
Response.Write "</table>" & vbCrLf
|
|
Better look | 45.87 | 35.45 | 25.83 | 24.23 | 3. Hide
Dim HTML
HTML = "<table CellPadding=0 CellSpacing=0>" & vbCrLf
Do While Not RS.EOF
HTML = HTML & "<TR>"
HTML = HTML & "<TD>" & RS("IntCol") & "</TD>"
HTML = HTML & "<TD>" & RS("CharCol") & "</TD>"
HTML = HTML & "<TD>" & RS("VarCharCol") & "</TD>"
HTML = HTML & "<TD>" & RS("MoneyCol") & "</TD>"
HTML = HTML & "</TR>" & vbCrLf
RS.MoveNext
Loop
HTML = HTML & "</table>" & vbCrLf
Response.Write HTML
|
|
Store to HTML variable | 60.79 | 53.58 | 55.08 | 52.17 | 4. Hide
Dim HTML, pHTML
HTML = "<table CellPadding=0 CellSpacing=0>" & vbCrLf
Do While Not RS.EOF
pHTML = "<TR>"
pHTML = pHTML & "<TD>" & RS("IntCol") & "</TD>"
pHTML = pHTML & "<TD>" & RS("CharCol") & "</TD>"
pHTML = pHTML & "<TD>" & RS("VarCharCol") & "</TD>"
pHTML = pHTML & "<TD>" & RS("MoneyCol") & "</TD>"
pHTML = pHTML & "</TR>" & vbCrLf
HTML = HTML & pHTML
RS.MoveNext
Loop
HTML = HTML & "</table>" & vbCrLf
Response.Write HTML
|
|
Temporary for row HTML | 33.75 | 26.34 | 28.44 | 25.64 | 5. Hide
Dim HTML, pHTML
Dim IntCol, CharCol, VarCharCol, MoneyCol
Set IntCol = RS("IntCol")
Set CharCol = RS("CharCol")
Set VarCharCol = RS("VarCharCol")
Set MoneyCol = RS("MoneyCol")
HTML = "<table CellPadding=0 CellSpacing=0>" & vbCrLf
Do While Not RS.EOF
pHTML = "<TR>"
pHTML = pHTML & "<TD>" & IntCol & "</TD>"
pHTML = pHTML & "<TD>" & CharCol & "</TD>"
pHTML = pHTML & "<TD>" & VarCharCol & "</TD>"
pHTML = pHTML & "<TD>" & MoneyCol & "</TD>"
pHTML = pHTML & "</TR>" & vbCrLf
HTML = HTML & pHTML
RS.MoveNext
Loop
HTML = HTML & "</table>" & vbCrLf
Response.Write HTML
|
|
Temporary for recordset fields | 24.23 | 17.12 | 19.22 | 16.92 | 6. Hide
Dim RowsString
Dim RowSeparator, ColSeparator, BeginTable, EndTable
RowSeparator = "</TD></TR>" & vbCrLf & "<TR><TD>"
ColSeparator = "</TD><TD>"
BeginTable = "<table CellPadding=0 CellSpacing=0>" & vbCrLf & "<TR><TD>"
EndTable = "</TD></TR>" & vbCrLf & "</table>" & vbCrLf
RowsString = RS.GetString(, , ColSeparator, RowSeparator, "")
'Remove redundant row separator
RowsString = Left(RowsString, Len(RowsString) - Len(RowSeparator))
Response.Write = BeginTable & RowsString & EndTable
|
|
GetString | 16.43 | 10.01 | 11.92 | 9.61 | 7. Hide
Do While Not rs.eof
rs.movenext
Loop
|
|
Test | 8.82 | 6.81 | 8.82 | 6.81 |
| Results for 50 rows |
|---|
| Without buffer | With buffer |
|---|
Code
number | Script
Time [ms] 4) | Kernel+User
Time [ms] 5) | Script
Time [ms] 4) | Kernel+User
Time [ms] 5) |
|---|
1. Hide
%>
<table CellPadding=0 CellSpacing=0>
<%
Do While Not rs.eof
%>
<TR><TD><%=RS("IntCol")%>
</TD><TD><%=RS("CharCol")%>
</TD><TD><%=RS("VarCharCol")%>
</TD><TD><%=RS("MoneyCol")%>
</TD></TR>
<%
rs.movenext
Loop
%>
</table>
<%
|
|
Beginner code | 115.78 | 91.54 | 55.08 | 52.28 | 2. Hide
Response.Write "<table CellPadding=0 CellSpacing=0>" & vbCrLf
Do While Not rs.eof
Response.Write "<TR>"
Response.Write "<TD>" & RS("IntCol") & "</TD>"
Response.Write "<TD>" & RS("CharCol") & "</TD>"
Response.Write "<TD>" & RS("VarCharCol") & "</TD>"
Response.Write "<TD>" & RS("MoneyCol") & "</TD>"
Response.Write "</TR>" & vbCrLf
rs.movenext
Loop
Response.Write "</table>" & vbCrLf
|
|
Better look | 108.36 | 88.92 | 62.50 | 57.68 | 3. Hide
Dim HTML
HTML = "<table CellPadding=0 CellSpacing=0>" & vbCrLf
Do While Not RS.EOF
HTML = HTML & "<TR>"
HTML = HTML & "<TD>" & RS("IntCol") & "</TD>"
HTML = HTML & "<TD>" & RS("CharCol") & "</TD>"
HTML = HTML & "<TD>" & RS("VarCharCol") & "</TD>"
HTML = HTML & "<TD>" & RS("MoneyCol") & "</TD>"
HTML = HTML & "</TR>" & vbCrLf
RS.MoveNext
Loop
HTML = HTML & "</table>" & vbCrLf
Response.Write HTML
|
|
Store to HTML variable | 286.62 | 270.78 | 277.20 | 271.60 | 4. Hide
Dim HTML, pHTML
HTML = "<table CellPadding=0 CellSpacing=0>" & vbCrLf
Do While Not RS.EOF
pHTML = "<TR>"
pHTML = pHTML & "<TD>" & RS("IntCol") & "</TD>"
pHTML = pHTML & "<TD>" & RS("CharCol") & "</TD>"
pHTML = pHTML & "<TD>" & RS("VarCharCol") & "</TD>"
pHTML = pHTML & "<TD>" & RS("MoneyCol") & "</TD>"
pHTML = pHTML & "</TR>" & vbCrLf
HTML = HTML & pHTML
RS.MoveNext
Loop
HTML = HTML & "</table>" & vbCrLf
Response.Write HTML
|
|
Temporary for row HTML | 91.12 | 76.50 | 79.70 | 73.10 | 5. Hide
Dim HTML, pHTML
Dim IntCol, CharCol, VarCharCol, MoneyCol
Set IntCol = RS("IntCol")
Set CharCol = RS("CharCol")
Set VarCharCol = RS("VarCharCol")
Set MoneyCol = RS("MoneyCol")
HTML = "<table CellPadding=0 CellSpacing=0>" & vbCrLf
Do While Not RS.EOF
pHTML = "<TR>"
pHTML = pHTML & "<TD>" & IntCol & "</TD>"
pHTML = pHTML & "<TD>" & CharCol & "</TD>"
pHTML = pHTML & "<TD>" & VarCharCol & "</TD>"
pHTML = pHTML & "<TD>" & MoneyCol & "</TD>"
pHTML = pHTML & "</TR>" & vbCrLf
HTML = HTML & pHTML
RS.MoveNext
Loop
HTML = HTML & "</table>" & vbCrLf
Response.Write HTML
|
|
Temporary for recordset fields | 68.90 | 52.08 | 55.08 | 50.08 | 6. Hide
Dim RowsString
Dim RowSeparator, ColSeparator, BeginTable, EndTable
RowSeparator = "</TD></TR>" & vbCrLf & "<TR><TD>"
ColSeparator = "</TD><TD>"
BeginTable = "<table CellPadding=0 CellSpacing=0>" & vbCrLf & "<TR><TD>"
EndTable = "</TD></TR>" & vbCrLf & "</table>" & vbCrLf
RowsString = RS.GetString(, , ColSeparator, RowSeparator, "")
'Remove redundant row separator
RowsString = Left(RowsString, Len(RowsString) - Len(RowSeparator))
Response.Write = BeginTable & RowsString & EndTable
|
|
GetString | 36.06 | 22.02 | 21.44 | 17.82 | 7. Hide
Do While Not rs.eof
rs.movenext
Loop
|
|
Test | 18.00 | 15.02 | 18.00 | 15.02 |
Results
Six codes, six different times. There are some tips based on the time tests :
1. Use buffering. All the codes have better time results with buffering.
2. Use temporary variables to store small parts of string HTML, and then paste the small parts to the whole (4 versus 3, 3 times faster).
3. Use temporary variables to resolve interface only one, not in each row loop (Set IntCol = RS("IntCol"), 5 versus 4, up to 50% faster)
4. Use GetString method of recordset object if you convert recordset data by only default way (6 versus 5, 2 times faster than best VBS code). GetString method is only 15% slower than plain recordset move to the last row (17.82 ms GetString, 15ms move).
4) Script time - TickCount [ms] of the operation. The time has two parts - Kernel+User time of calling thread and execution time of all other processes.
Script Time contains also Kernel+User time of SQL server and Kernel+User time of IIS.
5) Kernel+User time - times of calling thread in [ms]
The time has only relative meaning - because the time was on the pure configuration (P200/128M), the real times will be better.
Copyright and use this code
The source code on this page and other samples at https://www.motobit.com/tips/
are a free code, you can use it as you want: copy it, modify it, use it in your products, ...
If you use this code, please:
1. Leave the author note in the source.
or
2. Link this sample from you page.
<A
Href="https://www.motobit.com/tips/detpg_rshtml/"
Title="Performance tests of six different VBS
codes, each of them generates
the same HTML table."
>Best way to get HTML page from a recordset</A>
|