ado| Access | data | Database almost all articles about ADO database access performance analysis, all believe that the binary component performance is always more than the interpretation of the implementation of ASP code. In fact, this is wrong. From the test results of this article, we can see that some times the performance of ASP code far exceeds the component.
First, the introduction
"The Earth is flat ..."
"The sun goes round the earth ..."
"Always access the database through components ...",
The above three propositions have two common features: first, they have all been thought to be true, and secondly, the three propositions are actually wrong.
We've all read countless articles suggesting that components encapsulate business logic and database access in Internet applications, but the actual performance data on this technology is rarely seen. With the release of Windows 2000, the performance of the IIS platform, especially the ASP, has been significantly improved. Because of the improvement of the early-binding, template buffering and so on, the ASP has the first-class performance in all aspects, such as accessing the database through ADO, formatting and outputting the recordset and so on.
From the test results, we can see that ASP is better than component in ADO database access, recordset format, and in some cases the difference between the two is unbelievable. Performance is always the primary factor for most Internet applications, so it is important to use the test tools to test the scenario thoroughly before you can determine the optimal solution based on hearsay or book knowledge.
All three sets of code in this article (ASP,VB and C + +) have been optimized before testing. To ensure that the coding and testing results of the code participating in the test are optimal in their respective fields, they have been tested several times. Some optimizations have not been done, in order for the code to reflect more realistically the typical situation that might occur in the actual application environment.
Second, test environment
This test is only done on Windows 2000 platforms, and test results may vary significantly on the Windows NT platform, so the results of the tests do not apply to the Windows NT platform. The following is a schematic and description of the system used for this test:
The client connects to the Web server through three Cisco 2924 switches because the test client and the Web server, and the database server, are located in a different physical location. All of these machines are in the same building, but the server is in the data center and the test client is in another room, and the client connects to the data center switch via a 400Mb Fast EtherChannel.
In this configuration, the overhead of the test case's network latency is very small. The traffic between the switches is always less than 5% of the capacity in the daily run.
Third, test code
Since this is a test of database access from ASP, VB components, and C + + components through ADO, the functionality of the test code is limited to creating a table from the result recordset. All test programs can be downloaded from the back of this article. The process of executing these programs is similar, as follows:
Create/Open a database connection using ODBC DSN
Create a Command object (set its type to Adcmdstoreproc)
Specify parameters to return the number of records
To execute a command to return a recordset
Closes the recordset and the connection, freeing the memory occupied by these objects.
It is possible to verify that the above methods have the fastest database access speed because:
Stored procedures are faster to access than dynamic SQL, even if SQL Server 7.0 is enabled to cache functionality.
Using the Command object and explicitly specifying parameters is much faster than passing in a query string because the OLE DB provider does not need to parse the query type and all the types of parameters passed to the stored procedure.
The ODBC connection pool avoids creating a physical connection for each open command. Each shutdown will release the open connection back to the connection pool.
Structure, which is designed to allow the test program to more accurately simulate the actual recordset processing process. The HTML code returned to the client is the <table> structure created from a two-column recordset. All test programs iterate through the recordset using a while loop, rather than getting a <table> structure directly from the recordset data using a faster GetString method, in order to allow the test program to more accurately simulate the actual recordset process.
The stored procedure used to test extracts records from the table, and the number of returned records is passed to the stored procedure as a parameter.
The test runs in a number of different records and in the number of threads (concurrent requests). The number of records ranges from 0 lines to 100 lines, but there are no more than 100 rows of return records, since most well-designed Web applications do not have such a large set of recordset data extraction and formatting operations.
The number of threads varies from 25 to 2000. In all tests, the processor utilization of the iis/com+ server is greater than 99%, but the number of threads is low (25,50), and the ASP queue length is very small (or 0). Although these tests have been set to run at 1, 5, and 10 o'clock, the test for the number of threads in addition to processor utilization does not show any intrinsic difference.
The tools used for testing are Microsoft Web application Stress Tool, and the basic setup of the test scripts is as follows:
All test scripts run at the lowest network utilization level. Additionally, there are no other actions on the iis/com+ server and SQL Server during the test.
The "Application Protection" of IIS is set to low, which makes the application run with the best performance, especially in the case of COM + library application testing. This setting also allows all tasks to run within the Inetinfo process.
The five programs that participate in the test are: the ASP,VB component (COM + library application), the C + + component (COM + library application), the VB component (COM + server application), and the C + + component (server application for COM +).
In all test programs, the load on the test client has not been more than 35% processor utilization and memory footprint is low.
Some of the optimizations have not been done, in order for the test code to better reflect the current mainstream application. For example, this article tests the use of ODBC System DSN to establish a database connection, which will increase overall performance by approximately 5-10%, using the SQL Server provider that is connecting to OLE DB instead.
Iv. test Results
Perhaps you have guessed from the title of this article that the winner of this test should be an ASP. Let's look at the specific test results and explore the conclusions obtained from these test data.
The main statistical items of this test are as follows:
The number of records in the first set of tests is set to 10, and the number of threads varies between 25 and 2000. The main metrics of performance, i.e. requests per seconds results are as follows:
As can be seen from the above figure, ASP in performance than and its closest competitor VB (in proc-process, that is, COM + library applications) on average 30% faster than other methods faster than twice times. It is worth noting that the performance of VB (IN-PROC) increases slightly as the number of threads increases. However, when the number of threads exceeds approximately 250, the TTFP and ASP Requests queued are no longer relevant to the normal operation of a single server. In fact, many people think that even the 250 is too high. Therefore, the maximum number of test threads in the recorded quantity is no more than 250.
The script run for the test tool does not cause any delay. Therefore, whenever an answer to a thread arrives, a new request is always issued immediately.
Before analyzing the number of more test results, let's take a look at the other two test metrics TTFB and hits test results.
As we can expect, the ASP has a faster TTFB for all threads. The following table compares the effects of increased load on ASP Requests Queued and corresponding TTFB, all data are given in Requests QUEUED-TTFP format, and TTFB is still in milliseconds.
As you can see, it is necessary to add more servers to share traffic when the load is up to a certain extent (~50-100 thread range). However, this test still contains these high load scenarios to see if different changes may occur.
The final performance metrics hits test results also show the same tendency as the rest of the tests, and the ASP's performance is still the best.
The next test step is to observe the performance of each test program as the number of records in a recordset increases. The number of records tested here includes: 20,30,50, and 100. If you've read a large number of articles in this area, you might guess that as the number of records increases and the processing load increases, the component will show better performance. However, that is not the case. As the number of records increases, ASPs still maintain a leading edge over all other methods. Here is the test result after the average number of different threads.
Even if you increase the number of records in a recordset, the performance difference between the ASP and several other methods has not changed. Complete test results can be found in Appendix A of this article. If you are free, you may want to do the same test for other issues of interest. All the code used in this test is provided in Appendix B. You can use the code to test yourself, or make some changes if necessary.
V. Results analysis
In general, there is no one method of using components on the Windows 2000 platform that can exceed the ASP in the performance of pure ADO operations. Although components run as COM + library applications are closer to the ASP (and should be), they are still slightly less than the performance of the ASP. In fact, even the performance of VB components running in-process is 30% worse than that of ASP. However, in order to protect the Inetinfo process when an application is running a component that generates errors, many applications still run their COM + applications within a dedicated service process (Dllhost.exe), and in this common case, the ASP can provide 2:1 performance advantages!
We hope this article has successfully illustrated the problem that using components for database access in an Internet application is not necessarily the best solution. It is important that you perform a complete test before you dive into a scenario, because the end result of the scenario may be completely different from what you envision (or what you see and hear).
If the size of the Internet application is medium or large, performance becomes the primary consideration, more important than the reusability of the code. But in reality the main applications are few, if any, and are the best in terms of performance.
Of course, the use of components also has its advantages. Components are often the best option for encapsulating certain types of business logic, especially in applications that cross system integration. However, in some cases this seems to go to the extreme, it is wise to return to a deeper understanding of the existing technology platform and to truly understand the process, collection and transmission of data based on the business situation of the application. Many times we find that the Keng Razor principle holds the truth: when it comes to complex systems, the simplest solution is often the best solution (and probably the easiest way to measure it!). ）。
