[C# - MariaDB] Multi line Insert 함수

DB에 data를 insert할 때, 한번에 insert 해야하는 개수가 적다면 하나씩 하나씩 insert 구문을 작성 후 "ExecuteNonQuery" 메소드를 실행하면 되었다. 

 

그러나 개수가 점점 많아지면 많아질 수록 동작시간이 기하급수적으로 늘었고 주기적으로 Query문을 실행 하는 Timer의 주기 동안에도 Insert 구문이 다 실행되지 않아 DB Connection이 끊기는 등의 오류가 발생하였다. 

해당 문제를 해결하기 위해 BulkInsert를 검색하여 찾아보았지만 DataTable로 구현해놓지 않은 과거의 나에게 발목이 잡혀 BulkInsert는 뛰어넘고 다른 방법을 찾던중, Heidi SQL이라는 프로그램에서 "데이터베이스에서 SQL로 내보내기" 기능을 사용하면 아래의 사진과 같이 한 개의 INSERT 구문에 여러개의 values를 중첩해놓은 것을 확인하였다. 

Heidi SQL의 "데이터베이스에서 SQL로 내보내기"를 하여 확인한 SQL 구문이다

이거다 싶어서 해당 스킬을 사용하기 위해 함수를 직접 만들어 보았고 실제로 사용해보았다.
그리하여 해당 스킬을 사용하기 위해 직접 만든 함수와 테스트 내용을 남겨보고자 한다.

 

테스트를 위해 임시의 로컬 DB를 Maria DB로 구축하여 테스트 하였다.  

여러 테이블에 insert하는 테스트 환경을 만들기 위해 
user_information 과 number_book이라는 테이블을 아래와 같이 구성하였다.

user_information 테이블

number_book 테이블

이후 데이터를 insert 하는 코드를 작성하였다. (DatabaseManager의 클래스는 본 게시글 마지막에 작성해놓겠습니다)

static void Main(string[] args)
{
    DatabaseManager dbManager = new DatabaseManager("localhost", "3306", "test", "root", "123456789");
    Console.WriteLine("databaseManager.ConnectionCheck() :" + dbManager.ConnectionCheck());

    List<string> listStr_Query = new List<string>();

    Random rand = new Random();

    for (int i = 0; i < 500; i++)
    {
        listStr_Query.Add($"INSERT INTO user_information(ID,NAME,PHONE_NUM) " +
            $"VALUES('{rand.Next(50000)}',{i},'010-{rand.Next(9999).ToString().PadLeft(4,'0')}-{rand.Next(9999).ToString().PadLeft(4, '0')}')");

        listStr_Query.Add($"INSERT INTO number_book(PHONE_NUM,CHECK_NUM) " +
            $"VALUES('010-{rand.Next(9999).ToString().PadLeft(4, '0')}-{rand.Next(9999).ToString().PadLeft(4, '0')}','{rand.Next(5000)}')");
    }
    dbManager.executeBatchInsert(listStr_Query);

}

위의 코드를 실행하여 아래의 사진과 같이 결과가 나왔다. 1000개의 row를 insert 하기 위해서 16267msec 즉, 16초가 걸렸다. 너무 오래걸렸다...

1000개의 row를 insert 하기 위해 16초가 걸렸다..

이를 해결 하고자 multi line으로 value들을 insert 하는 함수를 작성하였다.

 

public static List<string> ClassifyInsertQuery(List<string> listStr_Query, string[] arr_dbTableLineUp)
{

    StringBuilder[] stringBuilders = new StringBuilder[arr_dbTableLineUp.Length];
    List<string> rs = new List<string>();

    //쿼리문 마다 StringBuilder를 이용해 INSERT 구문을 만들어 놓기
    for (int i = 0; i < arr_dbTableLineUp.Length; i++)
    {
        stringBuilders[i] = new StringBuilder();
        stringBuilders[i].Append(MatchQuery(i, arr_dbTableLineUp));
    }

    // 인자로 넘겨받은 쿼리문 리스트를 하나씩 확인
    // INSERT 구문의 마지막 '(' 괄호 (= VALUES 바로 뒤의 괄호)를 찾아 그 뒤부터 끝까지 문자열 잘라내기
    for (int i = 0; i < listStr_Query.Count; i++)
    {
        for (int j = 0; j < arr_dbTableLineUp.Length; j++)
        {
            if (listStr_Query[i].Contains(arr_dbTableLineUp[j])) 
            {
                stringBuilders[j].Append($"\n{listStr_Query[i].Substring(listStr_Query[i].LastIndexOf('('))},");

                break;
            }
        }
    }

    //Multi line으로 구성한 INSERT문의 맨마지막의 세미콜론으로 변경
    for (int i = 0; i < arr_dbTableLineUp.Length; i++)
    {

        if (stringBuilders[i][stringBuilders[i].Length - 1] == ',')
        {
            stringBuilders[i][stringBuilders[i].Length - 1] = ';';
            rs.Add(stringBuilders[i].ToString());
        }

    }

    Console.WriteLine("END ClassifyInsertLogQuery");
    return rs;

}

static string MatchQuery(int idx, string[] arr_dbTableLineUp)
{
    string query = "";

    if (arr_dbTableLineUp[idx].Equals("user_information"))
    {
        query = $"INSERT INTO {arr_dbTableLineUp[idx]}(ID,NAME,PHONE_NUM) VALUES";
    }
    else if (arr_dbTableLineUp[idx].Equals("number_book"))
    {
        query = $"INSERT INTO {arr_dbTableLineUp[idx]}(PHONE_NUM,CHECK_NUM) VALUES";
    }          
    return query;
}

위의 함수를 사용하기 위해서는 사용할 Query문을 모두 "MatchQuery()" 함수에 기재해야한다.

위 함수를 사용하기 위한 코드와  테스트 결과는 아래와 같다.

static void Main(string[] args)
{
    DatabaseManager dbManager = new DatabaseManager("localhost", "3306", "test", "root", "123456789");
    Console.WriteLine("databaseManager.ConnectionCheck() :" + dbManager.ConnectionCheck());

    List<string> listStr_Query = new List<string>();

    Random rand = new Random();

    for (int i = 0; i < 500; i++)
    {
        listStr_Query.Add($"INSERT INTO user_information(ID,NAME,PHONE_NUM) " +
            $"VALUES('{rand.Next(50000)}',{i},'010-{rand.Next(9999).ToString().PadLeft(4,'0')}-{rand.Next(9999).ToString().PadLeft(4, '0')}')");

        listStr_Query.Add($"INSERT INTO number_book(PHONE_NUM,CHECK_NUM) " +
            $"VALUES('010-{rand.Next(9999).ToString().PadLeft(4, '0')}-{rand.Next(9999).ToString().PadLeft(4, '0')}','{rand.Next(5000)}')");
    }


    //쿼리문을 구분하기 위해 테이블명 라인업
    string[] arr_dbTableLineUp = new string[] { "user_information", "number_book" };

    List<string> listStr_ClassifiedQuery = ClassifyInsertQuery(listStr_Query, arr_dbTableLineUp);

    dbManager.executeBatchInsert(listStr_ClassifiedQuery);
}

무려 637msec로 0.6초이다. 16276msec → 637msec로 무려 25.5배의 시간을 단축하였다

2개의 row 를 637msec에 insert 하였다.

row가 2개가 된 이유는 각각의 테이블에 하나씩 INSERT 구문이 실행되었기 때문이다.

아래의 사진은 "listStr_ClassifiedQuery"를 Console.WriteLine을 통해 확인한 사진이다. 

Heidi SQL에서 얻어낸 SQL 구문과 동일함을 확인할 수 있고 데이터도 잘 들어갔음을 확인 할 수 있다.

 

해당 함수를 만들면서 불편했던 점은 테이블이 많아지면 테이블 명과 테이블마다의 INSERT 구문을 "MatchQuery()" 함수에 다 기재해야한다는 것인데 이부분은 차후에 Maria DB의 information_schema에서 불러올수 있는지 확인하고 추가하면 될 듯하다.

 

database에 지식이 거의 전무하다시피 한데 실무에서 해당 기술을 바로 적용해야했으므로 맨땅에 헤딩을하며 만들어낸 결과이다. 25배라는 상상치도 못한 결과를 냈으므로 아주 뿌듯했다.

차후에 database 공부를 하여 trasaction, index와 같은 스킬을 배우면 더 나아질까 싶기도하다. 

 

 

---

** 참고 : DatabaseManager Class (Visual Studio -  Nuget 패키지 관리자 - Mysql.data 설치 필요)

public class DatabaseManager
{
    string ServerName;
    string PortNum;
    string DataBase;
    string userid;
    string userpw;

    string connection_string = "";

    public DatabaseManager(string _ServerName, string _PortNum, string _DataBase, string _Uid, string _Pwd)
    {
        ServerName = _ServerName;
        PortNum = _PortNum;
        DataBase = _DataBase;
        userid = _Uid;
        userpw = _Pwd;
        this.connection_string = "Server=" + ServerName + ";Port=" + PortNum + ";Database=" + DataBase + ";Uid=" + userid + ";Pwd=" + userpw;
    }

    public bool ConnectionCheck()
    {
        bool result = false;

        using (MySqlConnection conn = new MySqlConnection(this.connection_string))
        {
            conn.Open();
            result = conn.Ping();
        }
        return result;
    }

    public int executeNonQuery(string query)
    {
        using (MySqlConnection conn = new MySqlConnection(this.connection_string))
        {
            conn.Open();
            var cmd = new MySqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = query;
            int rs = cmd.ExecuteNonQuery();
            return rs;
        }
    }

    public void executeBatchInsert(List<string> queryList)
    {
        if (queryList.Count != 0)
        {
            using (MySqlConnection conn = new MySqlConnection(this.connection_string))
            {
                Stopwatch sw = new Stopwatch();


                conn.Open();
                Console.WriteLine($" {DateTime.Now.ToString()} [IN]  executeBatchInsert cnt :  {queryList.Count}");
                int cnt = 0;
                try
                {

                    sw.Restart();
                    for (int i = 0; i < queryList.Count; i++)
                    {
                        //Console.WriteLine(query_str);
                        MySqlCommand cmd = conn.CreateCommand();
                        cmd.Connection = conn;
                        cmd.CommandText = queryList[i];
                        cmd.ExecuteNonQuery();

                        Thread.Sleep(1);
                        cnt++;
                    }


                    sw.Stop();
                    Console.WriteLine($" Time : {sw.ElapsedMilliseconds} executeBatchInsert  cnt : {cnt}");
                }
                catch (Exception ex)
                {
                    Console.WriteLine("MySQL error : " + ex.Message);
                }

            }
        }

    }

}