작성자:

MySQL에서 csv파일 import 시 한글깨질 때

csv파일을 저장할 때 인코딩타입을 utf8로 변환하여 저장한 후 데이터를 읽어들입니다. 물론 MySQL 테이블의 collate type도 utf8이어야 하겠지요. csv파일의 인코딩 타입을 utf8로 저장하려면 메모장에서 ‘File>다른 이름으로 저장하기>인코딩형식 – utf8 > 저장’하면 쉽게 변환 됩니다.

작성자:

MySQL 컬럼명으로 테이블 찾기

가끔씩 컬럼명만 알고 테이블명이 생각나지 않을 때가 있는데 컬럼명으로 테이블이름을 찾아 주는 고마운(?) 쿼리입니다.

SELECT table_name
FROM information_schema.columns 
WHERE column_name IN ('column_name') 
  AND table_schema='database_name';
작성자:

MySQL에서 대량파일(CSV)파일 읽기, 쓰기

  • csv파일 읽기
    • load data infile '/directory/data/dummy.csv' into table `table_name`
fields terminated by ','
lines terminated by '\n'
ignore 1 lines -- 1행 무시
(col1,col2,col3) -- 원하는 컬럼만 지정
;
  • csv파일 저장
    • select * from `table_name` 
into outfile '/directory/data/dummy.csv'
fields terminated by ','
lines terminated by '\n';
    작성자:

    Nonclustered Index와 clustered Index

    Nonclustered Index의 경우에는, 선택성(selectivity) 을 고려해야 합니다. 크기가 큰 Table에 비해 적은 종류의 data 를 가지는 Column 인 경우에 Nonclustered Index를 만들어 주는 것은 I/O를 감소시키므로 비효율적입니다. 어떤 경우에는 Index를 사용하는 것이 sequential Table scan 을 수행하는 것보다 I/O를 증가하도록 할 수도 있습니다. Nonclustered Index 의 좋은 예로는 회사의 사번, 주민등록번호, 고객번호, 전화번호 등을 들 수 있습니다.

    Clustered Index 는 실제적으로 Table 데이터를 정렬하여 저장하기 때문에, Nonclustered Index에 비해, 유일한 값이며 종류가 많지 않은 Column에 대해서 Range Query를 수행하는 경우에 효과적입니다. 예를 들어, 회사지사, 판매날짜 등을 들 수 있습니다. 즉, 어떤 Column을 Clustered Index로 만들 때에는 순차적 특성을 가지는 컬럼을 대상으로하면 좋습니다.

    작성자:

    FILLFACTOR와 PAD_INDEX의 중요성

    테이블에 대량의 Insert 작업이 발생하는 경우가 있을 수 있는데, 이 때 Page 분할(Splitting)을 방지하기 위하여 Index Page에 여분의 공간을 확보하는 것이 필요합니다.

    Page Splitting 은 Index Page나 Data Page에 새로 추가될 Row를 수용할 공간이 없어서, SQL Server가 새로운 Page를 할당해서 기존 Page에 있던 데이터들을 두개의 Page에 나누는 작업을 하는 것을 의미하는데, 이러한 작업은 System 자원과 시간을 낭비하는 요인이 됩니다.

    해서, Index를 만들 때 fillfactor와 pad_index 설정을 고려하는 것이 필요하게 되는데, CREATE INDEX와 DBCC REINDEX 문의 FILLFACTOR 옵션을 사용하면 Index Page와 Data Page에 여분의 공간을 확보할 수 있습니다.

    Performance Monitor에서 “SQL Server: Access Methods – Page Splits.” 값을 주기적으로 관찰하여 0보다 큰 값이 발견되면, Page 분할이 발생하는 것이므로 DBCC SHOWCONTIG를 사용하여 자세하게 분석하는 것이 필요합니다. 과도한 Page 분할이 Table에서 발생하면, Index를 다시 생성해 주는 작업을 수행할 필요가 있습니다.

    작성자:

    SQL 문으로 이전행과 다음행 접근 구현 및 성능비교

    USE AdventureWorks2012
    GO
    SET STATISTICS IO ON;

    1. Query for SQL2012

    SELECT
LAG(p.FirstName) OVER (ORDER BY p.BusinessEntityID) PreviousValue,
p.FirstName,
LEAD(p.FirstName) OVER (ORDER BY p.BusinessEntityID) NextValue
FROM Person.Person p
GO

    2. Query for SQL Server 2005+ and later version

    WITH CTE AS (
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY p.BusinessEntityID),
p.FirstName
FROM Person.Person p
)
SELECT
prev.FirstName PreviousValue,
CTE.FirstName,
nex.FirstName NextValue
FROM CTE
LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1
LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1
GO

    3. Query for SQL Server 2005+ and later version

    CREATE TABLE #TempTable (rownum INT, FirstName VARCHAR(256));
INSERT INTO #TempTable (rownum, FirstName)
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY p.BusinessEntityID),
p.FirstName
FROM Person.Person p;
SELECT
prev.FirstName PreviousValue,
TT.FirstName,
nex.FirstName NextValue
FROM #TempTable TT
LEFT JOIN #TempTable prev ON prev.rownum = TT.rownum - 1
LEFT JOIN #TempTable nex ON nex.rownum = TT.rownum + 1;
GO

    4. Query for SQL Server 2000+ and later version

    SELECT
rownum = IDENTITY(INT, 1,1),
p.FirstName
INTO #TempTable
FROM Person.Person p
ORDER BY p.BusinessEntityID;
SELECT
prev.FirstName PreviousValue,
TT.FirstName,
nex.FirstName NextValue
FROM #TempTable TT
LEFT JOIN #TempTable prev ON prev.rownum = TT.rownum - 1
LEFT JOIN #TempTable nex ON nex.rownum = TT.rownum + 1;
GO

    [성능표]
    위 실행쿼리들의 Performance는 다음과 같습니다.

    Worktable Logical Reads Person Logical Read Total Logical Read
    Query1 0 3820 3820
    Query2 1977606 11460 1989066
    Query3 171 3820 3991
    Query4 216 3820 4036

    결론
    성능표에서 확인할 수 있듯이 Query1의 SQL2012 버전의 Lead 와 Lag 함수를 이용한 쿼리가 가장 최적화된 성능을 보여주는 군요.

    Reference

  • http://blog.sqlauthority.com/2013/09/25/sql-server-how-to-access-the-previous-row-and-next-row-value-in-select-statement-part-4
    • 작성자:

    Show Table List

    자주 사용할 일이 없어서 가끔씩 테이블 목록을 출력하는 쿼리를 까먹곤 하는데 잊지않기 위해 기록해둡니다.

    SELECT
  SCHEMA_NAME(tbl.schema_id) AS [Schema],
  tbl.name AS [Name], tbl.create_date, tbl.modify_date
FROM
  sys.tables AS tbl
ORDER BY
  [Schema] ASC,[Name] ASC
    작성자:

    INNER JOIN과 CROSS APPLY 비교

    일반적 JOIN을 할 수 없고 테이블과 테이블간 JOIN을 하는 경우 Inner join과 Cross Apply (또는 Left outer join과 Outer Apply 일지라도) 이 쿼리의 실행 결과는 모두 동일합니다.하지만 Performance 측면에서는 Apply 연산자를 사용하는 것이 더 좋다고 합니다.

    Summary:
    While most queries which employ CROSS APPLY can be rewritten using an INNER JOIN, CROSS APPLY can yield better execution plan and better performance, since it can limit the set being joined yet before the join occurs. (Written by Quassnoi)

    Tip
    현재 실행중인 쿼리 조회

    USE master 
GO 
SELECT DB_NAME(database_id) AS [Database], [text] AS [Query]  
FROM sys.dm_exec_requests r 
CROSS APPLY sys.dm_exec_sql_text(r.plan_handle) st 
WHERE session_Id > 50           -- Consider spids for users only, no system spids. 
AND session_Id NOT IN (@@SPID)  -- Don't include request from current spid.
  • http://explainextended.com/2009/07/16/inner-join-vs-cross-apply
  • http://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply
    • 작성자:

    Windows Server /3GB 스위치 사용

    시작하기전에

    /3GB 스위치는 다음 운영 체제에서만 지원됩니다.
• Windows 2000 Advanced Server
• Windows 2000 Datacenter Server
• Windows Server 2003 Enterprise Edition
• Windows Server 2003 Enterprise Edition
• Windows Server 2003 Datacenter Edition
    important중요:
    Windows 2000 Server에서는 /3GB 스위치가 지원되지 않으므로 응용 프로그램이나 운영 체제를 손상시킬 수 있으므로 사용해서는 안 됩니다.

    메모리 옵션 설정하기

    32bit SQLServer와 32bit Windows Server에서는 /3GB 스위치를 사용하여 커널에 1GB를 사용자 모드 공간에 3GB를 할당할 수 있습니다. 만약 4GB를 초과하는 메모리를 사용한다 면 /PAE 옵션을 통해 4GB메모리 주소 공간을 최대 64GB까지 확장할 수 있으며, AWE 옵션으로 SQLServer가 이 공간을 버퍼풀 영역으로 사용할 수있도록 설정이 가능합니다.

    하지만, 64bit SQLServer와 64bit Windows Server에서는 /3GB 스위치, /PAE 스위치 및 AWE 모드는 필요 없습니다.

    만약 32bit SQLServer를 사용하지만 64bit Windows Server를 사용하는 시스템의 경우 /PAE 스위치는 필요 없지만, 32bit SQLServer를 사용하기 때문에 AWE 모드를 함께 설정합니다.

    추가로 Windows Server 2003 제품군에서 /userva=xxxx 스위치를 사용하면 사용자 및 커널 가상 메모리 공간을 보다 정밀하게 조정할 수 있습니다. Boot.ini 파일에서 이 새 스위치를 /3GB 스위치와 함께 사용하면 3,072와 이보다 작은 xxxx의 차이를 다시 커널 모드로 되돌려서 사용자 모드 공간을 2-3GB 사이의 값으로 조정할 수 있습니다. 여기서 xxxx는 MB 값입니다.

    다음의 예제 Boot.ini 파일은 새 스위치로 컴퓨터를 조정해서 2,900MB의 사용자 모드 가상 메모리와 1,196MB의 커널 모드 가상 메모리를 할당하는 방법을 보여 줍니다. 이렇게 하면 사용 가능한 커널 공간이 172MB 증가합니다.

    [Boot Loader]
Timeout=30
Default=multi(0)disk(0)rdisk(0)partition(2)\WINNT
[Operating Systems]
multi(0)disk(0)rdisk(0)partition(2)\WINNT="Microsoft Windows Server 2003" /fastdetect /3GB /Userva=2900

    참고 Microsoft 고객기술지원부에서는 /userva=xxxx 스위치에 대해 2900-3030 범위의 메모리를 사용하도록 권장하고 있습니다.

    Windows Server 2003에서 /3GB 시작 스위치를 설정하려면 다음을 수행합니다.
    1.내 컴퓨터를 마우스 오른쪽 단추로 클릭하고 속성을 선택합니다. 시스템 속성 대화 상자가 나타납니다.
    2.고급 탭을 클릭합니다.
    3.시작 및 복구 영역에서 설정을 클릭합니다. 시작 및 복구 대화 상자가 나타납니다.
    4.시스템 시작 영역에서 편집을 클릭합니다. 메모장에 Windows boot.ini 파일이 열립니다.
    5.[Operating Systems] 섹션에서 /fastdetect 스위치가 포함된 시작 줄 끝에 /3GB 스위치를 추가합니다.
    6.변경 내용을 저장하고 메모장을 닫습니다.
    7.확인을 두 번 클릭하여 열려 있는 대화 상자를 닫은 다음 컴퓨터를 다시 시작하여 변경 내용을 적용합니다.

    Windows 2000 Advanced Server 또는 Windows 2000 Datacenter Server에서 /3GB 시작 스위치를 설정하려면 다음을 수행합니다.
    1.Windows 탐색기에서 시스템 파티션으로 이동합니다. 이 파티션에는 Boot.ini 및 NTLDR과 같은 하드웨어 특정 Windows 파일이 있습니다.
    2.Boot.ini 파일이 없다면 보호된 운영 체제 파일을 숨기도록 폴더 옵션이 설정되어 있기 때문일 수 있습니다. 이런 경우 탐색기 창에서 도구, 폴더 옵션, 보기를 차례로 클릭합니다 보호된 운영 체제 파일 숨기기(권장) 확인란의 선택을 해제합니다. 메시지가 표시되면 예를 클릭합니다.
    3.Boot.ini 파일이 탐색기에 표시되면 해당 파일을 마우스 오른쪽 단추로 클릭하고 연결 프로그램, 메모장을 차례로 클릭하여 파일을 엽니다.
    4.[Operating Systems] 섹션 아래의 “multi”로 시작하는 줄 끝에 /3GB 스위치를 추가합니다.
    5.변경 내용을 저장하고 메모장을 닫습니다.
    6.컴퓨터를 다시 부팅하여 변경 내용을 적용합니다.

  • http://support.microsoft.com/kb/316739/ko
  • http://technet.microsoft.com/ko-kr/library/bb124810(v=exchg.65).aspx