히파루스의 솔직한 여행 이야기

보통 게시판 페이징 쿼리를 만들 때 두가지 방법을 쓴다.
구닥다리 방법은 먼저 불러올 전체 카운트를 구하고 이를 토대로 실제 불러오는 쿼리에서 NOT IN으로 제외하고 불러오는 쿼리인데
똑같은 쿼리를 남발해야 하므로 아직도 사용하는 곳이 있다면 문제가 심각해 보이므로 PASS

두번째 방법은 ROW_NUMBER를 이용하는 쿼리인데 ROW_NUMBER()로 내마음속 번호를 생성시킨 후 이를 기준으로 페이징을 한다.
그나마 써줄만하다.

SELECT
   MEMTBL.USERID_IDX
FROM (
   SELECT ROW_NUMBER() OVER (ORDER BY USERID_IDX) AS ROWNUM, USERID_IDX FROM MEMBER_TABLE 
       ) AS MEMTBL
WHERE
   MEMTBL.ROWNUM BETWEEN 1 AND 10
 
마지막으로 MS-SQL 2012 이후 부터 사용가능한 쿼리인데 아래처럼 간결한 쿼리가 가능해졌다.

SELECT
   USERID_IDX
FROM
   MEMBER_TABLE WITH (NOLOCK)
ORDER BY 
   USERID_IDX
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

시작위치는 OFFSET으로 지정하고
FETCH NEXT로 몇번까지 불러올지 지정만 하면 그만이다.

appsettings.json에 db connection string을 적어두고 이를 가지고와 db connection을 맺어줄 때 사용되는 코드

            var builder = new ConfigurationBuilder().SetBasePath(Directory.GetCurrentDirectory())

           .AddJsonFile("appsettings.json", optional: false);

            IConfiguration configuration = builder.Build();

            string defaultConnectionString = configuration.GetValue<string>("ConnectionStrings:DEVDB");

            using (var conn = new SqlConnection(defaultConnectionString))   

           {

                conn.Open();

                using (var cmd = new SqlCommand())

                {

                    cmd.Connection = conn;

                    cmd.CommandText = @"

                                    SELECT

                                        M.M_USERID, M.M_USERNAME, M.M_REGDATE

                                        , MD.MD_ADDRESS, MD.MD_EMAIL, ....., JOBCD.CDNAME AS JOBCDNAME

                                    FROM 

                                        S_MEMBER AS M

                                        INNER JOIN S_MEMBERDETAIL AS MD ON M.M_IDX = MD.M_IDX AND MD.MD_DEL = 0

                                        INNER JOIN S_CODE AS JOBCD ON MD.MD_JOBCD = JOBCD.CDSUB

                                        INNER JOIN S_CODE AS JOBSUBCD ON MD.MD_JOBSUBCD = JOBSUBCD.CDSUB

                                    WHERE

                                        M.M_IDX = @M_IDX

                                        AND M.M_DEL = 0

                                    ";

                    cmd.Parameters.AddWithValue("@M_IDX", idx);

                    var reader = cmd.ExecuteReader();

                    reader.Read();

                    var member = new AdminMember();

                    member.Idx = idx;

                    member.UserId = (string)reader["M_USERID"];

                    member.UserName = (string)reader["M_USERNAME"];

                     ......   

                    member.DetailJobSubCd = (string)reader["MD_JOBSUBCD"];

                    member.DetailContent = (string)reader["MD_CONTENT"];

                    reader.Close();

                    return member;

                }

                return null;

            }

SELECT
   OBJECT_NAME(object_id),  OBJECT_DEFINITION(object_id)
FROM 
   sys.procedures
WHERE 
   OBJECT_DEFINITION(object_id) LIKE '%내용%'

튜닝도 거치고 쿼리분석기에서 예상실행계획상 누락된 인덱스도 없으며 0.1초만에 잘 나오는 쿼리상으론 아무 문제 없는 쿼리임에도 불구하고 웹페이지에서 실행하면 15초씩 걸리는 이상한 현상이 발견되었다.

가만히 놔둬선 안되겠다 싶어 구글링을 해보니 나와 같은 증상을 겪은 사람들이 이미 여럿 존재함을 확인할 수 있었다.

이유는 ARITHABOART 이녀석 때문인데 시퀄서버에선 기본값이 ON이지만 ADO에선 아래 표처럼 기본값이 OFF

결론은 쿼리 날릴 때 ADODB의 설정을 건드려주면 된다.
Static Query인 경우엔 쿼리문 앞에 SET ARITHABORT ON을 넣어주고
Set objCmd = Server.CreateObject("ADODB.Command")
    SQL = ""
    SQL = SQL & vbCrLf & "SET ARITHABORT ON"
    SQL = SQL & vbCrLf & "SELECT"
    ......
    With objCmd
    .ActiveConnection = dbConn
    .CommandType = adCmdText
    .CommandText = SQL
    
               .Parameters.Append .CreateParameter("param", adDate, adParamInput, , paramdata)
    End With
    
    Set rs = Server.CreateObject("ADODB.RecordSet")
    rs.CursorLocation = adUseClient
    rs.Open objCmd, , adOpenStatic, adLockReadOnly

    If rs.EOF Then
    Else
    End If

    rs.Close
    Set rs = Nothing
Set objCmd = Nothing

Stored Procedure인 경우
SqlConnection dbConn = new SqlConnection(strConnection);

dbConn.Open();
SqlCommand cmdAritabort = new SqlCommand("SET ARITHABORT ON", dbConn);
cmdAritabort.ExecuteNonQuery();
           
SqlCommand cmd = new SqlCommand("dbo.usp_example", dbConn);
cmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter adapter = new SqlDataAdapter(com);
adapter.Fill(table);

dbConn.Close();


관련하여 더 자세히 느끼고 싶다면 Slow in the Application, Fast in SSMS? Understanding Performance Mysteries란 버거형님의 진중하고 장대한 글을 참고해보길 바란다.
https://www.sommarskog.se/query-plan-mysteries.html
해당 글에선 좀 다르게 이야기 해주던데 너~~~무 길고 난 ARITHABOART ON 설정으로 느린 쿼리를 해결하였으므로 이쯤에서 만족하려 한다.

웹개발을 하다보면 속도가 느려 사용을 못할 쿼리를 짜게 되기도 한다.
DB설계란게 다 그렇듯 최종 완성물로 가기까지 계속 설계가 변경되기도 하고 설령 완료되어 운영하더라도 기능 변경 요구나 데이터가 쌓여감에 따라 초기 설계와 달라지거나 속도가 느려지는 일들이 비일비재 하므로 그때마다 DBA에게 위탁하기도 뭐하고..
작은 회사에선 DBA가 있을턱도 없고 그렇다고 느린 속도를 한탄만 하고 있을 순 없는데
MSSQL에선 기특하게도 디비 튜닝을 매우 쉽게 도와주는 기능을 기본 탑재하고 있다.

초보자도 쉽게 튜닝이 가능하므로 따라해보자

1. 시퀄(mssql을 있어 보이게 발음해보자) Management Studio을 실행 후  문제가 되는 쿼리창에 복사해 두자.
2. Management Studio의 상단 메뉴에 보면 예상실행계획 표시라는 아이콘이 보일것이다. 
네모 세개가 선으로 이어지고 거기에 삼각형이 뒤집어진 아이콘인데 실행 버튼 우측에 조금만 가면 있다.
모르겠으면 단축키로 Ctrl + L을 누르면 된다.

3. 쿼리가 실행되고 나면 하단 쿼리 결과 우측에 보면 요런 ... 네모 버튼이 보일것이다.
여기에서 마우스 우측 버튼을 누르면 컨텍스트 메뉴가 나타난다.

누락된 인덱스 세부 정보(M)을 마우스 왼쪽 버튼으로 누르면

 새 쿼리창이 뜨면서 아래처럼 주석안에 누락된 인덱스를 친절히 알려준다.
/* 주석안의 */ CREATE INDEX문에서 대괄호 [] 안의 인덱스 이름을 지정해주고 실행만 시켜주면 튜닝에 필요한 적합한 인덱스가 나타난다.
<NAME OF MISSING INDEX, SYSNAME, > 이부분을 원하는 인덱스 명으로 바꿔주면 된다.
내 경우 IDX_TABLENAME_FIELD 명을 규칙으로 정해서 알맞게 바꿔주고 있다.

5. 다시 2번을 눌러서 4번까지 반복해가며 속도가 빨라질 때까지 진행하면 된다.


조건문에 따라 쿼리문이 달라지기 때문에 그에 맞게끔 쿼리를 변경해가며 수행해야 할 수도 있다.
또 인덱스를 추가만 한다고 모든 경우에 다 맞아 떨어지리란 보장 또한 없다.
경우에 따라선 설계 자체를 변경해야 할 수도 있고 쿼리 자체를 바꿔야 할수도 있지만
경험상 상당수 속도 개선 효과를 볼 수 있었다.