|
1. sqldataread and dataset Selection
Sqldataread advantages: fast data reading. If you do not need to process the returned data in large quantities, we recommend that you use sqldatareader. Its performance is much better than that of datset. Disadvantage: the connection to the database cannot be closed until the data is read.
(Sqldatareader reads data quickly forward. The sqldatareader class provides a method for reading data streams only retrieved from the SQL Server database. It uses the Local Network Data Transmission Format of SQL Server to directly read data from database connections. Datareader must be closed explicitly in time. Data connections can be released in a timely manner .)
Dataset reads data and caches it in the memory. Disadvantage: High memory usage. If a large amount of processing is required for the returned data, it is better to use dataset to reduce the connection operations to the database. Advantage: Only one connection is required to close the connection to the database.
* In general, sqldatareader is used to read a large amount of data and not process the returned data in large quantities. It is more appropriate to use datset to process a large amount of returned data. The choice of sqldatareader and dataset depends on Program Function implementation.
2. executenonquery and executescalar
You do not need to return a result set for data update. executenonquery is recommended. Because no result set is returned, network data transmission can be saved. It returns only the number of affected rows. If you only need to update data, executenonquery has a low performance cost.
Executescalar only returns the first column of the First row in the result set. Use the executescalar method to retrieve a single value (for example, an ID number) from the database ). This operation requires Code Relatively small.
* You only need to update the data using executenonquery. Select the executescalar data binding option for a single value query.
3. Data Binding databinder
General Binding method <% # databinder. eval (container. dataitem, "field name") %> Use databinder. eval to bind data source (dataread or dataset ). The data type Eval will convert the data object into a string. It does a lot of work on the underlying binding and uses the reflection performance. This is because it is easy to use, but affects data performance. Let's see <% # databinder. eval (container. dataitem, "field name") %>. When dataset is bound, dataitem is actually a datarowview (if it is bound to a data reader (dataread), it is an idatarecord .) Therefore, directly converting to datarowview will greatly improve the performance.
<% # Ctype (container. dataitem, datarowview). Row ("field name") %>
* We recommend that you use <% # ctype (container. dataitem, datarowview). Row ("field name") %> to bind data. When the data volume is large, the speed can be increased by several hundred times. NOTE 2: 1. Add <% @ import namespace = "system. Data" %>. 2. Note the case sensitivity of the field name ). If it is inconsistent with the query, it may be slower than <% # databinder. eval (container. dataitem, "field name") %> in some cases. To further increase the speed, you can use the <% # ctype (container. dataitem, datarowview). Row (0) %> method. But its readability is not high.
The above is the VB.net method. In C #: <@ % (datarowview) container. dataitem) ["field name"] %>
The easiest way to view the status of each Execution Process on the page is to view details by setting the trace attribute of the page to true.
I. Using Stored Procedures:
1. Performance: stored procedures provide advanced features not available in many standard SQL languages. The function of passing parameters and executing logical expressions helps Application designers process 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 process. (The stored procedure has prepared SQL statements, so the execution speed is much faster than that of SQL statements in the program)
2. program structure: From the Perspective of program scalability, using stored procedures will facilitate future modifications to the program. For example, if the database structure changes, you only need to modify the corresponding storage structure and the calling part of the program. This part does not fall into the scope of this article, and belongs to the aspect of program structure design. So do not expand here.
3. Program security: using stored procedures can avoid SQL injection attacks.
Ii. Optimization of query statements (for SQL Server2000)
Many write SQL statements for the purpose, regardless of the SQL statement execution efficiency. Here I only provide a method to optimize the table sequence. (The optimization and principles of SQL statements will be discussed in My SQL Server2000 study notes)
The SQL statement execution efficiency can be viewed by the SQL Server2000 query analyzer.
Optimize table sequence: In general, sqlserver automatically optimizes table connections. Example: Select name, no from a join B on A. ID = B. ID join C on C. ID = A. ID where name = 'wang'
Although Table A is listed first in from, then B, and finally c. However, SQL Server may first use table C. Its selection principle is to limit the query to a single row or a few rows to reduce the total data volume to be searched in other tables. In most cases, SQL Server will make the best 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 table in the order that the table appears. In the preceding example, add: Set forceplan on ....... The execution sequence of the Set forceplan off table will be executed in the order you wrote. In the query analyzer, view the two execution efficiency types to select the table connection sequence.
* Use set forceplan to select the table join Sequence
3. Page optimization (. aspx)
Mainly for page attributes
1. enableviewstate (view State of the page ). Set this parameter to false unless otherwise specified. When viewstate is used, each object must be first serialized to viewstate, and then deserialized by returning the object. Therefore, viewstate has no cost. Minimize the number of objects in viewstate. Viewstate can be disabled in the following cases:
(1) Page Control (. ascx)
(2) The page is not returned to itself.
(3) You do not need to process control events.
(4) The control does not have dynamic or data-bound attribute values (or each Postpack is processed in the Code)
Viewstate is disabled for a single page or every page, as shown in the following figure: <% @ page enableviewstate = "false" %> for each page: in the web. in config, <pages enableviewstate = "false"/> enablesessionstate can be kept by default (if sessionstate is used on the page, it will occupy resources ). Enableviewstatemac: If there are no special security requirements, keep the default value.
2. pagelayout. page layout model. We recommend that you use flowlayout (the element is not added with the absolute positioning attribute ). gridlayout (absolute positioning attribute) uses absolute positioning to produce more code than flowlayout, mainly the positioning information of controls.
3. When releasing a project, remember to release the debug status of the page.
4. HTML language optimization. My advice is to be familiar with HTML/JavaScript and use less code automatically produced by vs. net2003. It will automatically generate useless HTML code.
5. setting SMART navigation to true can significantly improve user performance. Enabling this attribute has little impact on the client and server. It intelligently renews new parts that need to be renewed.
4. Control selection:
Select HTML control and server control. The convenience and functional implementation of server controls are incomparable to those of HTML controls. However, it is achieved at the sacrifice of server resources. My personal suggestion: If the HTML control does not meet the functions to be implemented, and cannot be implemented together with some scripting languages (such as javascrpt/VBScript. Select the server control. After selecting the server control, you should also optimize its control as much as possible, such as canceling some page states (For details, refer to the control optimization)
Server Control Selection: describes several common data controls:
DataGrid: comes with the most powerful data display control, built-in data modification, deletion, addition, paging, and many other practical functions. If you only need to display the data, try not to select the DataGrid (which stores the data in viewstate ). do not use the built-in paging function. Microsoft has done a lot of work on the underlying layer of automatic paging. Although it is easy to use, it has a high performance overhead.
Datalist: it has much less functions than DataGrid. However, the customization is much stronger. The unique multi-row data display brings us a lot of convenience. The functions that the DataGrid can implement. We recommend that you use it.
Repeater: the repeater has the least features, but is very user-defined. We recommend that you only display data. Due to the reduction of many functions, the performance of the server is minimized. Therefore, for data display, I usually select repeater and then the last DataGrid of datalist.
* Select the HTML control whenever possible. Functions that can be implemented on the client are implemented on the client (proficient in Javascript) to reduce the pressure on the server. Data Control selection sequence: repeater, datalist, and DataGrid
5. Server Control Optimization:
1. viewstate
The viewstate of the control is basically the same as the viewstate of the page. Used to save some status of the control. The processing principle is the same as the viewstate of the processing page. If you are interested, you can use the DataGrid to bind data to test the size of the data stored by viewstate. The data stored by viewstate is basically the same as the data size displayed by the DataGrid.
2. ispostpack
The default value is false. It must be set to true only when an event is generated.
Control optimization mainly depends on your familiarity with the control. The more you understand the internal operation principle of the control, the more appropriate optimization will be made for it.
Performance optimization is just the tip of the iceberg. Performance Optimization relies on the accumulation of experience and constant recognition of program operating principles.
|
