How to improve ASP data query execution efficiency

1 The field name of clear query

2 Use RS (0) faster than RS (name)

3 Before using the record set RS value, assign it to the variable

4 [test] Current 10W data, access database preservation

Through normal extraction | Extract through the storage procedure | Use the getRows () method to extract:

1 The field name of clear query

Select * from [data_table]

That is, the record value of all fields from the database data_table species

The execution efficiency of the SELECT * statement is very low, because when performing such a statement, two queries are performed. First, check the system table to determine the name and data type. Then check the data

Therefore

Select name, pwd from [data_table]

2 Use RS (0) faster than RS (name)

Record the field name, or field index nodes in RS (). For example

RS (0) corresponding to RS (name)

RS (1) Corresponding to RS (PWD)

It has been proven to use index number access records several times faster than the field name, and query according to the string to query more time and system resources than to query by integer query

3 Before using the record set RS value, assign it to the variable

<%

Set rs = const.execute (select cname, cpwd from [data_table] where id = 1)

If not rs.eof then

Do while not rs.eof

CNAME = RS (0) Assign RS to variables

CPWD = RS (1)

....

Rs.movenext

Loop

End if

%>

4 [test] There are 10W pieces of data, and the Access database is stored.

A. Through normal extraction:

<%

Set rs = server.createObject (Adodb.oldSet)

RS.Oopen Select * From People Order by ID DESC, CN, 1,1

Do while not rs.eof

Response.write RS (ID) & |

Rs.movenext

loop

%>

Take 3,250.000 milliseconds 3 seconds

B. Extraction through the storage procedure:

<%

Set cn = server.createObject (Adodb.connection)

CN.Oopen Driver = {microSoft Access Driver (*.mdb)}; DBQ = & Server.MAPPATH (DB2.MDB)

Set cmd = server.createObject (Adodb.Command)

CMD.ActiveConnection = CN

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

%>

Take 2,187.500 millisecond 2 seconds

C. Use the getrows () method to extract:

<%

Set cn = server.createObject (Adodb.connection)

Set cmd = server.createObject (Adodb.Command)

CN.Oopen Driver = {microSoft Access Driver (*.mdb)}; DBQ = & Server.MAPPATH (DB2.MDB)

CMD.ActiveConnection = CN

cmd.commandtext = select * from people order by ID desc

set rs = cmd.execute

rsarray = rs. GetRows () will deposit record set data into an array. The array defaults to the two -dimensional array

for i = 0 to ubound (RSARRAY, 2) ubound (array, num) where the NUM meaning index is dimensionally used, it is not filled in one dimension by default, 2 is equal to two -dimensional two -dimensional

response.write rsarray (0, i) & |

next

%>

Time consumption: 187.500 millisecond 0.2 seconds

rsarray (a, b)

A represents the field number B of the record set in the array of the array of the record set.

As follows: below: