Execution efficiency of ASP data extraction)

Generally, data records are extracted from the database. You need to use SQL statements to query and obtain the relevant record set. Then, select related fields and related record rows from the record set for display.

In the process of extracting and displaying a series of columns, if you pay attention to the following points, the efficiency of data extraction is greatly increased.

1. specify the names of extracted fields.

Normal SQL statement extraction records are:

Select * from [data_table]

That is, the record values of all fields are extracted from data_table.

The execution efficiency of select * statements is very low, because two queries are actually executed when such statements are executed. Before the SELECT statement is executed, first, you must query the system table to determine the name and data type.

Therefore, use the select * Statement at least, and use a clear field name, such:

Select cn_name, cn_pwd from [data_table]

2. RS (0) is faster than Rs (filename ).
Set rs = conn. Execute ("select cn_name, cn_pwd from [data_table]")

In the record set RS (), you can enter the field name (numeric type) or the field index number (number), which indicates the first field in the field list. For example:
RS (0) indicates RS ("cn_name ")
RS (1) indicates RS ("cn_pwd ")

It has been proved that the number of indexes used to access the record set elements is several times faster than the field name. Query by string takes more time and system resources than query by integer.

3. assign a value to the variable before using the RS value of the record set.
<%
Set rs = conn. Execute ("select cn_name, cn_pwd from [data_table] Where cn_id = 1 ")
If not Rs. EOF then
Do while not Rs. EOF
Cn_name = RS (0) 'assigns the RS value to the variable
Cn_pwd = RS (1)
'... Use variable processing
Rs. movenext
Loop
End if
Rs. Close
Set rs = nothing
%>
 
However, when changing the display sequence of select list fields in SQL statements or stored procedures, you must pay attention to the assignment and processing.

4. Of course, using getrows () is another thing.

Existing 10 million data records, stored in the Access Database

Normal extraction:

<%
Set conn = server. Createobject ("ADODB. Connection ")
Connstr = "provider = Microsoft. Jet. oledb.4.0; Data Source =" & server. mappath ("db2.mdb ")
Conn. Open connstr

Set rs = server. Createobject ("ADODB. recordset ")
SQL = "select * from people order by ID DESC"
Rs. Open SQL, Conn, 1, 1

Do while not Rs. EOF
Response. Write RS ("ID") & "|"
Rs. movenext
Loop
%>

It takes 3,250.000 milliseconds, and the total test average is about 3 seconds.

========================================================== ============================
Use Stored Procedure extraction:
<%
Set conn = server. Createobject ("ADODB. Connection ")
Set cmd = server. Createobject ("ADODB. Command ")
Conn. Open "provider = Microsoft. Jet. oledb.4.0; Data Source =" & server. mappath ("db2.mdb ")

Cmd. activeconnection = Conn
Cmd. commandtext = "select * from people order by id desc"
Set rs = cmd. Execute

Do while not Rs. EOF
Response. Write RS ("ID") & "|"
Rs. movenext
Loop
%>

It takes 2,187.500 milliseconds, and the total test average is about 2 seconds.

========================================================== ========================

The preceding two methods cannot completely solve the problem of long execution time. The main reason is that each cycle must extract records from the database (the command speed is relatively fast)
Use the getrows () method:

<%
Set conn = server. Createobject ("ADODB. Connection ")
Set cmd = server. Createobject ("ADODB. Command ")

Conn. Open "provider = Microsoft. Jet. oledb.4.0; Data Source =" & server. mappath ("db2.mdb ")
Cmd. activeconnection = Conn
Cmd. commandtext = "select * from people order by id desc"
Set rs = cmd. Execute
Rsarray = Rs. getrows ()

For I = 0 to ubound (rsarray, 2)
Response. Write rsarray (0, I) & "|"
Next
%>

It takes 187.500 milliseconds, and the total test average is about 0.2 seconds.

The getrows () method copies data from recordset to a two-dimensional array. This is a two-dimensional array. The first subscript identifies the field, and the second identifies the record number.

So rsarray = Rs. getrows ()
Rsarray (0, 0) indicates the first field value in the first row of the record set.
Rsarray (1, 0) indicates the second field value in the first row of the record set.

The array data is stored in the memory, which fundamentally solves the problem that each display record still needs to request from the database.