오전 9:07 2002-07-04
조경민 ADO for Performance
=============================================================
1. 매 작업시 Connection 객체를 새로 생성/ 소멸하도록 한다.
(Use ADO Like an Apartment Model)
=> 내부적으로 연결 Pooling을 지원하기 때문에 성능상 문제가 없다.
내가 계속 물고 있으면 리소스만 잡을 뿐이다.
2. 입력 수정 삭제의 경우 트랜젝션을 걸어두자.
=> 조회만 있을 경우 트랜잭션은 불필요
3. 옵션을 잘사용하자
adExecuteNoRecords 를 사용하면 레코드셋을 받을 필요없을때 유리하다
adReadOnly는 조회시 갱신이 없을때 유리하다.
4. Use Session (OLE DB) or Connection (ODBC) Pooling
=> Use MTS 또는 ODBC 3.0 Connection Pooling 커넥션 풀링 탭에서 설정
5. Disconnect Your Client Cursor from the Connection for R/O and Long-Use Scenarios
Sub DisconnectRS()
Dim con As Connection
Dim rs As Recordset
Set con = New Connection
Set rs = New Recordset
con.CursorLocation = adUseClient
con.Open "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
Set rs = con.Execute("SELECT SomeText, SomeNumber, SomeTime, " & _
"SomeMoney FROM Nonsense")
Set rs.ActiveConnection = Nothing <= 이부분
Set con = Nothing <= 이부분
'
' Process data
'
rs.Close
Set rs = Nothing
End Sub
6. 조회시 SELECT * 하지말고 원하는 컬럼만 받도록하자
7. 가능하다면 Stored Procedure를 사용하자
8. 커서는 가능한 사용하지 말자
9. Command Object은 가능하면 재사용하자. 한번 전역 객체화시킨 후
이를 계속 사용하는것이 좋다.
----------------------------------------------------------------------
참고 MSDN
----------------------------------------------------------------------
Using the ADO GetRows Method for Faster Data Retrieval
ADO에서 Recordset으로 반환보다 GetRow를 이용하면 더 빠르다고 한다.
Table A: Processing time (sec) for ADO 2.0 objects using the GetRows method
Total Records Recordset Command Parameter
10,000 3.07 2.86 2.91
20,000 5.88 5.66 5.77
40,000 11.42 11.20 11.37
Table B: Comparison of GetRows method to Recordset object by itself
with 40,000 records.
Method Type Recordset Command Parameter
GetRows 11.42 11.20 11.37
Recordset object 15.55 14.72 14.66
약간더 빠른거 같다 ㅡ.ㅡ
------------------------------------------------------------------------
Optimize Data Access Using ADO
1. Avoid Updating Through Recordsets
레코드셋을 반환받아 커서이동하면서 업데이트하는 것보다는
스토어드프로시저를 이요하거나 특별한 경우엔 여러번의 추가 및 삭제를 이용하는
것이 더 효율적이다.
It's more efficient to update the database through stored procedures, especially when performing multiple inserts and deletes
2. Use Stored Procedures
스토어드프로시저는 빠르게 실행되도록 컴파일되어 서버에 쿼리 실행에대한 plan(계획)
이 짜여지게되어 효율적으로 처리하게 된다.
DB 조작 코드가 애플리케이션에 위치하지 않고 서버에 있으니 보다 유지보수 및
보안에 유리하다
they are compiled to be faster
Other advantages to using stored procedures include easier maintenance and security
3. Create the Minimum Recordset
레코드셋을 열때는 CursorType, CursorLocation, LockType를 지정하는것이 좋다.
Use the CursorType, CursorLocation, and LockType properties to create the most efficient recordset
With adoRS
.ActiveConnection = "Pubs"
.CursorType = adOpenStatic <= 여기
.CursorLocation = adUseClient <= 여기
.LockType = adLockReadOnly <= 여기
.Source = "SELECT title_id, " & _
"title, pub_id FROM Titles"
.Open
End With
*MTS를 쓰게되면 Client 위치의 커서만 쓸수있다.
If you're going to pass recordsets to MTS components, you can only use disconnected (client-side) recordsets
4. Use Disconnected Recordsets
Dim adoRS As New ADODB.RecordsetWith adoRS
.ActiveConnection = "Pubs"
.CursorLocation = adUseClient .CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.Source = "SELECT emp_id, " & _
"lname,hire_date FROM Employee"
.Open Set .ActiveConnection = Nothing <= 여기
End With
There are many advantages to using disconnected recordsets. Scalability is improved, because adding 50 users doesn't mean 50 open database connections and 50 open cursors on the database server. Time-consuming operations executed on a disconnected recordset will not affect the database server and all modifications are batched. The recordset must be reconnected to the data source to update the database. Note that with a disconnected recordset, changes made on the database by other users will not be visible?concurrency issues must be handled with code.
5. Define Command Parameters
6. Encapsulate Data Access
3-tier식으로 해서, 클라이언트 애플리케이션은 DCOM으로 미들티어 비지니스로직
VB DLL, COM에게 로직을 호출하고, 이 미들티어는 OLEDB를 통해 DB서버로 연결하도록하자
8. Use MTS
Putting your components in MTS gives you the benefits of scalability, connection pooling, and transactions.
연결 풀링을 지원하게 된다.
SetComplete, SetAbort를 하게되면 모든 트랜젝션은 Commit되거나 Roll Back되게된다.
All transactions occur in an object's context, and all the transaction's operations are committed or rolled back using the SetComplete or SetAbort method
단점은
There are some trade-offs to consider when using MTS. Increased overhead is involved when referencing MTS components because they reside remotely. Additional time is spent marshalling the data to the MTS component if parameters are passed from the client to an MTS component. Consider these issues when making design decisions before creating components to run in MTS. Depending on your requirements, it could be well worth the effort.
9. Use Output Parameters
Use output parameters instead of returning a recordset if you have a stored procedure that returns a single value or row of data
With adoCommand
.Parameters.Append .CreateParameter("RETURN_VALUE", _
adInteger, adParamReturnValue, 0)
.Parameters.Append .CreateParameter("@Zip", _
adVarChar, adParamInput, 20, Null)
.Parameters.Append .CreateParameter("@State", _
adVarChar, adParamInputOutput, 2, Null)
.Parameters.Append .CreateParameter("@City", _
adVarChar, adParamInputOutput, 50, Null) .Parameters("@Zip") = Zip SQL = "usp_GetCityStateForZip" .CommandText = SQL
.CommandType = adCmdStoredProc
.ActiveConnection = "Pubs"
.Execute 'Check the return value after the update
If .Parameters("RETURN_VALUE") = 0 Then _
State = .Parameters("@State") & "
City = .Parameters("@City") & "
End If
End With
10. Retrieve Only Required Data
SELECT * FROM table 하지말자.
SELECT title_id, title, type, price FROM Titles이런게 더 좋지 않냐
또
Dim adoCommand As New ADODB.CommandWith adoCommand
.ActiveConnection = "Pubs"
.CommandText = "usp_InsertNewTitle"
.CommandType = adStoredProc
.Execute , , adExecuteNoRecords <= 이렇게 하면 레코드셋 반환필요없을시 유리
End With
------------------------------------------------------------------------
High-performance Visual Basic Apps
ASP 서보트 VB COM으로 웹페이지에서 ADO MTS를 써서 빠르게 하자.
- 스토어드 프로시저를 사용하여 웹 로그인 모듈을 맡게한다.
- Disconnected 레코드셋으로 하여 효율을 높인다.
Alter Procedure Account_VerifyLogin
(
@EMail varchar(50),
@Password varchar(50),
@AccountID int output,
@FullName varchar(100) output
)
As
select @AccountID = AccountID,
@FullName = FirstName + ' ' + LastName
from accounts
where email = @EMail
and Password = @Password
group by AccountID, FirstName + ' ' + LastName
return @@ROWCOUNT ? 0 means not found, 1 means it matched
한 후
VB ASP용 COM에서
dim email, password
Response.Buffer = true
Response.Clear
email = trim(Request("login"))
password = trim(Request("password"))
If email <> "" Then
dim objAccount
set objAccount = Server.CreateObject("FMStocks_Bus.Account")
dim AccountID, FullName
if objAccount.VerifyLogin(email, password, FullName, _
AccountID) then
Response.Cookies("Account")("AccountID") = AccountID
set objAccount = nothing
Response.Redirect("home.htm")
else
set objAccount = nothing
Response.Redirect("default.htm")
end if
else
Response.Redirect ("default.htm")
end if
를 하고
스토어드 프로시저 호출하는 방법은 아래처럼 한다.
Public Function VerifyUser(ByVal email As String, _
ByVal password As String, _
ByRef AccountID As Variant, ByRef FullName As Variant) _
As Boolean
On Error GoTo errorHandler
Dim strUserInfo As String
'The following code was taken from Database.cls RunSPWithString
'and modified to support 2 outputs and 1 return value
' Set up Command and Connection objects
Dim cmd As ADODB.Command
Set cmd = CtxCreateObject("ADODB.Command")
'Run the procedure
cmd.ActiveConnection = GetDSN()
cmd.CommandText = "Account_VerifyLogin"
cmd.CommandType = adCmdStoredProc
With cmd
.Parameters.Append .CreateParameter(, adInteger, _
adParamReturnValue) 'rs(4).Value
.Parameters.Append .CreateParameter("@EMail", _
adVarChar, adParamInput, 50, email)
.Parameters.Append .CreateParameter("@Password", _
adVarChar, adParamInput, 50, password)
.Parameters.Append .CreateParameter("@AccountID", _
adInteger, adParamOutput, 4)
.Parameters.Append .CreateParameter("@FullName", _
adVarChar, adParamOutput, 50)
End With
cmd.Execute , , ADODB.adExecuteNoRecords
If cmd.Parameters(0).Value = 0 Then
VerifyUser = False
Else
AccountID = cmd.Parameters(3).Value
FullName = cmd.Parameters(4).Value
VerifyUser = True
End If
Set cmd = Nothing
CtxSetComplete
Exit Function
errorHandler:
RaiseError g_modName, "VerifyUser"
End Function
물론 RS는 Disconnected로 만드는게 좋다
Function RunSPWithRS(ByVal strSP As String, _
ParamArray params() As Variant) As adodb.Recordset
On Error GoTo errorHandler
Dim rs As adodb.Recordset, cmd As adodb.Command
Set rs = CtxCreateObject("ADODB.Recordset")
Set cmd = CtxCreateObject("ADODB.Command")
'Run the procedure
cmd.ActiveConnection = GetDSN()
cmd.CommandText = strSP
cmd.CommandType = adCmdStoredProc
collectParams cmd, params
rs.CursorLocation = adUseClient
rs.Open cmd, , adOpenForwardOnly, adLockReadOnly
Set cmd.ActiveConnection = Nothing
Set cmd = Nothing
Set rs.ActiveConnection = Nothing
Set RunSPWithRS = rs
Exit Function
errorHandler:
RaiseError g_modName, "RunSPWithRS(" & strSP & ", ...)"
그리고 웹페이지에 뿌리도록 하자
%>
<table cellpadding=2 cellspacing=2 bordercolor=Gray bgcolor=White border=2 cols=2 rules=ALL>
<tr><td bgcolor=Gray align=Left width=50><Font face=Tahoma size=1 color=White><b>Ticker
<td bgcolor=Gray align=Left width=150><Font face=Tahoma size=1 color=White><b>Company
</tr>
<%
end if
do until rs.EOF
%>
<tr><td><a href="TickerDetail.asp?ticker=<%=fldTicker.value%>">
<%=fldTicker.value%></a><td><%=fldCompany.value%></tr>
<%
rs.MoveNext
loop
count = rs.RecordCount
rs.Close
set rs = nothing
%>
</table>
<%
end if
Response.Write "<p>" & count & " records found.<p>"
end if
end sub
--------------------------------------------------------------------------
ADO Performance Best Practices
Get the best performance from your ADO application
Establishing the Connection
비동기적으로 Connection을 열도록 하면 애플리케이션이 기달릴필요없어 좋다.
First, try connecting asynchronously. With asynchronous connections
DSN을 이용한 ODBC를 사용하지 말자. ODBC는 더이상 버그가 있어도 개발안되고있다.
사용자 컴퓨터에 ODBC드라이버를 설치해야하며, 레지스트리를 뒤지기 때문에
느리다.
마이크로소프트는 기본 ODBC 프로바이더보단 OLE DB를 쓰길 권장한다.
Another way to improve connection performance is to avoid using ODBC with DSNs
Microsoft recommends that you use OLE DB instead of the default ODBC provider
그리고 꼭 좀 썼으면 커넥션 Close하고 Set Nothing 좀 해줘라.
미들티어에서나 ASP에서는 실제적으로 커넥션을 계속 열고 있을 수 없다.
일반적으로 미들티어안의 컴포넌트나 ASP페이지는 로드되었다가 MS IIS에 의해서
언로드되어 생성되었다가 소멸되는것이 종종 이뤄진다. ADO 기반 코드는 반드시
연결성립, 사용, 해제하는 connection하는 코드를 매번 실행해야 한다. connection
셋팅을 치ㅗ소한으로 줄이기 위해서는 connection/session pool을 하면 좋다.
만일 코드에 Command 객체의 ConnectionString 속성이 같은 server, initial catalog,
login ID, 다른 속성들이 풀안에 있다면 풀의 것을 쓰게 되므로 좋다. 만일
풀안에 같은것을 쓰게 되면 connect나 reconnect는 250ms안에 이뤄질수있다.
그러나 만일 ADO 코드에서 Connection Object를 해제하지 않거나 ConnectionString
만 바꾸는 식으로 한다면 OLE DB는 새로운 Connection을 매번 성립하게 된다.
그렇게되면 가용 풀을 금새 다 써버리게 된다. 그러지 않으려면 커넥션은 close한다음
set nothing 해주어야 한다. 또한 Recordset Open메소드안의 ConnectionString은
쓰지말아야한다.
In the middle tier and in ASP, you can't (in a practical sense) hold a connection open and still create a scalable component?at least not between invocations. Typically, a component or ASP page is loaded and discarded frequently as Microsoft IIS references and releases instances. Because the ADO-based code must establish, use, and release a connection each time the code is executed, strategies to minimize connection setup can help in clearly measurable terms. For these scenarios, the connection/session pool plays a big role in how quickly you can get connected. If you code your Command object's ConnectionString property correctly (i.e., by using the same server, initial catalog, login ID, and other parameters each time), the chances of a connection being open and available are good. If a matching connection is found in the pool, the time to connect (or reconnect) will be virtually nil (usually less than 250ms).
However, if your ADO (or VB) code doesn't release your Connection object or if you change the ConnectionString from instance to instance, OLE DB has to establish a new connection every time. If that happens, you'll soon run out of connections as the available pool dries up. To make sure the connection is freed, set the Connection object to Nothing after closing it. Also, don't use a ConnectionString in the Recordset Open method; open the Connection object independently so that the Connection object is easy to reference when you close it and set it to Nothing.
Building and Submitting a Query
스토어드 프로시저 써라.
Don't force SQL Server to recompile and construct a query plan for your query each time it's executed
여러 쿼리시 스토어드 프로시저나 서버사이드 스크립트로 서버와의 통신량을 줄여라.
Make as few round-trips to the server as possible
서버/클라이언트 애플리케이션의 경우 Command Parameter 속성을 reset한 후 필요시
re-execute해서 써라.
In client/server applications, construct Command objects once, not each time they're used
역시나 스토어드 프로시저써라.
Try to get ADO to generate a direct remote procedure call
adExecuteNoRecords 써서 레코드셋 반한필요없을때 그거 쓰면 좋다.
Whenever your query doesn't return a rowset, be sure to use the adExecuteNoRecords option to tell ADO to bypass any code required to set up and receive a rowset (in Recordset form)
Don't use Command objects when you're executing simple rowset-returning stored procedures.
Don't return a rowset unless you must.
레코드셋 열어서 추정 삭제 하지말고 execute 액션 쿼리를 가능하면 좀 써줘라.
Whenever possible, execute action queries (INSERT, UPDATE, and DELETE statements or stored procedures that perform these operations) instead of using updateable Recordset cursors
가능하면 쏘트하라고좀 하지말아라. 힘들어 한다.
Don't ask the server to sort unless necessary
인덱스걸린 필드를 쿼리시 잘 이용해 빠르게 쿼리하자.
Know your index structure before you write your queries
WHERE 클래스구문에 범위를 잘 지정하거나 위에서 몇개만 쿼리해 오던지하자.
Don't return too many rows
Limit the scope of the query with parameter-driven WHERE clauses or through judicious use of TOP N queries
SELECT * FROM 하지 말자니까.
Don't return too many columns
필요치 않으면 커서를 사용치 말자.
Avoid using cursors
Don't ask for scrolling, updateability, or cached data if you don't need it.
Tell ADO what you want it to do in more detail
Be sure to include the CommandType option when opening a Recordset or building a Command object. This prevents ADO from "guessing" your intentions. You can save round-trips to the server and make your code more stable.
Processing the Results
RS("Cows") 이 놈보단
RS.Fields("Cows").Value 이게 약간더 빠르다. 하지만 둘다 late binding이다.
RS!Cows도 비슷하게 late binding이다.
RS(0)) 를 하면 late binding이지만 ordinal서식 컬럼으로해서 좀더 빠르게 할 수
있지만, 읽기 어렵다.
아래 처럼하면 읽기도 쉽고 빠르게 할 수 있겠다.
Enum을 사용하여 읽기 쉽고 디자인타임에 바인드되며 빠를 수 있다.
SELECT CatName, CatType, CatSize from Cats Where...
Enum enuCatsQuery
CatName
CatType
CatSize
End Enum
StrMyName = Rs(enuCatsQuery.CatName)
late binding을 피하는 다른 방법은 아래와 같은데 코드는 노가다지만
성능은 드라마틱하다
필드 개체를 선언
Dim fldName as ADODB.Field
Dim fldType as ADODB.Field
Dim fldSize as ADODB.Field
필드 prebind rs 얻어온 후
If fldName is Nothing then
Set fldName = RS!CatName
Set fldType = RS!CatType
Set fldSize = RS!CatSize
End if
쓸때는 스트링에 복사해서 쓰면 된다.
strName = fldName
strType = fldType
strSize = fldSize
가장 성능좋은 코드를 위해서 기본을 기억하라. 커넥션 풀쓰고, 비동기 커넥션,
ADO round-trip(서버와 왕복패킷 전달)을 줄이고 , COM-Binding을 통한 early바인딩
하고 불필요한 recordset, command 객체를 쓰지말고 rowset대신 Return status나
output 파라미터를 사용해라.
For best code and coder performance, remember these basics: Leverage the connection pool and asynchronous connections, reduce the number of round-trips your ADO code makes, choose an early COM-binding technique, avoid expensive and unnecessary ADO objects such as the Recordset and Command objects, and use the Return Status or Output parameters instead of rowsets. Always try to write intelligent queries and capitalize on stored procedures whenever possible. And tell ADO what you want it to do in detail to prevent it from having to guess?specify an explicit ADO CommandType, and use options such as adExecuteNoRecords
-------------------------------------------------------------------------
MDAC Technical Articles
Improving MDAC Application Performance
Suresh Kannan
August 1999
Introduction
This document provides suggestions and guidelines for improving the performance of your MDAC application. The suggestions offered here constitute good coding practice. Performance might not noticeably improve unless accessing data is a significant part of your application processing.
Where possible, sample code has been provided to illustrate key points. In studying the samples, it is important to keep in mind the following considerations:
The best way to improve the performance of your application is to understand where your bottlenecks are. If your goal is to improve the speed of an existing application, you should start by profiling and analyzing your application.
This document describes general guidelines for improving your application performance. Some suggestions work only under certain circumstances. Where possible, such constraints have been called out. After you implement a change suggested in this document, you should measure your application's performance to validate benefits. Some modifications could be detrimental to your application performance if they are inappropriate for your application scenario.
Although Microsoft® Visual Basic® for Applications has been used to illustrate most of the suggestions, you can also apply suggested techniques to clients writing to ADO in other languages, such as C, Microsoft Visual Basic Scripting Edition (VBScript), Microsoft Visual C++®, and Java.
General Considerations
Use Strongly Typed Variables
A strongly typed variable is explicitly declared to represent only one variable type. Once declared, it cannot be used to represent a different variable type, as can weakly typed variables.
Microsoft Visual Basic allows weakly typed variables through the use of the Variant type. The Variant type can store almost any other kind of variable or object. While this gives you automatic type conversions so that you don't have to pay attention to the variable types, it can make debugging difficult. Occasionally, this automatic conversion will transform the data into a type that you didn't expect or intend, and tracking down where that happened is very difficult.
Microsoft Visual Basic defaults to the Variant type for all variables that are declared without a specific type and for all variables that are not declared at all. However, for better performance (unless the Variant type is specifically required for a property or method), avoid using Variant variables. Instead, use the Option Explicit statement to require declarations for all variables, and provide a specific type declaration for all variables that you declare. Alternatively, you can use the Deftype statements to change the default type for variables that are created and not declared or that are declared without a specific type.
Declare All Variables
A variable that is not declared will be created by Visual Basic with a default type. As discussed above, normally it will be implicitly created as a Variant. However, if you've used the Deftype statements, you can change the default.
Following is an example where the variables are implicitly created:
Public Sub NoExplicitDeclare(cnn)
Set rsl = cnn.Execute("SELECT * FROM Nonsense")
txt = rs1!SomeText.Value
Debug.Print txt
End Sub
This code has a problem that the compiler won't catch, so you probably won't find it until you run the code. What is supposed to happen is that rsl should be assigned to a Recordset returned from the open Connection object cnn. However, when you run it, you'll get an error on the line txt = rs1!SomeText.Value. Visual Basic detects that rs1 is an empty Variant and generates a run-time error, "Object Required." In a more complex algorithm, you might be led to believe that the Execute method encountered an error. The real problem is that the variable rsl is misspelled as rs1. This problem is masked because the syntax and the identifiers appear correct at first glance.
If you place the Option Explicit statement at the top of the module, Visual Basic will generate a compiler error, and after you've declared rsl as a Recordset object and txt as a String, Visual Basic will highlight rs1 with the error "Variable not defined."
Next is an example where the variables are explicitly created:
Option Explicit
Public Sub NoExplicitDeclare(cnn As Connection)
Dim rsl As Recordset
Dim txt As String
Set rsl = cnn.Execute("SELECT * FROM Nonsense")
txt = rsl!SomeText.Value
Debug.Print txt
rs1.Close
Set rs1 = Nothing
End Sub
Use Strongly Typed Object Variables
Object variables represent pointers to COM objects. They can be declared in two ways, as follows:
Use the keywords As Object so that the variable can be used to represent any kind of object; this is a weakly typed object variable.
Use the As keyword with the specific type of the object; this is a strongly typed object variable.
Microsoft Visual Basic must use late binding for all weakly typed object variables. This means Visual Basic must indirectly make every method and property call as each call is encountered at run time. Visual Basic does this by using the IDispatch interface to retrieve the identifier of each method and property function at run time and then calling IDispatch again to actually invoke the method or property. Although Visual Basic caches some of the information from IDispatch, using the late binding approach is still the slowest way to invoke methods and properties.
To create a strongly typed object variable, use the As keyword with the Dim, Private, Public, or Static statements and use the name of an object from one of the object libraries selected in the Project References dialog box. Visual Basic will read the object library at compile time to avoid calling IDispatch to get the identifiers of the methods and properties of the object. This is called early binding. In early binding, Visual Basic still invokes the properties and methods of the object through the IDispatch interface.
If the objects described in the object library use dual interfaces, it means they support not only the IDispatch interface but also a table of function addresses that mirror the properties and methods available through IDispatch. Visual Basic can then call the functions that handle the methods and properties of the object directly, bypassing the IDispatch interface altogether. This is called vtable binding, and it is the fastest way to invoke the properties and methods of an object. A vtable is simply a table of function addresses. If the object does not support dual interfaces, Visual Basic will use early binding instead.
All objects in ADO use dual interfaces, so Visual Basic can work several times faster with your ADO objects if you make your object variables strongly typed with the Dim variable As type statement. The type of the object is typically the name of an object from the object library, although it can also be the name of an interface. If you use the name of the object, Visual Basic silently substitutes the name of the default interface for that object.
Visual Basic chooses early or late binding depending on how you declare the object variable and not on how you create the object. In other words, it doesn't matter whether you use the Set variable = New type statement or the CreateObject function to create the object. What matters is how you declare the object variable. If you declare it as a specific type, Visual Basic will use early binding. If you declare the object variable as Object, Variant, or leave it undeclared, Visual Basic will use late binding.
The code in the StronglyTyped subroutine below uses the faster vtable binding by declaring the variable con as a Connection object:
Sub StronglyTyped()
Dim lngState As Long
Dim con As Connection
Set con = New Connection
con.Open "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
lngState = con.State
con.Close
Set con = Nothing
End Sub
In the NotStronglyTyped subroutine below, the type of the Connection variable isn't known at compile time, so it's declared As Object. Visual Basic uses the slower late binding method to call the methods and properties of the same Connection object used in the StronglyTyped subroutine.
Sub NotStronglyTyped()
Dim lngState As Long
Dim con As Object
Set con = New Connection
con.Open "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
lngState = con.State
con.Close
Set con = Nothing
End Sub
Explicitly Create Objects
Although object variables represent pointers to COM objects, merely declaring an object variable does not automatically create an instance of a COM object. Visual Basic offers two ways to create an instance of a COM object: implicit and explicit. While implicit creation can save some time in development, it usually costs you much more time in debugging and doesn't help at all in performance.
To implicitly create an object, use the As New keyword of the Dim statement. This permits Visual Basic to create the object automatically when you use the object variable and when the object has not been created already. When you use the Dim...As New... feature, Visual Basic adds code before every object reference to determine at run time whether the object is instantiated. This code automatically creates the object if the object variable is either not initialized or set to Nothing.
By using Dim... As New..., you'll lose some of the control over object references. For simple procedures, this won't be an issue. Visual Basic will automatically release all objects after the procedure has ended. You will take a negligible performance hit for using the automatic object creation feature that As New provides. Only you can decide this trade-off is worthwhile for simple procedures, but once you decide, be very consistent.
The following example shows implicit object creation with the As New keywords:
Sub AutomaticCreation()
Dim rsNS As New Recordset
rsNS.ActiveConnection = "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
rsNS.CursorLocation = adUseServer
End Sub
There is an important reason why you might want to avoid Dim...As New in some circumstances, even for simple procedures. There are times when having an object variable set to Nothing is a valid, testable state. The Dim...As New feature prevents an object variable from ever being set to Nothing when you test its state. For example, the following snippet will always print, "n is set" to the Debug window:
Dim n As New Recordset
If n Is Nothing Then
Debug.Print "n is nothing"
Else
Debug.Print "n is set"
End If
In the preceding example, the very act of testing to see whether the object is created creates the object! In cases where the object is set to Nothing to indicate a valid condition, as is the case with the NextRecordset method of the Recordset object, you will never be able to detect that the object is set to Nothing.
To explicitly create an object, do not use the As New feature. If you use the object variable and the object has not been created already, Visual Basic will raise an error. You must use the Set statement to create the object when you will use it.
The following example shows explicit object creation:
Sub ExplicitCreation()
Dim rsNS As Recordset
Set rsNS = New Recordset
rsNS.ActiveConnection = "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
rsNS.CursorLocation = adUseServer
Set rsNS = Nothing
End Sub
Reuse Command Objects
If you are going to use a particular stored procedure, view, or SQL statement several times, don't create a new Command object each time. Use a static variable or a module-level variable to keep a reference to each Command object that you will reuse.
Note This technique might not work well for VBScript running in Active Server Pages, or for applications written for Microsoft Component Transaction Services.
Bind Columns to Field Objects When Looping Through the Records
There are at least two common ways to get the values for fields in a Recordset. One way is to look up each field by its name or ordinal position from the Fields collection of the Recordset object each time you need the value. The other way is to reuse the Field objects in the Fields collection as you iterate through the records in the Recordset.
The code in ColumnsNotBound looks up fields by ordinal position. This incurs the overhead of looking up each field in the Fields collection for each record in the Recordset. For a Recordset with many records, this can get quite expensive.
Sub ColumnsNotBound()
Dim rsNS As Recordset
Dim strText As String
Dim strMoney As String
Set rsNS = New Recordset
rsNS.Open "SELECT SomeText, SomeNumber, SomeTime, SomeMoney " _
"FROM Nonsense", "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
Do Until rsNS.EOF
strText = rsNS.Fields(0).Value
strMoney = rsNS.Fields(3).Value
rsNS.MoveNext
Loop
rsNS.Close
Set rsNS = Nothing
End Sub
The code in ColumnsBound, however, obtains references to the Field objects at the beginning and simply uses those same references when looping through the records.
Sub ColumnsBound()
Dim rsNS As Recordset
Dim strText As String
Dim strMoney As String
Dim fldText As Field
Dim fldMoney As Field
Set rsNS = New Recordset
rsNS.Open "SELECT SomeText, SomeNumber, SomeTime, SomeMoney " _
"FROM Nonsense", "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
Set fldText = rsNS.Fields(0)
Set fldMoney = rsNS.Fields(3)
Do Until rsNS.EOF
strText = fldText.Value
strMoney = fldMoney.Value
rsNS.MoveNext
Loop
rsNS.Close
Set rsNS = Nothing
End Sub
Use Cursor-Based Updating Only If Needed
Try to avoid the use of cursor-based updating. Although using an SQL statement to update data is not feasible in many scenarios, you should use it where possible. Although updating data through the Recordset object is often more convenient, it is also much more expensive. Despite being cumbersome to use, updating data through an SQL statement is well worth the trouble. The routine that uses SQL will update several dozen records in a 5,000 record table about 30 times faster than the routine that uses a cursor.
The following example shows cursor-based updating:
Sub ADOUpdate()
Dim cnNS As Connection
Dim rsNS As Recordset
Dim fldText As Field
Dim fldNumber As Field
Dim lngUpdateEach As Long
lngUpdateEach = Rnd * 99 + 1
Set cnNS = New Connection
cnNS.ConnectionString = "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
cnNS.Open
Set rsNS = New Recordset
Set rsNS.ActiveConnection = cnNS
rsNS.CursorLocation = adUseClient
rsNS.CursorType = adOpenStatic
rsNS.LockType = adLockBatchOptimistic
rsNS.Open "Nonsense", , , , adCmdTable
Set fldText = rsNS!SomeText
Set fldNumber = rsNS!SomeNumber
rsNS.MoveFirst
Do Until rsNS.EOF
If fldNumber.Value Mod lngUpdateEach = 0 Then
fldText.Value = UCase$(fldText.Value)
End If
rsNS.MoveNext
Loop
rsNS.UpdateBatch
rsNS.Close
cnNS.Close
Set rsNS = Nothing
Set cnNS = Nothing
End Sub
The next example uses an SQL statement to update data:
Sub SQLUpdate()
Dim cnNS As Connection
Dim lngUpdateEach As Long
Dim cmNS As Command
Set cmNS = New Command
lngUpdateEach = Rnd * 99 + 1
Set cnNS = New Connection
cnNS.Open "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
Set cmNS.ActiveConnection = cnNS
cmNS.CommandText = "UPDATE Nonsense SET SomeText = UPPER(SomeText) " _
& "WHERE SomeNumber % " & lngUpdateEach & " = 0"
cmNS.CommandType = adCmdText
cmNS.Prepared = True
cmNS.Execute , , adExecuteNoRecords
cnNS.Close
Set cnNS = Nothing
Set cmNS = Nothing
End Sub
Use a Stored Procedure with Output Parameters Instead of Singleton Selects
When you know that the result of your query will yield only a single row of data, instead of opening a Recordset for fetching that data, you can use a stored procedure with output parameters.
When you use a Recordset, the query results returned by the data source object include data and metadata. Often the metadata is much larger than the data or is a significant part of the query results. Because of this, you may want to use a stored procedure with output parameters instead.
The following example shows a singleton select statement:
Sub SingletonSelect()
Dim rs As Recordset
Dim strText As String
Dim timTime As Date
Dim n As Integer
Set rs = New Recordset
rs.ActiveConnection = "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
rs.CursorLocation = adUseServer
rs.CursorType = adOpenForwardOnly
rs.LockType = adLockReadOnly
For n = 1 To 50
rs.Source = "SELECT sometext, sometime FROM nonsense " & _
"WHERE ID = '" & CStr(CLng(Rnd * 5000)) & "'"
rs.Open
strText = rs.Fields(0).Value
timTime = rs.Fields(1).Value
rs.Close
Next
Set rs = Nothing
End Sub
The next example retrieves two output parameters from a stored procedure that selects a single record:
Sub SingletonSp()
Dim cmd As Command
Dim strText As String
Dim timTime As Date
Dim n As Integer
Set cmd = New Command
cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
cmd.CommandText = "GetTextTimeUsingID"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append _
cmd.CreateParameter("inID", adInteger, adParamInput, 4)
cmd.Parameters.Append _
cmd.CreateParameter("outText", adChar, adParamOutput, 32)
cmd.Parameters.Append _
cmd.CreateParameter("outTime", adDate, adParamOutput, 8)
For n = 1 To 50
cmd(0).Value = Rnd * 5000
cmd.Execute , , adExecuteNoRecords
strText = cmd(1).Value
timTime = cmd(2).Value
Next
Set cmd = Nothing
End Sub
Here is the code for the stored procedure:
CREATE PROCEDURE [GetTextTimeUsingID]
@inID int = 0,
@outText char(32) OUTPUT,
@outTime datetime OUTPUT
AS
IF @inID = 0 RETURN 2
SELECT @outText = SomeText, @outTime = SomeTime FROM Nonsense
WHERE ID = @inID
RETURN
If You Must Use a Cursor, Use the Collect Methods for Singleton Selects
The Collect method is a hidden method of the Recordset object that lets you quickly get and set the Value property of a Field object without having to obtain a field reference first. This method is appropriate to use when you aren't interested in obtaining or setting any properties other than Field.Value.
Following is an example using the Collect method:
Sub Collect()
Dim rs As Recordset
Dim strText As String
Dim timTime As Date
Set rs = New Recordset
rs.ActiveConnection = "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
rs.Source = "SELECT SomeText, SomeNumber, SomeTime, SomeMoney " & _
"FROM Nonsense WHERE ID = '2500'"
rs.Open
strText = rs.Collect(0)
timTime = rs.Collect(2)
rs.Close
Set rs = Nothing
End Sub
Query Only for the Data You Need
Although it is easy to fall into the habit of using SELECT * queries, consider asking only for the columns you need. Also, consider adding restrictions, such as WHERE clauses, to your query to limit the records returned.
Choose CursorLocation, CursorType, and LockType Properties Carefully
If you don't need scrolling or updatability, don't ask for it. The ADO defaults of adUseServer, adOpenForwardOnly, and adLockReadOnly offer you the best performance for doing a forward-only scan through the records. Don't ask for a more functional cursor if your application doesn't require it.
If you do want scrolling, don't default to server cursors as ADO does. The ADO CursorLocation default is adUseServer primarily for backward compatibility reasons. However, for most scrolling scenarios, you will be much better off with a client cursor. Only in specific scenarios, such as extremely large data sets, will you be better off with a server cursor. When using a client cursor, don't ask for anything other than a LockType value of adLockReadOnly unless you really need it. If you ask for updatability, the client cursor engine needs to get additional metadata, which can be very expensive to retrieve.
Tune the Recordset.CacheSize Property
ADO uses the Recordset.CacheSize property to determine the number of rows to fetch and cache. This especially affects server-side cursors. While you are within the range of cached rows, ADO just returns data from the cache. When you scroll out of the range of cached rows, ADO releases the cache and fetches the next CacheSize rows. The default value for the CacheSize property is 1.
How do you determine what value you should use for the CacheSize property in your application? Unfortunately, there isn't a single optimal CacheSize value for all applications. You should try tuning your application with different CacheSize values, and use the value that offers you the best performance. For example, knowing that a small CacheSize value significantly improves performance for fetching data from an Oracle data store might be an important factor for you.
Release Unused ADO Objects
Whenever possible, release ADO objects as soon as you're done with them. This frees up the database and other resources that might be expensive to hold for an extended period. Explicitly close all objects rather than allowing the object to close itself as it is destroyed.
Describe Command Parameters Yourself
In many data stores, getting command parameter information is often as expensive as executing the command. Describe the command parameters yourself instead of getting the parameter information from the provider.
The following example shows how to get the parameter information from the provider:
Sub ProviderDescribedParameters()
Dim cmd As Command
Dim lngRetVal As Long
Dim strText As String
Dim timTime As Date
Set cmd = New Command
cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
cmd.CommandText = "GetTextTimeUsingID"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters(1).Value = 100
cmd.Execute
lngRetVal = cmd.Parameters(0).Value
strText = cmd.Parameters(2).Value
timTime = cmd.Parameters(3).Value
Set cmd = Nothing
End Sub
The next example shows how to describe the parameters manually:
Sub UserDescribedParameters()
Dim cmd As Command
Dim lngRetVal As Long
Dim strText As String
Dim timTime As Date
Set cmd = New Command
cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
cmd.CommandText = "GetTextTimeUsingID"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append _
cmd.CreateParameter("Return Value", adInteger, adParamReturnValue)
cmd.Parameters.Append _
cmd.CreateParameter("inID", adInteger, adParamInput)
cmd.Parameters.Append _
cmd.CreateParameter("outText", adChar, adParamOutput, 32)
cmd.Parameters.Append _
cmd.CreateParameter("outTime", adDate, adParamOutput, 8)
cmd.Parameters(1).Value = 100
cmd.Execute
lngRetVal = cmd.Parameters(0).Value
strText = cmd.Parameters(2).Value
timTime = cmd.Parameters(3).Value
Set cmd = Nothing
End Sub
Use Native OLE DB Providers
MDAC ships with native providers for several data stores, including SQL Server, Oracle, and Microsoft Jet (.mdb). With earlier versions, you had to go through the OLE DB Provider for ODBC, which in turn used the appropriate ODBC driver to access these data stores. The provider used as the default for connections is still the OLE DB Provider for ODBC, but you should use these native OLE DB providers so that you can access your data faster and with lower disk and memory footprint. The SQL Server provider is written to TDS, the Oracle provider to OCI, and the Microsoft Jet provider to the Microsoft Jet Engine API.
Disconnect Your Client Cursor from the Connection for R/O and Long-Use Scenarios
Disconnected Recordset objects are supported by the client cursor engine. Use this feature when you are performing a time-consuming operation that doesn't require expensive database resources to be held open. If you need to, you can later reconnect the Recordset to the connection to perform updates.
Following is an example that shows how to disconnect a Recordset:
Sub DisconnectRS()
Dim con As Connection
Dim rs As Recordset
Set con = New Connection
Set rs = New Recordset
con.CursorLocation = adUseClient
con.Open "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
Set rs = con.Execute("SELECT SomeText, SomeNumber, SomeTime, " & _
"SomeMoney FROM Nonsense")
Set rs.ActiveConnection = Nothing
Set con = Nothing
'
' Process data
'
rs.Close
Set rs = Nothing
End Sub
Anticipate Non?Row Returning Commands
ADO includes an ExecuteOptionEnum option called adExecuteNoRecords. Use this option for commands that do not return rows. When this option is specified, ADO does not create a Recordset object, does not set any cursor properties, and specifies IID_NULL REFIID on ICommand::Execute. Also, because IID_NULL is specified on ICommand::Execute, the provider can optimize for this case by not verifying any rowset properties.
The following example demonstrates how the adExecuteNoRecords option is used:
Sub ExecuteNoRecords()
Dim con As Connection
Set con = New Connection
con.Open strConnection
con.Execute "INSERT INTO Nonsense VALUES('" & Greeking(32) & _
"', " & CStr(CLng(Rnd * &H7FFFFFFF)) & _
", " & CStr(CLng(Now)) & _
", " & CStr(CCur(curStart)) & ",DEFAULT ,DEFAULT, DEFAULT, " & _
"DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)", , _
adExecuteNoRecords
con.Close
Set con = Nothing
End Sub
Use Connection.Execute for Single Execution
ADO has some optimizations for one-time command executions when the executions are done through the Execute method of the Connection object. This is a common user scenario in Microsoft Internet Information Server (IIS), Active Server Pages (ASP), and Microsoft Component Services (formerly Microsoft Transaction Server) environments, where the code typically opens a connection, executes a row returning or non?row returning command, processes results, and closes the connection. For such scenarios, use Connection.Execute instead of Recordset.Open or Command.Execute. When you use Connection.Execute, ADO doesn't preserve any command state information, which leads to an improvement in performance. However, if you need a more functional cursor or if you need to use Command.Parameters, you might still need to use Recordset.Open or Command.Execute.
Use ADO C++ Extensions (C++ Users)
ADO is an Automation server, which means it implements Automation interfaces. Therefore, it provides language-independent access to OLE DB. However, although ADO interfaces are accessible from C, C++, and Java, structures such as VARIANTs, which are easy to use from Visual Basic for Applications (VBA), are quite cumbersome to use from C, C++, and Java.
In versions of Microsoft Visual C++ earlier than 5.0, Automation interfaces were hard to use because of lack of language support for COM and VARIANTs. Now, with native compiler support for COM in Visual C++ 5.0, Automation interfaces are much easier to use from Visual C++. However, if you still want to fetch data into C types instead of VARIANTs, you can do so by using the ADO C++ extensions. Besides avoiding the VARIANT overhead, the C++ extensions offer good performance. When you use them, ADO doesn't need to get the column information from the provider. Instead, ADO uses the column information supplied at design time in the form of binding entries.
The following C++ code example shows how to use the ADO C++ extensions to get the values from three VARCHAR fields.
class CAuthor :
public CADORecordBinding
{
BEGIN_ADO_BINDING(CCustomRs1)
ADO_VARIABLE_LENGTH_ENTRY4(1, adVarChar, m_szau_id,
sizeof(m_szau_id), FALSE)
ADO_VARIABLE_LENGTH_ENTRY4(2, adVarChar, m_szau_fname,
sizeof(m_szau_fname), FALSE)
ADO_VARIABLE_LENGTH_ENTRY4(3, adVarChar, m_szau_lname,
sizeof(m_szau_lname), FALSE)
END_ADO_BINDING()
protected:
char m_szau_id[12];
char m_szau_fname[21];
char m_szau_lname[41];
};
void FetchAuthorData()
{
CAuthor author;
_RecordsetPtr pRs;
IADORecordBinding *piAdoRecordBinding;
pRs.CreateInstance(__uuidof(Recordset));
pRs->Open("select au_id, au_fname, au_lname from Employees",
"Provider=SQLOLEDB;Data Source=sureshk1;Database=pubs;"
"User Id=sa;Password=;",
adOpenForwardOnly, adLockReadOnly, adCmdText);
pRs->QueryInterface(__uuidof(IADORecordBinding),
(LPVOID *)&piAdoRecordBinding);
piAdoRecordBinding->BindToRecordset(&author);
while (VARIANT_FALSE == pRs->EOF)
{
printf("%s %s %s", author.m_szau_id, author.m_szau_fname,
author.m_szau_lname);
pRs->MoveNext();
}
piAdoRecordBinding->Release();
}
Middle-Tier Considerations
Use Session (OLE DB) or Connection (ODBC) Pooling
A database connection is an expensive resource to open and close. Therefore, pooling this resource offers a huge performance improvement for middle-tier applications.
When you use MDAC, you don't have to worry about how to pool your database connections. MDAC takes care of it for you. Pooling is supported at two levels: OLE DB sessions and ODBC connections. If you use ADO, your database connections are pooled automatically by OLE DB session pooling. If you use ODBC, the new Connection Pooling tab in the ODBC Data Source Administrator lets you control the ODBC connection pooling settings and the ODBC Driver Manager takes care of pooling for you.
Use ADO Like an Apartment Model
Although the ADO implementation is free-threaded, don't use it in that way in the middle tier. Don't cache an instance of an ADO object, such as a Connection, globally and invoke methods on it concurrently from multiple threads. If each client request in your application model invokes the Connection.Execute method on a globally cached Connection object on the middle tier, your application will not scale. This is because of synchronization in the Connection.Execute method.
You will get much better throughput by using an application model where each client request creates a new instance of a Connection object, calls Connection.Open and then Connection.Execute, and releases the Connection object on the middle tier. Each request does have the additional overhead of creating a new instance of a Connection object and obtaining a connection from the connection pool. However, because your Connection.Execute call isn't synchronized, the throughput is much better.
ASP Considerations
Don't cache ADO objects in Global.asa. See "Use ADO Like an Apartment Model" earlier in this document.
Conclusion
Improving the performance of any application is both a science and an art. The recommendations listed here will help with many areas, but every application has different circumstances. The environments may differ from one installation to the next. A setting that makes the application run faster on one machine may make it run slower on a different machine. Even different database schemas will affect many of the suggestions in this article.
There is no substitute for advanced techniques like code profiling, performance monitoring, and good old trial and error. As with any scientific approach, you should vary only one element at a time and note whether performance improves or worsens with each variation. If performance deteriorates, before assuming that the change was the cause of the problem, revert the change and verify that you can reproduce the original behavior.
ⓒ 1991-2000 Microsoft Corporation. All rights reserved.
조경민 ADO for Performance
=============================================================
1. 매 작업시 Connection 객체를 새로 생성/ 소멸하도록 한다.
(Use ADO Like an Apartment Model)
=> 내부적으로 연결 Pooling을 지원하기 때문에 성능상 문제가 없다.
내가 계속 물고 있으면 리소스만 잡을 뿐이다.
2. 입력 수정 삭제의 경우 트랜젝션을 걸어두자.
=> 조회만 있을 경우 트랜잭션은 불필요
3. 옵션을 잘사용하자
adExecuteNoRecords 를 사용하면 레코드셋을 받을 필요없을때 유리하다
adReadOnly는 조회시 갱신이 없을때 유리하다.
4. Use Session (OLE DB) or Connection (ODBC) Pooling
=> Use MTS 또는 ODBC 3.0 Connection Pooling 커넥션 풀링 탭에서 설정
5. Disconnect Your Client Cursor from the Connection for R/O and Long-Use Scenarios
Sub DisconnectRS()
Dim con As Connection
Dim rs As Recordset
Set con = New Connection
Set rs = New Recordset
con.CursorLocation = adUseClient
con.Open "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
Set rs = con.Execute("SELECT SomeText, SomeNumber, SomeTime, " & _
"SomeMoney FROM Nonsense")
Set rs.ActiveConnection = Nothing <= 이부분
Set con = Nothing <= 이부분
'
' Process data
'
rs.Close
Set rs = Nothing
End Sub
6. 조회시 SELECT * 하지말고 원하는 컬럼만 받도록하자
7. 가능하다면 Stored Procedure를 사용하자
8. 커서는 가능한 사용하지 말자
9. Command Object은 가능하면 재사용하자. 한번 전역 객체화시킨 후
이를 계속 사용하는것이 좋다.
----------------------------------------------------------------------
참고 MSDN
----------------------------------------------------------------------
Using the ADO GetRows Method for Faster Data Retrieval
ADO에서 Recordset으로 반환보다 GetRow를 이용하면 더 빠르다고 한다.
Table A: Processing time (sec) for ADO 2.0 objects using the GetRows method
Total Records Recordset Command Parameter
10,000 3.07 2.86 2.91
20,000 5.88 5.66 5.77
40,000 11.42 11.20 11.37
Table B: Comparison of GetRows method to Recordset object by itself
with 40,000 records.
Method Type Recordset Command Parameter
GetRows 11.42 11.20 11.37
Recordset object 15.55 14.72 14.66
약간더 빠른거 같다 ㅡ.ㅡ
------------------------------------------------------------------------
Optimize Data Access Using ADO
1. Avoid Updating Through Recordsets
레코드셋을 반환받아 커서이동하면서 업데이트하는 것보다는
스토어드프로시저를 이요하거나 특별한 경우엔 여러번의 추가 및 삭제를 이용하는
것이 더 효율적이다.
It's more efficient to update the database through stored procedures, especially when performing multiple inserts and deletes
2. Use Stored Procedures
스토어드프로시저는 빠르게 실행되도록 컴파일되어 서버에 쿼리 실행에대한 plan(계획)
이 짜여지게되어 효율적으로 처리하게 된다.
DB 조작 코드가 애플리케이션에 위치하지 않고 서버에 있으니 보다 유지보수 및
보안에 유리하다
they are compiled to be faster
Other advantages to using stored procedures include easier maintenance and security
3. Create the Minimum Recordset
레코드셋을 열때는 CursorType, CursorLocation, LockType를 지정하는것이 좋다.
Use the CursorType, CursorLocation, and LockType properties to create the most efficient recordset
With adoRS
.ActiveConnection = "Pubs"
.CursorType = adOpenStatic <= 여기
.CursorLocation = adUseClient <= 여기
.LockType = adLockReadOnly <= 여기
.Source = "SELECT title_id, " & _
"title, pub_id FROM Titles"
.Open
End With
*MTS를 쓰게되면 Client 위치의 커서만 쓸수있다.
If you're going to pass recordsets to MTS components, you can only use disconnected (client-side) recordsets
4. Use Disconnected Recordsets
Dim adoRS As New ADODB.RecordsetWith adoRS
.ActiveConnection = "Pubs"
.CursorLocation = adUseClient .CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.Source = "SELECT emp_id, " & _
"lname,hire_date FROM Employee"
.Open Set .ActiveConnection = Nothing <= 여기
End With
There are many advantages to using disconnected recordsets. Scalability is improved, because adding 50 users doesn't mean 50 open database connections and 50 open cursors on the database server. Time-consuming operations executed on a disconnected recordset will not affect the database server and all modifications are batched. The recordset must be reconnected to the data source to update the database. Note that with a disconnected recordset, changes made on the database by other users will not be visible?concurrency issues must be handled with code.
5. Define Command Parameters
6. Encapsulate Data Access
3-tier식으로 해서, 클라이언트 애플리케이션은 DCOM으로 미들티어 비지니스로직
VB DLL, COM에게 로직을 호출하고, 이 미들티어는 OLEDB를 통해 DB서버로 연결하도록하자
8. Use MTS
Putting your components in MTS gives you the benefits of scalability, connection pooling, and transactions.
연결 풀링을 지원하게 된다.
SetComplete, SetAbort를 하게되면 모든 트랜젝션은 Commit되거나 Roll Back되게된다.
All transactions occur in an object's context, and all the transaction's operations are committed or rolled back using the SetComplete or SetAbort method
단점은
There are some trade-offs to consider when using MTS. Increased overhead is involved when referencing MTS components because they reside remotely. Additional time is spent marshalling the data to the MTS component if parameters are passed from the client to an MTS component. Consider these issues when making design decisions before creating components to run in MTS. Depending on your requirements, it could be well worth the effort.
9. Use Output Parameters
Use output parameters instead of returning a recordset if you have a stored procedure that returns a single value or row of data
With adoCommand
.Parameters.Append .CreateParameter("RETURN_VALUE", _
adInteger, adParamReturnValue, 0)
.Parameters.Append .CreateParameter("@Zip", _
adVarChar, adParamInput, 20, Null)
.Parameters.Append .CreateParameter("@State", _
adVarChar, adParamInputOutput, 2, Null)
.Parameters.Append .CreateParameter("@City", _
adVarChar, adParamInputOutput, 50, Null) .Parameters("@Zip") = Zip SQL = "usp_GetCityStateForZip" .CommandText = SQL
.CommandType = adCmdStoredProc
.ActiveConnection = "Pubs"
.Execute 'Check the return value after the update
If .Parameters("RETURN_VALUE") = 0 Then _
State = .Parameters("@State") & "
City = .Parameters("@City") & "
End If
End With
10. Retrieve Only Required Data
SELECT * FROM table 하지말자.
SELECT title_id, title, type, price FROM Titles이런게 더 좋지 않냐
또
Dim adoCommand As New ADODB.CommandWith adoCommand
.ActiveConnection = "Pubs"
.CommandText = "usp_InsertNewTitle"
.CommandType = adStoredProc
.Execute , , adExecuteNoRecords <= 이렇게 하면 레코드셋 반환필요없을시 유리
End With
------------------------------------------------------------------------
High-performance Visual Basic Apps
ASP 서보트 VB COM으로 웹페이지에서 ADO MTS를 써서 빠르게 하자.
- 스토어드 프로시저를 사용하여 웹 로그인 모듈을 맡게한다.
- Disconnected 레코드셋으로 하여 효율을 높인다.
Alter Procedure Account_VerifyLogin
(
@EMail varchar(50),
@Password varchar(50),
@AccountID int output,
@FullName varchar(100) output
)
As
select @AccountID = AccountID,
@FullName = FirstName + ' ' + LastName
from accounts
where email = @EMail
and Password = @Password
group by AccountID, FirstName + ' ' + LastName
return @@ROWCOUNT ? 0 means not found, 1 means it matched
한 후
VB ASP용 COM에서
dim email, password
Response.Buffer = true
Response.Clear
email = trim(Request("login"))
password = trim(Request("password"))
If email <> "" Then
dim objAccount
set objAccount = Server.CreateObject("FMStocks_Bus.Account")
dim AccountID, FullName
if objAccount.VerifyLogin(email, password, FullName, _
AccountID) then
Response.Cookies("Account")("AccountID") = AccountID
set objAccount = nothing
Response.Redirect("home.htm")
else
set objAccount = nothing
Response.Redirect("default.htm")
end if
else
Response.Redirect ("default.htm")
end if
를 하고
스토어드 프로시저 호출하는 방법은 아래처럼 한다.
Public Function VerifyUser(ByVal email As String, _
ByVal password As String, _
ByRef AccountID As Variant, ByRef FullName As Variant) _
As Boolean
On Error GoTo errorHandler
Dim strUserInfo As String
'The following code was taken from Database.cls RunSPWithString
'and modified to support 2 outputs and 1 return value
' Set up Command and Connection objects
Dim cmd As ADODB.Command
Set cmd = CtxCreateObject("ADODB.Command")
'Run the procedure
cmd.ActiveConnection = GetDSN()
cmd.CommandText = "Account_VerifyLogin"
cmd.CommandType = adCmdStoredProc
With cmd
.Parameters.Append .CreateParameter(, adInteger, _
adParamReturnValue) 'rs(4).Value
.Parameters.Append .CreateParameter("@EMail", _
adVarChar, adParamInput, 50, email)
.Parameters.Append .CreateParameter("@Password", _
adVarChar, adParamInput, 50, password)
.Parameters.Append .CreateParameter("@AccountID", _
adInteger, adParamOutput, 4)
.Parameters.Append .CreateParameter("@FullName", _
adVarChar, adParamOutput, 50)
End With
cmd.Execute , , ADODB.adExecuteNoRecords
If cmd.Parameters(0).Value = 0 Then
VerifyUser = False
Else
AccountID = cmd.Parameters(3).Value
FullName = cmd.Parameters(4).Value
VerifyUser = True
End If
Set cmd = Nothing
CtxSetComplete
Exit Function
errorHandler:
RaiseError g_modName, "VerifyUser"
End Function
물론 RS는 Disconnected로 만드는게 좋다
Function RunSPWithRS(ByVal strSP As String, _
ParamArray params() As Variant) As adodb.Recordset
On Error GoTo errorHandler
Dim rs As adodb.Recordset, cmd As adodb.Command
Set rs = CtxCreateObject("ADODB.Recordset")
Set cmd = CtxCreateObject("ADODB.Command")
'Run the procedure
cmd.ActiveConnection = GetDSN()
cmd.CommandText = strSP
cmd.CommandType = adCmdStoredProc
collectParams cmd, params
rs.CursorLocation = adUseClient
rs.Open cmd, , adOpenForwardOnly, adLockReadOnly
Set cmd.ActiveConnection = Nothing
Set cmd = Nothing
Set rs.ActiveConnection = Nothing
Set RunSPWithRS = rs
Exit Function
errorHandler:
RaiseError g_modName, "RunSPWithRS(" & strSP & ", ...)"
그리고 웹페이지에 뿌리도록 하자
%>
<table cellpadding=2 cellspacing=2 bordercolor=Gray bgcolor=White border=2 cols=2 rules=ALL>
<tr><td bgcolor=Gray align=Left width=50><Font face=Tahoma size=1 color=White><b>Ticker
<td bgcolor=Gray align=Left width=150><Font face=Tahoma size=1 color=White><b>Company
</tr>
<%
end if
do until rs.EOF
%>
<tr><td><a href="TickerDetail.asp?ticker=<%=fldTicker.value%>">
<%=fldTicker.value%></a><td><%=fldCompany.value%></tr>
<%
rs.MoveNext
loop
count = rs.RecordCount
rs.Close
set rs = nothing
%>
</table>
<%
end if
Response.Write "<p>" & count & " records found.<p>"
end if
end sub
--------------------------------------------------------------------------
ADO Performance Best Practices
Get the best performance from your ADO application
Establishing the Connection
비동기적으로 Connection을 열도록 하면 애플리케이션이 기달릴필요없어 좋다.
First, try connecting asynchronously. With asynchronous connections
DSN을 이용한 ODBC를 사용하지 말자. ODBC는 더이상 버그가 있어도 개발안되고있다.
사용자 컴퓨터에 ODBC드라이버를 설치해야하며, 레지스트리를 뒤지기 때문에
느리다.
마이크로소프트는 기본 ODBC 프로바이더보단 OLE DB를 쓰길 권장한다.
Another way to improve connection performance is to avoid using ODBC with DSNs
Microsoft recommends that you use OLE DB instead of the default ODBC provider
그리고 꼭 좀 썼으면 커넥션 Close하고 Set Nothing 좀 해줘라.
미들티어에서나 ASP에서는 실제적으로 커넥션을 계속 열고 있을 수 없다.
일반적으로 미들티어안의 컴포넌트나 ASP페이지는 로드되었다가 MS IIS에 의해서
언로드되어 생성되었다가 소멸되는것이 종종 이뤄진다. ADO 기반 코드는 반드시
연결성립, 사용, 해제하는 connection하는 코드를 매번 실행해야 한다. connection
셋팅을 치ㅗ소한으로 줄이기 위해서는 connection/session pool을 하면 좋다.
만일 코드에 Command 객체의 ConnectionString 속성이 같은 server, initial catalog,
login ID, 다른 속성들이 풀안에 있다면 풀의 것을 쓰게 되므로 좋다. 만일
풀안에 같은것을 쓰게 되면 connect나 reconnect는 250ms안에 이뤄질수있다.
그러나 만일 ADO 코드에서 Connection Object를 해제하지 않거나 ConnectionString
만 바꾸는 식으로 한다면 OLE DB는 새로운 Connection을 매번 성립하게 된다.
그렇게되면 가용 풀을 금새 다 써버리게 된다. 그러지 않으려면 커넥션은 close한다음
set nothing 해주어야 한다. 또한 Recordset Open메소드안의 ConnectionString은
쓰지말아야한다.
In the middle tier and in ASP, you can't (in a practical sense) hold a connection open and still create a scalable component?at least not between invocations. Typically, a component or ASP page is loaded and discarded frequently as Microsoft IIS references and releases instances. Because the ADO-based code must establish, use, and release a connection each time the code is executed, strategies to minimize connection setup can help in clearly measurable terms. For these scenarios, the connection/session pool plays a big role in how quickly you can get connected. If you code your Command object's ConnectionString property correctly (i.e., by using the same server, initial catalog, login ID, and other parameters each time), the chances of a connection being open and available are good. If a matching connection is found in the pool, the time to connect (or reconnect) will be virtually nil (usually less than 250ms).
However, if your ADO (or VB) code doesn't release your Connection object or if you change the ConnectionString from instance to instance, OLE DB has to establish a new connection every time. If that happens, you'll soon run out of connections as the available pool dries up. To make sure the connection is freed, set the Connection object to Nothing after closing it. Also, don't use a ConnectionString in the Recordset Open method; open the Connection object independently so that the Connection object is easy to reference when you close it and set it to Nothing.
Building and Submitting a Query
스토어드 프로시저 써라.
Don't force SQL Server to recompile and construct a query plan for your query each time it's executed
여러 쿼리시 스토어드 프로시저나 서버사이드 스크립트로 서버와의 통신량을 줄여라.
Make as few round-trips to the server as possible
서버/클라이언트 애플리케이션의 경우 Command Parameter 속성을 reset한 후 필요시
re-execute해서 써라.
In client/server applications, construct Command objects once, not each time they're used
역시나 스토어드 프로시저써라.
Try to get ADO to generate a direct remote procedure call
adExecuteNoRecords 써서 레코드셋 반한필요없을때 그거 쓰면 좋다.
Whenever your query doesn't return a rowset, be sure to use the adExecuteNoRecords option to tell ADO to bypass any code required to set up and receive a rowset (in Recordset form)
Don't use Command objects when you're executing simple rowset-returning stored procedures.
Don't return a rowset unless you must.
레코드셋 열어서 추정 삭제 하지말고 execute 액션 쿼리를 가능하면 좀 써줘라.
Whenever possible, execute action queries (INSERT, UPDATE, and DELETE statements or stored procedures that perform these operations) instead of using updateable Recordset cursors
가능하면 쏘트하라고좀 하지말아라. 힘들어 한다.
Don't ask the server to sort unless necessary
인덱스걸린 필드를 쿼리시 잘 이용해 빠르게 쿼리하자.
Know your index structure before you write your queries
WHERE 클래스구문에 범위를 잘 지정하거나 위에서 몇개만 쿼리해 오던지하자.
Don't return too many rows
Limit the scope of the query with parameter-driven WHERE clauses or through judicious use of TOP N queries
SELECT * FROM 하지 말자니까.
Don't return too many columns
필요치 않으면 커서를 사용치 말자.
Avoid using cursors
Don't ask for scrolling, updateability, or cached data if you don't need it.
Tell ADO what you want it to do in more detail
Be sure to include the CommandType option when opening a Recordset or building a Command object. This prevents ADO from "guessing" your intentions. You can save round-trips to the server and make your code more stable.
Processing the Results
RS("Cows") 이 놈보단
RS.Fields("Cows").Value 이게 약간더 빠르다. 하지만 둘다 late binding이다.
RS!Cows도 비슷하게 late binding이다.
RS(0)) 를 하면 late binding이지만 ordinal서식 컬럼으로해서 좀더 빠르게 할 수
있지만, 읽기 어렵다.
아래 처럼하면 읽기도 쉽고 빠르게 할 수 있겠다.
Enum을 사용하여 읽기 쉽고 디자인타임에 바인드되며 빠를 수 있다.
SELECT CatName, CatType, CatSize from Cats Where...
Enum enuCatsQuery
CatName
CatType
CatSize
End Enum
StrMyName = Rs(enuCatsQuery.CatName)
late binding을 피하는 다른 방법은 아래와 같은데 코드는 노가다지만
성능은 드라마틱하다
필드 개체를 선언
Dim fldName as ADODB.Field
Dim fldType as ADODB.Field
Dim fldSize as ADODB.Field
필드 prebind rs 얻어온 후
If fldName is Nothing then
Set fldName = RS!CatName
Set fldType = RS!CatType
Set fldSize = RS!CatSize
End if
쓸때는 스트링에 복사해서 쓰면 된다.
strName = fldName
strType = fldType
strSize = fldSize
가장 성능좋은 코드를 위해서 기본을 기억하라. 커넥션 풀쓰고, 비동기 커넥션,
ADO round-trip(서버와 왕복패킷 전달)을 줄이고 , COM-Binding을 통한 early바인딩
하고 불필요한 recordset, command 객체를 쓰지말고 rowset대신 Return status나
output 파라미터를 사용해라.
For best code and coder performance, remember these basics: Leverage the connection pool and asynchronous connections, reduce the number of round-trips your ADO code makes, choose an early COM-binding technique, avoid expensive and unnecessary ADO objects such as the Recordset and Command objects, and use the Return Status or Output parameters instead of rowsets. Always try to write intelligent queries and capitalize on stored procedures whenever possible. And tell ADO what you want it to do in detail to prevent it from having to guess?specify an explicit ADO CommandType, and use options such as adExecuteNoRecords
-------------------------------------------------------------------------
MDAC Technical Articles
Improving MDAC Application Performance
Suresh Kannan
August 1999
Introduction
This document provides suggestions and guidelines for improving the performance of your MDAC application. The suggestions offered here constitute good coding practice. Performance might not noticeably improve unless accessing data is a significant part of your application processing.
Where possible, sample code has been provided to illustrate key points. In studying the samples, it is important to keep in mind the following considerations:
The best way to improve the performance of your application is to understand where your bottlenecks are. If your goal is to improve the speed of an existing application, you should start by profiling and analyzing your application.
This document describes general guidelines for improving your application performance. Some suggestions work only under certain circumstances. Where possible, such constraints have been called out. After you implement a change suggested in this document, you should measure your application's performance to validate benefits. Some modifications could be detrimental to your application performance if they are inappropriate for your application scenario.
Although Microsoft® Visual Basic® for Applications has been used to illustrate most of the suggestions, you can also apply suggested techniques to clients writing to ADO in other languages, such as C, Microsoft Visual Basic Scripting Edition (VBScript), Microsoft Visual C++®, and Java.
General Considerations
Use Strongly Typed Variables
A strongly typed variable is explicitly declared to represent only one variable type. Once declared, it cannot be used to represent a different variable type, as can weakly typed variables.
Microsoft Visual Basic allows weakly typed variables through the use of the Variant type. The Variant type can store almost any other kind of variable or object. While this gives you automatic type conversions so that you don't have to pay attention to the variable types, it can make debugging difficult. Occasionally, this automatic conversion will transform the data into a type that you didn't expect or intend, and tracking down where that happened is very difficult.
Microsoft Visual Basic defaults to the Variant type for all variables that are declared without a specific type and for all variables that are not declared at all. However, for better performance (unless the Variant type is specifically required for a property or method), avoid using Variant variables. Instead, use the Option Explicit statement to require declarations for all variables, and provide a specific type declaration for all variables that you declare. Alternatively, you can use the Deftype statements to change the default type for variables that are created and not declared or that are declared without a specific type.
Declare All Variables
A variable that is not declared will be created by Visual Basic with a default type. As discussed above, normally it will be implicitly created as a Variant. However, if you've used the Deftype statements, you can change the default.
Following is an example where the variables are implicitly created:
Public Sub NoExplicitDeclare(cnn)
Set rsl = cnn.Execute("SELECT * FROM Nonsense")
txt = rs1!SomeText.Value
Debug.Print txt
End Sub
This code has a problem that the compiler won't catch, so you probably won't find it until you run the code. What is supposed to happen is that rsl should be assigned to a Recordset returned from the open Connection object cnn. However, when you run it, you'll get an error on the line txt = rs1!SomeText.Value. Visual Basic detects that rs1 is an empty Variant and generates a run-time error, "Object Required." In a more complex algorithm, you might be led to believe that the Execute method encountered an error. The real problem is that the variable rsl is misspelled as rs1. This problem is masked because the syntax and the identifiers appear correct at first glance.
If you place the Option Explicit statement at the top of the module, Visual Basic will generate a compiler error, and after you've declared rsl as a Recordset object and txt as a String, Visual Basic will highlight rs1 with the error "Variable not defined."
Next is an example where the variables are explicitly created:
Option Explicit
Public Sub NoExplicitDeclare(cnn As Connection)
Dim rsl As Recordset
Dim txt As String
Set rsl = cnn.Execute("SELECT * FROM Nonsense")
txt = rsl!SomeText.Value
Debug.Print txt
rs1.Close
Set rs1 = Nothing
End Sub
Use Strongly Typed Object Variables
Object variables represent pointers to COM objects. They can be declared in two ways, as follows:
Use the keywords As Object so that the variable can be used to represent any kind of object; this is a weakly typed object variable.
Use the As keyword with the specific type of the object; this is a strongly typed object variable.
Microsoft Visual Basic must use late binding for all weakly typed object variables. This means Visual Basic must indirectly make every method and property call as each call is encountered at run time. Visual Basic does this by using the IDispatch interface to retrieve the identifier of each method and property function at run time and then calling IDispatch again to actually invoke the method or property. Although Visual Basic caches some of the information from IDispatch, using the late binding approach is still the slowest way to invoke methods and properties.
To create a strongly typed object variable, use the As keyword with the Dim, Private, Public, or Static statements and use the name of an object from one of the object libraries selected in the Project References dialog box. Visual Basic will read the object library at compile time to avoid calling IDispatch to get the identifiers of the methods and properties of the object. This is called early binding. In early binding, Visual Basic still invokes the properties and methods of the object through the IDispatch interface.
If the objects described in the object library use dual interfaces, it means they support not only the IDispatch interface but also a table of function addresses that mirror the properties and methods available through IDispatch. Visual Basic can then call the functions that handle the methods and properties of the object directly, bypassing the IDispatch interface altogether. This is called vtable binding, and it is the fastest way to invoke the properties and methods of an object. A vtable is simply a table of function addresses. If the object does not support dual interfaces, Visual Basic will use early binding instead.
All objects in ADO use dual interfaces, so Visual Basic can work several times faster with your ADO objects if you make your object variables strongly typed with the Dim variable As type statement. The type of the object is typically the name of an object from the object library, although it can also be the name of an interface. If you use the name of the object, Visual Basic silently substitutes the name of the default interface for that object.
Visual Basic chooses early or late binding depending on how you declare the object variable and not on how you create the object. In other words, it doesn't matter whether you use the Set variable = New type statement or the CreateObject function to create the object. What matters is how you declare the object variable. If you declare it as a specific type, Visual Basic will use early binding. If you declare the object variable as Object, Variant, or leave it undeclared, Visual Basic will use late binding.
The code in the StronglyTyped subroutine below uses the faster vtable binding by declaring the variable con as a Connection object:
Sub StronglyTyped()
Dim lngState As Long
Dim con As Connection
Set con = New Connection
con.Open "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
lngState = con.State
con.Close
Set con = Nothing
End Sub
In the NotStronglyTyped subroutine below, the type of the Connection variable isn't known at compile time, so it's declared As Object. Visual Basic uses the slower late binding method to call the methods and properties of the same Connection object used in the StronglyTyped subroutine.
Sub NotStronglyTyped()
Dim lngState As Long
Dim con As Object
Set con = New Connection
con.Open "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
lngState = con.State
con.Close
Set con = Nothing
End Sub
Explicitly Create Objects
Although object variables represent pointers to COM objects, merely declaring an object variable does not automatically create an instance of a COM object. Visual Basic offers two ways to create an instance of a COM object: implicit and explicit. While implicit creation can save some time in development, it usually costs you much more time in debugging and doesn't help at all in performance.
To implicitly create an object, use the As New keyword of the Dim statement. This permits Visual Basic to create the object automatically when you use the object variable and when the object has not been created already. When you use the Dim...As New... feature, Visual Basic adds code before every object reference to determine at run time whether the object is instantiated. This code automatically creates the object if the object variable is either not initialized or set to Nothing.
By using Dim... As New..., you'll lose some of the control over object references. For simple procedures, this won't be an issue. Visual Basic will automatically release all objects after the procedure has ended. You will take a negligible performance hit for using the automatic object creation feature that As New provides. Only you can decide this trade-off is worthwhile for simple procedures, but once you decide, be very consistent.
The following example shows implicit object creation with the As New keywords:
Sub AutomaticCreation()
Dim rsNS As New Recordset
rsNS.ActiveConnection = "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
rsNS.CursorLocation = adUseServer
End Sub
There is an important reason why you might want to avoid Dim...As New in some circumstances, even for simple procedures. There are times when having an object variable set to Nothing is a valid, testable state. The Dim...As New feature prevents an object variable from ever being set to Nothing when you test its state. For example, the following snippet will always print, "n is set" to the Debug window:
Dim n As New Recordset
If n Is Nothing Then
Debug.Print "n is nothing"
Else
Debug.Print "n is set"
End If
In the preceding example, the very act of testing to see whether the object is created creates the object! In cases where the object is set to Nothing to indicate a valid condition, as is the case with the NextRecordset method of the Recordset object, you will never be able to detect that the object is set to Nothing.
To explicitly create an object, do not use the As New feature. If you use the object variable and the object has not been created already, Visual Basic will raise an error. You must use the Set statement to create the object when you will use it.
The following example shows explicit object creation:
Sub ExplicitCreation()
Dim rsNS As Recordset
Set rsNS = New Recordset
rsNS.ActiveConnection = "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
rsNS.CursorLocation = adUseServer
Set rsNS = Nothing
End Sub
Reuse Command Objects
If you are going to use a particular stored procedure, view, or SQL statement several times, don't create a new Command object each time. Use a static variable or a module-level variable to keep a reference to each Command object that you will reuse.
Note This technique might not work well for VBScript running in Active Server Pages, or for applications written for Microsoft Component Transaction Services.
Bind Columns to Field Objects When Looping Through the Records
There are at least two common ways to get the values for fields in a Recordset. One way is to look up each field by its name or ordinal position from the Fields collection of the Recordset object each time you need the value. The other way is to reuse the Field objects in the Fields collection as you iterate through the records in the Recordset.
The code in ColumnsNotBound looks up fields by ordinal position. This incurs the overhead of looking up each field in the Fields collection for each record in the Recordset. For a Recordset with many records, this can get quite expensive.
Sub ColumnsNotBound()
Dim rsNS As Recordset
Dim strText As String
Dim strMoney As String
Set rsNS = New Recordset
rsNS.Open "SELECT SomeText, SomeNumber, SomeTime, SomeMoney " _
"FROM Nonsense", "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
Do Until rsNS.EOF
strText = rsNS.Fields(0).Value
strMoney = rsNS.Fields(3).Value
rsNS.MoveNext
Loop
rsNS.Close
Set rsNS = Nothing
End Sub
The code in ColumnsBound, however, obtains references to the Field objects at the beginning and simply uses those same references when looping through the records.
Sub ColumnsBound()
Dim rsNS As Recordset
Dim strText As String
Dim strMoney As String
Dim fldText As Field
Dim fldMoney As Field
Set rsNS = New Recordset
rsNS.Open "SELECT SomeText, SomeNumber, SomeTime, SomeMoney " _
"FROM Nonsense", "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
Set fldText = rsNS.Fields(0)
Set fldMoney = rsNS.Fields(3)
Do Until rsNS.EOF
strText = fldText.Value
strMoney = fldMoney.Value
rsNS.MoveNext
Loop
rsNS.Close
Set rsNS = Nothing
End Sub
Use Cursor-Based Updating Only If Needed
Try to avoid the use of cursor-based updating. Although using an SQL statement to update data is not feasible in many scenarios, you should use it where possible. Although updating data through the Recordset object is often more convenient, it is also much more expensive. Despite being cumbersome to use, updating data through an SQL statement is well worth the trouble. The routine that uses SQL will update several dozen records in a 5,000 record table about 30 times faster than the routine that uses a cursor.
The following example shows cursor-based updating:
Sub ADOUpdate()
Dim cnNS As Connection
Dim rsNS As Recordset
Dim fldText As Field
Dim fldNumber As Field
Dim lngUpdateEach As Long
lngUpdateEach = Rnd * 99 + 1
Set cnNS = New Connection
cnNS.ConnectionString = "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
cnNS.Open
Set rsNS = New Recordset
Set rsNS.ActiveConnection = cnNS
rsNS.CursorLocation = adUseClient
rsNS.CursorType = adOpenStatic
rsNS.LockType = adLockBatchOptimistic
rsNS.Open "Nonsense", , , , adCmdTable
Set fldText = rsNS!SomeText
Set fldNumber = rsNS!SomeNumber
rsNS.MoveFirst
Do Until rsNS.EOF
If fldNumber.Value Mod lngUpdateEach = 0 Then
fldText.Value = UCase$(fldText.Value)
End If
rsNS.MoveNext
Loop
rsNS.UpdateBatch
rsNS.Close
cnNS.Close
Set rsNS = Nothing
Set cnNS = Nothing
End Sub
The next example uses an SQL statement to update data:
Sub SQLUpdate()
Dim cnNS As Connection
Dim lngUpdateEach As Long
Dim cmNS As Command
Set cmNS = New Command
lngUpdateEach = Rnd * 99 + 1
Set cnNS = New Connection
cnNS.Open "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
Set cmNS.ActiveConnection = cnNS
cmNS.CommandText = "UPDATE Nonsense SET SomeText = UPPER(SomeText) " _
& "WHERE SomeNumber % " & lngUpdateEach & " = 0"
cmNS.CommandType = adCmdText
cmNS.Prepared = True
cmNS.Execute , , adExecuteNoRecords
cnNS.Close
Set cnNS = Nothing
Set cmNS = Nothing
End Sub
Use a Stored Procedure with Output Parameters Instead of Singleton Selects
When you know that the result of your query will yield only a single row of data, instead of opening a Recordset for fetching that data, you can use a stored procedure with output parameters.
When you use a Recordset, the query results returned by the data source object include data and metadata. Often the metadata is much larger than the data or is a significant part of the query results. Because of this, you may want to use a stored procedure with output parameters instead.
The following example shows a singleton select statement:
Sub SingletonSelect()
Dim rs As Recordset
Dim strText As String
Dim timTime As Date
Dim n As Integer
Set rs = New Recordset
rs.ActiveConnection = "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
rs.CursorLocation = adUseServer
rs.CursorType = adOpenForwardOnly
rs.LockType = adLockReadOnly
For n = 1 To 50
rs.Source = "SELECT sometext, sometime FROM nonsense " & _
"WHERE ID = '" & CStr(CLng(Rnd * 5000)) & "'"
rs.Open
strText = rs.Fields(0).Value
timTime = rs.Fields(1).Value
rs.Close
Next
Set rs = Nothing
End Sub
The next example retrieves two output parameters from a stored procedure that selects a single record:
Sub SingletonSp()
Dim cmd As Command
Dim strText As String
Dim timTime As Date
Dim n As Integer
Set cmd = New Command
cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
cmd.CommandText = "GetTextTimeUsingID"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append _
cmd.CreateParameter("inID", adInteger, adParamInput, 4)
cmd.Parameters.Append _
cmd.CreateParameter("outText", adChar, adParamOutput, 32)
cmd.Parameters.Append _
cmd.CreateParameter("outTime", adDate, adParamOutput, 8)
For n = 1 To 50
cmd(0).Value = Rnd * 5000
cmd.Execute , , adExecuteNoRecords
strText = cmd(1).Value
timTime = cmd(2).Value
Next
Set cmd = Nothing
End Sub
Here is the code for the stored procedure:
CREATE PROCEDURE [GetTextTimeUsingID]
@inID int = 0,
@outText char(32) OUTPUT,
@outTime datetime OUTPUT
AS
IF @inID = 0 RETURN 2
SELECT @outText = SomeText, @outTime = SomeTime FROM Nonsense
WHERE ID = @inID
RETURN
If You Must Use a Cursor, Use the Collect Methods for Singleton Selects
The Collect method is a hidden method of the Recordset object that lets you quickly get and set the Value property of a Field object without having to obtain a field reference first. This method is appropriate to use when you aren't interested in obtaining or setting any properties other than Field.Value.
Following is an example using the Collect method:
Sub Collect()
Dim rs As Recordset
Dim strText As String
Dim timTime As Date
Set rs = New Recordset
rs.ActiveConnection = "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
rs.Source = "SELECT SomeText, SomeNumber, SomeTime, SomeMoney " & _
"FROM Nonsense WHERE ID = '2500'"
rs.Open
strText = rs.Collect(0)
timTime = rs.Collect(2)
rs.Close
Set rs = Nothing
End Sub
Query Only for the Data You Need
Although it is easy to fall into the habit of using SELECT * queries, consider asking only for the columns you need. Also, consider adding restrictions, such as WHERE clauses, to your query to limit the records returned.
Choose CursorLocation, CursorType, and LockType Properties Carefully
If you don't need scrolling or updatability, don't ask for it. The ADO defaults of adUseServer, adOpenForwardOnly, and adLockReadOnly offer you the best performance for doing a forward-only scan through the records. Don't ask for a more functional cursor if your application doesn't require it.
If you do want scrolling, don't default to server cursors as ADO does. The ADO CursorLocation default is adUseServer primarily for backward compatibility reasons. However, for most scrolling scenarios, you will be much better off with a client cursor. Only in specific scenarios, such as extremely large data sets, will you be better off with a server cursor. When using a client cursor, don't ask for anything other than a LockType value of adLockReadOnly unless you really need it. If you ask for updatability, the client cursor engine needs to get additional metadata, which can be very expensive to retrieve.
Tune the Recordset.CacheSize Property
ADO uses the Recordset.CacheSize property to determine the number of rows to fetch and cache. This especially affects server-side cursors. While you are within the range of cached rows, ADO just returns data from the cache. When you scroll out of the range of cached rows, ADO releases the cache and fetches the next CacheSize rows. The default value for the CacheSize property is 1.
How do you determine what value you should use for the CacheSize property in your application? Unfortunately, there isn't a single optimal CacheSize value for all applications. You should try tuning your application with different CacheSize values, and use the value that offers you the best performance. For example, knowing that a small CacheSize value significantly improves performance for fetching data from an Oracle data store might be an important factor for you.
Release Unused ADO Objects
Whenever possible, release ADO objects as soon as you're done with them. This frees up the database and other resources that might be expensive to hold for an extended period. Explicitly close all objects rather than allowing the object to close itself as it is destroyed.
Describe Command Parameters Yourself
In many data stores, getting command parameter information is often as expensive as executing the command. Describe the command parameters yourself instead of getting the parameter information from the provider.
The following example shows how to get the parameter information from the provider:
Sub ProviderDescribedParameters()
Dim cmd As Command
Dim lngRetVal As Long
Dim strText As String
Dim timTime As Date
Set cmd = New Command
cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
cmd.CommandText = "GetTextTimeUsingID"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters(1).Value = 100
cmd.Execute
lngRetVal = cmd.Parameters(0).Value
strText = cmd.Parameters(2).Value
timTime = cmd.Parameters(3).Value
Set cmd = Nothing
End Sub
The next example shows how to describe the parameters manually:
Sub UserDescribedParameters()
Dim cmd As Command
Dim lngRetVal As Long
Dim strText As String
Dim timTime As Date
Set cmd = New Command
cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
cmd.CommandText = "GetTextTimeUsingID"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append _
cmd.CreateParameter("Return Value", adInteger, adParamReturnValue)
cmd.Parameters.Append _
cmd.CreateParameter("inID", adInteger, adParamInput)
cmd.Parameters.Append _
cmd.CreateParameter("outText", adChar, adParamOutput, 32)
cmd.Parameters.Append _
cmd.CreateParameter("outTime", adDate, adParamOutput, 8)
cmd.Parameters(1).Value = 100
cmd.Execute
lngRetVal = cmd.Parameters(0).Value
strText = cmd.Parameters(2).Value
timTime = cmd.Parameters(3).Value
Set cmd = Nothing
End Sub
Use Native OLE DB Providers
MDAC ships with native providers for several data stores, including SQL Server, Oracle, and Microsoft Jet (.mdb). With earlier versions, you had to go through the OLE DB Provider for ODBC, which in turn used the appropriate ODBC driver to access these data stores. The provider used as the default for connections is still the OLE DB Provider for ODBC, but you should use these native OLE DB providers so that you can access your data faster and with lower disk and memory footprint. The SQL Server provider is written to TDS, the Oracle provider to OCI, and the Microsoft Jet provider to the Microsoft Jet Engine API.
Disconnect Your Client Cursor from the Connection for R/O and Long-Use Scenarios
Disconnected Recordset objects are supported by the client cursor engine. Use this feature when you are performing a time-consuming operation that doesn't require expensive database resources to be held open. If you need to, you can later reconnect the Recordset to the connection to perform updates.
Following is an example that shows how to disconnect a Recordset:
Sub DisconnectRS()
Dim con As Connection
Dim rs As Recordset
Set con = New Connection
Set rs = New Recordset
con.CursorLocation = adUseClient
con.Open "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
Set rs = con.Execute("SELECT SomeText, SomeNumber, SomeTime, " & _
"SomeMoney FROM Nonsense")
Set rs.ActiveConnection = Nothing
Set con = Nothing
'
' Process data
'
rs.Close
Set rs = Nothing
End Sub
Anticipate Non?Row Returning Commands
ADO includes an ExecuteOptionEnum option called adExecuteNoRecords. Use this option for commands that do not return rows. When this option is specified, ADO does not create a Recordset object, does not set any cursor properties, and specifies IID_NULL REFIID on ICommand::Execute. Also, because IID_NULL is specified on ICommand::Execute, the provider can optimize for this case by not verifying any rowset properties.
The following example demonstrates how the adExecuteNoRecords option is used:
Sub ExecuteNoRecords()
Dim con As Connection
Set con = New Connection
con.Open strConnection
con.Execute "INSERT INTO Nonsense VALUES('" & Greeking(32) & _
"', " & CStr(CLng(Rnd * &H7FFFFFFF)) & _
", " & CStr(CLng(Now)) & _
", " & CStr(CCur(curStart)) & ",DEFAULT ,DEFAULT, DEFAULT, " & _
"DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)", , _
adExecuteNoRecords
con.Close
Set con = Nothing
End Sub
Use Connection.Execute for Single Execution
ADO has some optimizations for one-time command executions when the executions are done through the Execute method of the Connection object. This is a common user scenario in Microsoft Internet Information Server (IIS), Active Server Pages (ASP), and Microsoft Component Services (formerly Microsoft Transaction Server) environments, where the code typically opens a connection, executes a row returning or non?row returning command, processes results, and closes the connection. For such scenarios, use Connection.Execute instead of Recordset.Open or Command.Execute. When you use Connection.Execute, ADO doesn't preserve any command state information, which leads to an improvement in performance. However, if you need a more functional cursor or if you need to use Command.Parameters, you might still need to use Recordset.Open or Command.Execute.
Use ADO C++ Extensions (C++ Users)
ADO is an Automation server, which means it implements Automation interfaces. Therefore, it provides language-independent access to OLE DB. However, although ADO interfaces are accessible from C, C++, and Java, structures such as VARIANTs, which are easy to use from Visual Basic for Applications (VBA), are quite cumbersome to use from C, C++, and Java.
In versions of Microsoft Visual C++ earlier than 5.0, Automation interfaces were hard to use because of lack of language support for COM and VARIANTs. Now, with native compiler support for COM in Visual C++ 5.0, Automation interfaces are much easier to use from Visual C++. However, if you still want to fetch data into C types instead of VARIANTs, you can do so by using the ADO C++ extensions. Besides avoiding the VARIANT overhead, the C++ extensions offer good performance. When you use them, ADO doesn't need to get the column information from the provider. Instead, ADO uses the column information supplied at design time in the form of binding entries.
The following C++ code example shows how to use the ADO C++ extensions to get the values from three VARCHAR fields.
class CAuthor :
public CADORecordBinding
{
BEGIN_ADO_BINDING(CCustomRs1)
ADO_VARIABLE_LENGTH_ENTRY4(1, adVarChar, m_szau_id,
sizeof(m_szau_id), FALSE)
ADO_VARIABLE_LENGTH_ENTRY4(2, adVarChar, m_szau_fname,
sizeof(m_szau_fname), FALSE)
ADO_VARIABLE_LENGTH_ENTRY4(3, adVarChar, m_szau_lname,
sizeof(m_szau_lname), FALSE)
END_ADO_BINDING()
protected:
char m_szau_id[12];
char m_szau_fname[21];
char m_szau_lname[41];
};
void FetchAuthorData()
{
CAuthor author;
_RecordsetPtr pRs;
IADORecordBinding *piAdoRecordBinding;
pRs.CreateInstance(__uuidof(Recordset));
pRs->Open("select au_id, au_fname, au_lname from Employees",
"Provider=SQLOLEDB;Data Source=sureshk1;Database=pubs;"
"User Id=sa;Password=;",
adOpenForwardOnly, adLockReadOnly, adCmdText);
pRs->QueryInterface(__uuidof(IADORecordBinding),
(LPVOID *)&piAdoRecordBinding);
piAdoRecordBinding->BindToRecordset(&author);
while (VARIANT_FALSE == pRs->EOF)
{
printf("%s %s %s", author.m_szau_id, author.m_szau_fname,
author.m_szau_lname);
pRs->MoveNext();
}
piAdoRecordBinding->Release();
}
Middle-Tier Considerations
Use Session (OLE DB) or Connection (ODBC) Pooling
A database connection is an expensive resource to open and close. Therefore, pooling this resource offers a huge performance improvement for middle-tier applications.
When you use MDAC, you don't have to worry about how to pool your database connections. MDAC takes care of it for you. Pooling is supported at two levels: OLE DB sessions and ODBC connections. If you use ADO, your database connections are pooled automatically by OLE DB session pooling. If you use ODBC, the new Connection Pooling tab in the ODBC Data Source Administrator lets you control the ODBC connection pooling settings and the ODBC Driver Manager takes care of pooling for you.
Use ADO Like an Apartment Model
Although the ADO implementation is free-threaded, don't use it in that way in the middle tier. Don't cache an instance of an ADO object, such as a Connection, globally and invoke methods on it concurrently from multiple threads. If each client request in your application model invokes the Connection.Execute method on a globally cached Connection object on the middle tier, your application will not scale. This is because of synchronization in the Connection.Execute method.
You will get much better throughput by using an application model where each client request creates a new instance of a Connection object, calls Connection.Open and then Connection.Execute, and releases the Connection object on the middle tier. Each request does have the additional overhead of creating a new instance of a Connection object and obtaining a connection from the connection pool. However, because your Connection.Execute call isn't synchronized, the throughput is much better.
ASP Considerations
Don't cache ADO objects in Global.asa. See "Use ADO Like an Apartment Model" earlier in this document.
Conclusion
Improving the performance of any application is both a science and an art. The recommendations listed here will help with many areas, but every application has different circumstances. The environments may differ from one installation to the next. A setting that makes the application run faster on one machine may make it run slower on a different machine. Even different database schemas will affect many of the suggestions in this article.
There is no substitute for advanced techniques like code profiling, performance monitoring, and good old trial and error. As with any scientific approach, you should vary only one element at a time and note whether performance improves or worsens with each variation. If performance deteriorates, before assuming that the change was the cause of the problem, revert the change and verify that you can reproduce the original behavior.
ⓒ 1991-2000 Microsoft Corporation. All rights reserved.
'KB > MFC/Win32' 카테고리의 다른 글
| 윈도우 2071년 설정시 CTime 뻑남 (0) | 2004.03.19 |
|---|---|
| ISAPI필터dll Attach to Process로 디버깅하기 (0) | 2004.03.19 |
| [db] ADO 데이타 바인딩 다이얼로그 얻기 (0) | 2004.03.19 |
| ime이용 한영키 전환 (0) | 2004.03.19 |
| 수동 언인스톨 (0) | 2004.03.19 |