카테고리 보관물: DB

sqlite의 stmt를 재활용(sqlite3_reset)하여 성능 향상 시키기

http://greenfishblog.tistory.com/m/post/view/id/151

sqlite3를 사용하여 Insert나 Query할 때 아래와 같은 방법으로 사용할 수 있습니다.
(리턴값 체크는 필수이지만, 간략하게 표시하기 위해 제외하였습니다)

(1) sqlite3_exec을 통해 SQL문을 그대로 만들어 전달하고, query는 callback 함수를 통해 전달 받음

int fnCallback(void* arg, int columns, wchar_t** value, wchar_t** name)
{

}

for (…)
{

// DB QUERY
        sqlite3_exec(db, “SELECT …“, fnCallback, pContext, NULL);

// DB INSERT
        sqlite3_exec(db, “INSERT …“, NULL, NULL, NULL);

(2) statement를 prepare하여 callback 함수 없이 각 값들의 bind를 통해 전달하고 전달 받음

    …
sqlite3_stmt* pStmt = NULL;

for (…)
{
// DB INSERT
        sqlite3_finialize(pStmt);
sqlite3_prepare(db, “INSERT … (?,?)“, -1, &pStmt, NULL);
sqlite3_bind_int(pStmt, 1, 123);
sqlite3_bind_int(pStmt, 2, 456);
sqlite3_step(pStmt);
sqlite3_finalize(pStmt);
pStmt = NULL;

// DB QUERY
        sqlite3_prepare(db, “SELECT a,b,c … WHERE a=? AND b=?“, -1, &pStmt, NULL);
sqlite3_bind_int(pStmt, 1, 111);
sqlite3_bind_int(pStmt, 2, 333);
for (;;)
{
if (SQLITE_ROW == sqlite3_step(pStmt))
{
nA = sqlite3_column_int(pStmt, 0);
nB = sqlite3_column_int(pStmt, 1);
nC = sqlite3_column_int(pStmt, 2);
}
else
{
break;
}
}
sqlite3_finalize(pStmt);
pStmt = NULL;

(3) statement를 단 한번만 prepare하고 필요시 reset하여 재사용

    …
sqlite3_stmt* pStmtInsert = NULL;
sqlite3_stmt* pStmtQuery  = NULL;

sqlite3_prepare(db, “INSERT … (?,?)“, -1, &pStmtInsert, NULL);
sqlite3_prepare(db, “SELECT a,b,c … WHERE a=? AND b=?“, -1, &pStmtQuery, NULL);

for (…)
{
// DB INSERT
        sqlite3_reset(pStmtInsert);
sqlite3_bind_int(pStmtInsert, 1, 123);
sqlite3_bind_int(pStmtInsert, 2, 456);
sqlite3_step(pStmtInsert);

// DB QUERY
        sqlite3_reset(pStmtQuery);
sqlite3_bind_int(pStmtQuery, 1, 111);
sqlite3_bind_int(pStmtQuery, 2, 333);
for (;;)
{
if (SQLITE_ROW == sqlite3_step(pStmtQuery))
{
nA = sqlite3_column_int(pStmtQuery, 0);
nB = sqlite3_column_int(pStmtQuery, 1);
nC = sqlite3_column_int(pStmtQuery, 2);
}
else
{
break;
}
}

}

sqlite3_finalize(pStmtInsert);
pStmtInsert = NULL;
sqlite3_finalize(pStmtQuery);
pStmtQuery = NULL;

겉으로 확인해 보면 방법 (1)이 가장 짧고 명확해 보입니다. 그리고, (3)이 (2)보다 좀더 복잡하고 까다로와 보입니다. 하지만 성능으로 따지자면 (1) < (2) < (3) 입니다. 물론 최근의 computing power가 좋아져서, 큰 차이는 없을 수 있습니다. 하지만, query의 양이 많거나 길이가 길 경우, 혹은 record 수가 많다던지, 여하튼 db의 크기가 커질 수 록 그 성능의 차이는 눈으로 드러날 것입니다.

(2)과 (3)의 차이는 sqlite3_prepare(…)의 호출 빈도라 할 수 있습니다. (3)에서는 그 자리에 대신 sqlite3_reset(…)이 들어가 있습니다. 해당 코드를 보면 sqlite3_reset이 훨씬 부하가 적어 보입니다(단순히 memory free 위주로 되어 있음). 물론 sqlite3_prepare는 btree 호출도 있고 구문 parsing도 있습니다. 위 예에서는 누락되어 있지만, sqlite3_reset의 리턴값 체크는 해주는 것이 중요합니다.

DDL, DML, DCL

DDL(Data Definition Language)
데이터베이스의 스키마 객체를 생성(CREATE), 변경(ALTER), 제거(DROP) 하거나 권한의 부여나 박탈(GRANT, REVOKE), 주석(COMMENT), 자료의 버림(TRUNCATE) 등을 수행하는 문장의 집단을 의미한다.  이 밖에도 많은 종류의 DDL이 존재한다. 그러나 PL/SQL 블록에서는 DDL을 사용할 수 없다.

DML(Data Manipulation Language)[Manipulation=교묘한처리,조종]
스키마 객체의 데이터를 입력(INSERT), 수정(UPDATE), 조회(SELECT), 삭제(DELETE)하거나 테이블에 잠금을 설정하거나 (LOCK TABLE), SQL문의 처리의 절차에 대한 정보를 얻거나 (EXPLAIN PLAN), PL/SQL 모듈을 호출하는 작업(CALL)의 집단이다.  PL/SQL의 모듈에서 사용할 수 없는 DML로서는 EXPLAIN PLAN과 CALL이 있다.

DCL(Data Control Language)
트랜잭션의 성격을 제어하는 것으로서 SET TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT와 같은 종류가 있다. PL/SQL 모듈에서는 DCL을 사용할 수 있고 DCL을 이용하여 모듈의 트랜잭션을 제어 할 수 있다.

원본(http://bluejames77.blog.me/80006792087)