7.1. ADO에 대한 모든 것

Web/ASP & ASP.NET

7.1. ADO에 대한 모든 것 - ADO Tuning

Huikyun 2009. 4. 11. 01:25

< 출처 : korea.internet.com, 지은이 : 최현진 >

ADO를 사용해서 데이터베이스의 데이터를 조작하는 응용 프로그램을 작성할 경우, ADO에 대한 특성에 대해서 충분한 지식을 갖고 있어야 하며, 주어진 응용 프로그램 환경에 적합한 솔루션을 얻기 위해 적절하게 성능을 튜닝하는 작업을 하는 것이 좋다.

ADO의 성능을 시스템 환경에 맞도록 튜닝하는 간단한 예제 응용 프로그램을 작성해 본다.

비주얼 베이직에서 새로운 프로젝트를 시작하고, 폼을 추가해서 다음과 같이 디자인한다.

[그림] ADO Performance Tuning 화면

개체	속성	값
Form	Name	frmPerfTuning
	Caption	ADO Performance Tuning
CommandButton	Name	cmdCommandTypeSpec
	Caption	Command Types Specifying vs Not
CommandButton	Name	cmdCommandPrep
	Caption	Command - Prepared vs Not
CommandButton	Name	cmdStoredvsNot
	Caption	Command Type - Stored Procedure vs SQL Command
CommandButton	Name	cmdParam
	Caption	Command - Parameter vs String
CommandButton	Name	cmdOpenRs
	Caption	Recordset - Client vs Server Open
CommandButton	Name	cmdScroll
	Caption	Recordset - Client vs Server Scrolling
CommandButton	Name	cmdUpdateRs
	Caption	Recordset - Client vs Server Update
CommandButton	Name	cmdOpenRsRead
	Caption	Recordset - Read Write vs Read Only Open
Label	Name	Label1
	Caption	Original Time:
Label	Name	Label2
	Caption	Enhanced Time:
Label	Name	Label3
	Caption	Percent Improvement:
TextBox	Name	txtEnhancedTime
	Text	""
TextBox	Name	txtPercentImp
	Text	""
CommandButton	Name	cmdReady
	Caption	Ready
	index	0
CommandButton	Name	cmdReady
	Caption	Ready
	index	1
CommandButton	Name	cmdReady
	Caption	Ready
	index	2
CommandButton	Name	cmdReady
	Caption	Ready
	index	3

폼의 코드 모듈에 다음과 같이 코드를 작성한다.

Option Explicit

Dim adoCn As Connection

Private Sub cmdCommandPrep_Click()
  Dim adoCmd As ADODB.Command
  Dim StartTime As Double
  Dim i As Integer

  Screen.MousePointer = vbHourglass

  txtOriginalTime = ""
  txtEnhancedTime = ""
  txtPercentImp = ""

  Set adoCmd = New Command
  Set adoCmd.ActiveConnection = adoCn
  adoCmd.CommandText = "Update TestTable Set field1 = ?, field2 = ?, field3 = ? Where dbkey = ?"
  adoCmd.Parameters.Refresh
  adoCmd.CommandType = adCmdText

  StartTime = Timer
  DoEvents

  For i = 1 To 200
    adoCmd.Execute , Array("string" & i, Time, "longer string", 1)
  Next i

  txtOriginalTime = Str(Timer - StartTime)
  DoEvents

  Set adoCmd.ActiveConnection = adoCn
  adoCmd.CommandText = "Update TestTable set field1 = ?, field2 = ?, field3 = ? where dbkey = ?"
  adoCmd.Parameters.Refresh
  adoCmd.CommandType = adCmdText
  adoCmd.Prepared = True

  StartTime = Timer
  DoEvents

  For i = 1 To 200
    adoCmd.Execute , Array("string" & i, Time, "longer string", 1)
  Next i

  txtEnhancedTime = Str(Timer - StartTime)
  DoEvents

  txtPercentImp = Round(((Val(txtOriginalTime) / Val(txtEnhancedTime)) - 1) * 100, 2)

  Set adoCmd = Nothing

  Screen.MousePointer = vbDefault
End Sub

Private Sub cmdCommandTypeSpec_Click()
  Dim adoCmd As ADODB.Command
  Dim StartTime As Double
  Dim i As Integer

  Screen.MousePointer = vbHourglass

  txtOriginalTime = ""
  txtEnhancedTime = ""
  txtPercentImp = ""

  Set adoCmd = New Command

  StartTime = Timer
  DoEvents

  For i = 1 To 200
    Set adoCmd.ActiveConnection = Nothing
    Set adoCmd.ActiveConnection = adoCn
    adoCmd.CommandText = "TestTable"
    adoCmd.Execute
  Next i

  txtOriginalTime = Str(Timer - StartTime)
  DoEvents

  StartTime = Timer
  DoEvents

  For i = 1 To 200
    Set adoCmd.ActiveConnection = Nothing
    Set adoCmd.ActiveConnection = adoCn
    adoCmd.CommandText = "TestTable"
    adoCmd.CommandType = adCmdTable
    adoCmd.Execute
  Next i

  txtEnhancedTime = Str(Timer - StartTime)
  DoEvents

  txtPercentImp = Round(((Val(txtOriginalTime) / Val(txtEnhancedTime)) - 1) * 100, 2)

  Set adoCmd = Nothing

  Screen.MousePointer = vbDefault
End Sub

Private Sub cmdOpenRs_Click()
  Dim adoRs As ADODB.Recordset
  Dim StartTime As Double
  Dim i As Integer

  Screen.MousePointer = vbHourglass

  txtOriginalTime = ""
  txtEnhancedTime = ""
  txtPercentImp = ""

  Set adoRs = New Recordset
  Set adoRs.ActiveConnection = adoCn
  adoRs.Source = "Select * from Customers, Orders"
  adoRs.CursorLocation = adUseClient
  adoRs.CacheSize = 50

  StartTime = Timer
  DoEvents

  adoRs.Open , , adOpenStatic, adLockReadOnly
  adoRs.Close

  txtOriginalTime = Str(Timer - StartTime)
  DoEvents

  adoRs.Source = "Select * from Customers, Orders"
  adoRs.CursorLocation = adUseServer
  adoRs.CacheSize = 50

  StartTime = Timer
  DoEvents

  adoRs.Open , , adOpenKeyset, adLockReadOnly
  adoRs.Close

  txtEnhancedTime = Str(Timer - StartTime)
  DoEvents

  txtPercentImp = Round(((Val(txtOriginalTime) / Val(txtEnhancedTime)) - 1) * 100, 2)

  Set adoRs = Nothing

  Screen.MousePointer = vbDefault
End Sub

Private Sub cmdOpenRsRead_Click()
  Dim adoRs As ADODB.Recordset
  Dim StartTime As Double
  Dim i As Integer

  Screen.MousePointer = vbHourglass

  txtOriginalTime = ""
  txtEnhancedTime = ""
  txtPercentImp = ""

  Set adoRs = New Recordset
  Set adoRs.ActiveConnection = adoCn
  adoRs.Source = "Select * from TestTable where dbkey = 50"
  adoRs.CursorLocation = adUseClient

  StartTime = Timer
  DoEvents

  For i = 1 To 50
    adoRs.Open , , adOpenStatic, adLockOptimistic
    adoRs.Close
  Next i

  txtOriginalTime = Str(Timer - StartTime)
  DoEvents

  adoRs.Source = "Select * from TestTable where dbkey = 50"
  adoRs.CursorLocation = adUseClient

  StartTime = Timer
  DoEvents

  For i = 1 To 50
    adoRs.Open , , adOpenStatic, adLockReadOnly
    adoRs.Close
  Next i

  txtEnhancedTime = Str(Timer - StartTime)
  DoEvents

  txtPercentImp = Round(((Val(txtOriginalTime) / Val(txtEnhancedTime)) - 1) * 100, 2)

  Set adoRs = Nothing

  Screen.MousePointer = vbDefault
End Sub

Private Sub cmdParam_Click()
  Dim adoCmd As ADODB.Command
  Dim StartTime As Double
  Dim i As Integer

  Screen.MousePointer = vbHourglass

  txtOriginalTime = ""
  txtEnhancedTime = ""
  txtPercentImp = ""

  Set adoCmd = New Command
  Set adoCmd.ActiveConnection = adoCn
  adoCmd.CommandType = adCmdText
  adoCmd.Parameters.Refresh

  StartTime = Timer
  DoEvents

  For i = 1 To 200
    adoCmd.CommandText = "Update TestTable Set field1 = 'string" & i & "' Where dbkey = 1"
    adoCmd.Execute
  Next i

  txtOriginalTime = Str(Timer - StartTime)
  DoEvents

  adoCmd.CommandType = adCmdText
  adoCmd.CommandText = "Update TestTable Set field1 = ? Where dbkey = ?"
  adoCmd.Parameters.Refresh
  adoCmd.Prepared = True

  StartTime = Timer
  DoEvents

  For i = 1 To 200
    adoCmd.Execute , Array("string" & i, 1)
  Next i

  txtEnhancedTime = Str(Timer - StartTime)
  DoEvents

  txtPercentImp = Round(((Val(txtOriginalTime) / Val(txtEnhancedTime)) - 1) * 100, 2)

  Set adoCmd = Nothing

  Screen.MousePointer = vbDefault
End Sub

Private Sub cmdReady_Click(Index As Integer)
  Dim i As Integer

  Screen.MousePointer = vbHourglass

  On Error Resume Next

  Select Case Index
    Case 0
      adoCn.Execute "Drop Table TestTable"
      adoCn.Execute "Drop Procedure InsertTest"

      adoCn.Execute "Create Table TestTable (dbkey integer identity primary key, field1 varchar(10))"
      adoCn.Execute "Create Procedure InsertTest As Insert into TestTable (field1) values ('string1')"
    Case 1
      adoCn.Execute "Drop Table TestTable"

      adoCn.Execute "Create Table TestTable (dbkey integer identity primary key, field1 varchar(10), field2 datetime, field3 varchar(100))"
      adoCn.Execute "Insert into TestTable (field1, field2, field3) values ('string1', '19940701 12:30.00 AM', 'longer string')"
    Case 2
      adoCn.Execute "drop Table TestTable"

      adoCn.Execute "Create Table TestTable (dbkey integer identity primary key, field1 varchar(10), field2 datetime, field3 varchar(100))"

      For i = 1 To 200
        adoCn.Execute "Insert into TestTable (field1, field2, field3) values ('string1', '19940701 12:30.00 AM', 'longer string')"
      Next i
    Case 3
      adoCn.Execute "Drop Table TestTable"

      adoCn.Execute "Create Table TestTable (dbkey integer identity primary key, field1 varchar(10), field2 datetime, field3 varchar(100))"

      For i = 1 To 100
        adoCn.Execute "Insert into TestTable (field1, field2, field3) values ('string1', '19940701 12:30.00 AM', 'longer string')"
      Next i
  End Select

  Screen.MousePointer = vbDefault
End Sub

Private Sub cmdScroll_Click()
  Dim adoRs As ADODB.Recordset
  Dim StartTime As Double
  Dim i As Integer

  Screen.MousePointer = vbHourglass

  txtOriginalTime = ""
  txtEnhancedTime = ""
  txtPercentImp = ""

  Set adoRs = New Recordset
  Set adoRs.ActiveConnection = adoCn
  adoRs.Source = "Select * from Orders"
  adoRs.CursorLocation = adUseClient
  adoRs.CacheSize = 50
  adoRs.Open , , adOpenStatic, adLockReadOnly

  StartTime = Timer
  DoEvents

  While adoRs.EOF <> True
    adoRs.MoveNext
  Wend

  adoRs.Close

  txtOriginalTime = Str(Timer - StartTime)
  DoEvents

  adoRs.Source = "Select * from Orders"
  adoRs.CursorLocation = adUseServer
  adoRs.CacheSize = 50
  adoRs.Open , , adOpenKeyset, adLockReadOnly

  StartTime = Timer
  DoEvents

  While adoRs.EOF <> True
    adoRs.MoveNext
  Wend

  adoRs.Close

  txtEnhancedTime = Str(Timer - StartTime)
  DoEvents

  txtPercentImp = Round(((Val(txtOriginalTime) / Val(txtEnhancedTime)) - 1) * 100, 2)

  Set adoRs = Nothing

  Screen.MousePointer = vbDefault
End Sub

Private Sub cmdStoredvsNot_Click()
  Dim adoCmd As ADODB.Command
  Dim StartTime As Double
  Dim i As Integer

  Screen.MousePointer = vbHourglass

  txtOriginalTime = ""
  txtEnhancedTime = ""
  txtPercentImp = ""

  Set adoCmd = New Command
  Set adoCmd.ActiveConnection = adoCn
  adoCmd.CommandText = "InsertTest"

  StartTime = Timer
  DoEvents

  For i = 1 To 200
    adoCmd.Execute
  Next i

  txtOriginalTime = Str(Timer - StartTime)
  DoEvents

  adoCmd.CommandText = "Inserttest"
  adoCmd.CommandType = adCmdStoredProc

  StartTime = Timer
  DoEvents

  For i = 1 To 200
    adoCmd.Execute
  Next i

  txtEnhancedTime = Str(Timer - StartTime)
  DoEvents

  txtPercentImp = Round(((Val(txtOriginalTime) / Val(txtEnhancedTime)) - 1) * 100, 2)

  Set adoCmd = Nothing

  Screen.MousePointer = vbDefault
End Sub

Private Sub cmdUpdateRs_Click()
  Dim adoRs As ADODB.Recordset
  Dim StartTime As Double
  Dim i As Integer

  Screen.MousePointer = vbHourglass

  txtOriginalTime = ""
  txtEnhancedTime = ""
  txtPercentImp = ""

  Set adoRs = New Recordset
  Set adoRs.ActiveConnection = adoCn
  adoRs.Source = "Select * from TestTable"
  adoRs.CursorLocation = adUseClient
  adoRs.CacheSize = 50
  adoRs.Open , , adOpenStatic, adLockOptimistic

  StartTime = Timer
  DoEvents

  While adoRs.EOF <> True
    adoRs.Update Array("field1"), Array("newstring")
    adoRs.MoveNext
  Wend

  adoRs.Close

  txtOriginalTime = Str(Timer - StartTime)
  DoEvents

  adoRs.Source = "Select * from TestTable"
  adoRs.CursorLocation = adUseServer
  adoRs.CacheSize = 50
  adoRs.Open , , adOpenKeyset, adLockOptimistic

  StartTime = Timer
  DoEvents

  While adoRs.EOF <> True
    adoRs.Update Array("field1"), Array("newstring")
    adoRs.MoveNext
  Wend

  adoRs.Close

  txtEnhancedTime = Str(Timer - StartTime)
  DoEvents

  txtPercentImp = Round(((Val(txtOriginalTime) / Val(txtEnhancedTime)) - 1) * 100, 2)

  Set adoRs = Nothing

  Screen.MousePointer = vbDefault
End Sub

Private Sub Form_Load()
  Set adoCn = New Connection

  adoCn.Open "Provider=sqloledb;Data Source=(local);Initial Catalog=NorthWind;User ID=sa;Password=;"
End Sub

위의 예제에서 테스트한 각 모듈에 대해서 간략하게 설명한다.

모듈	설명
Command Types Specifying vs Not	이 명령은 Command 개체의 CommandType 속성을 명시적으로 설정할 경우와 그렇지 않은 경우의 성능을 비교한다.CommandType 속성을 명시적으로 설정하지 않으면, 디폴트 값인 adCmdUnknown으로 실행된다. 그렇기 때문에 ADO는 실행 중에 Command 개체의 CommandType을 결정하는 작업을 추가적으로 수행하게 되고, 성능은 저하된다.일반적으로 Command 개체의 CommandType은 명시적으로 설정하고 사용하기를 권장한다.
Command - Prepared vs Not	이 명령은 Command 개체의 Prepared 속성을 명시적으로 설정할 경우와 그렇지 않은 경우의 성능을 비교한다.SQL 문장이 클라이언트에 의해서 SQL 서버로 전송되면, SQL 문장은 Parse-Resolve-Optimize-Compile-Execution 단계로 실행된다. SQL 서버에 저장 프로시저를 만들게 되면, Compile 된 상태에서 메모리에 올려지게 되고 클라이언트가 호출하면 바로 실행되기 때문에 속도가 상당히 빠르게 실행된다.Command 개체의 Prepared 속성은 저장 프로시저와 같은 효과를 얻기 위해 첫번째 SQL 문이 실행될 때, 저장 프로시저처럼 메모리에 올려지도록 설정하는 속성이다. 이 속성을 사용하면, 저장 프로시저로 만들어 놓지 않은 SQL 문장을 반복적으로 수행해야 될 경우에 성능 향상을 기대할 수 있다.이론적으로 본다면 반복해서 SQL 문을 수행할 경우에는 당연히 속도가 떠 빨라야 하지만, 상황에 따라 조금씩 달라진다.SQL 서버의 메모리에 SQL 문장을 동적으로 올리는 부하 역시 적지 않아서, 이 속성을 True로 실행한 경우와 Flase로 실행한 결과는 주어진 상황에 따라서 상이하며, 시스템을 개발하는 환경과 동일하게 테스트를 하여서 결과에 따라 사용 여부를 결정하는 것이 좋다.
Command Type - Stored Procedure vs SQL Command	이 명령은 Command 개체의 CommandType 속성을 adCmdStoredProc로 명시적으로 지정한 경우와 그렇지 않은 경우의 성능을 비교한다.Command 개체의 CommandType 속성은 명시적으로 설정하는 것이 ADO의 부하를 줄여주기 때문에 권장된다.
Command - Parameter vs String	이 명령은 Command 개체를 통해서 SQL 문을 실행할 때, SQL 문장으로 실행할 경우와 매개변수를 통해서 실행할 경우에 성능을 비교한다.이 두 경우에 성능은 비슷하며, 코드의 판독력을 높이기 위해서 매개변수를 사용하는 것을 권장한다.
Recordset - Client vs Server Open	이 명령은 Recordset 개채를 생성할 때, CursorLocation을 어떻게 지정하느냐에 따른 성능을 비교한다.CursorLocation을 클라이언트로 설정하면 네트워크 부하와 성능이 떨어지며, 서버로 설정하면 서버 측의 부하가 증가하게 된다. 이 두가지의 상황을 적절하게 조율하여 CursorLocation을 결정하여야 한다. 또한, 생성되는 Recordset 개체에 포함된 레코드가 많을 경우에는 CursorLocation을 서버로 설정하는 것이 좋고, 레코드가 적을 경우에는 클라이언트로 설정하는 것이 좋다.
Recordset - Client vs Server Scrolling	이 명령은 생성한 Recordset 개체를 Scroll 할 경우에, CursorLocation에 따른 성능을 비교한다. 일반적으로 CursorLocation이 서버일 경우에 Scroll 속도가 훨씬 더 빠른다.
Recordset - Client vs Server Update	이 명령은 생성한 Recordset 개체를 Update 할 경우에, CursorLocation에 따른 성능을 비교한다.이 명령의 실행 결과는 서버 측의 성능과 클라이언트 측의 성능에 따라 상이한 결과를 나타낸다. 그래서 적절한 튜닝을 통해서 작업을 선택적으로 수행하면 된다.
Recordset - Read Write vs Read Only Open	이 명령은 Recordset 개체를 생성할 때, LockType을 읽기와 쓰기를 모두 가능하게 하거나, 읽기 전용으로 할 경우에 대한 성능을 비교한다. Recordset 개체의 LockType 속성은 조회용 화면일 경우에는 반드시 읽기 전용으로 작업하기를 권장한다. 읽기 전용으로 만들어진 Recordset 개체는 읽기와 쓰기를 모두 지원하는 Recordset 개체보다 생성하는 속도 또는 필요한 메모리의 양에서 보다 더 효율적이기 때문이다.

ADO의 Performance Tuning은 응용 프로그램의 환경에 따라서 CursorLocation, CursorType, LockType 등의 속성에 따라 성능 차이가 많이 발생할 수 있으므로, 개발자는 반드시 주어진 환경에 대한 튜닝 작업을 거쳐서 응용 프로그램을 최적화해야 한다.