Chapter 5 VB Efficient Data Access 5.1 Rules 5-1: Efficient Access Basics: Round-Tour, SQL Statements and Data Providers
5.1.1 makes the round-trip overhead
# 数据 数据,, remote DCOM object, or HTTP network communication, requests for the response process: 1) Create a request. Determine the content and package in the correct format. 2) Send a message. To call the necessary protocol Send a message to the server, usually involve calls to some system services. 3) Receive the message. The server receives, identifies the message, and forwards to the appropriate program processing. 4) Handling the request. Server program processing request, complete the action. 5) Server side Repeat steps 1) to 4), send the response to the requester.
# Reduce the number of round trips will make the efficiency of the program significantly improved.
# Specifically to the step of the VB database interaction process: 1) Generate a SQL query. Create a SQL statement. 2) Send a query request via the ADO Connection. You need to create an ADO Connection object and call the OPEN method to open the connection, and then call the execute to send a request. 3) The database receives a request through established connection. 4) The database handles the SQL statement. First compile into binary code, followed by code optimization, then execute code. 5) The database returns the result in a RECORDSET. The result is returned by the Tabular Data Stream (TDS) format, and TDS is received by ADO and converted into a Recordset object. This step will also include 4 steps.
# To reduce round-trip overhead, you may need to have a large amount of data in advance to retrieve the retrieval; or query the database query, and then send it.
5.1.2 Determine the best way to send SQL queries
# Continted and continuously executed multiple queries, you can send it once. Such a database can be optimized as a whole.
# Use the stored procedure, eliminate the steps to compile and optimize.
5.1.3 Select the appropriate provider
# Use the OLEDB Provider as possible instead of the ODBC to connect to the database.
# The default Provider value is provider = msdasql (OLEDB Provider for ODBC)
5.2 Rules 5-2: Do not excessively encapsulate data access
# After you have the object-oriented technology, you should not use the form and the BAS module to include all the function processes, but should be modeled for business objects in the application.
5.2.1 Pure Object-Oriented Technology
# Fully apply object-oriented style modeling, you may have access to each database table in a separate class, which should include logic used to create and release database connections, and also include read data. Table and modify the logic of the data sheet. ## RentVideo clientPublic Sub RentVideo (sCustID As String, sVideoID As String, Optional iRentalTerms As Integer = 3) Dim rCustomers As New CCustomersDim rRentals As New CRentalsDim rViedos As New CVideos
'** connect to customers, check standing, and disconnectrCustomers.ConnectIf Not rCustomers.Standing (sCustID) ThenErr.Raise vbObjectError 2049, _ "RentVideo.CheckStanding", _ "Customer not in good standing." End IfrCustomers.Disconnect
'** connect to videos, check stock, decrement stock, and disconnectrVideos.ConnectIf Not rVideos.InStock (sVideoID) ThenErr.Raise vbObjectError 2050, _ "RentVideo.CheckStock", _ "Video currently not in stock." End IfrVideos. DecrementStock Svideoidrvideos.disconnect '** Connect To Rentals, Add New Record, and Disconnectrrentals.connectrrentals.addrental ScustId, SvideoIDRrentals.disconnect
End Sub
5.2.2 Pursuing the shortcomings of pure OOD effects
# Give each data sheet, the corresponding class, although it improves the maintenanceability of the code, but system performance will decrease. ## Failed to take advantage of the database engine itself ## too much use precious system resources (database connection, etc.) ## Database round trip Too much
5.2.3 Solution: Use the stored procedure
# Use the stored procedure to write business logic that needs to access multiple tables, and the client only needs a database access to complete the task. ## / * stored procedure checkoutvideo * / create procedure checkoutvideo @ID_cus varchar (10), @ID_vid varchar (10) AS
/ * SET NOCOUNT Attribute To ALLOW IMMEDIATE ERROR Trapping * // * SQLOLEDB provider defaults to each stored procedure provides multiple result record sets, and the raised errors are not explicitly passed to the client. To capture errors, you need to use the NEXTRECORDSET method for the record set, check the error message saved with each result record set * / set nocount on
DECLARE @Quantity intSELECT @Quantity = vid_Instock FROM tbl_VideosWHERE id_Video = @ID_Vid DECLARE @Standing intSELECT @Standing = cus_GoodStanding FROM tbl_CustomersWHERE id_Customer = @ID_Cus
IF @Standing = 1BEGINIF @Quantity> 0BEGINUPDATE tbl_Video SET vid_InStock = @Quantity - 1 WHERE id_Video = @ID_VidINSERT INTO tbl_Rentals (idRental, id_Customer, id_Video, ren_RentedOn, ren_DueBack) VALUES (NEWID (), @ID_Cus, @ ID_Vid, GetDate () , DateAdd (dayofyear, 3, GetDate ())) ENDELSERAISEERROR ( 'Video currently not in stock.', 11, 1) / * You may choose, instead of using RAISEERROR, simply toreturn a value that your client app will understand to be afailure condition, thus avoiding having exceptions over the network * / ENDENDELSERAISEERROR ( 'customer no in good standing.', 11, 1) GO ## client CDataAccessPublic Sub RentVideo (sCustID As String, sVideoID As String) On Error GoTo hErrDim rConn As new ADODB.ConnectionrConn.Open "
End Sub
# The stored procedure can also use the output parameter, or the output recordset returns data. This is handled by ADO Command object and parameters collection.
# Disadvantages: Weakened encapsulation; business logic is split in code and database, not convenient for maintenance; writing and maintaining the tool function of stored procedures.
5.2.4 How to handle multiple database servers
# The stored procedure can only access the data within the range of the database server. Although another database is allowed, it is not possible to take advantage of the stored procedure on the second server, and this may bring the difficulties of transplantation and maintenance.
# When modeling, connect each database, with a business object. (Modeling in a reasonable minimum particle size)
5.3 Rules 5-3: Do not connect the database as a data member
# Early VB procedures typically use the same database connection as the form life to complete database access work within the form.
# In a large number of users, use the connection buffer pool to effectively save database connections. ODBC 3.0 (ODBC.DLL) and OLEDB 2.0 (MTXDM.DLL) include connection buffer features.
# ADO Connection object is not just a proxy for the real data connection of the underlying, and the physical connection is only created after Open. (Evening allocation, early release) # unless there is special needs, the ADO connection should be on the local statement, open the connection before requesting the data, and close it as soon as possible. Do not simulate the connection buffer by sharing the ADO connection object.
5.4 Rules 5-4: Dead lock is common - anti-wrong procedural development
5.4.1 Lock
# 锁 类 与 类 类 类 类 类 读 读 读 读 | | 锁 写 写 写 写 写 无 写 写 写 写 写 写 写 写 写 写 写 写 写 写 | 写 | | | | | | | | | | | | | |
# The more locks in the system, the more data needed by one business is being locked by another transaction, and the greater the chance.
5.4.2 Serial Transaction and Lock Manager
# Serial transaction: All transactions are discharged from advanced first queue. Con concluding.
# SQL Server lock manager can achieve different locking modes by setting the isolation level. ## Dead Commited. The read lock of T1 is released after the read operation is completed, not the existence of the life of the transaction. If T2 modifies the recorded records that T1 have been read, it may affect the operation of T1. ## 隔 等 等 SERIALIZABLE, T2 cannot modify the record of T1 read.
5.4.3 dead lock
# 死 锁 发生 条: ## Transaction T1 Start, and get some data lock ## Transaction T2 start, and get your own data lock ## Transaction T1 requires data to lock the data locked by T2 to complete the transaction ## transaction T2 requires locking data that has been locked by T1 to complete the transaction
# The data record under the same database lock manager control is simultaneously by different transaction requests, which can be determined to be deadlock. If the requested database records a different lock manager, you need to exchange information between the lock manager.
# Prevent deadlocks: ## Let the transaction serialize. There is damaged parallelism. ## Get all required locks before running your business. Excessive overhead.
# Mts / COM Use the timeout to detect the deadlock. The MTS transaction timeout can only be set globally. COM allows the overall or to component configuration.
# MTS starts timing after the first method call, even if it is not required. COM starts timing after requesting the data lock.
5.4.4 Minimize the chance of deadlocks in application design
#Eglete as soon as possible, as soon as possible, do it quickly to proceed. Reference P207, Prociples of Transaction Processing for the Systems Professional (Philip Bernstein and Eric Newcomer, Morgan Kaufmann Publishing, 1997)
5.4.5 minimize transaction time
# Reduce the number of round trips to the server. (# 5.1, # 5.2)
# Do not read the same data repeatedly, but leave it in memory after the first reading data to increase access speed, because the data has been locked.
5.4.6 Reduce the lock time to the shortest
# You can redesign the object, put the transaction method in an object, set it in the Serializable mode. This does not cause unnecessary data locking.
# Organize a good code, you can do the work before reading the database before reading the database, do not waste the time after obtaining the data lock.
5.5 Rules 5-5: Use Firehose (Firehorse?) Cursor as much as possible
# Cursortype CursorLocation | Forwardonly | Static | KeySet | Dynamic Server | Y | Y | Y | YCLIENT | N | Y | N | N
# Firehose Cursor: CURSORTYPE = AdforwardONLY; CURSORLOCATION = asuseserver; LockType = AdlockReadOnly; cachesize = 1 Allow calling MoveFirst to re-execute the query. # Use the Firehorse cursor, just use the RECORDSET as a way to read data. Use rcn.execute "update ..." to update the data.
# Update the MEMO or BLOB field to use the RecordSet's Update method.
5.6 Rules 5-6: Make the right data search decision (avoid abuse of Selectsinglenode)
# Implement data exchange between SQL Server and ADO through XML in some cases may not be a good way.
5.6.1 seek-and-find components
# Search several scenarios in the database: ## Top the data into the local XML file, use the Selectsinglenode method to find. ## Stay the data on the SQL Server, use the Transact-SQL to select the record line. ## Top the data into the local ADO record set and use RecordSet.Find.
5.6.2 Understanding which method for solving specific problems
# Record a collection of hundreds of thousands, it is suitable for server search # data from non-database sources, considering the XML scheme # Data set size, all passed to the client burden is not too heavy, can be disconnected ADO Recordset to search
