수안이의 컴퓨터 연구실

오전 9:07 2002-07-04

조경민 ADO for Performance

[ADO for Performance]

ADO 성능에 관련되어 MSDN 들과 여러 자료를 취합하여 정리합니다.

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  

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.

정리

ADO에 관련된 성능 향상을 위한 여러 문서를 정리하여 보았습니다.

많은 문서에서 ADO 성능 향상을 위해서 여러 방면으로 설명하고 있으나, 대부분 골지는 제가 처음에 정리한 몇가지 설명으로 충분할 듯 합니다.