Program and database optimization methods for asp.net performance optimization

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 tutorial 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 selection of SqlDataReader and Dataset depends on the implementation of program functions.

Database access performance optimization

Database Connection and shutdown

To access database resources, you need to create a connection, open a connection, and close the connection. These processes need to exchange information with the database multiple times for authentication, which consumes server resources. ASP. NET provides a Connection Pool to improve the impact of enabling and disabling databases on performance. The system places the user's database connection in the connection pool. If necessary, the connection is taken out. When the connection is closed, the connection is withdrawn and the next connection request is waited. The size of the connection pool is limited. If you still need to create a connection after the connection pool reaches the maximum limit, the performance will be greatly affected. Therefore, after a database connection is established, the connection is enabled only when operations are required. The connection is closed immediately after use, so as to minimize the time for opening the database connection and avoid exceeding the connection limit.

Use Stored procedures

Stored procedures are a set of pre-compiled SQL statements stored on the server, similar to the batch processing files in the DOS system. Stored procedures provide the ability to access databases immediately and process information quickly. Using the stored procedure can avoid multiple compilation of commands. After one execution, the execution plan will reside in the cache. In the future, you only need to directly call the binary code in the cache. In addition, the stored procedure runs on the server and is independent from the ASP. NET program to facilitate modification. The most important thing is that it can reduce the transmission of database operation statements over the network.

Optimize query statements

In ASP. NET, the ADO Connection consumes a considerable amount of resources. The longer the SQL statement runs, the longer the system resources are occupied. Therefore, try to use optimized SQL statements to reduce execution time. For example, a query statement that does not contain a subquery statement makes full use of indexes.

String operation performance optimization

Use the ToString method of the value type

When connecting strings, you often use the "+" sign to directly add numbers to strings. This method is simple and can get the correct results. However, because different data types are involved, the numbers must be converted to the reference type through the boxing operation before they can be added to the string. However, the packing operation has a great impact on the performance, because during such processing, a new object will be allocated in the managed heap, and the original value will be copied to the newly created object. The value type ToString method can be used to avoid packing and improve application performance.

Use StringBuilder class

The String class object cannot be changed. In essence, the re-assignment of the String object re-creates a String object and assigns the new value to the object, the ToString method does not significantly improve the performance. When processing strings, it is best to use the StringBuilder class. Its. NET namespace is System. Text. This class does not create a new object, but directly performs operations on strings through Append, Remove, Insert, and other methods, and returns the operation results through the ToString method. Its definition and Operation statement are as follows:

Reference content is as follows:
Int num;
System. Text. StringBuilder str = new System. Text. StringBuilder (); // create a string
Str. Append (num. ToString (); // add the numeric value num
Response. Write (str. ToString); // display the operation result
 

Optimize configuration files for Web server computers and specific applications to meet your specific needs

By default, ASP. NET configuration is set to enable the widest range of features and adapt to the most common solutions. Therefore, application developers can optimize and modify some of the configurations based on the features used by the application to improve application performance. The following lists some options you should consider.

Only enable authentication for the desired application.

By default, the authentication mode is Windows, or NTLM is integrated. In most cases, it is best to disable authentication in the Machine. config file and enable authentication in the Web. config file for applications that require authentication. Configure the application based on the appropriate request and response encoding settings. ASP. NET default encoding format is UTF-8. If your application is strictly ASCII, configure the application to use ASCII for a slight performance improvement.

Disable AutoEventWireup for applications.

In the Machine. config file, set the AutoEventWireup attribute to false, which means that the page does not match the method name with the event or hook the two (for example, Page_Load ). If Page developers want to use these events, they need to override these methods in the base class (for example, they need to rewrite Page. OnLoad for Page loading events, rather than using the Page_Load method ). If AutoEventWireup is disabled, the page will be slightly improved by leaving the event connection to the page author rather than automatically executing it.

Remove unused modules from the request processing pipeline.

By default, all functions of nodes in the Machine. config file of the server computer are retained to active. Based on the features used by the application, you can remove unused modules from the request pipeline for a slight performance improvement. Check each module and its functions and customize it as needed. For example, if you do not use session status and output cache in an application, you can remove them from the list so that requests do not perform other meaningful processing, you do not have to execute the code for entering and leaving each module.

Make sure to disable the debugging mode.

Remember to disable debug mode before deploying production applications or performing any performance measurements. If the debug mode is enabled, the performance of the application may be greatly affected.

For applications that rely heavily on external resources, consider enabling network gardening on a multi-processor computer.

ASP. NET process model helps enable scalability on a multi-processor computer, distribute work to multiple processes (one CPU per worker), and each process sets the processor relationship to its CPU. This technology is called Web gardening. Enable Web gardening for your application if your application uses a slow database server or calls a COM object with external dependencies (only two possibilities are mentioned here. However, before deciding to enable Web gardening, you should test how applications are executed in the web garden.

Cache data and page output whenever possible

ASP. NET provides some simple mechanisms to cache the output or data of these pages when you do not need to request dynamic computing page output or data for each page. In addition, by designing pages and data requests to be cached (especially in areas where the site is expected to have a large amount of communication), you can optimize the performance of these pages. Compared with any Web form function of. NET Framework, using the cache appropriately can improve the performance of the website. Sometimes this increase is more than an order of magnitude. There are two points to note when using the ASP. NET Cache mechanism. First, do not cache too many items. Each item in the cache has an overhead, especially for memory usage. Do not cache items that are easy to recalculate and rarely used. Second, the period of validity allocated to cached items should not be too short. Items that expire soon will result in unnecessary turnover in the cache, and often lead to more code cleanup and garbage collection work. If you are concerned about this issue, monitor the Cache Total Turnover Rate performance counters associated with ASP. NET Applications performance objects. High turnover rate may indicate problems, especially when items are removed before expiration. This is also called memory pressure.

Select a Data View mechanism suitable for pages or applications

There are often important trade-offs between convenience and performance based on how you choose to display data on a Web form page. For example, The DataGrid Web server control may be a convenient and quick way to display data, but its overhead is often the largest in terms of performance. In some simple cases, it may be very effective to generate an appropriate HTML to present your own data, but custom and browser targeting will quickly offset the extra benefits you get. The Repeater Web server control is a compromise between convenience and performance. It is efficient, customizable, and programmable.

Use the SqlDataReader class for fast data-only cursor

The SqlDataReader class provides a method for reading data streams only retrieved from the SQL Server database. If you are allowed to use an ASP. NET application when creating it, the SqlDataReader class provides higher performance than the DataSet class. This is because SqlDataReader uses the local network data transmission format of SQL Server to directly read data from the database connection. In addition, the SqlDataReader class implements the IEnumerable Interface, which allows you to bind data to server controls. For more information, see SqlDataReader class. For information about how ASP. NET accesses data, see access data through ASP. NET.

Use SQL Server stored procedures for data access

Among all data access methods provided by. NET Framework, SQL Server-based data access is a recommended choice for generating high-performance, scalable Web applications. When using a hosted SQL Server provider, you can use compiled stored procedures instead of special queries to obtain additional performance improvements.

Avoid single-threaded unit (STA) COM components

By default, ASP. NET does not allow any Stas COM component to run on the page. To run them, you must include the ASPCompat = true attribute in the. asp tutorial x file in the @ Page command. In this way, the thread pool used for execution is switched to the STA thread pool, and HttpContext and other built-in objects can be used for COM objects. The former is also a kind of performance optimization, because it avoids any call to mail multi-threaded units (MTA) to the STA thread. Using the sta com component may greatly damage the performance and should be avoided as much as possible. If you must use the sta com component, such as in any interop scheme, you should make a large number of calls during execution and send as much information as possible during each call. Also, be careful not to create any sta com components during page construction. For example, in the following code, the MySTAComponent created by a thread will be instantiated during page construction, and this thread will not run the STA thread of the page. This may have a negative impact on performance, because to construct a page, you must complete the sending and receiving process between the MTA and the STA thread.

Reference content is as follows:
<% @ Page Language = "VB" ASPCompat = "true" %>
<Script runat = server>
Dim myComp as new MySTAComponent ()
Public Sub Page_Load ()
MyComp. Name = "Bob"
End Sub
</Script>
<Html>
<%
Response. Write (myComp. SayHello)
%>
</Html>
 

The preferred mechanism is to postpone object creation until the above code is executed in the STA thread in the future, as shown in the following example.

Reference content is as follows:
<% @ Page Language = "VB" ASPCompat = "true" %>
<Script runat = server>
Dim myComp
Public Sub Page_Load ()
MyComp = new MySTAComponent ()
MyComp. Name = "Bob"
End Sub
</Script>
<Html>
<%
Response. Write (myComp. SayHello)
%>
</Html>
 

We recommend that you construct any COM components and external resources as needed or in the Page_Load method. Never store any Stas COM component in shared resources that can be accessed by other threads other than the threads that construct it. Such resources include resources such as cache and session status. Even if the STA thread calls the sta com component, only the thread that constructs the sta com component can actually serve the call, which requires sending and processing calls to the creator thread. This mail may cause significant performance loss and scalability problems. In this case, consider the possibility of making the COM component an mta com component, or migrate code to make the object a hosted object.

Migrate call-intensive COM components to managed code

. NET Framework provides a simple way to interact with traditional COM components. The advantage is that the new platform can be used while the existing investment is retained. However, in some cases, retaining the performance overhead of the old component makes it worthwhile to migrate the component to the managed code. Every situation is different. The best way to determine whether to migrate components is to measure the running performance of Web sites. We recommend that you study how to migrate any COM component that requires a large number of calls for interaction to managed code. In many cases, it is impossible to migrate legacy components to managed code, especially when a Web application is initially migrated. In this case, one of the biggest performance barriers is to mail data from an unmanaged environment to a hosted environment. Therefore, in interactive operations, perform as many tasks as possible at any end, and then make a large call instead of a series of small calls. For example, all strings in the Common Language Runtime Library are Unicode, so all strings in the component should be converted to Unicode format before calling managed code. In addition, after processing any COM objects or local resources, release them. In this way, other requests can use them and minimize the performance problems caused by later requests to the garbage collector to release them.

Use early binding in Visual Basic. NET or JScript. Code

In the past, one of the reasons why developers like to use Visual Basic, VBScript, and JScript is the so-called "non-type" nature. Variables do not need explicit type declarations and can be created simply by using them. When the data is allocated from one type to another, the conversion is automatically executed. However, this convenience will greatly damage the performance of the application. Visual Basic now supports type-safe programming by using the Option Strict compiler command. For backward compatibility, ASP. NET does not enable this option by default. However, to achieve optimal performance, we strongly recommend that you enable this option on the page. To enable Option Strict, include the Strict attribute in the @ Page command, or include this attribute in the @ Control command for user controls. The following example shows how to set this attribute and calls four variables to show how this attribute causes a compiler error.

Reference content is as follows:
<% @ Page Language = "VB" Strict = "true" %>
<%
Dim B
Dim C As String
'This will cause a compiler error.
A = "Hello"
'This will cause a compiler error.
B = "World"
 

Reference content is as follows:
'This will not cause a compiler error.
C = "!!!!!! "
'But this will cause a compiler error.
C = 0
%> Dim B
Dim C As String
'This will cause a compiler error.
A = "Hello"
'This will cause a compiler error.
B = "World"
'This will not cause a compiler error.
C = "!!!!!! "
'But this will cause a compiler error.
C = 0
%>
 

JScript. NET also supports non-type programming, but it does not provide compiler commands that force early binding. If any of the following conditions occurs, the variable is bound in the late stage: it is explicitly declared as an Object and is a field of the class without type declaration, is a special function or method member without explicit type declaration, and cannot deduce the type from its use. The last difference is complicated, because if the JScript.. NET compiler can deduce the type based on the usage of the variable, it will be optimized. In the following example, variable A is bound early, but variable B is bound late.

Reference content is as follows:
Var;
Var B;
A = "Hello ";
B = "World ";
B = 0;
 

To achieve optimal performance, assign a type to the JScript.. NET variable when declaring it. For example, var A: String.

Make all modules in the request pipeline as efficient as possible

All modules in the request pipeline have the opportunity to be run in each request. Therefore, it is critical to quickly trigger code when a request enters and leaves the module, especially in the code path that does not use the module function. The throughput test is executed when modules and configuration files are used and not used separately, which is very useful for determining the execution speed of these methods.

Use HttpServerUtility. Transfer to redirect between pages of the same application
Use the Server. Transfer syntax to avoid unnecessary client redirection by using this method on the page.

Adjust the number of threads for each auxiliary process of the application if necessary

The request structure of ASP. NET tries to strike a balance between the number of threads executing the request and the available resources. An application that uses sufficient CPU power is known. This structure determines the number of requests that can be executed simultaneously based on the CPU power available for requests. This technology is called Thread-control. However, in some conditions, the thread-control algorithm is not very effective. By using the Pipeline Instance Count performance counter associated with the performance object of ASP. NET Applications, you can monitor thread-control in PerfMon. When a page calls external resources, such as database access or XML Web services requests, the page requests usually stop and release the CPU. If a request is waiting for processing and a thread in the thread pool is free, the waiting request will start to be processed. Unfortunately, sometimes this may lead to a large number of concurrent requests and many waiting threads on the Web server, which have adverse effects on server performance. Generally, if the gate factor is the response time of external resources, too many requests are waiting for resources, which is not helpful for the Web server throughput. To alleviate this problem, you can manually set the number of threads in the process by changing the maxWorkerThreads and maxIOThreads attributes of the Machine. config configuration file node.

Note: the auxiliary thread is used to process ASP. NET requests, while the IO thread is used to provide services for data from files, databases, or XML Web services. The value assigned to these attributes is the maximum number of threads in each CPU type in the process. For dual-processor computers, the maximum number is twice the set value. For a four-processor computer, the maximum value is four times the set value. In any case, it is best to change the default value for computers with four or eight CPUs. The default value can be used for computers with one or two processors. However, for computers with more processors, one hundred or two hundred threads in a process may cause more disadvantages. Note that too many threads in the process will often reduce the server speed, because the operating system will spend the CPU cycle on the maintenance thread rather than processing the request due to extra context switching.

Use the garbage collector and automatic memory management of the Common Language Runtime Library as appropriate
Be careful not to allocate too much memory to each request, because the garbage collector must do more work more frequently. In addition, do not point unnecessary pointers to objects because they will keep objects active and avoid objects containing the Finalize method as much as possible, because they will lead to more work in the future. In particular, do not release resources in Finalize calls, because resources may always consume memory before they are recycled by the garbage collector. At last, this problem often causes a devastating impact on the performance of the Web server environment, because it is easy to exhaust a specific resource while waiting for Finalize to run.

If there is a large Web application, you can consider executing pre-Batch compilation.

Each time a first request to a directory is sent, a batch compilation is performed. If pages in the directory are not analyzed and compiled, this function analyzes and compiles all pages in the directory in batches to make better use of disks and memory. If this takes a long time, a single page is quickly analyzed and compiled so that the request can be processed. This feature brings ASP. NET performance benefits because it compiles many pages into a single assembly. Accessing a page from a loaded assembly is faster than loading a new assembly on each page. The disadvantage of Batch compilation is that if the server receives many requests for pages that have not yet been compiled, the performance may be poor when the Web server analyzes and compiles them. To solve this problem, you can execute pre-Batch compilation. Therefore, you only need to request a page from the application before it is activated, regardless of the page. Then, when the user visits your site for the first time, the page and its assembly will have been compiled. There is no simple mechanism to know when Batch compilation will take place. Wait until the CPU is idle or no more compiler processes (such as csc.exe (C # compiler) or vbc.exe (Visual Basic Compiler) start. Avoid changing the assembly in the bin directory of the application. Changing the page will cause re-analysis and compilation of the page, while replacing the assembly in the bin directory will lead to full re-compilation of the Directory. On a large site that contains many pages, a better way is to design different directory structures based on the frequency of page replacement or assembly planned. Pages that do not often change can be stored in the same directory and pre-compiled at a specific time. Frequently changed pages should be in their own directories (each directory can contain hundreds of pages at most) for quick compilation. Web applications can contain many subdirectories. Batch compilation occurs at the directory level, not the application level.

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. For example:

Select name, no from

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 join sequence of the table.

3. Page optimization (. ASPx)

Mainly for the following 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 on a single page or every page, as shown below:

Single Page: <% @ Page EnableViewState = "False" %>

Each page: in web. config, <Pages EnableViewState = "false"/>

EnableSessionState: retain the default value (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/webpage special effects and use less code automatically produced by vs. net2003. It will automatically generate some 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.
IV. 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. (Recommended one-page control: http://webdiyer.europe.webmatrixhosting.net/default.ASPx)

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
V. 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 for testing.

The size of the data stored by viewstate is as large as the data stored by viewstate and the size of the data displayed by the Datagrid.

Is equivalent.

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 accumulation of experience and constant recognition of program operating principles.

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 less code than executing the operations required to generate a single value for the returned data using the ExecuteReader method.

* You only need to update the data using ExecuteNonQuery. Use ExecuteScalar to query a single value.

Data binding selection

3. Data binding DataBinder
General binding method <% # DataBinder. Eval (Container. DataItem, "Field name") %>

Binding with DataBinder. eval does not require you to care about the 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.

4. Use stored procedures:
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)
Program Structure: From the perspective of program scalability, using stored procedures will facilitate subsequent 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.

5. 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. For example:

Select name, no from

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

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 on a single page or every page, as shown below:

Single Page: <% @ Page EnableViewState = "False" %>

Each page: in web. config, <Pages EnableViewState = "false"/>

EnableSessionState: retain the default value (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.

2. 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. (Recommended one-page control: http://webdiyer.europe.webmatrixhosting.net/default.aspx)

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

III. 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 for testing.

The size of the data stored by viewstate is as large as the data stored by viewstate and the size of the data displayed by the Datagrid.

Is equivalent.

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 is the accumulation of experience and constant recognition of program operating principles.