본문 바로가기
JAVA/JAVA 기초

JDBC) 대량 쿼리문 실행 for SQLite

by 도쿠니 2022. 5. 21.

대량의 데이터를 데이터베이스에 저장할 일이 생겼는데, statement를 작성하고 execute하는걸 반복문으로 돌리니 완료될때 까지 하루종일 걸렸다.

기존 코드

package com.dokuny.find_public_wifi.repository;

import com.dokuny.find_public_wifi.model.WifiApiDto;
import com.dokuny.find_public_wifi.service.ApiService;

import java.sql.*;
import java.util.ArrayList;

public class WifiRepositoryImpl implements WifiRepository {
    public static void main(String[] args) {
        WifiRepositoryImpl wifiRepository = new WifiRepositoryImpl();
        wifiRepository.saveAll();
    }
    @Override
    public void saveAll() {
        ArrayList<WifiApiDto> wifiDataAll = ApiService.getWifiDataAll();
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DriverManager.getConnection("jdbc:sqlite:D:\\study\\플젝\\Find_Public_Wifi\\find_public_wifi");

            String sql = "DELETE FROM wifi;";
            pstmt = conn.prepareStatement(sql);
            pstmt.execute();


            for (WifiApiDto wifiApiDto : wifiDataAll) {
                sql = "INSERT INTO wifi VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
                pstmt = conn.prepareStatement(sql);
                pstmt.setString(1,wifiApiDto.getX_SWIFI_MGR_NO());
                pstmt.setString(2,wifiApiDto.getX_SWIFI_WRDOFC());
                pstmt.setString(3,wifiApiDto.getX_SWIFI_MAIN_NM());
                pstmt.setString(4,wifiApiDto.getX_SWIFI_ADRES1());
                pstmt.setString(5,wifiApiDto.getX_SWIFI_ADRES2());
                pstmt.setString(6,wifiApiDto.getX_SWIFI_INSTL_FLOOR());
                pstmt.setString(7,wifiApiDto.getX_SWIFI_INSTL_TY());
                pstmt.setString(8,wifiApiDto.getX_SWIFI_INSTL_MBY());
                pstmt.setString(9,wifiApiDto.getX_SWIFI_SVC_SE());
                pstmt.setString(10,wifiApiDto.getX_SWIFI_CMCWR());
                pstmt.setString(11,wifiApiDto.getX_SWIFI_CNSTC_YEAR());
                pstmt.setString(12,wifiApiDto.getX_SWIFI_INOUT_DOOR());
                pstmt.setString(13,wifiApiDto.getX_SWIFI_REMARS3());
                pstmt.setFloat(14,wifiApiDto.getLAT());
                pstmt.setFloat(15,wifiApiDto.getLNT());
                pstmt.setString(16,wifiApiDto.getWORK_DTTM());
                pstmt.execute();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {  
                if (rs != null && !rs.isClosed()) {
                    rs.close();
                }

                if (pstmt != null && !pstmt.isClosed()) {
                    pstmt.close();
                }

                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

진짜 너무 오래 걸린다. 끝까지 기다려보려했는데 오래걸려서 포기했다...

그래서 찾아보니 addBatch()와 executeBatch()를 사용해서 한번에 쿼리를 날려보내면 빨라질 수도 있다고 하길래

  • Statement.addBatch()
    • 쿼리 구문을 대량으로 처리하기 위해 메모리에 올려두는 메소드
  • Statement.executeBatch()
    • 메모리에 올려져있는 쿼리들을 한번에 DB로 날리는 메소드

개선 코드 1 - addBatch(), executeBatch() 사용

package com.dokuny.find_public_wifi.repository;

import com.dokuny.find_public_wifi.model.WifiApiDto;
import com.dokuny.find_public_wifi.service.ApiService;

import java.sql.*;
import java.util.ArrayList;

public class WifiRepositoryImpl implements WifiRepository {
    public static void main(String[] args) {
        WifiRepositoryImpl wifiRepository = new WifiRepositoryImpl();
        wifiRepository.saveAll();
    }
    @Override
    public void saveAll() {
        ArrayList<WifiApiDto> wifiDataAll = ApiService.getWifiDataAll();
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DriverManager.getConnection("jdbc:sqlite:D:\\study\\플젝\\Find_Public_Wifi\\find_public_wifi");

            String sql = "DELETE FROM wifi;";
            pstmt = conn.prepareStatement(sql);
            pstmt.execute();

            pstmt = conn.prepareStatement("BEGIN TRANSACTION;");
            pstmt.execute();

            sql = "INSERT INTO wifi VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
            pstmt = conn.prepareStatement(sql);

            for (WifiApiDto wifiApiDto : wifiDataAll) {
                pstmt.setString(1,wifiApiDto.getX_SWIFI_MGR_NO());
                pstmt.setString(2,wifiApiDto.getX_SWIFI_WRDOFC());
                pstmt.setString(3,wifiApiDto.getX_SWIFI_MAIN_NM());
                pstmt.setString(4,wifiApiDto.getX_SWIFI_ADRES1());
                pstmt.setString(5,wifiApiDto.getX_SWIFI_ADRES2());
                pstmt.setString(6,wifiApiDto.getX_SWIFI_INSTL_FLOOR());
                pstmt.setString(7,wifiApiDto.getX_SWIFI_INSTL_TY());
                pstmt.setString(8,wifiApiDto.getX_SWIFI_INSTL_MBY());
                pstmt.setString(9,wifiApiDto.getX_SWIFI_SVC_SE());
                pstmt.setString(10,wifiApiDto.getX_SWIFI_CMCWR());
                pstmt.setString(11,wifiApiDto.getX_SWIFI_CNSTC_YEAR());
                pstmt.setString(12,wifiApiDto.getX_SWIFI_INOUT_DOOR());
                pstmt.setString(13,wifiApiDto.getX_SWIFI_REMARS3());
                pstmt.setFloat(14,wifiApiDto.getLAT());
                pstmt.setFloat(15,wifiApiDto.getLNT());
                pstmt.setString(16,wifiApiDto.getWORK_DTTM());
                pstmt.addBatch();
                pstmt.clearParameters();
            }
            pstmt.executeBatch();
            pstmt.clearBatch();

            pstmt = conn.prepareStatement("END TRANSACTION;");
            pstmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if (rs != null && !rs.isClosed()) {
                    rs.close();
                }

                if (pstmt != null && !pstmt.isClosed()) {
                    pstmt.close();
                }

                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

위 코드처럼 개선해보았지만 똑같이 느렸다..

그래서 더 깊게 찾아보고 생각해보니 어차피 저렇게 한번에 날린다 하더라도

SQLite는 모든 INSERT, UPDATE 구문을 각각 하나의 트랜잭션으로 처리하기 때문에 오래걸리는 것 같다는 결론이 났다.

 

그렇다면 해결방법을 찾아보니 하나의 트랜잭션으로 묶어보는 방법이 있었다.

개선 코드 2 - 트랜잭션 하나로 묶기(Begin Transaction, End Transaction)

    @Override
    public void saveAll() {
        ArrayList<WifiApiDto> wifiDataAll = ApiService.getWifiDataAll();
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DriverManager.getConnection("jdbc:sqlite:D:\\study\\플젝\\Find_Public_Wifi\\find_public_wifi");

            String sql = "DELETE FROM wifi;";
            pstmt = conn.prepareStatement(sql);
            pstmt.execute();

            pstmt = conn.prepareStatement("BEGIN TRANSACTION;");
            pstmt.execute();

            sql = "INSERT INTO wifi VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
            pstmt = conn.prepareStatement(sql);

            for (WifiApiDto wifiApiDto : wifiDataAll) {
                pstmt.setString(1, wifiApiDto.getX_SWIFI_MGR_NO());
                pstmt.setString(2, wifiApiDto.getX_SWIFI_WRDOFC());
                pstmt.setString(3, wifiApiDto.getX_SWIFI_MAIN_NM());
                pstmt.setString(4, wifiApiDto.getX_SWIFI_ADRES1());
                pstmt.setString(5, wifiApiDto.getX_SWIFI_ADRES2());
                pstmt.setString(6, wifiApiDto.getX_SWIFI_INSTL_FLOOR());
                pstmt.setString(7, wifiApiDto.getX_SWIFI_INSTL_TY());
                pstmt.setString(8, wifiApiDto.getX_SWIFI_INSTL_MBY());
                pstmt.setString(9, wifiApiDto.getX_SWIFI_SVC_SE());
                pstmt.setString(10, wifiApiDto.getX_SWIFI_CMCWR());
                pstmt.setString(11, wifiApiDto.getX_SWIFI_CNSTC_YEAR());
                pstmt.setString(12, wifiApiDto.getX_SWIFI_INOUT_DOOR());
                pstmt.setString(13, wifiApiDto.getX_SWIFI_REMARS3());
                pstmt.setFloat(14, wifiApiDto.getLAT());
                pstmt.setFloat(15, wifiApiDto.getLNT());
                pstmt.setString(16, wifiApiDto.getWORK_DTTM());
                pstmt.addBatch();
                pstmt.clearParameters();
            }
            pstmt.executeBatch();
            pstmt.clearBatch();

            pstmt = conn.prepareStatement("END TRANSACTION;");
            pstmt.execute();
            
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null && !pstmt.isClosed()) {
                    pstmt.close();
                }

                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

그 오래 걸리던게 그래도 속도가 540ms ~ 600ms 사이로 걸리게 되었다!

그 외에 캐시 사이즈를 늘리는 방법도 있는데

개선 코드 3 - 캐시사이즈 변경 (PRAGMA cache_size)

public class WifiRepositoryImpl implements WifiRepository {
    public static void main(String[] args) {
        WifiRepositoryImpl wifiRepository = new WifiRepositoryImpl();
        wifiRepository.saveAll();
    }

    @Override
    public void saveAll() {
        ArrayList<WifiApiDto> wifiDataAll = ApiService.getWifiDataAll();
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DriverManager.getConnection("jdbc:sqlite:D:\\study\\플젝\\Find_Public_Wifi\\find_public_wifi");


            pstmt = conn.prepareStatement("PRAGMA cache_size=10000");
            pstmt.execute();

            String sql = "DELETE FROM wifi;";
            pstmt = conn.prepareStatement(sql);
            pstmt.execute();

            long startTime = System.currentTimeMillis();
            pstmt = conn.prepareStatement("BEGIN TRANSACTION;");
            pstmt.execute();

            sql = "INSERT INTO wifi VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
            pstmt = conn.prepareStatement(sql);

            for (WifiApiDto wifiApiDto : wifiDataAll) {
                pstmt.setString(1, wifiApiDto.getX_SWIFI_MGR_NO());
                pstmt.setString(2, wifiApiDto.getX_SWIFI_WRDOFC());
                pstmt.setString(3, wifiApiDto.getX_SWIFI_MAIN_NM());
                pstmt.setString(4, wifiApiDto.getX_SWIFI_ADRES1());
                pstmt.setString(5, wifiApiDto.getX_SWIFI_ADRES2());
                pstmt.setString(6, wifiApiDto.getX_SWIFI_INSTL_FLOOR());
                pstmt.setString(7, wifiApiDto.getX_SWIFI_INSTL_TY());
                pstmt.setString(8, wifiApiDto.getX_SWIFI_INSTL_MBY());
                pstmt.setString(9, wifiApiDto.getX_SWIFI_SVC_SE());
                pstmt.setString(10, wifiApiDto.getX_SWIFI_CMCWR());
                pstmt.setString(11, wifiApiDto.getX_SWIFI_CNSTC_YEAR());
                pstmt.setString(12, wifiApiDto.getX_SWIFI_INOUT_DOOR());
                pstmt.setString(13, wifiApiDto.getX_SWIFI_REMARS3());
                pstmt.setFloat(14, wifiApiDto.getLAT());
                pstmt.setFloat(15, wifiApiDto.getLNT());
                pstmt.setString(16, wifiApiDto.getWORK_DTTM());
                pstmt.addBatch();
                pstmt.clearParameters();
            }
            pstmt.executeBatch();
            pstmt.clearBatch();

            pstmt = conn.prepareStatement("END TRANSACTION;");
            pstmt.execute();
            long endTime = System.currentTimeMillis();
            System.out.println(String.format("코드 실행 시간: %20dms", endTime - startTime));
        } catch (SQLException e) {
            e.printStackTrace();

        } finally {
            try {
                if (pstmt != null && !pstmt.isClosed()) {
                    pstmt.close();
                }

                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

살짝 더 빨라졌다. 500ms초반대로 나온다.

 

그 외에 더 빨라질 수 있는 방법이 있는지 찾아봐야겠지만 지금도 만족스럽다.

 

'JAVA > JAVA 기초' 카테고리의 다른 글

JDBC  (0) 2022.05.17
자바 8 람다를 이용한 다중 조건 정렬  (0) 2022.04.07
스트림 (Stream)  (0) 2022.03.29
람다식  (0) 2022.03.29
컬렉션 프레임워크 (Collection Framework)  (0) 2022.03.29

관련글

댓글

티스토리툴바