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

< 출처 : 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

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

view source

print?

001.<font size="2">Option Explicit

002. 

003.Dim adoCn As Connection

004. 

005.Private Sub cmdCommandPrep_Click()

006.  Dim adoCmd As ADODB.Command

007.  Dim StartTime As Double

008.  Dim i As Integer

009. 

010.  Screen.MousePointer = vbHourglass

011. 

012.  txtOriginalTime = ""

013.  txtEnhancedTime = ""

014.  txtPercentImp = ""

015. 

016.  Set adoCmd = New Command

017.  Set adoCmd.ActiveConnection = adoCn

018.  adoCmd.CommandText = "Update TestTable Set field1 = ?, field2 = ?, field3 = ? Where dbkey = ?"

019.  adoCmd.Parameters.Refresh

020.  adoCmd.CommandType = adCmdText

021. 

022.  StartTime = Timer

023.  DoEvents

024. 

025.  For i = 1 To 200

026.    adoCmd.Execute , Array("string" & i, Time, "longer string", 1)

027.  Next i

028. 

029.  txtOriginalTime = Str(Timer - StartTime)

030.  DoEvents

031. 

032.  Set adoCmd.ActiveConnection = adoCn

033.  adoCmd.CommandText = "Update TestTable set field1 = ?, field2 = ?, field3 = ? where dbkey = ?"

034.  adoCmd.Parameters.Refresh

035.  adoCmd.CommandType = adCmdText

036.  adoCmd.Prepared = True

037. 

038.  StartTime = Timer

039.  DoEvents

040. 

041.  For i = 1 To 200

042.    adoCmd.Execute , Array("string" & i, Time, "longer string", 1)

043.  Next i

044. 

045.  txtEnhancedTime = Str(Timer - StartTime)

046.  DoEvents

047. 

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

049. 

050.  Set adoCmd = Nothing

051. 

052.  Screen.MousePointer = vbDefault

053.End Sub

054. 

055.Private Sub cmdCommandTypeSpec_Click()

056.  Dim adoCmd As ADODB.Command

057.  Dim StartTime As Double

058.  Dim i As Integer

059. 

060.  Screen.MousePointer = vbHourglass

061. 

062.  txtOriginalTime = ""

063.  txtEnhancedTime = ""

064.  txtPercentImp = ""

065. 

066.  Set adoCmd = New Command

067. 

068.  StartTime = Timer

069.  DoEvents

070. 

071.  For i = 1 To 200

072.    Set adoCmd.ActiveConnection = Nothing

073.    Set adoCmd.ActiveConnection = adoCn

074.    adoCmd.CommandText = "TestTable"

075.    adoCmd.Execute

076.  Next i

077. 

078.  txtOriginalTime = Str(Timer - StartTime)

079.  DoEvents

080. 

081.  StartTime = Timer

082.  DoEvents

083. 

084.  For i = 1 To 200

085.    Set adoCmd.ActiveConnection = Nothing

086.    Set adoCmd.ActiveConnection = adoCn

087.    adoCmd.CommandText = "TestTable"

088.    adoCmd.CommandType = adCmdTable

089.    adoCmd.Execute

090.  Next i

091. 

092.  txtEnhancedTime = Str(Timer - StartTime)

093.  DoEvents

094. 

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

096. 

097.  Set adoCmd = Nothing

098. 

099.  Screen.MousePointer = vbDefault

100.End Sub

101. 

102.Private Sub cmdOpenRs_Click()

103.  Dim adoRs As ADODB.Recordset

104.  Dim StartTime As Double

105.  Dim i As Integer

106. 

107.  Screen.MousePointer = vbHourglass

108. 

109.  txtOriginalTime = ""

110.  txtEnhancedTime = ""

111.  txtPercentImp = ""

112. 

113.  Set adoRs = New Recordset

114.  Set adoRs.ActiveConnection = adoCn

115.  adoRs.Source = "Select * from Customers, Orders"

116.  adoRs.CursorLocation = adUseClient

117.  adoRs.CacheSize = 50

118. 

119.  StartTime = Timer

120.  DoEvents

121. 

122.  adoRs.Open , , adOpenStatic, adLockReadOnly

123.  adoRs.Close

124. 

125.  txtOriginalTime = Str(Timer - StartTime)

126.  DoEvents

127. 

128.  adoRs.Source = "Select * from Customers, Orders"

129.  adoRs.CursorLocation = adUseServer

130.  adoRs.CacheSize = 50

131. 

132.  StartTime = Timer

133.  DoEvents

134. 

135.  adoRs.Open , , adOpenKeyset, adLockReadOnly

136.  adoRs.Close

137. 

138.  txtEnhancedTime = Str(Timer - StartTime)

139.  DoEvents

140. 

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

142. 

143.  Set adoRs = Nothing

144. 

145.  Screen.MousePointer = vbDefault

146.End Sub

147. 

148.Private Sub cmdOpenRsRead_Click()

149.  Dim adoRs As ADODB.Recordset

150.  Dim StartTime As Double

151.  Dim i As Integer

152. 

153.  Screen.MousePointer = vbHourglass

154. 

155.  txtOriginalTime = ""

156.  txtEnhancedTime = ""

157.  txtPercentImp = ""

158. 

159.  Set adoRs = New Recordset

160.  Set adoRs.ActiveConnection = adoCn

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

162.  adoRs.CursorLocation = adUseClient

163. 

164.  StartTime = Timer

165.  DoEvents

166. 

167.  For i = 1 To 50

168.    adoRs.Open , , adOpenStatic, adLockOptimistic

169.    adoRs.Close

170.  Next i

171. 

172.  txtOriginalTime = Str(Timer - StartTime)

173.  DoEvents

174. 

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

176.  adoRs.CursorLocation = adUseClient

177. 

178.  StartTime = Timer

179.  DoEvents

180. 

181.  For i = 1 To 50

182.    adoRs.Open , , adOpenStatic, adLockReadOnly

183.    adoRs.Close

184.  Next i

185. 

186.  txtEnhancedTime = Str(Timer - StartTime)

187.  DoEvents

188. 

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

190. 

191.  Set adoRs = Nothing

192. 

193.  Screen.MousePointer = vbDefault

194.End Sub

195. 

196.Private Sub cmdParam_Click()

197.  Dim adoCmd As ADODB.Command

198.  Dim StartTime As Double

199.  Dim i As Integer

200. 

201.  Screen.MousePointer = vbHourglass

202. 

203.  txtOriginalTime = ""

204.  txtEnhancedTime = ""

205.  txtPercentImp = ""

206. 

207.  Set adoCmd = New Command

208.  Set adoCmd.ActiveConnection = adoCn

209.  adoCmd.CommandType = adCmdText

210.  adoCmd.Parameters.Refresh

211. 

212.  StartTime = Timer

213.  DoEvents

214. 

215.  For i = 1 To 200

216.    adoCmd.CommandText = "Update TestTable Set field1 = 'string" & i & "' Where dbkey = 1"

217.    adoCmd.Execute

218.  Next i

219. 

220.  txtOriginalTime = Str(Timer - StartTime)

221.  DoEvents

222. 

223.  adoCmd.CommandType = adCmdText

224.  adoCmd.CommandText = "Update TestTable Set field1 = ? Where dbkey = ?"

225.  adoCmd.Parameters.Refresh

226.  adoCmd.Prepared = True

227. 

228.  StartTime = Timer

229.  DoEvents

230. 

231.  For i = 1 To 200

232.    adoCmd.Execute , Array("string" & i, 1)

233.  Next i

234. 

235.  txtEnhancedTime = Str(Timer - StartTime)

236.  DoEvents

237. 

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

239. 

240.  Set adoCmd = Nothing

241. 

242.  Screen.MousePointer = vbDefault

243.End Sub

244. 

245.Private Sub cmdReady_Click(Index As Integer)

246.  Dim i As Integer

247. 

248.  Screen.MousePointer = vbHourglass

249. 

250.  On Error Resume Next

251. 

252.  Select Case Index

253.    Case 0

254.      adoCn.Execute "Drop Table TestTable"

255.      adoCn.Execute "Drop Procedure InsertTest"

256. 

257.      adoCn.Execute "Create Table TestTable (dbkey integer identity primary key, field1 varchar(10))"

258.      adoCn.Execute "Create Procedure InsertTest As Insert into TestTable (field1) values ('string1')"

259.    Case 1

260.      adoCn.Execute "Drop Table TestTable"

261. 

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

263.      adoCn.Execute "Insert into TestTable (field1, field2, field3) values ('string1', '19940701 12:30.00 AM', 'longer string')"

264.    Case 2

265.      adoCn.Execute "drop Table TestTable"

266. 

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

268. 

269.      For i = 1 To 200

270.        adoCn.Execute "Insert into TestTable (field1, field2, field3) values ('string1', '19940701 12:30.00 AM', 'longer string')"

271.      Next i

272.    Case 3

273.      adoCn.Execute "Drop Table TestTable"

274. 

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

276. 

277.      For i = 1 To 100

278.        adoCn.Execute "Insert into TestTable (field1, field2, field3) values ('string1', '19940701 12:30.00 AM', 'longer string')"

279.      Next i

280.  End Select

281. 

282.  Screen.MousePointer = vbDefault

283.End Sub

284. 

285.Private Sub cmdScroll_Click()

286.  Dim adoRs As ADODB.Recordset

287.  Dim StartTime As Double

288.  Dim i As Integer

289. 

290.  Screen.MousePointer = vbHourglass

291. 

292.  txtOriginalTime = ""

293.  txtEnhancedTime = ""

294.  txtPercentImp = ""

295. 

296.  Set adoRs = New Recordset

297.  Set adoRs.ActiveConnection = adoCn

298.  adoRs.Source = "Select * from Orders"

299.  adoRs.CursorLocation = adUseClient

300.  adoRs.CacheSize = 50

301.  adoRs.Open , , adOpenStatic, adLockReadOnly

302. 

303.  StartTime = Timer

304.  DoEvents

305. 

306.  While adoRs.EOF <> True

307.    adoRs.MoveNext

308.  Wend

309. 

310.  adoRs.Close

311. 

312.  txtOriginalTime = Str(Timer - StartTime)

313.  DoEvents

314. 

315.  adoRs.Source = "Select * from Orders"

316.  adoRs.CursorLocation = adUseServer

317.  adoRs.CacheSize = 50

318.  adoRs.Open , , adOpenKeyset, adLockReadOnly

319. 

320.  StartTime = Timer

321.  DoEvents

322. 

323.  While adoRs.EOF <> True

324.    adoRs.MoveNext

325.  Wend

326. 

327.  adoRs.Close

328. 

329.  txtEnhancedTime = Str(Timer - StartTime)

330.  DoEvents

331. 

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

333. 

334.  Set adoRs = Nothing

335. 

336.  Screen.MousePointer = vbDefault

337.End Sub

338. 

339.Private Sub cmdStoredvsNot_Click()

340.  Dim adoCmd As ADODB.Command

341.  Dim StartTime As Double

342.  Dim i As Integer

343. 

344.  Screen.MousePointer = vbHourglass

345. 

346.  txtOriginalTime = ""

347.  txtEnhancedTime = ""

348.  txtPercentImp = ""

349. 

350.  Set adoCmd = New Command

351.  Set adoCmd.ActiveConnection = adoCn

352.  adoCmd.CommandText = "InsertTest"

353. 

354.  StartTime = Timer

355.  DoEvents

356. 

357.  For i = 1 To 200

358.    adoCmd.Execute

359.  Next i

360. 

361.  txtOriginalTime = Str(Timer - StartTime)

362.  DoEvents

363. 

364.  adoCmd.CommandText = "Inserttest"

365.  adoCmd.CommandType = adCmdStoredProc

366. 

367.  StartTime = Timer

368.  DoEvents

369. 

370.  For i = 1 To 200

371.    adoCmd.Execute

372.  Next i

373. 

374.  txtEnhancedTime = Str(Timer - StartTime)

375.  DoEvents

376. 

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

378. 

379.  Set adoCmd = Nothing

380. 

381.  Screen.MousePointer = vbDefault

382.End Sub

383. 

384.Private Sub cmdUpdateRs_Click()

385.  Dim adoRs As ADODB.Recordset

386.  Dim StartTime As Double

387.  Dim i As Integer

388. 

389.  Screen.MousePointer = vbHourglass

390. 

391.  txtOriginalTime = ""

392.  txtEnhancedTime = ""

393.  txtPercentImp = ""

394. 

395.  Set adoRs = New Recordset

396.  Set adoRs.ActiveConnection = adoCn

397.  adoRs.Source = "Select * from TestTable"

398.  adoRs.CursorLocation = adUseClient

399.  adoRs.CacheSize = 50

400.  adoRs.Open , , adOpenStatic, adLockOptimistic

401. 

402.  StartTime = Timer

403.  DoEvents

404. 

405.  While adoRs.EOF <> True

406.    adoRs.Update Array("field1"), Array("newstring")

407.    adoRs.MoveNext

408.  Wend

409. 

410.  adoRs.Close

411. 

412.  txtOriginalTime = Str(Timer - StartTime)

413.  DoEvents

414. 

415.  adoRs.Source = "Select * from TestTable"

416.  adoRs.CursorLocation = adUseServer

417.  adoRs.CacheSize = 50

418.  adoRs.Open , , adOpenKeyset, adLockOptimistic

419. 

420.  StartTime = Timer

421.  DoEvents

422. 

423.  While adoRs.EOF <> True

424.    adoRs.Update Array("field1"), Array("newstring")

425.    adoRs.MoveNext

426.  Wend

427. 

428.  adoRs.Close

429. 

430.  txtEnhancedTime = Str(Timer - StartTime)

431.  DoEvents

432. 

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

434. 

435.  Set adoRs = Nothing

436. 

437.  Screen.MousePointer = vbDefault

438.End Sub

439. 

440.Private Sub Form_Load()

441.  Set adoCn = New Connection

442. 

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

444.End Sub

445.</font>

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

모듈	설명
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 등의 속성에 따라 성능 차이가 많이 발생할 수 있으므로, 개발자는 반드시 주어진 환경에 대한 튜닝 작업을 거쳐서 응용 프로그램을 최적화해야 한다.