ASP Program Optimization

Program | optimization

Optimization is an issue that every developer should be concerned about. For database access, optimization is a critical issue. Data access appears to be relatively slow compared to other tasks.
Because there are so many changes in data access, it is almost impossible to propose a fixed set of optimization rules for database operations. Often this kind of question comes up with the answer: "It depends on ..." because this sort of optimization problem depends on what you are prepared to do.

9.3.1 Common ADO Techniques
Although optimization depends on the tasks that are performed, there are still some common techniques:
· Select only the columns that you want. When you open a recordset, do not automatically use the table name (that is, select *) unless you need to get all the columns. Using a separate column means that the number of data sent to or removed from the server will be reduced. Even if you need to use all the columns, naming each column individually can get the best performance, because the server doesn't have to explain what these columns are.
· Use stored procedures whenever possible. A stored procedure is a precompiled program that contains a prepared execution plan, so it executes faster than an SQL statement.
· Use stored procedures to change data. This is always faster than using the ADO method on the recordset.
· Do not create a recordset unless required. When you run an action query, you determine that the adExecuteNoRecords option is added so that the recordset is not created. You can also use this method in query state when you return only one or two field line records, such as the ID value. In this case, stored procedures and output parameters will be faster.
· Use the appropriate cursor and lock mode. If all you do is read data from a recordset and display it on the screen (for example, create a table), use the default only forward-only, read-only recordset. The less work ADO uses to maintain records and lock details, the higher the performance of the execution.

9.3.2 object variable
When traversing a recordset, one way to guarantee performance is to use object variables to point to members in the collection. For example, consider the following example of traversing a recordset containing a authors table.
While not rsauthors.eof
Response.Write rsauthors ("au_fname") & "" & _
Rsauthors ("au_lname") & "<BR>"
Rsauthors.movenext
Wend
You can use the following methods to speed up code execution while making it easier to understand.
Set FirstName = rsauthors ("au_fname")
Set LastName = rsauthors ("au_lname")

While not rsauthors.eof
Response.Write FirstName & "" & LastName & "<BR>"
Rsauthors.movenext
Wend
This uses two variables and points to a specific field in the recordset's Fidds collection (remember that the Fidds collection is the default collection). Because a reference to an object is established here, you can use an object variable instead of the actual variable, which means that the script engine's work is reduced because the number of indexes in the collection becomes less.

9.3.3 Cache Size
The size of the cache refers to the number of records that ADO reads from the data store each time, and defaults to 1. This means that when you use a server-based cursor, you must extract records from the data store whenever you move to another record. For example, if you increase the cache size to 10, the number of records per read ADO buffer will change to 10. If you access records in the cache, ADO does not need to fetch records from the data store. When you access a record that is outside the cache, the next batch of records is read into the cache.
You can set the size of the cache by using the recordset's CacheSize property.
Rsauthors.cachesize = 10
You can change the size of the cache at any time in the recordset's lifetime, but the new quantity is valid only after the next batch of records is fetched.
Like many techniques for improving performance, caching does not have the best general size because it changes depending on the task, data, and provider. However, increasing the size of the cache from 1 can always improve performance.
If you want to see this, you can use SQL Server Profiler and see what happens when you open a recordset with the default cache, and compare what happens when you increase the cache. Increasing the size of the cache not only reduces the amount of ADO work, but also lowers the workload of SQL Server.

9.3.4 Database Design
Don't want to be programmed to improve access to the data, you should consider the design of the database at the same time. There is no plan for more discussion of database design, but the following points should be considered when using the Web site database:
· Real-time data: It is important to ensure that the data content is always up to date when it is displayed to the user. How often does the content of a catalog change, taking a product catalog as an example? If the directory does not change frequently, you do not have to extract the data from the database every time. It should be a better idea to create a static HTML page from the database once a week or when the data changes.
· Index: If you need to do a large number of queries on a table without performing too many data-adding operations, consider indexing the table.
· Non-normalization: If the site has two different purposes (data maintenance and data analysis), then consider using some of the less-normalized tables to facilitate data analysis. You can provide separate, completely irregular, but normally updated analysis tables, and you can even move these analysis tables to another machine to improve performance.
· Database statistics: If you are using SQL Server 6.x, you should update the statistics regularly if the data is added or deleted. These statistical results are used to produce a query plan that affects the operation of the query. Read the update statistic in SQL Books Online for more detailed information.       This task is completed automatically in SQL Server 7.0. These are very basic database design techniques, but they may not be taken into account if you are only immersed in the ASP code.
9.3.5 data Cache
The first thing to note is that both the data cache and the recordset cache are used to improve performance, but they are irrelevant. The data cache is a temporary data store that allows data in the cache to be used instead of regenerating new data. This applies only to data that is not frequently changed but is accessed multiple times.
One of the easiest ways to cache data in an ASP is to use application and session-scoped variables. For example, suppose there are some pages that need to select a book type. Normally, you might create a include file that contains the following functions.
<%
Function Booktypes ()

Dim Rsbooktypes
Dim Strquote

Strquote = Chr (34)

Set rsbooktypes = Server.CreateObject ("ADODB. Recordset ")

' Get the book types
Rsbooktypes.open "Usp_booktypes", strconn

Response.Write "<select name=" & Strquote & Lstbooktype & strquote & ">"
While not rsbooktypes.eof
Response.Write & "<OPTION>" & Rsbooktypes ("Type") & "</OPTION>"
Rsbooktypes.movenext
Wend
Response.Write & "</SELECT>"

Rsbooktypes.close
Set rsbooktypes = Nothing

End Function
%>
This is just a call to a stored procedure to get the type of book and create a select list. The disadvantage of the preceding code is that the database must be accessed each time the function is invoked. So, modify this function again.
<%
Function Booktypes ()

Dim Rsbooktypes
Dim Strquote
Dim strlist

' The ' if ' the list is in the cache
Strlist = Application ("Booktypes")
If strlist = "" Then
' Not cached, so builds up list and cache it
Strquote = Chr (34)

Set rsbooktypes = Server.CreateObject ("ADODB. Recordset ")

' Get the book types
Rsbooktypes.open "Usp_booktypes", strconn

Strlist = "<select name=" & Strquote & Lstbooktype & strquote & ">"
While not rsbooktypes.eof
Strlist = strlist & "<OPTION>" & Rsbooktypes ("type") & "</OPTION>"
Rsbooktypes.movenext
Wend
Strlist = strlist & "</SELECT>"

Rsbooktypes.close
Set rsbooktypes = Nothing

' Check the list
Application ("booktypes") = Strlist
End If

Booktypes = Strlist

End Function
%>
This code does not just open the recordset, it checks to see if the value of the application variable BookType is empty. If not empty, the contents of the variable are used. If blank, open the recordset as before. Obviously, once the first person runs this routine, the data is cached, so this is only useful for data that is not always changing.
If you want to cache data on a user basis, you can use a session-scoped variable, but note that the session has a validity period. After expiration, the session-level variable will be canceled with the conversation, and the code may end up running.
Using the Web Application Stress (was) tool, the analysis results of table 9-4 are obtained:
Table 9-4 Analysis results obtained using the WAS tool

It is obvious that performance has improved. But do not use the above method to cache all content. After all, this approach applies only to data that has been formatted for display. In addition, consider that if the Web server serves only a specific person, it is hardly a typical Web server usage. By using was, you can simulate multiple users on a single server, which allows you to test your application more realistically.
By simulating a certain number of users, the Web Application Stress tool can test the endurance of a Web page. The tool has a simple graphical interface that is easy to use. You can get more information from http://homer.rte.microsoft.com/, or you can download the tool.
Cache objects
What do I do if I want to cache unformatted data? Can it be used differently in different places? Of course, you can do this with application or session variables. Consider the title of the book. You may want to use this title on multiple pages, perhaps displaying all the headings in a table, or displaying them in a list box for user selection, and so on. You might think that you can cache the recordset itself without caching the HTML text that contains the label.
You can cache objects in application or session variables, but there are two major issues to note:
· Objects stored in the application variable must support free threading, and therefore must be either a free thread object or a two-thread object. This means that components created by VB cannot be cached in the application variable.
· Holding a unit thread object in session state means that the thread that created the object is the only thread that is allowed to access it. As a result, IIS cannot perform thread management well because any page that attempts to access the object must wait for the original thread to serve the page. This will kill any opportunity to extend the application.
For a discussion of threading issues, see Chapter 15th.
By default, ADO is loaded as a unit thread object, primarily because some OLE DB providers are not thread-safe. In the ADO installation directory, there is a registry file that transforms ADO into a two-threading model, making it safe for ADO objects to be stored in application and session objects.
You might think that all the problems are solved by using various types of objects to get a significant speed boost, but that's not necessarily the point. Many people have realized that since connecting to a database is a relatively expensive operation, caching connection objects can save a lot of time when connecting again. Yes, but caching the connection object means that the connection will never shut down, so the connection to the cache pool is less efficient. The implicit idea of a connection cache pool is actually to reduce the resources used on the server, while caching objects in the ASP state obviously does not reduce the use of resources. In fact, they are also increased, because each one of the objects will consume the resources of the server, which will greatly reduce the efficiency of the Web server for a busy site.
So the connection object should not be stored, but for the Recordset object, especially the disconnected recordset? Assuming that ADO has changed from a cell thread to a two-thread, there's no reason to not do so, as long as you know exactly what you're doing. Don't think this will automatically improve the performance of your ASP pages. Each cached recordset occupies the server's resources in terms of memory and ASP management, so do not cache large recordsets.
Another trick is to use the GetRows method of the recordset to convert the recordset to an array. Because arrays are not as affected by thread problems as Recordset objects, they are ideal for variables that are used for session layers. However, it also takes up server resources and must also consider the time to process the array.
Caching techniques are not necessary to build your own applications.