I. Overview
The term "performance" has several different, differently meaningful meaning. When people talk about how much is good, they want to express how much work is completed within a certain time. For example, a good engine runs more stable and is more powerful. For the development team, you can also apply this judgment standard: a good performance development team is more quiet, and can produce a large number of high quality code. For me, the performance means that there are two things - how good my code is running, my development group and my own work efficiency. No matter which aspect, the skills introduced this article will play a certain help: help you write code faster, help you write faster code - complete this, reduce this. The techniques described in this article are mainly for ADO, especially how to access SQL Server through ADO. But at the same time, I will also involve some COM skills for applicable scope, and they apply to all Visual Basic code you have written.
In order to understand which SQL Server data access code is written, which system, which development habits can get the best performance, I have already spent a lot of time. In some cases, for the overall performance of the application, a single technical significance is small unless we am more magnified by looping performance. For example, in a client / server application, when we are not connected by specifying an ODBC data source (DSN), it can save approximately one to two seconds. For applicability or performance of the application, this part of the cost is small. However, if we apply this technology on a middle-layer component, this component must establish and turn off the database connection hundreds (or even thousands) per minute (or every hour), then this technology will be significant Performance of the performance of the system. Therefore, for each technique I discussed here, be sure to consider this factor - that is, in a certain period of time, your system will perform the same segment code how many times.
When you start seeking to improve performance, consider waiting for your application (component, or ASP code) where you are waiting and processing time. If you find that the application spends a lot of time to perform completion of the Open or Execute method, then you should carefully check the server-side query policy. Includes ADO, all data access interfaces wait for the 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 the result than other interfaces in faster speed. Although some interface opens the connection speed, some interface processing result sets are faster, but no interface can affect the speed of the database engine compile and execute queries. Therefore, if your query has too high "challenge" - for example, you don't have an index to optimize, you don't use the stored procedure, the server load is too heavy, or the number of records you asked to return too much - then, the world No ADO technology can help you improve performance. Unless you solve these basic query problems, no performance adjustment technology can significantly improve overall performance. Query Analyzer of SQL Server is an excellent tool for analyzing query performance. It can display the execution process of the query in a graphic manner, and make recommendations for improved performance methods.
If you can confirm that the query has a high efficiency, then you can use the techniques described in this article to further adjust the performance of the ADO code. The techniques described here will help you simplify and improve ADO programming, including: establishing and maintaining connectivity, constructing, and submitting faster queries, improve efficiency of processing query results, and so on. Second, establish a connection
In a client / server application, we can use several ways to hide the time required to establish and initialize the database connection, so that the application can open the connection, and no user waits for the application to start. First, we can try asynchronous connections. When using asynchronous connections, the ADO launches the connection operation, the non-connection completion is not waiting to return to the application - so that the application can then perform most of the initialization operation, complete the Form_Load event processing at a faster speed. If the time is closed and the connection is re-established less than the connection to the connection pool, then this connection is actually immediate. But in many cases (especially the number of users is not available), let the connection keep open state more practical significance. Inside the intermediate layer component or the ASP page, if the database query repeatedly appears, I suggest that you let the Connection object open.
Another way to improve connectivity is to avoid ODBC with DSN. In Microsoft, ODBC has been transferred to the status of Quick Fix Engineering (QFE, Quick Repair Project), which means that the company will no longer spend time on ODBC or its driver unless significant bugs are found. In addition, when considering performance and deployment, ODBC DSN is also a problem that must be concerned. The DSN must be installed on the customer system, requiring a registry search, compared to the OLE DB connection, which has a longer time required to establish a connection - especially when you specify Connectionstring in direct encoding, this is especially prominent. From the actual effect, it is limited to the system overhead using DSN reduced system: if the connection creation process is completely unconnected, you may be able to keep two to five seconds (assuming that there is no connection in the database connection pool). However, if your application needs to establish a connection frequently, it is very considerable to save time.
When you create a database connection, you have to select a data provider. Microsoft recommends that we use the OLE DB provider to replace the default ODBC provider. Compare the latest OLE DB local providers and features Similar to the earlier ODBC provider, I feel less unpleasant accidents. But no matter which case, you should make a complete test-code performance, support function, and behavioral ways, and behavioral methods can be changed before deciding to use a new provider.
In the middle layer and the ASP, in the case where the connection is kept open, we cannot (see from practice) Creating a scalable component - there is such a multiple call. Generally, when IIS references and release components, the Asp page instances, the components and ASP pages are frequently loaded and discarded. Since the ADO-based code must be established, use, release database connections, minimize connection complexity, the level of improvement, has achieved significant degree of performance. In these cases, the connection / session pool has important significance for the speed of our connection database. If you specify a suitable value for the CONNECTIONSTRING attribute of the Command object (ie, each time you use the same server, initial directory, login ID, and other parameters), then the connection has been opened and in the available state. If a matching connection can be found in the connection pool, the time of connection (or reconnect) will be close to 0 (usually less than 250 ms). However, if the ADO (or VB) code does not release the Connection object, or we have changed the Connectionstring between different instances, OLE DB must establish a new connection each time. If this happens, we will quickly exhaust the number of available connections in the pool. To ensure that the connection is released, we must set the Connection object to Nothing after closing the connection. Also, don't use ConnectionString in the RecordSet Open method, which opens the Connection object in an independent manner; this way, when we want to close the Connection object and to set it to Nothing, it is convenient.
Third, construct and submission query
When constructing inquiry, we must figure out why you have to do this, why can't you be a very complicated issue. However, some basic guidelines can make the process of constructing efficient queries more smooth and easy. Generally, you should not let the query waste server time. The following techniques can help you construct better and more efficient queries.
Don't force SQL Server to recompile and construct a query execution plan each time you execute queries. A simple way to avoid this repetition is to use a stored procedure with parameters. Note Try not to use the Prepare properties of the ADO Command object - sometimes it does not work correctly. If you use a stored procedure, you can further improve the ADO performance by eliminating unnecessary "affected rows" return values - you can add SET NOCOUNT ON while stored.
Minimize the number of communications with the server. If you have several related operations to be executed, merge them into a stored procedure, or a composite query that can be performed on the server as a script. Avoid using methods (such as refresh) and inappropriate parameters collection references, they will force ADO to increase additional server communication processes.
In the client / server application, only the Command object is constructed once, not every time the Command object is used. You can reset the parameter values for Command and execute it when you need it.
When the query is not a recordset, make sure you use the AdexecuteEnorecords option to tell the ADO to cross all the code used to receive and construct the record set (RECORDSET format). You can pass the adexecutenorecords option to the Execute method, or use it as a Command option.
Do not use a Command object when performing a stored procedure that returns a simple record set. All stored procedures (and Command objects) can appear as a COM method for the Connection object. Let the memory process have a significant performance advantage as a method of Connection objects, and it also simplifies the code. Although this technology has no help for those stored procedures with return status or Output parameters, this technology is useful for action queries (INSERT, DELETE, etc.) and those returns one or more records. After the stored procedure as the method of Connection, you can pass the input parameters of the stored procedure in the form of the method parameters; if the calling process returns a recordset, you can reference the RECORDSET through the last parameter in the method call. For example, the following ADO statement executes a stored procedure called "Fred", the Fred stored procedure has two input parameters, returns a Recordset: MyConnection.fred "Inputarg1", 2, MyRecordset
When writing code, don't send the auto-completion feature that VB will treat the stored procedure or the Command object name as a method of legitimate Connection objects. CoM does not resolve such names before formally running.
Do not return to records unless absolutely necessary. When the query being executed returns a record, the ADO will construct a Recordset object. The cost of constructing the Recordset object is very large, so you should try to avoid using the Recordset object. Note Sometimes the execution query is returned, but not returns a record. For example, you can return an integer value through the Return status parameter. In addition, you can return the Output parameter to replace the record set that needs to construct the Recordset object, and the Output parameter returned by SQL Server is up to 1000.
As long as it is possible, use the action query (INSERT, UPDATE, DELETE, and stored procedures for these operations) to replace the updatable Recordset cursor. At this point, you should use the Execute method and its adexecutenorecords option, make sure the ADO can know that the query does not need to construct the Recordset object.
Do not request server unless necessary, do not request to sort. In most cases, for a modest RECORDSET object, after it is sent to the client, the sorting speed will be faster. In addition, if the ADO client sorts the record in the Recordset, the client application can be sorted according to the order selection, thereby improving flexibility.
Understand the structure of the index before writing queries. Create a suitable index, adjust the syntax of the query to use these indexes, you will be able to improve the speed of the record extraction. Query Analyzer can help you decide whether it is necessary to add more indexes.
Do not return too much records at once. Many times, the recorder that has too large capacity seriously affects the performance of the application. Returns only those records you need, if the client requires more records, then extract later. Limit the range of queries by using a WHERE clause with parameters or flexible use of TOP N query.
Don't return too much column. Avoid using SELECT *. SELECT * statement tells SQL Server to return all columns, regardless of the actual columns. Just choose those columns you need, so that when someone adds more columns to the table, you will not get a big result set.
Avoid using a cursor. If you have to use a cursor, don't use the number of resources you need to exceed the necessary cursor type. If there is no need, don't ask the cursors to provide scrolling, update, and data buffering capabilities.
Tell the ADO in detail what you want it. Don't forget to set the CommandType option when you open the Recordset or construct the Command object. It avoids ADO "guess" your intention, you will be able to reduce communication with the server, and make the code more stable. In addition, learning to use diagnostic tools, measure how much time for code running on the server and the application of the application - and where this time is spent. In this regard, SQL Server Profiler is a valuable tool. It can clarify that your code is required to do something on request, and can highlight in a query constructed in the slopproof or command attribute for the error selection. In addition, Query Analyzer can also show how SQL Server executes queries in a way, and propose recommendations for improving queries to help you adjust queries. Query Analyzer can even perform it its proposed suggestions (eg, add or delete indexes), you can complete it with just click on the button.
Fourth, handle the results of the query
After the query result record is sent to the client, the client application may require considerable time to process the result set. Each system (intermediate layers in the client / server, multilayer system, and ASP) provide corresponding technologies for optimizing this phase. Below is a few techniques that can significantly improve performance.
One of the most errors I saw in the code is: Use the latency binding when referring to RecordSet Field.Value. Since the code needs to be frequently referenced by the value attribute, and the Field objects that are usually referenced, the multiple factors mentioned above will play an important impact - therefore, all the techniques described here can significantly improve performance. Some developers use latency binding techniques are what they want to explicitly identify the SELECT statement. For this goal, many people use quotation marks to surround strings. For example, in order to reference the "COWS" field in the record set RS field collection, you may use:
RS ("COWS")
or:
Rs.fields ("cows"). Value
The following method explicitly references the value attribute of the specified member in the field of the Fields collection in the record. This approach is slightly faster, and when you migrate these code to Visual Basic.Net, it is better. One change in the above method is to use an exclamation mark (!) Operator:
RS! COWS
Compared with the first binding (Early-binding), COM is much longer, because they force COM to resolve the reference to the value attribute at runtime (rather than compile), each A series of similar, backed stadiums are required to find a series of findings.
However, when using the delay binding, there is no column of the code reference to the code. If you write the code in the following way:
RS (0) 'points to the first column (member of the Fields collection)
At this time, COM can resolve the value attribute address at compile time, and the running speed of the code will speed up. However, only those who know the columns returned to the query and the return order can understand which column points to the line. This approach may bring problems if developers do not have access to control query data sources (this is very common). In order to determine which SELECT column is referenced by RS (0), you must find which SELECT statement that is born in this RECORDSET, engage in the columns returned by the SELECT statement.
However, there are several techniques that allow you to be able to run rapidly, while ensuring readability of the code. One of the methods is as follows: Developers must create a list of enumerations returned by queries. If the query is changed, such as returning more columns, or the order of the order changes, the developer must modify and re-deploy the enumeration list. Unfortunately, keep the enumeration list and query matching, which is a somewhat difficult task for managers. For example, in order to quickly, it identifies which of the ADO code references, you can combine the following SELECT statement and enumeration list: Select CatName, Cattype, Catsize from cats where ...
ENUM ENUCATSQUERY
Catname
Cattype
Catsize
END ENUM
Note that the column returned by the SELECT statement matches the columns declared in the enumerated list. After that, you can use the following code when you need to quote Recordset's fields:
Strmyname = rs (enucatsquery.catname)
According to this approach, the code not only has better readability, but it is still binding upon compile, and the running speed of the code is significantly accelerated.
However, to avoid delayed binding, you can also use another method. VBData-l@peach.ease.lsoft.com List Service Last long discussion has drawn a method I call Prebinding, which combines two technologies. This technology has no help when you only need to reference the Field object, but in the client / server application, the pre-bound method is ideal. When using this method, you have to create multiple separate, named Field objects and set these objects to members of the RECORDSET object Fields collection. When writing code, you first create a named Field object for each field you want to use, for example:
Dim fldname as adodb.field
Dim FldType as adodb.field
Dim Fldsize as adodb.field
Creating these Field objects requires a certain overhead. However, you should be estimated, this is a one-time overhead, but it can dramatically improve performance.
Once you open the Recordset, you only need to set these named Field objects to the SELECT query to select the column:
IF fldname is nothing then
Set fldname = rs! Catname
Set FldType = rs! Cattype
Set fldsize = rs! Catsize
END IF
You can use quotation marks here with quotation marks here, or even an exclamation operator can be used. Since the code here is only running once, no matter what method is used, the difference in performance is not large. Next, when you need to reference the Field object (after query), just use a pre-bound variable:
Strname = fldname
Strtype = fldtype
strsize = fldsize
This pre-binding method is better than the predetermous reference method (for example, RS (0)).
V. Client / Server, Intermediate and ASP Strategy
When writing code, you must also take into account other factors that affect performance. Some of these factors have no relationship with ADO - they are related to COM. A recent white paper in Microsoft pointed out that the ADO operation (connection, query, processing) is executed faster than calling COM components in the Windows 2000 ASP page. This conclusion is not surprising: When we call an external COM component from VB (some code outside the current process), access the COM component and pass the control to it to it, the operation is complicated in the background, and the speed is very slow. Although we don't have to deliberately avoid calling COM components to run ADO code. However, we should not simply encapsulate multiple separate ADO operations into a large number of small COM components, then call them every time they need it. Instead, we should try to package all logical into a COM component, making the program only one call, COM can do most (if not all) operations. I believe that you have found that run a binary form (for example, COM components) ADO code is fast than explaining the execution code in the interpretation of the ASP. Therefore, you should find some ways to reduce the expensive overhead they need to enter COM components and returning from COM components. If you are inseparable from a Command object, or you cannot bind the Field object and reuse it if necessary, you should consider the technology to avoid excess objects. In this case, it is more important to call the stored procedure as the method of Connection objects. In addition, reference the field attribute is also contributed to improving performance. Remember the cleaning of the operation after the operation is completed: Turn off the Connection and RecordSet objects and set them to Nothing.
In order to let the code and code writers exhibit the best performance, remember the basic rules: use the connection pool and asynchronous connection; reduce the number of times the ADO code and database server communication; use a COM-first binding technology; unless If necessary, try to avoid using expensive ADO objects, such as RECORDSET and Command objects; if possible, replace the record set with Return Status and Output parameters. Improve the efficiency of the query as much as possible, if possible, don't forget to use the stored procedure. Tell the ADO in detail what you want to do, avoid ADO to guess your intent - explicitly specify ADO CommandType, use the options such as Adexecutenorecords.
For all the techniques and other articles present, I suggest you look at them with a look at it. What we do, the code written, the system constructed is a very complicated thing, and many changing factors affect them. After understanding this, if you are interested in a certain technology discussed herein, you can conduct a test. If it is really valid, then it is officially implemented, and then the test is tested. If it is still valid, congratulations. If it is no longer valid, you have to see if it violates the necessary conditions for applying this technology.
