1. Choice of SqlDataRead and Dataset Advantages of Sqldataread: Reading data is very fast. If the returned data does not require a lot of processing, it is recommended to use SqlDataReader, whose performance is much better than datset. Disadvantages: The connection to the database cannot be closed until the data is read
(SqlDataReader reads data in a fast forward direction. The SqlDataReader class provides a way to read the forward-only data stream retrieved from the SQL Server database. It uses SQL Server's The native network data transmission format reads data directly from the database connection. DataReader needs to be explicitly closed in time to release the data connection in time.)
Dataset reads the data and caches it in memory. Disadvantages: High memory usage. If you need to do a lot of processing on the returned data, it is better to use Dataset to reduce the connection operations to the database. Advantages: You only need to connect once to close the connection to the database
. In general, if you want to read a large amount of data and do not do a lot of processing on the returned data, use SqlDataReader. For a large amount of processing on the returned data, it is more appropriate to use datset. The choice of SqlDataReader and Dataset depends on for the realization of program functions.
2. ExecuteNonQuery and ExecuteScalar
do not need to return a result set when updating data. It is recommended to use ExecuteNonQuery. Since no result set is returned, network data transmission can be omitted. It simply returns the number of rows affected. If you only need to update data, the performance overhead of ExecuteNonQuery is relatively small.
ExecuteScalar returns only the first column of the first row in the result set. Use the ExecuteScalar method to retrieve a single value (such as an id number) from the database. This operation requires less code than using the ExecuteReader method to perform the operations required to generate a single value on the returned data.
*Just update the data using ExecuteNonQuery. Query of a single value uses ExecuteScalar data binding option
3. Data binding DataBinder
general binding method <%# DataBinder.Eval(Container.DataItem, "field name") %> use DataBinder.eval The binding does not care about the data source (Dataread or dataset). You don't have to worry about the type of data. eval will convert this data object to a string. A lot of work was done on the underlying binding, using reflection capabilities. Just because it is convenient to use, it affects data performance. Let's take a look at <%# DataBinder.Eval(Container.DataItem, "field name") %>. When bound to a dataset, DataItem is actually a DataRowView (if it is bound to a data reader (dataread), it is an IdataRecord.) Therefore, directly converting it to a DataRowView will greatly improve performance.
<%# ctype(Container.DataItem,DataRowView).Row("field name") %>
*It is recommended to use <%# ctype(Container.DataItem,DataRowView).Row("field name") %> for data binding. . When the amount of data is large, the speed can be increased hundreds of times. Pay attention to two aspects when using it: 1. You need to add <%@ Import namespace="System.Data"%> to the page. 2. Pay attention to the case of the field name (pay special attention). If it is inconsistent with the query, in some cases it will be slower than <%# DataBinder.Eval(Container.DataItem, "field name") %>. If you want to further improve the speed, you can use the <%# ctype(Container.DataItem,DataRowView).Row(0) %> method. However, its readability is not high.
The above is the writing method of vb.net. In c#: <@% ((DataRowView)Container.DataItem)["field name"] %>
The simplest way to view the status of each execution process on the page: you can view the details if the trace attribute of the page is true.
1. Use stored procedures:
1. Performance: Stored procedures provide many advanced features that are not found in the standard SQL language. Its ability to pass parameters and execute logical expressions helps application designers handle complex tasks. In addition, the stored procedure is stored on the local server, reducing the network transmission bandwidth and execution time required to execute the procedure. (The stored procedure has precompiled the sql statement, so its execution speed is much faster than executing the sql statement in the program)
2. Program structure: From the perspective of program scalability, using stored procedures will affect future modifications of the program. Come for convenience. For example, if the structure of the database changes, you only need to modify the corresponding storage structure and the calling part of the program. This part is not within the scope of this article and belongs to the program structure design. So I won’t expand on it here.
3. Program security: SQL Injection attacks can be avoided by using stored procedures.
2. Optimization of query statements (for sql server2000)
Many people write sql statements only for the purpose, without considering the execution efficiency of the sql statement. Here I only provide a method to optimize the order of tables. (The optimization and principles of SQL statements will be discussed in my SQL Server2000 study notes.)
For the execution efficiency of SQL statements, you can use the query analyzer of SQL Server2000 to view the execution process of the statements. .
Optimize table order: Under normal circumstances, sqlserver will automatically optimize table connections. For example: select name,no from A join B on A. id=B.id join C on C.id=A.id where name='wang'Although
the A table is listed first in From, then B, and finally It's C. But sql server may use the c table first. Its selection principle is to limit the query to a single row or a few rows, so that the total amount of data searched in other tables can be reduced. In most cases, SQL Server will make the optimal choice, but if you find that a complex join query is slower than expected, you can use the SET FORCEPLAN statement to force SQL Server to use the tables in the order in which they appear. As in the above example, add: SET FORCEPLAN ON.......SET FORCEPLAN OFF. The execution order of the table will be executed in the order you wrote. View the 2 execution efficiencies in Query Analyzer to choose the order in which tables are joined.
*Use SET FORCEPLAN to select the table connection sequence
. 3. Page optimization (.aspx)
mainly focuses on several page attributes
1. EnableViewState (the view state of the page). Set to false if there are no special requirements. With ViewState, each object must first be serialized into ViewState and then deserialized via postback, so there is no cost to using ViewState. Use as few objects as possible and, if possible, reduce the number of objects you put in ViewState. Viewstate can basically be disabled in the following situations:
(1) Page control (.ascx)
(2) The page is not passed back to itself.
(3) No event processing for controls is required.
(4) The control has no dynamic or data-bound property values (or is handled in code for each postpack)
Disable ViewState for a single page or for each page, as follows: Single page: <%@ Page EnableViewState=" False" %> Each page: In web.config <Pages EnableViewState="false" /> EnableSessionState can keep the default value (it will only occupy resources if the page uses sessionstate). EnableViewStateMac If there are no special security requirements, keep the default value.
2. Pagelayout. Page layout model. It is recommended to use Flowlayout (elements are added without absolute positioning attributes). Gridlayout (absolute positioning attributes) will produce more code than Flowlayout due to the use of absolute positioning, mainly the positioning information of the control.
3. When releasing the project, remember to release the Debug status of the page.
4. Optimization of Html language. My suggestion is to be proficient in Html/JavaScript and use less code automatically generated by vs.net2003. It will automatically generate some useless html code.
5. Setting smart navigation to true can significantly improve user performance. Enabling this property has little impact on the client and server. It can intelligently update the parts that need to be updated.
4. Selection of controls:
Choice of Html controls and server controls. The convenience and functional realization brought by server controls are unmatched by HTML controls. But it is obtained at the expense of server-side resources. My personal suggestion: If the html control cannot achieve the functions it wants to achieve, and it cannot be achieved when combined with some scripting languages (such as javascrpt/vbscript). Only then will the server control be selected. After selecting the server control, try to optimize its control, such as canceling some page states, etc. (see the optimization of the control specifically).
Selection of server controls: Mainly explain several common data controls:
DataGrid: comes with the most powerful data display The control has built-in many practical functions such as modifying, deleting, adding, and paging data. If you only need to display data, try not to choose DataGrid (it stores all data in viewstate). Also do not use the built-in paging function. Microsoft has done a lot of work on the bottom layer of automatic paging. Although it is convenient to use, it The performance overhead is huge.
DataList: It has many fewer functions than DataGrid. But it’s much more customizable. The unique multi-line data display brings us a lot of convenience. It can basically implement the functions that DataGrid can achieve. So it is recommended to use it.
Repeater: The least functional, but very customizable. It is recommended to use it if you only need to display data. Due to the reduction of many functions, the consumption of server performance is minimal. Therefore, if it is to display data, I basically choose Repeater, then DataList and finally DataGrid
* try to choose the html control. Functions that can be implemented on the client are implemented on the client (proficient in javascript), reducing the pressure on the server. Data control selection sequence: Repeater, DataList, DataGrid
5. Optimization of server controls:
1.
The viewstate of the Viewstate control is basically the same as the viewstate of the page. Used to save some states of the control. The processing principle is the same as processing the viewstate of the page. If you are interested, you can use Datagrid to bind data to test the amount of data saved by viewstate. The data it saves is basically the same as the amount of data displayed by Datagrid.
2. Ispostpack
defaults to false. It needs to be set to true when an event needs to be generated.
The optimization of the control mainly depends on your familiarity with this control. The better you understand the inner workings of a control, the better you can optimize it appropriately.
Performance optimization cannot be explained in a few sentences. What I have written is just the tip of the iceberg. Performance optimization relies on the accumulation of daily experience and the continuous understanding of the operating principles of the program.
6. Exception problems
There is no need to use exceptions for general problems, such as problems where the user does not exist in the forum, the user password is incorrect, etc., because instantiating an exception requires a lot of resources and requires filling in the exception information (exception type, exception location where the exception is thrown) etc.), of course it is not to avoid using exceptions, but to handle necessary exceptions. The principle for exceptions is: don’t use them if you can, and don’t generate your own exceptions if you can use the existing exceptions in the system.