보미 네 : : ' ado ' 태그 의 글 목록 
 Total 185 , 809 
 ' ado ' 에 해당 되 는 글 1 건 
 2010 . 09 . 30 CursorTypes , LockTypes , and CursorLocations ( 2 ) 
 밥벌이 / 2010 . 09 . 30 14 : 22 
 누가 ADO 에서 Cache Size 변화 에 따른 로컬 Vs . 원격 IDC 간 성능 차이 를 알 고 싶 다고 의뢰 해 와서 테스트 해 보 게 되 었 습니다 . 
 MySQL 은 adUseServer 를 지원 하 지 않 습니다 . SQL Server 는 둘 다 지원 합니다 . 
 전 Test 해 본 후 에 야 알 았 습니다 . 역시 문서 는 꼼꼼히 읽 어 봐야 하나 봅니다 . MySQL 로 후딱 테스트 해 보 고 끝 낼려고 하 다가 반나절 넘 게 고생 하 고 테스트 못 할 뻔 . .. 
 아래 는 Cache Size ( Fetch Size 라고 적힌 부분 입니다 . ) 에 대한 성능 Test 곡선 입니다 . 
 실험 결과 16 개 이상 에서 는 성능 상 별 이득 이 없 었 습니다 . 아래 글 에서 마크 한 부분 이 라도 읽 어 두 시 면 좋 습니다 . 
 붉 은 선 은 Remote IDC 간 성능 곡선 이 고 , 
 푸른색 은 Local IDC 간 성능 곡선 입니다 . 
 TEST 프로그램 은 첨부 파일 로 담 았 으니 혹시 관심 있 는 분 은 Test 해 보 시 길 바랍니다 . 
 VS 2010 c # Console application 입니다 . 
 CursorPerfTest . zip 
 마지막 으로 당부 드리 고 싶 은 말 은 SQL Server 어플리케이션 성능 이 좋 지 않 아 원인 을 분석 해 보 니 ServerSide 커서 를 사용 하 고 그 CacheSize 혹은 FetchSize 가 1 로 되 어 있 다면 , 이 CacheSize 만 늘이 시 길 바랍니다 . 아니면 , 어플리케이션 의도 상 반드시 ServerSide 커서 가 필요 한 어플리케이션 이 있 을 수 있 으니 주의 해서 설정 ( 튜닝 ) 하 시 길 바랍니다 . 그냥 클라이언트 커서 로 바꾸 시 구요 , 에러 나 는 건 잡 으면서 반나절 만 고생 하 시 면 되 요 . 이럼 아니 되 옵 니다 . 
 원본 글 링크 http : / / dev . mysql . com / tech - resources / articles / vb - cursors - and - locks . html 
 One of the most common sources of trouble for the beginning VB / MySQL developer seems to revolve around which cursor location to use . Related to the cursor location problem is the choice of cursor type and lock type . The purpose of this article is to assist the Visual Basic developer in choosing the proper cursor location , cursor type , and lock type to use when programming Visual Basic ( or VB ) applications that use MySQL as a back - end database through ADO and Connector / ODBC ( MyODBC ) . 
 This article will assume that the reader is using the MySQL RDBMS , but should apply to developers using other database management systems . For an overview of why MySQL is a good choice for Visual Basic developers , see the Why VB / MySQL article on this site . This sample requires the latest version of Connector / ODBC ( MyODBC ) , available for download here ( currently 3 . 51 . 06 ) . 
 On the Visual Basic side , I recommend you have service pack 5 installed for VB , which you can download here . Additionally , the latest version of MDAC ( Microsoft Data Access Components ) should be installed , and can be found here ( currently 2 . 7 SP 1 ) . Finally , this article applies to ADO 2 . 7 and VB 6 . It is not applicable to ADO . NET under VB . NET ( Unless you reference ADODB in your project and use it for database access ) . 
 In ADO , when we talk about cursors , we are essentially talking about a set of rows . When you execute a query that returns rows of data , such as SELECT * FROM mytable , the resulting data is handled using a cursor . A cursor can be located either on the client with the adUseClient argument , or on the server with the adUseServer argument . In addition , the are 4 types of cursor : adOpenForwardOnly , adOpenStatic , adOpenDynamic , and adOpenKeyset . 
 The different types and locations will be discussed in further detail below . Your choice of cursor type and cursor location will affect what you are able to do with the data you retrieve , and how changes made to the data by other users are reflected in your copy of the data . 
 The ADODB . Connection object ( the ADO object used to broker all data exchanges between the VB application and the MySQL server ) has a property known as CursorLocation which is used to set / retrieve the cursor location that will be used by any recordset objects that access their data through the connection object . 
 The CursorLocation property can only be set while the connection is closed , and the property will be inherited by any recordset objects that access their data through the given connection object . Recordset objects can also explicitly set a cursorlocation different than the connection objects cursorlocation as long as it is set before the recordset is open . The two options available for this property are adUseClient and adUseServer , with adUseServer being the default property . 
 When using the adUseServer server - side cursorlocation , responsibility for handling the data generated by a query lies with the database server . MySQL itself does not support server - side cursors , so the data handling is actually done by the Connector / ODBC driver . The benefit of server - side cursors is that we gain access to the dynamic cursor type . This allows us to see any changes to the data that are made by other users in the data our application is accessing . 
 For example : let ' s say we are selling tickets to a concert with our application , we need to know that a given seat is available for sale in real - time to ensure we do not double - book the seat . With a server - side cursor , we can be sure that the data we are manipulating is the most current possible . In addition , we have the ability to lock the data we are working on as we edit it , to make sure our changes are going to be posted to the database successfully . 
 With a server - side cursor ( adUseServer ) , we have access to the adOpenDynamic and adOpenForwardOnly cursor types , and all four of the recordset lock types , which will be discussed below . 
 It should be noted that using a server - side cursor , and the adOpenDynamic cursor in particular , will result in a significant performance loss , and should be avoided if at all possible . In addition , certain functionality , such as the RecordCount property of a Recordset and the GetChunk and Appendchunk function for handling BLOB data , will fail or return abnormal results when used with a server - side cursor . 
 Client - side cursors , specified with the adUseClient keyword , are handled internally by ADO . These cursors offer more functionality than their server - side counterparts , and also result in less load being placed on the server . Most advanced ADO functionality is designed for use with client - side cursors , and I personally use client - side cursors for all my applications ( with one exception ) . 
 When using a client - side adUseClient cursor , only the adOpenStatic cursor is available , and we cannot use the adLockPessimistic lock type ( see below ) . 
 Client - side cursors also help decrease load on our MySQL server , since with a static cursor data is sent to the client and then the server has no further communications with the client . This allows your server to scale a lot better than with server - side cursors . 
 In addition to the two cursor locations , there are four cursor types , three of which are supported under Connector / ODBC : 
 adOpenStatic ( Client - Side ) 
 adOpenForwardOnly ( Server - Side ) 
 adOpenDynamic ( Server - Side ) 
 The different cursor types support different functionality and features , and I will now discuss each one in detail . The fourth cursor type , adOpenKeySet , is not currently supported by MySQL / MyODBC . 
 The static cursor is the only cursor type that is currently available when using adUseClient as your cursor location . With a static cursor , the server will send the result set to the client , after which there will be no further communication from the server to the client . The client may communicate with the server to send changes back to the server . This makes the static cursor more resource - intensive for the client and less resource - intensive for the server , as the result set is stored in the client ' s memory instead of the server ' s . 
 If a different client makes changes to the underlying data after the query results are sent , the original client will receive no notification of the change . A static cursor is bi - directional , meaning that your application can move forwards and backwards through the recordset . The following methods are available to a recordset using a static cursor and the adLockOptimistic lock type ( more on lock types later ) : 
 UpdateBatch 
 The static cursor will also show an accurate value for the RecordCount property of your recordset , and supports the getchunk and appendchunk methods for dealing with BLOB data . If you are having trouble with either of these problems , explicitly setting your connection ' s cursorlocation to adUseClient should solve them . 
 One handy feature of the static cursor is the ability to fetch data asynchronously . When data is fetched asynchronously . , a separate thread is started to handle row retrieval , and your VB application can begin processing returned rows immediately . An in depth article on asynchronous data fetching is pending , but to activate this feature , simple use the adFetchAsync option during your recordset . open method call . 
 If you specify any cursor type other than adOpenStatic when opening a recordset with an adUseClient cursor location , it will be automatically converted to a static cursor . 
 The adForwardOnly cursor type is the fastest performing cursortype , and also the most limited . The forward - only cursor does not support the RecordCount property , and does not support the MovePrevious methods of the recordset object . 
 The most efficient way to access data for display to the screen out output to a file is to use a adOpenForwardOnly cursor with a adLockReadOnly lock type when opening a recordset . This combination is often referred to as a Firehose Cursor . A firehose cursor bypasses a lot of handling code between the client and server and allows for very fast data access when moving sequentially through the resulting rows . 
 The following recordset methods are supported when using a forward - only cursor with an optimistic lock : 
 In addition , the forward - only cursor type supports non - caching queries . While an asynchronous query allows data to be worked on immediately , it offers no memory benefits when accessing large resultsets , as all rows eventually wind up in memory , taxing system resources when accessing a large number of rows , or a medium number of rows when BLOB data is involved . 
 With MySQL and Connector / ODBC , we can specify option 1048576 in our connection string or check off the option " Don ' t Cache Results " in the ODBC manager in order to specify to the ODBC driver that it should only retrieve one row at a time from the server . With this option set , memory usage on the client is limited as only one row at a time is stored in memory . With every call to the recordset ' s MoveNext method , the previous row is discarded and the next row is queried from the server . 
 While the forward - only cursor is the most efficient of the cursor types , the dynamic cursor , specified but adOpenDynamic , is the least efficient . Because of it ' s inefficiency , dynamic cursor support must be manually activated by using option 32 in your connection string , or by checking " Enable Dynamic Cursor " in the ODBC manager . Without this option enabled , any cursortype other than forward - only with be automatically converted to a static cursor , with it enabled , all cursor types other than forward - only will be converted to dynamic . 
 Why is a dynamic cursor so slow ? As there is no native support for dynamic , server - side cursors in MySQL , every call to a row - moving method ( MoveNext , MovePrevious , etc . ) results in the Connector / ODBC driver converting your method call to a SQL query , posting the query , and returning the resulting row . This also means that for a dynamic cursor to work properly , your underlying table needs a primary key column to determine the next / previous row with . As such , dynamic cursors are not recommended unless absolutely necessary . 
 The dynamic cursor supports the following recordset methods when opened with a optimistic lock : 
 While cursor locations and cursor types specify how our data is going to be handled , the lock type property specifies how we are going to lock the underlying data to protect any changes we make and ensure they are processed . There are four different lock types , and the locktype is set in the recordset object as part of the open method ( it can also be set using the LockType property of the recordset object ) . The four locktypes are : adLockReadOnly ( default ) , adLockOptimistic , adLockPessimistic , and adLockBatchOptimistic . All four locktypes are available to a server - side cursor , the adLockPessimistic locktype is unavailable to a client - side cursor . 
 The default lock type is adLockReadOnly . A read - only lock is the most efficient when accessing data , as there is no checking for data changes and therefore no extra traffic between the client and server while loading records . 
 As the name implies , using a read - only lock will block you from making any changes to the table . If you find yourself with an error message like " Current recordset does not support updating ", then you need to change away from the default adLockReadOnly lock type . 
 An optimistic lock is used for modifications that either happen in a low - concurrency environment , or where having multiple users making changes to the same records is not a major concern . With an optimistic lock , the table or row locks will occur when the update method of the recordset object is called . This will ensure the change is successfully made , but will not prevent other users from changing the underlying data while you are modifying it in VB . 
 The adLockOptimistic lock type is typically your best choice when deciding on a table lock for a non - read - only situation . In almost all my applications , the only two lock types I use are adLockReadOnly and adLockOptimistic . 
 When using the adBatchOptimistic lock type , your changes will be cached locally until the recordset ' s UpdateBatch method is called . When UpdateBatch is called , all changes will be pushed to the server in a group . This can make the bulk insert of a large number of records more efficient . ( Note : Calling ALTER TABLE mytable DISABLE KEYS before a large batch of inserts , followed by ALTER TABLE mytable ENABLE KEYS after the batch completes , can dramatically speed up the batch insert process , as MySQL can rebuild an index faster than it can add one entry at a time ) . 
 In a situation of high concurrency , with multiple users modifying the same data , you may need a pessimistic lock type . With asLockPessimistic , the underlying rows ( or table ) will be locked as soon as you begin making changes to the current record , and will not be unlocked until the Update method is called . 
 While this will ensure that you do not have overlapping changed with other users , it could cause performance issues , especially with a MyISAM table , with features table - level locking only . Make sure that the changes are immediately followed by the recordset ' s update method , and that there is no break for user input between a change and the update in order to ensure no long breaks ( and potentially canceled locks by the database ) in the operation of the database . 
 While adLockPessimistic has it ' s place , the same advice I gave regarding dynamic cursors applies : avoid it when possible , as it is very resource intensive and involves a lot more work both on the client and server side . 
 While there are a large number of potential CursorType / CursorLocation combinations , the ones that are currently available to the MySQL / VB developer are : adUseClient / adOpenStatic , adUseServer / adOpenForwardOnly , and adUseServer / adOpenDynamic . 
 For most uses , adUseClient / adOpenStatic is your best choice , with adLockReadOnly as your lock type for any read - only operations ( export to a file , load rows to a listview , combobox , etc . ) and adLockOptimistic as your lock type for any read / write operations . 
 adOpenDynamic and adLockPessimistic are best suited for high - concurrency situations where you need to ensure that multiple users do not corrupt each other ' s data . While these offer the most current views of data and the most restrictive locking , they do so at a severe price as far as performance is concerned . 
 The combination of adUseServer / adOpenForwardOnly / adLockReadonly offers the best performance overall for operations like populating controls and exporting to files . When combined with option 1048576 ( Don ' t cache query results ) , adOpenForwardOnly also provides excellent memory efficiency , as only one record at a time is loaded into memory . Be awate that if a locktype other than adLockReadOnly is used , memory usage will slowly build as rows are loaded into memory and kept in case they are needed for an update or cancel statement . The one row at a time operation is only present with an adLockReadOnly / adOpenForwardOnly combination . 
 TAG ado , adUseServer , Cursor