블로그 이미지
SQL Server VS. 석이 minsouk@hotmail.com MSSQL 쿼리성능 관련해 궁금한 사항이 있다면 언제나 누구나 TeamViewer + Line (네이버 japan 메신저) 에 minsouk1 추가 후 연락주세요~ 010-9967-0955 보미아빠

카테고리

보미아빠, 석이 (433)
밥벌이 (16)
싸이클 (1)
일상 (1)
Total185,981
Today3
Yesterday60

달력

« » 2017.08
    1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31    

공지사항

아래 스크립트는 비 효율적인 프로그램 디자인으로 인해 플랜이 무수히 생기는 과정을 시뮬레이션 하고
해당 플랜 서버에서 제거 하는 방법입니다.

1. 쿼리에서 처음부터 바인드 변수 처리
2. OPTION (RECOMPILE) 을 이용해 플랜이 생기지 않도록 처리
3. 생긴플랜 지우기 등을 생각해 볼 수 있을듯 합니다.

일단 sql 모니터링 프로그램이 죽어도 프로그램을 고쳐주지 않으므로 제거하는 방법을 생각해 보았습니다.

-- 테스트 테이블 만들기
SET NOCOUNT ON

IF OBJECT_ID('TBLX') IS NOT NULL
DROP TABLE TBLX
GO

CREATE TABLE TBLX
(IDX INT IDENTITY(1,1)
,C1 INT
)
GO

-- 데이터 삽입
INSERT INTO TBLX VALUES(1)
GO 10000

CREATE UNIQUE CLUSTERED INDEX UCL_TBLX ON TBLX (IDX)
GO

-- 비 효율적인 어플리케이션 시뮬레이션 (플랜 메모리 과도하게 사용 하도록)
DECLARE @MAX INT, @SQL_TEXT VARCHAR(8000)

SELECT @MAX = MAX(IDX) FROM TBLX
WHILE (@MAX > 0) BEGIN
 SET @SQL_TEXT = 'SELECT TOP 10 * FROM TBLX WHERE IDX ='+CAST(@MAX AS VARCHAR(100)) --+ 'OPTION (RECOMPILE)' -- 파라메터 바인딩이 되지 않도록 TOP 사용
 --PRINT @SQL_TEXT
 EXEC (@SQL_TEXT)
 SET @MAX -= 1
END
GO

-- 쓸모 없는 플랜을 제거
DECLARE @T_PLAN_HANDLE TABLE
(IDX INT IDENTITY(1,1)
,PLAN_HANDLE VARBINARY (1000))

INSERT INTO @T_PLAN_HANDLE
SELECT PLAN_HANDLE--, *
  FROM SYS.DM_EXEC_CACHED_PLANS
 CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE)
 WHERE USECOUNTS = 1
   AND OBJTYPE = 'ADHOC'
-- AND 기타조건 I모사 SQL 모니터링 쿼리?

DECLARE @MAX INT, @PLAN_HANDLE VARCHAR (1000), @SQL_TEXT VARCHAR(8000)
SELECT @MAX = MAX(IDX) FROM @T_PLAN_HANDLE

WHILE (@MAX > 0) BEGIN
 SELECT @PLAN_HANDLE = SYS.FN_SQLVARBASETOSTR( PLAN_HANDLE) FROM @T_PLAN_HANDLE WHERE IDX = @MAX
 SET @SQL_TEXT = 'DBCC FREEPROCCACHE ('+CAST(@PLAN_HANDLE AS VARCHAR(8000))+')'
 --PRINT @SQL_TEXT
 EXEC (@SQL_TEXT)
 SET @MAX -= 1
END

--DBCC MEMORYSTATUS
--******************************************************************************
--* AD-HOC 쿼리로 인해 PLAN MEMORY 영역이 비 효율적으로 사용되고 있는 상태
--******************************************************************************

CACHESTORE_SQLCP (NODE 0)                KB
---------------------------------------- -----------
VM RESERVED                              0
VM COMMITTED                             0
AWE ALLOCATED                            0
SM RESERVED                              0
SM COMMITTED                             0
SINGLEPAGE ALLOCATOR                     119232 -- 대충 넣다가 정지해서 이것보다 많은 메모리가 올라 갈 수 있습니다.
MULTIPAGE ALLOCATOR                      248

--******************************************************************************
--* 필요 없다고 판단되는 쿼리를 PLAN 에서 삭제하고 난 후의 상태
--******************************************************************************

CACHESTORE_SQLCP (NODE 0)                KB
---------------------------------------- -----------
VM RESERVED                              0
VM COMMITTED                             0
AWE ALLOCATED                            0
SM RESERVED                              0
SM COMMITTED                             0
SINGLEPAGE ALLOCATOR                     8240
MULTIPAGE ALLOCATOR                      248

 

신고
Posted by 보미아빠

Statistics Strategy

밥벌이 / 2010.09.30 19:05

문제

1. 잘 돌아가던 서버가 갑자기 CPU 100% 를 치면서 이상 현상을 보인다.
2. 갑자기 SQL Server 나 Application 서버가 Hang 이 된다. 
    프로파일러에는 EventSubClass Statistics changed 가 찍혀있다.

해결법

1. 문제의 쿼리를 찾아 힌트를 명시하고, 최적화 기반 recompile 을 막습니다.
2. statistics disable ! 최후의 선택. 이때는 컬럼이나 테이블 단위로 설정하는 것이 좋고 전체 데이터베이스에 적용하면 관리가 상당히 힘들어 집니다.

1. 플랜이 다른 쿼리찾기
SELECT *
  FROM (
    SELECT TOP 5 QUERY_HASH,  COUNT(*) CNT, MAX(SQL_HANDLE) SQL_HANDLE
      FROM SYS.DM_EXEC_QUERY_STATS  
  GROUP BY QUERY_HASH
  ORDER BY 2 DESC ) QS
 CROSS APPLY (
    SELECT TOP 1 *
      FROM SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) ) ST

2. 힌트명시 (통계 히스토그램을 보면 특정값에만 이상하게 값이 편차가 매우 큰 값이 들어있고 실제로는 다른 값들과 유사한 개수의 데이터가 들어있는 경우 입니다. 한마디로 통계가 잘 못 측정된 경우 입니다.) 
   1. index join method 
   2. specify index name
   3. option (힌트나열, keepfixed plan, maxdop ....)

http://www.sql-server-performance.com/articles/per/asynchronous_statistics_p1.aspx

ALTER DATABASE AUTO_UPDATE_STATISTICS_ASYNC ON
ALTER DATABASE AUTO_UPDATE_STATISTICS_ASYNC OFF


Plan optimality-related recompilations: The Big Picture

문제는 영문 소개 자료를 보면 잘 나와 있고, 재미있는 질문이 있었습니다. DEVDDC (미국에서 큰 개발자 컨퍼런스)에서 SQL 개발자에게 질문 했습니다. 그럼 이 기능을 Default 로 off 한 이유는 무엇인가요? SQL 개발자는 하위 호환성을 위해서 off 로 설정되어 있는것 같다. 라고 설명 하더군요. 한마디로 off 할 이유가 없다는 것이죠.



ALTER DATABASE dbname
    SET AUTO_CREATE_STATISTICS ON;

SELECT name AS "Name",
    is_auto_create_stats_on AS "Auto Create Stats",
    is_auto_update_stats_on AS "Auto Update Stats",
    is_read_only AS "Read Only"
FROM sys.databases
WHERE database_ID > 4;

SELECT name AS "Name",
    is_auto_update_stats_async_on AS "Asynchronous Update"
FROM sys.databases;

다음으로 좋은 문서 링크 드립니다. 

http://technet.microsoft.com/en-us/library/cc966425.aspx

If any of the statistics are outdated, they are updated one-at-a-time. The query compilation waits for the updates to finish. An important difference between SQL Server 2000 and SQL Server 2005 regarding this step is that in SQL Server 2005, statistics may optionally be updated asynchronously. That is, the query compilation thread is not blocked by statistics updating threads. The compilation thread proceeds with stale statistics.
DB에 해당 옵션이 활성화 되어 있어야만 이렇게 됩니다.

Identifying statistics-related recompilations

Statistics-related recompilations can be identified by the "EventSubClass" column of the profiler trace (to be described later in this paper) containing the string "Statistics changed".

Closing remarks

An issue not directly related to the topic of this document is: given multiple statistics on the same set of columns in the same order, how does the query optimizer decide which ones to load during query optimization? The answer is not simple, but the query optimizer uses such guidelines as: Give preference to recent statistics over older statistics; Give preference to statistics computed using FULLSCAN option to those computed using sampling; and so on.

There is a potential of confusion regarding the "cause and effect" relationship between plan optimality-related compilations, recompilations, and statistics creation/updates. Recall that statistics can be created or updated manually or automatically. Only compilations and recompilations cause automatic creation or updates of statistics. On the other hand, when a statistic is created or updated (manually or automatically), there is an increased chance of recompilation of a query plan which might find that statistic "interesting."

Best practices

Four best practices for reducing plan optimality-related batch recompilations are given next:

Best Practice: Because a change in cardinality of a table variable does not cause recompilations, consider using a table variable instead of a temporary table. However, because the query optimizer does not keep track of a table variable's cardinality and because statistics are not created or maintained on table variables, non-optimal query plans might result. One has to experiment whether this is the case, and make an appropriate trade-off.

Best Practice: The KEEP PLAN query hint changes the recompilation thresholds for temporary tables, and makes them identical to those for permanent tables. Therefore, if changes to temporary tables are causing many recompilations, this query hint can be used. The hint can be specified using the following syntax:

SELECT B.col4, sum(A.col1) FROM dbo.PermTable A INNER JOIN #TempTable B ON A.col1 = B.col2 WHERE B.col3 < 100 GROUP BY B.col4 OPTION (KEEP PLAN)

Best Practice: To avoid recompilations due to plan optimality-related
(statistic update-related) reasons totally, KEEPFIXED PLAN query hint can be specified using the syntax:

SELECT c.TerritoryID, count(*) as Number, c.SalesPersonID FROM Sales.Store s INNER JOIN Sales.Customer c ON s.CustomerID = c.CustomerID WHERE s.Name LIKE '%Bike%' AND c.SalesPersonID > 285 GROUP BY c.TerritoryID, c.SalesPersonID ORDER BY Number DESC OPTION (KEEPFIXED PLAN)

With this option in effect, recompilations can only happen because of correctness-related reasons — for example, schema of a table referenced by a statement changes, or a table is marked with sp_recompile procedure.

In SQL Server 2005, there is a slight change in behavior as described below. Suppose that a query with OPTION(KEEPFIXED PLAN) hint is being compiled for the first time, and compilation causes auto-creation of a statistic. If SQL Server 2005 can get a special "stats lock," a recompilation happens and the statistic is auto-created. If the "stats lock" cannot be obtained, there is no recompilation, and the query is compiled without that statistic. In SQL Server 2000, a query with OPTION(KEEPFIXED PLAN) is never recompiled because of statistics-related reasons, and therefore, in this scenario, no attempt is made to get a "stats lock" or to auto-create the statistic.

Best Practice: Turning off automatic updates of statistics for indexes and statistics defined on a table or indexed view will ensure that plan optimality-related recompilations caused by those objects will stop. Note, however, that turning off the "auto-stats" feature using this method is usually not a good idea because the query optimizer is no longer sensitive to data changes in those objects, and sub-optimal query plans might result. Adopt this method only as a last resort after exhausting all of the other alternatives.

 
신고

'밥벌이' 카테고리의 다른 글

read-ahead 미리읽기란?  (0) 2010.10.08
시스템이 복구중이라고 하고 시작되지 않을때  (0) 2010.10.07
sp_lock2  (4) 2010.10.06
주의대상 복구 suspect  (0) 2010.10.06
Statistics Strategy  (0) 2010.09.30
CursorTypes, LockTypes, and CursorLocations  (2) 2010.09.30
Posted by 보미아빠
누가 ADO 에서 Cache Size 변화에 따른 로컬 Vs. 원격 IDC간 성능 차이를 알고 싶다고 의뢰해와서 테스트 해보게 되었습니다.

MySQL 은 adUseServer 를 지원하지 않습니다. SQL Server 는 둘 다 지원 합니다.
전 Test 해본 후에야 알았습니다. 역시 문서는 꼼꼼히 읽어봐야 하나 봅니다. MySQL 로 후딱 테스트 해보고 끝낼려고 하다가 반나절 넘게 고생하고 테스트 못할뻔...

아래는 Cache Size (Fetch Size 라고 적힌 부분 입니다.) 에 대한 성능 Test 곡선 입니다.
실험결과 16개이상에서는 성능상 별 이득이 없었습니다. 아래 글에서 마크한 부분이라도 읽어두시면 좋습니다.

붉은선은 Remote IDC 간 성능 곡선이고,
푸른색은 Local IDC 간 성능 곡선 입니다.



TEST 프로그램은 첨부파일로 담았으니 혹시 관심있는 분은 Test 해보시길 바랍니다.
VS2010 c# Console application 입니다.


마지막으로 당부 드리고 싶은말은 SQL Server 어플리케이션 성능이 좋지않아 원인을 분석해보니 ServerSide 커서를 사용하고 그 CacheSize 혹은 FetchSize 가 1로 되어 있다면, 이 CacheSize 만 늘이시길 바랍니다. 아니면, 어플리케이션 의도상 반드시 ServerSide 커서가 필요한 어플리케이션이 있을 수 있으니 주의해서 설정(튜닝) 하시길 바랍니다. 그냥 클라이언트 커서로 바꾸시구요, 에러나는건 잡으면서 반나절만 고생하시면 되요. 이럼 아니되옵니다.

원본글 링크 http://dev.mysql.com/tech-resources/articles/vb-cursors-and-locks.html

Introduction

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 SP1). Finally, this article applies to ADO 2.7 and VB6. It is not applicable to ADO.NET under VB.NET (Unless you reference ADODB in your project and use it for database access).

What Are Cursors?

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.

Cursor Location

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.

adUseServer

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.

adUseClient

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.

Cursor Types

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.

adOpenStatic

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):

  • AddNew
  • Delete
  • Find
  • MoveFirst
  • MovePrevious
  • MoveNext
  • MoveLast
  • Resync
  • Update
  • 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.

adOpenForwardOnly

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:

  • AddNew
  • Delete
  • Find
  • Update
  • UpdateBatch

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.

adOpenDynamic

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:

  • AddNew
  • Delete
  • Find
  • MoveFirst
  • MovePrevious
  • Update
  • UpdateBatch

While Dynamic cursors can be beneficial for multi-user applications, it is best to avoid them when possible, and work around multi-user issues when possible by calling the resync and requery methods when possible, and executing UPDATE queries that increment and decrement count values instead of using the recordset to do updates (i.e. rather than getting an inventory count in a recordset, incrementing it in VB, and doing a recordset.update, use the connection object to execute a query similar to UPDATE inventory SET count = count - 1 WHERE itemcode = 5)

Lock Types

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.

adLockReadOnly

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.

adLockOptimistic

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.

adLockBatchOptimistic

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).

adLockPessimistic

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.

Conclusion

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.

신고

'밥벌이' 카테고리의 다른 글

read-ahead 미리읽기란?  (0) 2010.10.08
시스템이 복구중이라고 하고 시작되지 않을때  (0) 2010.10.07
sp_lock2  (4) 2010.10.06
주의대상 복구 suspect  (0) 2010.10.06
Statistics Strategy  (0) 2010.09.30
CursorTypes, LockTypes, and CursorLocations  (2) 2010.09.30
Posted by 보미아빠

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

티스토리 툴바