ado| Performance One, overview
The term "performance" has several different, nuanced meanings. When people talk about how well something is performing, what they want to say may be how much work it has done in a certain amount of time. For example, a good engine runs more stably and generates more power. For a development team, you can also apply this criterion: a good development team is quiet when working, and can produce a lot of high-quality code. For me, performance means at least two things-how good My code works, how well my development team and myself work. In either case, the techniques described in this article will help: help you write code faster, help you write faster code--do it quietly, and reduce that error. The techniques described in this article are primarily for ADO, especially how to access SQL Server through ADO. But at the same time, I'll cover a broader range of COM tips that apply to all of the Visual Basic code you write.
I've spent a lot of time trying to find out what SQL Server data access code writing technology, which systems, and which development habits can get the best performance. In some cases, the single technical significance for the overall performance of the application is small, unless we multiply the performance improvement by looping. For example, in a client/server application, it can save about 1-2 seconds when we are not connecting to a database by specifying an ODBC data source (DSN). This portion of the time saved has little impact on the applicability or performance of the application as a whole. However, if we apply this technique to a middle-tier component, which will establish and close the database connection hundreds of (or even thousands of) times per minute (or every hour), this technique can significantly affect the performance of the system. So for every technique I'm talking about here, it's important to consider this multiplier factor-that is, how many times your system will execute the same piece of code within a certain period of time.
When you start looking for ways to improve performance, consider where your application (component, or ASP code) most of the waiting and processing time is spent. If you find that your application spends a lot of time waiting for the open or Execute method to perform, you should carefully examine the server-side query strategy. All data access interfaces, including ADO, wait for the same time for query results. For example, if you have a query, SQL Server takes 20 seconds to complete it, no matter what interface is used to execute the query, no interface can return results more quickly than other interfaces. Although some interfaces open the connection faster, some interfaces handle the result set faster, but none of the interfaces can affect the speed at which the database engine compiles and executes queries. So if your queries are too "challenging"--for example, if you're not optimizing your indexes, you're not using stored procedures, your servers are overloaded, or you're asking for too many records to return--there's no ADO technology in the world that can help you improve performance. Unless you solve these basic query problems, no performance tuning technology can significantly improve overall performance. SQL Server Query Analyzer is an excellent tool for analyzing query performance. It can display the execution process of the query graphically, and make suggestions on the methods of improving the performance.
If you can be sure that the query is more efficient, you can use the techniques described in this article to further fine-tune the performance of the ADO code. The techniques presented here will help you simplify and improve ADO programming in a variety of ways, including: building and maintaining connections, constructing and submitting faster queries, improving the efficiency of processing query results, and more.
Ii. establishment of a connection
In a client/server application, there are several ways to hide the time required to establish and initialize a database connection so that the application can open the connection without requiring the user to wait for the application to start. First, we can try an asynchronous connection. With an asynchronous connection, when ADO initiates a connection operation, it returns control to the application without waiting for the connection to complete--so that the application can then perform most of the initialization operations to complete the Form_Load event processing at a faster rate. If the time to close and re-establish the connection is less than the connection pool free connection time, the connection is actually instantaneous. However, in many cases (especially the number of users), it is more realistic to keep the connection open. Within a middle-tier component or ASP page, if a database query repeats itself multiple times, I recommend that you keep the connection object open.
Another way to improve connectivity performance is to avoid the use of ODBC with DSN. The MICROSOFT,ODBC has been transferred to the quick fix Engineering (QFE, fast Repair Engineering) status, which means that the company will no longer spend time on ODBC or its drivers unless a major bug is found. In addition, ODBC DSN is also a concern when considering performance and deployment issues. DSN must be installed on a client system, requiring a registry lookup, and it takes longer to establish a connection than an OLE DB connection-especially when you specify ConnectionString in a direct encoding. In practical terms, the overhead of avoiding DSN reduction is limited: If you completely cancel the connection creation process, you may have 2-5 seconds left for each connection (assuming there is no connection in the database connection pool). However, if your application needs to establish a connection frequently, the amount of time saved can be significant.
When setting up a database connection, you need to select a data provider. Microsoft recommends that we use the OLE DB provider instead of the default ODBC provider. Compared to the latest OLE DB local providers and similar but older ODBC providers, I feel less of an unpleasant surprise than the former. In either case, you should test the application thoroughly before you decide to use a new provider--the performance of the code, the functionality of the support, and the way you behave can change.
In the middle tier and ASP, we cannot (in practice) create scalable components-at least between multiple invocations-while keeping the connection open. Generally, components and ASP pages are loaded and discarded frequently when IIS references and releases instances of components, ASP pages. Since the code based on ADO must establish, use and release the database connection every time it executes, the strategy of minimizing the complexity of the connection can achieve a measurable degree of performance improvement. In these cases, the connection/session pool is of great significance for the speed at which we connect to the database. If you specify the appropriate value for the Command object's ConnectionString property (that is, you use the same server, initial directory, login ID, and other parameters each time), the connection is open and available. If a matching connection can be found in the connection pool, the connection (or reconnection) time will be close to 0 (usually less than the MS).
However, if the ADO (or VB) code does not release the connection object, or if we change between different instances Connectionstring,ole DB must establish a new connection each time. If this happens, we will soon deplete the number of connections available within the connection pool. To ensure that the connection is freed, we must set the connection object to nothing after closing the connection. Also, instead of using ConnectionString in the Recordset open method, open the Connection object in a separate way, so that when we want to close the connection object and set it to nothing, It's convenient to refer to it.
Iii. structuring and submitting queries
When constructing queries, it is a complex issue to figure out why you have to do this and why you can't do that. However, some basic guidelines can make the process of constructing efficient queries smoother and easier. Generally, you should not let queries waste server time. Here are a few tips to help you build better, more efficient queries.
Do not force SQL Server to recompile and construct the query execution plan each time the query is executed. One simple way to avoid this duplication is to use stored procedures with parameters. Be careful not to use the Prepare property of the ADO Command object-Sometimes it does not work correctly. If you use stored procedures, you can further improve ADO performance by eliminating unnecessary "affected row number" return values-simply by adding set NOCOUNT on in the stored procedure.
Minimize the number of communications with the server. If you have several related actions to perform, merge them into a stored procedure, or a composite query that can be executed as a script on the server. Avoid using methods such as refresh and improper parameters collection references, which force ADO to add additional server communication processes.
In a client/server application, only the command object is constructed once, not every time the command object is used. You can reset the command's parameter value and then execute it if necessary.
When the query returns not a recordset, make sure that the adExecuteNoRecords option is used to tell ADO to cross all the code used to receive and construct the recordset format. You can pass the adExecuteNoRecords option to the Execute method or use it as the command option.
Do not use the Command object when performing a stored procedure that returns a simple recordset. All stored procedures (and command objects) can appear as COM methods of the connection object. Having a stored procedure as a Connection object has a significant performance advantage, and it simplifies the code. Although this technique is not helpful for stored procedures that have return status values or output parameters, this technique is useful for action queries (INSERT, delete, and so on) and for queries returning one or more records. After using the stored procedure as a connection method, you can pass in the input parameters of the stored procedure in the form of a method parameter; If the calling stored procedure returns a recordset, you can refer to the recordset by the last argument in the method call. For example, the following ADO statement executes a stored procedure named "Fred," with two input parameters for the Fred stored procedure, which returns a recordset:
Myconnection.fred "InputArg1", 2, Myrecordset
When writing code, do not hope that the automatic completion of the VB function will be stored procedures or command object name as a legitimate connection object method. COM does not parse such names until it is run formally.
Do not return a recordset unless absolutely necessary. When the query being executed returns a record, ADO constructs a Recordset object. The overhead of constructing a Recordset object is high, so you should avoid using the Recordset object as much as possible. Note Sometimes executing a query returns a result, but not a record. For example, you can return an integer value by returning the status argument. Alternatively, you can return an output parameter instead of a recordset that requires the construction of the Recordset object, and SQL Server allows up to 1000 output parameters to be returned.
Whenever possible, replace updatable Recordset cursors with action queries (insert,update,delete and stored procedures that perform these operations). At this point, you should use the Execute method and its adExecuteNoRecords option to ensure that ADO knows that the query does not need to construct the Recordset object.
Do not request the server to sort unless necessary. In most cases, for a moderately sized Recordset object, the sort speed will be faster when it is sent to the client. Also, if you let the ADO client sort the records in the recordset, the client application can be sorted in the order that the user chooses, thereby increasing the flexibility.
Understand the structure of the index before writing the query. By creating appropriate indexes and adjusting the syntax of the query to take advantage of these indexes, you will be able to increase the speed at which records are extracted. Query Analyzer can help you decide if it is necessary to add more indexes.
Do not return too many records at once. Many times, a record gathering with too much capacity seriously affects the performance of the application. Return only those records that you currently need, if the client program needs more records
