Best way to get HTML page from a recordset
ActiveX RegEdit.   ActiveX User account Manager   Pure-ASP Upload
Export MDB/DBF from ASP   Active LogFile   WebChecker   ActiveX/ASP Multi Dictionary object
 See 
 also 		  IISTracer, real-time IIS monitor and logging tool.
 Huge ASP file upload with progress bar. 


Scripts.com Rate Script!
 Top messages
 4.5.2002 9:16:43 
 Send an email from ASP (WSH) using VBSscript, CDONTS and Outlook. (nbsp;ASP / ASP.Net, WSH, VBScript, Email)
 22.3.2003 19:18:41 
 Read and write SQL image data, store binary file to sql table. (nbsp;WSH, Database, Conversion, VBScript)
 26.1.2007 13:57:34 
 ASP image resize and jpg/gif/png convert, free VBScript code (nbsp;ASP / ASP.Net, VBScript)

 Best way to get HTML page from a recordset 

 Areas>ASP / ASP.Net>Performance tests
 Areas>ASP / ASP.Net>Database
      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 bufferWith buffer
 Code 
 number 		 Script 
 Time [ms] 4)		 Kernel+User 
 Time [ms] 5)		 Script 
 Time [ms] 4)		 Kernel+User 
 Time [ms] 5)
 1. Beginner code  46.46  36.45  23.93  21.23 
 2. Better look  45.87  35.45  25.83  24.23 
 3. Store to HTML variable  60.79  53.58  55.08  52.17 
 4. Temporary for row HTML  33.75  26.34  28.44  25.64 
 5. Temporary for recordset fields  24.23  17.12  19.22  16.92 
 6. GetString  16.43  10.01  11.92  9.61 
 7. Test  8.82  6.81  8.82  6.81 

Results for 50 rows
Without bufferWith buffer
 Code 
 number 		 Script 
 Time [ms] 4)		 Kernel+User 
 Time [ms] 5)		 Script 
 Time [ms] 4)		 Kernel+User 
 Time [ms] 5)
 1. Beginner code  115.78  91.54  55.08  52.28 
 2. Better look  108.36  88.92  62.50  57.68 
 3. Store to HTML variable  286.62  270.78  277.20  271.60 
 4. Temporary for row HTML  91.12  76.50  79.70  73.10 
 5. Temporary for recordset fields  68.90  52.08  55.08  50.08 
 6. GetString  36.06  22.02  21.44  17.82 
 7. 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.  
 

See also for 'Best way to get HTML page from a recordset' article:

     Recordset convertor performance test Recordset convertor is great object which enables direct output of binary database data from ASP page as DBF/MDB.
     ASP/VBS database performance test and comparison. ADO, DAO, RDO, MDB, SQL, OLEDB and ODBC comparison, performance test of objects that can be used for database access on server-side in ASP.

If you like this page, please include next link on your pages: 
<A
 Href="http://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>
     IISTracer - IIS ISAPI real-time monitor IISTracer is a real-time monitoring tool for Microsoft IIS, which will show/log you what is happenning on IIS server right now. It let's you reveal problems with long-running scripts (.asp, .cgi, cfm...), hang-up states and low resource situations and lets you stop long-running requests (uploads/downloads).      ActiveX User account Manager - Set of simple objects for creating, deleting, and managing user accounts, groups, servers and domains in the Windows NT environment.
     Active log file - Hi-performance text file logging for ASP/VBS/VBA applications. Lets you create daily/weekly/monthly log files with variable number of logged values and extra timing and performance info.      ActiveX windows registry editor - Intuitive, easy to use COM interface to windows registry. Set of classes to read/enumerate/modify windows registry keys and values from ASP, VBS and T-SQL.
     ActiveX/ASP Multi Dictionary object - Free-threaded hi-speed dictionary algorithm with unique/nonunique keys (map/multimap). Connect to another dictionary object in the same process. Lock and Unlock methods to synchronize tasks (application scope). Share ASP Application/Session objects.      Export DBF/MDB from ASP - Conversion from recordset to MDB/DBF. Direct binary output of MDB or DBF files from ASP pages with one row of code.
     Pure-ASP upload - lets you upload files using Pure ASP VBS code (using multipart/form-data and input type=file).      ByteArray - Works with safearray binary data (VT_UI1 | VT_ARRAY) - save/restore binary data from disk, find, work with code pages, convert to string/hexstring(SQL).
     WebChecker - Checks http, https, ftp and gopher internet connections in regular intervals. Lets you monitor web site functionality (uptime). Enables restart or notification on problems.      HTTPLog ISAPI filter - Lets you log incomming/outgoing http header and document data to separate files. Monitor of IIS service input/output.
© 1996 – 2008 Antonin Foller, PSTRUH Software, e-mail help@pstruh.cz