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

일단 내 상황은 이렇다.
사용해본 DB는 MSSQL을 15년 이상 주력사였고 최근 Oracle(과거 SI 잠깐 했을 당시에 2년 정도 토드사용해본게 전부였지만 최근 다시 사용) 그 외 잡다한 MySql, MSACCESS(ㅋㅋㅋ), DB같지도 않은 파라독스(S모 카드사에서 처음 접하고 개깜놀) 대충 이렇다.

이 상황에서 처음 PostgreSQL을 설치해보았다.
그리고 첫 실행을 하고자 설치된 폴더로 이동해보니 딱 저렇게 보인다.
느낌적으로 pgAdmin 4라는 툴이 MSSQL의 SQL Server Management Studio스러워 보인다.
SQL Shell은 아마 커맨드 라인으로 실행해주는 paAdmin 4일듯 싶다.

pgAdmin 4를 냅다 실행해본다.

음 역시나가 역시다.
탭 영역을 보니 대시보드가 있고 그 옆에 프로퍼티, 시퀄 등등등이 있다. 
눈깔을 좌측 트리로 이동해보자 Servers가 있다.

MSSQL처럼 UI가 마음에 드는구나.
트리를 펼쳐서 PostgreSQL 16을 눌러보자.
팝업윈도우가 뜬다.

아까 설치할 때 기록한 비번을 넣어준다.

이러니 Databases와 Roles, Tablespaces 등등 기본 시스템 DB쪽으로 추정된다.

자 그럼 이제 내 디비를 만들어봐야겠제?
PostgreSQL 16에서 마우스 오른쪽을 눌러본다. Create가 있다 그 옆에 Database가 있구나.
정말 직관적이다.(MSSQL 이용자라면 너무 쉽다.)
있어야 할 곳에 기능이 있다. 그게 MS의 최대 장점 아닌가?

Database를 선택하니 역시나 창이 하나 뜬다.
뻔하다. DB명 만들어주고 저장하면 그만이다.
그 옆 Definition, Security 등을 눌러봤지만 뭘 하는건진 아직 모르겠다. 
일단 DB나 만들자. Save 전에 SQL탭이 눈에 들어온다.
뻔하다. 내가 만드는 액션의 SQL문이겠지. 그리고 저 SQL문은 SQL Shell 프로그램에서 동작시키거나 SQL management처럼 쿼리분석기에서 실행시킬 수 있을것이다.
그냥 저런것도 보여주는구나 하고 넘기자. SAVE

Save를 하고 나니 내 첫 PostgreSQL DB가 생성된게 보인다.

그럼 테이블을 만들어 보자.
테이블이야 뭐 뻔하다 Schemas에 있겠지
역시나 Tables가 있다.
만들려면? 뻔하지 마우스 오른쪽 눌러보자.
Create가 나온다. 그리고 Table

그러면 Database 처럼 Table 생성창이 뜬다.
이름은 게시판이 제일 만만하니 board_tbl이라 정해준다.
그다음은? 당연히 컬럼이다. General 옆에 Columns가 보인다. 그거 누른다.

난 매우 간단하게 기준키 필드는 integer, 제목은 varchar, 내용은 text나 varchar로 만들고자 한다.
그런데 varchar가 안보인다.
이건 좀 당황스럽다.

chatGPT센세에게 물어보고 싶지만 내가 쓰는 버전은 3.5버전이므로 최신 정보는 알 수 없을 것 같다.
claude.ai 티쳐에게 물어보자.

16버전에선 varchar대신 text를 사용하는게 좋단다.
초기 버전엔 varchar를 제공했지만 요즘은 그냥 닥치고 text쓰란다.
그럼 varchar와 text가 아무런 차이점이 없다란 말인데... 흠..일단 믿어보자.

그런데 text[] 이건 뭐지? 배열인데...
또 claude.ai 티쳐에게 퀘스쳔

{'태그1', '태그2'...}이런식을 저장하는 자료형이란다.
대충 PostgreSQL이 일반적인 RDBMS가 아니라는말은 익히 들어왔었던지라 지리정보나 그따위 정보에 특화된 필드가 아닐까 추측해본다.
조금 구글링 해보니 JSON형도 있던데... 음... 그렇군 
옥히 도키

b_idx는 integer로 PK로 설정하고 다른 제목과 내용 역시 Not NULL로 설정한다. 기본값은 없다.

Save

저장하고 나니 역시나 좌측 트리 영역에 테이블과 필드가 잘 나타난다.

이제 데이터를 insert해보자
ANSI문법은 언제나 통하니 간단하게 insert문을 실행해보자.
드래그 후 F5(execute script)를 실행하니 error가 뜬다.

이런.. b_title인데 필드명 오타를 냈나보다.
보자.. 테이블 스키마 수정은... 뭐 뻔하지 마찬가지로 테이블의 board_tbl에서 마우스 우측 눌러 Properties를 누르면 되겠지

역시나다. b_titile 오타 i를 하나 제거하자
Save

저장하기 전 SQL문을 구경해보자
좀 특이하다. 이게 ANSI문법인가.
MSSQL과는 좀 다르다.
ALTER TABLE IF EXISTS.... RENAME 뭐 그런가보다.

다시 SQL문을 실행하자.
그 전에 나는 그냥 select 문도 추가하고 F5를 실행하였는데 오류가 난다.
아마 오라클처럼 ;를 넣어줘야 할 것 같다.

정상적으로 insert, select가 실행된다.
다시 한 번 실행해보면 b_idx값이 똑같이 1로 넣으니 당연히 duplicate 오류가 뜬다.

2로 바꾸고 실행하면 잘 넣어지고 잘 보여진다.

일단 첫인상.
매우 직관적이다.
MSSQL을 사용해봤다면 토드를 사용할 때와 다르게 친숙한 느낌을 받을 수 있다.
여기에 뭐랄까 script같은 느낌도 들고...
조만간 회사 오라클 DB를 PostgreSQL로 바꾸게 될 지 모르겠다.
대공사겠지만.

보통 게시판 페이징 쿼리를 만들 때 두가지 방법을 쓴다.
구닥다리 방법은 먼저 불러올 전체 카운트를 구하고 이를 토대로 실제 불러오는 쿼리에서 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번까지 반복해가며 속도가 빨라질 때까지 진행하면 된다.


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