sqlite3 insert 속도 높이기

 요즘 개인적인 개발로 https://yiunsr.tistory.com/792이것을 하고 있다. 이 동작중에 하나는 csv 파일을 읽어서 sql 에 insert 하는 동작을 한다. 처음에는 electronjs 에서 사용할 것이기 때문에 nodejs 를 사용했다. 그러나 속도가 생각보다 나오지 않아서 go 를 사용해서 새롭게 개발하고 있다. 

 내부적인 동작이 조금 복잡한데, CSV 파일의 encoding 을 자동으로 분석해서(utf8 인지 euc-kr 인지 아니면 기타 encoding 인지 자동으로 detect 한다. ), utf8 로 변환시키고 sqlite3에 insert 하는 동작을 한다. 이 동작이 nodejs 에서는 초당 만(10K) 건이 겨우 넘는 수준이었다. 여러 옵션을 섞어 받지만 초당 3만건 정도가 한계였다. 언어를 Golang 으로 변경 했더니 초당 75K 건이 나왔다. 이 옵션은 https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite 를 참고했다. 

 내 경우 아래 코드를 사용했다. 

db.Exec("PRAGMA synchronous = OFF")
db.Exec("PRAGMA journal_mode = MEMORY")
db.Exec("PRAGMA cache_size = 10000")
db.Exec("PRAGMA locking_mode = EXCLUSIVE")
db.Exec("PRAGMA temp_store = MEMORY")

 

그리고 여러 insert code 는 한번의 트랜잭션으로 동작해야 한다. 이 부분을 적용한 것이 속도 개선에 엄청난 차이가 있다. insert 코드 전에는 꼭 Prepare 코드를 사용해서 코드를 만든 후 사용했다. 이것도 속도의 엄청난 개선을 주었다. 

 테이블을 만들 때, inter primary key 를 만들었다. 이 primary key 가 없으면 자동으로 _rowid 가 추가된다고 한다. 그리고 auto increment 를 사용하지 않았다. 수동으로 증가하는 integer 를 전달하는게 조금 더 빨랐다.

그리고 시도는 해보지 않았지만, 하나의 insert query 에 values 를 여러가 넣는것도 시도해볼만 할 것 같다. 

INSERT INTO table1 (column1,column2 ,..) 
VALUES  
   (value1,value2 ,...), 
   (value1,value2 ,...), 
    ... 
   (value1,value2 ,...);