Optimal method for ADO database access

Almost all articles about the ADO database access performance analysis, it is considered that the performance of binary components always exceeds the ASP code executed. In fact, this is wrong. As can be seen from the test results of this article, sometimes the performance of the ASP code far exceeds the component.

I. Introduction

"The earth is flat ...";

"The sun turns around the earth ...";

"Always access databases through components ..."

There are two common features above three propositions: First, they have been considered correct; secondly, these three propositions are actually wrong.

We have already read countless articles. It is recommended to use component package business logic and database access in Internet applications, but the actual performance data of this technology is rarely seen. With the distribution of Windows 2000, the performance performance of IIS platforms, especially ASPs has also improved significantly. Due to the multi-Binding internal objects, template buffers, etc., ASP has first-class performance performance through ADO access database, format and outputs record sets through ADO.

As can be seen from this test results, ASP is better than component in ADO database, and the difference in the formatting in some cases has reached an incredible degree of incredibility. For most Internet applications, performance is always a primary factor, so it is important to use testing tools to perform testing of the program for complete testing of the program before the optimal programs are determined according to rumors or book knowledge.

All three sets of code (ASP, VB, and C ) of this article were optimized before testing. In order to ensure that the code in which the test is involved, its coding method and test results are the best in their respective fields, they have been tested multiple times. Some optimization work has not been conducted, which is to make the code more truly reflect typical situations in the actual application environment.

Second, the test environment

This test is only performed on the Windows 2000 platform, and the test results on the Windows NT platform may be large, so the results obtained from the test do not apply to the Windows NT platform. Below is a schematic diagram of the system used in this test and its description:

Due to test clients and web servers, the physical location of the database server is different, and the client is connected to the web server through three Cisco 2924 switches. All of these machines are in the same building, but the server is located in the data center, and the test client is located in another room, and the client is connected to the data center through a 400MB Fast EtherChannel connection.

Under this configuration, the overhead caused by the network delay of the test case is very small. The traffic between the switches between the daily operations is always less than 5% of its capabilities.

Third, test code

Since this is a test from the ASP, VB components, C components through ADO, the function of test code is limited to creating a form from the result recordset. All test programs can be downloaded from this article. The execution flow of these programs is similar, as follows:

Create / open a database connection with ODBC DSN

Create a Command object (set its type to adcmdstoreProc)

Specify parameters of the number of records returned

Execute command, return to record set

Turn off the record set and connection, release the memory occupied by these objects.

It can be confirmed that the above method has the fastest database access speed because:

Storage procedure access speed is fast than dynamic SQL, even if the SQL scheduled cache function of SQLServer 7.0 is enabled.

Use the Command object and explicitly specify the parameter to be much faster than the incoming query string, because the OLEDB provider does not need to analyze the type of query and all the types of parameters passed to the stored procedure.

The ODBC connection pool avoids creation of physical connections for each open command. Each closing connection will release the open connection back to the connection pool.

Structure, this is to allow test programs to simulate the actual recordset processing process more accurately.

The HTML code returned to the client is the

structure created from a two column recordset. All test programs use the While loop to traverse record sets, rather than using the faster getString method directly from the recordset data to the structure, which is to make the test program more accurately simulate the actual recordset processing process. The stored procedure used by the test is extracted from the table, and the number of returns to the record is passed to the stored procedure in parameters.

Tests runs with a variety of different records and threads (quantity of concurrent requests). The number of records ranges from 0 to 100 lines, but there is no test number of returns to the number of records over 100 rows. This is because considering that most design good web applications do not have such a large-scale recordset data extraction and formatting.

The number of threads varies from 25 to 2000. In all tests, the IIS / COM server's processor utilization is greater than 99%, but when the number of threads is small (25, 50), the ASP queue length is very small (or 0). Although these tests are also run when the number of threads is set to 1, 5, 10, but in addition to the processor utilization, the test number of these threads does not exhibit any essential difference.

The tool used by the test is Microsoft's Web Application Stress Tool, the basic settings of the test script are as follows:

All test scripts run when the network utilization is the lowest. In addition, there is no other operations on the IIS / COM server and SQL Server during the test.

IIS's Application Protection is set to "low", which makes the application runs the best performance, especially for the test of COM library applications. This setting also allows all tasks to run in the INetInfo process.

The five programs participating in the test are: ASP, VB components (COM library applications), C components (COM library applications), VB components (COM server applications), C components (Server applications for COM ).

In all test programs, the test client's load has not exceeded 35% of the processor utilization, and the memory is very small.

Some optimization work is not done, which is to better reflect the current mainstream application better. For example, this article is tested using an ODBC system DSN to establish a database connection, and the SQL Server provider that connects to the OLEDB will increase the overall performance by approximately 5-10%.

Fourth, test results

Maybe you have already guess the winners of this test should be ASP. Let's take a look at the specific test results to explore the conclusions obtained from these test data.

The main statistics of this test are as follows:

The number of records recorded in the first group of tests is set to 10, and the number of threads varies between 25 to 2000. The main metrics of performance, that is, the REQUESTS PER Seconds is as follows:

As can be seen from the above figure, ASP is more than 30% of the performance ratio and its closest opponent VB (in Proc - process), which is more than 2 times faster than other methods. It is worth noted that the performance of VB (In-PROC) increases slightly as the number of threads increases. However, when the number of threads exceeds about 250, TTFP and ASP Requests Queued have no significance of no longer have the normal operation of a single server. In fact, many people will think that even 250 is too high. Therefore, the maximum number of threads does not exceed 250 in the test number.

The script of the test tool does not generate any delay. Therefore, as long as the response to a thread arrives, the new request is always sent immediately. Before analyzing the number of test results, let's take a look at the test results of the other two test indicators TTFB and HITS.

As we can expect, ASP has a faster TTFB under all threads. The following table is compared to the increase in load on the ASP Requests Queued and the corresponding TTFB, all data is given in the form of Requests Queued-TTFP, and TTFB is still in milliseconds.

It can be seen that there is a need to add more servers when the load is as high as a certain degree (~ 50-100 thread range). However, this article test still includes these high load situations, which is to observe whether there may be different changes.

The final performance metric HITS test results also showed the same tendency to test, and the performance of ASP is still the best.

The next test step is to observe the performance performance of each test program when the number of records of records increases. The number of records tested here includes: 20, 30, 50, and 100. If you have read a lot of this article, it may guess that the processing load due to the number of records will increase, and the component will exhibit better performance. However, it is not. ASP still maintains a leading advantage for other various methods. Below is a test result of the number of different threads.

Even if the number of records recorded, there is no change in performance difference between ASP and other methods. The complete test results can be found in Appendix A, this article. If you have time, you may wish to test the same testing for other interests. All the code used in this article is provided in Appendix B. You can use these code yourself to test, or make some modifications if necessary.

V.

In summary, there is no method in the Windows 2000 platform that can exceed the ASP in the performance of the pure ADO operation. Although it is more close to ASP (and thus) as compared with components in COM library applications, they are still slightly short compared with the performance of ASP. In fact, even if the performance of VB components run within the process is 30% higher than ASP. However, in order to protect the INetInfo process when the component is generated in the application, many applications are still running their COM applications in a dedicated service process (DLLHOST.exe), in this common case, ASP can provide 2 ratio 1 performance advantage!

We hope that this article has successfully explained such a question: Using components in an Internet application, it is not a best solution. Be sure to perform a complete test before implementing a certain solution, this is extremely important because the final result of the program may be completely different from what you want (or you see, hear).

If the size of the Internet app is medium or relatively large, performance will become the primary consideration, more important than the reusability of the code. But the main applications among reality are rare (if any) is also the optimal optimal program.

Of course, use components also have its benefits. Components are often the best choice for encapsulating certain types of business logic, especially in cross-system integration applications. However, in some cases, this seems to have joined an extreme, returning more deeply understanding the existing technical platform, truly understanding the processing, collection and transmission of data according to the application's business situation, which is wise. Many times we will find that the principle of the Outu razor has mastered the truth: the easiest solution is often the best solution when involving complex systems (and it is likely to be the most easily measured method!).

Although this article only involves a limited ADO database access operation, similar results are obtained by more complex scripts and components. If you are using a Windows 2000 platform, it is clear that you should consider the performance of the program in accordance with this article in accordance with this article. If your ultimate goal is performance, you will find yourself replace some components to ASP. Perhaps even you will be surprised. Please download other information from this article here.

---