Usually, data records are extracted from the database, SQL statements are used to query and obtain the relevant record set, and then select relevant fields and relevant record rows from the record set for display.
Then, in the process of extracting a series of columns displayed, if the following key points are paid attention to, the execution efficiency of extracting data will be greatly increased.
1. Clearly extracted field name
The normal SQL statement extraction record is:
Select*from[data_table]
That is, extract the record values of all fields from the data table data_table.
The execution efficiency of the select* statement is very low, because when executing such a statement, two queries are actually executed. Before executing the select statement, you must first query the system table to determine the name and data type.
So try to use the select* statement as little as possible, and use clear field names, such as:
Selectcn_name,cn_pwdfrom[data_table]
2. Use rs(0) faster than rs(filename)
Setrs=conn.Execute("Selectcn_name,cn_pwdfrom[data_table]")
The record set rs() can write a field name (character type) or a field index number (number) in it, which represents the number of fields in the field list. for example:
rs(0) means rs("cn_name")
rs(1) means rs("cn_pwd")
It has been proved that accessing record set elements with index number is several times faster than using field names. Querying by string takes more time and system resources than querying by integers.
3. Before using the record set rs value, assign it to the variable
<%
Setrs=conn.Execute("Selectcn_name,cn_pwdfrom[data_table]wherecn_id=1")
ifnotrs.eofthen
dowhilenotrs.eof
cn_name=rs(0)' Assign the rs value to the variable
cn_pwd=rs(1)
'...Use variable processing work
rs.movenext
loop
endif
rs.close
Setrs=Nothing
%>
However, if the field display order of the select list is changed in SQL statements or stored procedures, you should pay attention to the assignment and processing.
4. Of course, using GetRows() is another matter