[C#/SQLITE/.NET5] 연결 문자열 설정을 사용해 대량 데이터 추가시 속도 향상시키기

TestProject.zip

0.00MB

▶ TestModel.cs

namespace TestProject
{
    /// <summary>
    /// 테스트 모델
    /// </summary>
    public class TestModel
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Property
        ////////////////////////////////////////////////////////////////////////////////////////// Public

        #region 컬럼 01 - COLUMN01

        /// <summary>
        /// 컬럼 01
        /// </summary>
        public string COLUMN01 { get; set; }

        #endregion
        #region 컬럼 02 - COLUMN02

        /// <summary>
        /// 컬럼 02
        /// </summary>
        public string COLUMN02 { get; set; }

        #endregion
        #region 컬럼 03 - COLUMN03

        /// <summary>
        /// 컬럼 03
        /// </summary>
        public string COLUMN03 { get; set; }

        #endregion
        #region 컬럼 04 - COLUMN04

        /// <summary>
        /// 컬럼 04
        /// </summary>
        public string COLUMN04 { get; set; }

        #endregion
        #region 컬럼 05 - COLUMN05

        /// <summary>
        /// 컬럼 05
        /// </summary>
        public string COLUMN05 { get; set; }

        #endregion
        #region 컬럼 06 - COLUMN06

        /// <summary>
        /// 컬럼 06
        /// </summary>
        public string COLUMN06 { get; set; }

        #endregion
        #region 컬럼 07 - COLUMN07

        /// <summary>
        /// 컬럼 07
        /// </summary>
        public string COLUMN07 { get; set; }

        #endregion
        #region 컬럼 08 - COLUMN08

        /// <summary>
        /// 컬럼 08
        /// </summary>
        public string COLUMN08 { get; set; }

        #endregion
        #region 컬럼 09 - COLUMN09

        /// <summary>
        /// 컬럼 09
        /// </summary>
        public string COLUMN09 { get; set; }

        #endregion
        #region 컬럼 10 - COLUMN10

        /// <summary>
        /// 컬럼 10
        /// </summary>
        public string COLUMN10 { get; set; }

        #endregion
    }
}

 

▶ RandomStringHelper.cs

using System;
using System.Text;

namespace TestProject
{
    /// <summary>
    /// 임의 문자열 헬퍼
    /// </summary>
    public static class RandomStringHelper
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Field
        ////////////////////////////////////////////////////////////////////////////////////////// Static
        //////////////////////////////////////////////////////////////////////////////// Public

        #region Field

        /// <summary>
        /// 숫자만
        /// </summary>
        public static readonly string OnlyDigit = "9";

        /// <summary>
        /// 소문자만
        /// </summary>
        public static readonly string OnlyLowerCharacter = "z";

        /// <summary>
        /// 대문자만
        /// </summary>
        public static readonly string OnlyUpperCharacter = "Z";

        /// <summary>
        /// 숫자와 대문자
        /// </summary>
        public static readonly string DigitANdUpperCharacter = "9Z";

        /// <summary>
        /// 숫자와 소문자
        /// </summary>
        public static readonly string DigitAndLowerCharacter = "9z";

        /// <summary>
        /// 모든 문자
        /// </summary>
        public static readonly string AllCharacter = "9Zz";

        #endregion

        //////////////////////////////////////////////////////////////////////////////////////////////////// Method
        ////////////////////////////////////////////////////////////////////////////////////////// Static
        //////////////////////////////////////////////////////////////////////////////// Public

        #region 임의 문자열 구하기 - GetRandomString(length, randomType)

        /// <summary>
        /// 임의 문자열 구하기
        /// </summary>
        /// <param name="length">길이</param>
        /// <param name="randomType">임의 타입</param>
        /// <returns>임의 문자열</returns>
        public static string GetRandomString(int length, string randomType = "9")
        {
            StringBuilder result = new StringBuilder();

            int digitStart          = 48;
            int digitEnd            = 57;
            int upperCharacterStart = 65;
            int upperCharacterEnd   = 90;
            int lowerCharacterStart = 97;
            int lowerCharacterEnd   = 122;
            
            int start = 0;
            int end   = 0;

            Random random = new Random(DateTime.Now.Millisecond);

            for(int i = 1; i <= length; i++)
            {
                string nextRandomType = randomType;

                if(randomType.Length >= 2)
                {
                    Random _ = new Random(DateTime.Now.Millisecond);

                    int next = 1;

                    switch(randomType.Length)
                    {
                        case 2 :

                            next = random.Next(1, 3);

                            if(next == 2)
                            {
                                if(randomType == "9Z")
                                {
                                    next = 3;
                                }
                                else if(randomType == "9z")
                                {
                                    next = 2;
                                }
                            }

                            break;

                        case 3 :

                            next = random.Next(1, 4);

                            break;
                    }

                    switch(next)
                    {
                        case 1 : nextRandomType = "9"; break;
                        case 2 : nextRandomType = "z"; break;
                        case 3 : nextRandomType = "Z"; break;
                    }
                }

                switch(nextRandomType)
                {
                    case "Z" : start = upperCharacterStart; end = upperCharacterEnd; break;
                    case "z" : start = lowerCharacterStart; end = lowerCharacterEnd; break;
                    case "9" : start = digitStart;          end = digitEnd;          break;
                }

                result.Append(char.ConvertFromUtf32(random.Next(start, end + 1)));
            }

            return result.ToString();
        }

        #endregion
    }
}

 

▶ Program.cs

using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Diagnostics;
using System.IO;

using Dapper;

namespace TestProject
{
    /// <summary>
    /// 프로그램
    /// </summary>
    class Program
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Field
        ////////////////////////////////////////////////////////////////////////////////////////// Static
        //////////////////////////////////////////////////////////////////////////////// Private

        #region Field

        /// <summary>
        /// 테이블 생성 SQL
        /// </summary>
        private static string _createTableSQL = @"
CREATE TABLE test
(
    COLUMN01 TEXT
   ,COLUMN02 TEXT
   ,COLUMN03 TEXT
   ,COLUMN04 TEXT
   ,COLUMN05 TEXT
   ,COLUMN06 TEXT
   ,COLUMN07 TEXT
   ,COLUMN08 TEXT
   ,COLUMN09 TEXT
   ,COLUMN10 TEXT
);
";

        /// <summary>
        /// 삽입 SQL
        /// </summary>
        private static string _insertSQL = @"
INSERT INTO Test
(
    COLUMN01
   ,COLUMN02
   ,COLUMN03
   ,COLUMN04
   ,COLUMN05
   ,COLUMN06
   ,COLUMN07
   ,COLUMN08
   ,COLUMN09
   ,COLUMN10
)
VALUES
(
    @COLUMN01
   ,@COLUMN02
   ,@COLUMN03
   ,@COLUMN04
   ,@COLUMN05
   ,@COLUMN06
   ,@COLUMN07
   ,@COLUMN08
   ,@COLUMN09
   ,@COLUMN10
)
";

        #endregion

        //////////////////////////////////////////////////////////////////////////////////////////////////// Method
        ////////////////////////////////////////////////////////////////////////////////////////// Static
        //////////////////////////////////////////////////////////////////////////////// Private

        #region 로그 쓰기 - WriteLog(source)

        /// <summary>
        /// 로그 쓰기
        /// </summary>
        /// <param name="source">소스 문자열</param>
        private static void WriteLog(string source)
        {
            Console.WriteLine($"[{DateTime.Now:HH:mm:ss}] {source}");
        }

        #endregion
        #region 프로그램 시작하기 - Main()

        /// <summary>
        /// 프로그램 시작하기
        /// </summary>
        private static void Main()
        {
            string filePath = "test.db3";

            if(File.Exists(filePath))
            {
                File.SetAttributes(filePath, FileAttributes.Normal);

                File.Delete(filePath);
            }

            WriteLog("추가할 데이터 생성을 시작합니다.");

            List<TestModel> sourceList = new List<TestModel>();

            for(int i = 0; i < 100_000; i++)
            {
                TestModel test = new()
                {
                    COLUMN01 = RandomStringHelper.GetRandomString(10, RandomStringHelper.AllCharacter),
                    COLUMN02 = RandomStringHelper.GetRandomString(10, RandomStringHelper.AllCharacter),
                    COLUMN03 = RandomStringHelper.GetRandomString(10, RandomStringHelper.AllCharacter),
                    COLUMN04 = RandomStringHelper.GetRandomString(10, RandomStringHelper.AllCharacter),
                    COLUMN05 = RandomStringHelper.GetRandomString(10, RandomStringHelper.AllCharacter),
                    COLUMN06 = RandomStringHelper.GetRandomString(10, RandomStringHelper.AllCharacter),
                    COLUMN07 = RandomStringHelper.GetRandomString(10, RandomStringHelper.AllCharacter),
                    COLUMN08 = RandomStringHelper.GetRandomString(10, RandomStringHelper.AllCharacter),
                    COLUMN09 = RandomStringHelper.GetRandomString(10, RandomStringHelper.AllCharacter),
                    COLUMN10 = RandomStringHelper.GetRandomString(10, RandomStringHelper.AllCharacter)
                };

                sourceList.Add(test);
            }

            WriteLog("추가할 데이터 생성을 종료합니다.");

            string connectionString = $"Data Source={filePath};Version=3;Synchronous=OFF;Journal Mode=OFF;";

            using(SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Execute(_createTableSQL);

                WriteLog($"데이터 추가를 시작합니다 : {sourceList.Count:#,##0}건");

                Stopwatch watch = new Stopwatch();

                watch.Start();

                connection.Execute(_insertSQL, sourceList);

                watch.Stop();

                WriteLog("데이터 추가를 종료합니다.");

                WriteLog($"데이터 추가 경과 시간 : {watch.Elapsed}");
            }
        }

        #endregion
    }
}