Update 쿼리 시 아래와 같은 에러가 발생되었다.

System.Data.SqlClient.SqlException: 'The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Warning: Null value is eliminated by an aggregate or other SET operation.
The statement has been terminated.'

찾아보니 Select 시 SUM 등을 할때 null 인 항목이 있는 경우 에러가 도출된다고 하는데

SET ANSI_WARNINGS OFF

GO


위 명령을 통해 오류는 도출되지 않도록 할수 있다고한다.


하지만 내가 겪은건 희한하게 다른 컬럼을 처리하면 상관없는데

유독 한 컬럼만 문제가 있었다.

컬럼명이 예약어와 동일해서 문제가 생기는줄 알고 열심히 백틱, 더블쿼테이션, 대괄호 처리 를 했지만 오류가 계속 도출..;;


결론은 
해당 컬럼에 Trigger 가 걸려있었고 그 Trigger 에서 발생되는 에러였다.




SSMS 실행 (Microsoft SQL Server Management Studio)

1. 윈도우 인증으로 접속


2. 개체 탐색기에서 데이터베이스 선택 후 우클릭 메뉴에서 속성선택


3. 보안탭에서 서버인증의 SQL Server 및 Windows 인증 모드로 변경


4. 보안 > 로그인 > sa 우클릭 메뉴에서 속성


암호 변경


5. 데이터 베이스 다시 시작


SQL Server 2016 구성 관리자 실행( SQL Server 2016 Configuration Manager )

좌측 메뉴에서 아래 화면과 같이 선택한 후 우측의 TCP/IP 를 사용 으로 변경



두 테이블 간 조인을 할때 NOT IN 조건으로 JOIN 을 할 경우 처리하는 방법이다.

아래 예제 에서는 B 테이블의 value 컬럼의 내용을 NOT IN 조건으로 처리한다.

1. 

SELECT  A.*
FROM    A LEFT JOIN B ON A.value = B.value
WHERE   B.value IS NULL


2.

SELECT  A.*
FROM    A
WHERE   value NOT IN
(
SELECT  value
FROM    B
)

3.

SELECT  A.*
FROM    A
WHERE   NOT EXISTS
(
SELECT  value
FROM    B
WHERE   B.value = A.value
)


value 컬럼이 인덱스처리가 된 경우 성능차이는 없다고 한다.

그러나 value 컬럼이 NOT NULL임을 보장하지 않는 경우 하위 쿼리 결과 집합에 NULL 값이 있는지 여부에 따라 

다른 결과가 생성되므로 NOT IN 대신 LEFT JOIN / IS NULL 또는 NOT EXISTS를 사용해야한다.

참고

https://explainextended.com/2009/09/17/not-in-vs-not-exists-vs-left-join-is-null-oracle/

TEST 테이블의 A 컬럼에 아래와 같이 데이터가 있을 때

A


A 컬럼의 데이터를 모두 ,(콤마) 로 연결하여 하나의 데이터("가,나,다")로 나타내고 싶은 경우

아래 처럼 listagg 를 이용해 쿼리하면 된다.


SELECT LISTAGG(A,' ,') RESULT FROM TEST


결과>

RESULT

가,나,다



select trim(regexp_substr('a,b,c','[^,]+',1,LEVEL)) item_id,

trim(regexp_substr('1,2,3','[^,]+',1,LEVEL)) item_value

from dual

connect by level < 4


결과>

 item_id

 item_value

 a

 1

 b

 2

 c

 3



https://www.tutorialspoint.com/sqlite/index.htm

사용법과 설명등이 잘되어있다.

 

 

SQLite Bulk Insert In C#/.NET

Recently, I had a project where I needed to load 1 million+ records into a SQLite database. I downloaded the SQLite ADO.NET adapter and setup the Entity framework to map to my SQLite database. All was simple and all was well!

I started inserting the data into my database; lo and behold, it was taking forever! In fact, it took most of a full working day to insert my data. I knew something wasn't right. A simple Google search pointed me to the SQLite FAQ. It turns out that SQLite wraps every INSERT into a transaction. Simple solution: start a transaction and perform multiple INSERTs. I needed my inserts to be fast, so I just had to write a class to encapsulate this.

It's very simple to use. Make sure that your SQLite database file has been created and your table has been created as well.

Let's assume your database schema looks like the following: Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, LastName VARCHAR(16) NOT NULL, Height REAL NOT NULL

You would then use SQLiteBulkInsert as follows:

SQLiteBulkInsert sbi = new SQLiteBulkInsert(yourDatabaseConnectionObject, "yourTableName");
sbi.AddParameter("LastName", DbType.String);
sbi.AddParameter("Height", DbType.Single);

You can then insert records:

for (int x = 0; x &lt; 10000; x++)
    sbi.Insert(new object[]{someString, someFloat});
sbi.Flush();

That's it! You should look at the file SQLiteBulkInsertTest.cs for more details. SQLiteBulkInsert.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;

namespace YourProject.Data.SQLite
{
    public class SQLiteBulkInsert
    {
        private SQLiteConnection m_dbCon;
        private SQLiteCommand m_cmd;
        private SQLiteTransaction m_trans;

        private Dictionary m_parameters = new Dictionary();

        private uint m_counter = 0;

        private string m_beginInsertText;

        public SQLiteBulkInsert(SQLiteConnection dbConnection, string tableName) {
            m_dbCon = dbConnection;
            m_tableName = tableName;

            StringBuilder query = new StringBuilder(255);
            query.Append("INSERT INTO ["); query.Append(tableName); query.Append("] (");
            m_beginInsertText = query.ToString();
        }

        private bool m_allowBulkInsert = true;
        public bool AllowBulkInsert { get { return m_allowBulkInsert; } set { m_allowBulkInsert = value; } }

        public string CommandText {
            get {
                if (m_parameters.Count &lt; 1)
                    throw new SQLiteException("You must add at least one parameter.");

                StringBuilder sb = new StringBuilder(255);
                sb.Append(m_beginInsertText);

                foreach (string param in m_parameters.Keys) {
                    sb.Append('[');
                    sb.Append(param);
                    sb.Append(']');
                    sb.Append(", ");
                }
                sb.Remove(sb.Length - 2, 2);

                sb.Append(") VALUES (");

                foreach (string param in m_parameters.Keys) {
                    sb.Append(m_paramDelim);
                    sb.Append(param);
                    sb.Append(", ");
                }
                sb.Remove(sb.Length - 2, 2);

                sb.Append(")");

                return sb.ToString();
            }
        }

        private uint m_commitMax = 10000;
        public uint CommitMax { get { return m_commitMax; } set { m_commitMax = value; } }

        private string m_tableName;
        public string TableName { get { return m_tableName; } }

        private string m_paramDelim = ":";
        public string ParamDelimiter { get { return m_paramDelim; } }

        public void AddParameter(string name, DbType dbType) {
            SQLiteParameter param = new SQLiteParameter(m_paramDelim + name, dbType);
            m_parameters.Add(name, param);
        }

        public void Flush() {
            try {
                if (m_trans != null)
                    m_trans.Commit();
            }
            catch (Exception ex) { throw new Exception("Could not commit transaction. See InnerException for more details", ex); }
            finally {
                if (m_trans != null)
                    m_trans.Dispose();

                m_trans = null;
                m_counter = 0;
            }
        }

        public void Insert(object[] paramValues) {
            if (paramValues.Length != m_parameters.Count)
                throw new Exception("The values array count must be equal to the count of the number of parameters.");

            m_counter++;

            if (m_counter == 1) {
                if (m_allowBulkInsert)
                    m_trans = m_dbCon.BeginTransaction();

                m_cmd = m_dbCon.CreateCommand();
                foreach (SQLiteParameter par in m_parameters.Values)
                    m_cmd.Parameters.Add(par);

                m_cmd.CommandText = this.CommandText;
            }

            int i = 0;
            foreach (SQLiteParameter par in m_parameters.Values) {
                par.Value = paramValues[i];
                i++;
            }

            m_cmd.ExecuteNonQuery();

            if (m_counter == m_commitMax) {
                try {
                    if (m_trans != null)
                        m_trans.Commit();
                }
                catch (Exception ex) { }
                finally {
                    if (m_trans != null) {
                        m_trans.Dispose();
                        m_trans = null;
                    }

                    m_counter = 0;
                }
            }
        }
    }
}

SQLiteBulkInsertTest.cs:

using YourProject.Data.SQLite;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Data.SQLite;
using System.Data;
using System.IO;
using System;

namespace TestYourProject
{
    /// 
    ///This is a test class for SQLiteBulkInsertTest and is intended
    ///to contain all SQLiteBulkInsertTest Unit Tests
    ///
    [TestClass()]
    public class SQLiteBulkInsertTest
    {
        private static string m_testDir;
        private static string m_testFile;
        private static string m_testTableName = "test_table";
        private static string m_connectionString;

        private static string m_deleteAllQuery = "DELETE FROM [{0}]";
        private static string m_countAllQuery = "SELECT COUNT(id) FROM [{0}]";
        private static string m_selectAllQuery = "SELECT * FROM [{0}]";

        private static SQLiteConnection m_dbCon;
        private static SQLiteCommand m_deleteAllCmd;
        private static SQLiteCommand m_countAllCmd;
        private static SQLiteCommand m_selectAllCmd;

        private TestContext testContextInstance;

        /// 
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///
        public TestContext TestContext {
            get {
                return testContextInstance;
            }
            set {
                testContextInstance = value;
            }
        }

        #region Additional test attributes
        //
        //You can use the following additional attributes as you write your tests:
        //
        //Use ClassInitialize to run code before running the first test in the class
        [ClassInitialize()]
        public static void MyClassInitialize(TestContext testContext){
            Random rand = new Random(Environment.TickCount);
            int rn = rand.Next(0, int.MaxValue);
            m_testDir = @"C:\SqliteBulkInsertTest-" + rn + @"\";
            m_testFile = m_testDir + "db.sqlite";

            if (!Directory.Exists(m_testDir))
                Directory.CreateDirectory(m_testDir);

            if (!File.Exists(m_testFile)) {
                FileStream fs = File.Create(m_testFile);
                fs.Close();
            }

            m_connectionString = string.Format(@"data source={0};datetimeformat=Ticks", m_testFile);
            m_dbCon = new SQLiteConnection(m_connectionString);
            m_dbCon.Open();

            SQLiteCommand cmd = m_dbCon.CreateCommand();
            string query = "CREATE TABLE IF NOT EXISTS [{0}] (id INTEGER PRIMARY KEY AUTOINCREMENT, somestring VARCHAR(16), somereal REAL, someint INTEGER(4), somedt DATETIME)";
            query = string.Format(query, m_testTableName);
            cmd.CommandText = query;
            cmd.ExecuteNonQuery();
        }
        //
        //Use ClassCleanup to run code after all tests in a class have run
        [ClassCleanup()]
        public static void MyClassCleanup(){
            m_dbCon.Close();

            File.Delete(m_testFile);
            Directory.Delete(m_testDir);
        }
        #endregion

        private void AddParameters(SQLiteBulkInsert target) {
            target.AddParameter("somestring", DbType.String);
            target.AddParameter("somereal", DbType.String);
            target.AddParameter("someint", DbType.Int32);
            target.AddParameter("somedt", DbType.DateTime);
        }

        private long CountRecords() {
            m_countAllCmd = m_dbCon.CreateCommand();
            m_countAllCmd.CommandText = string.Format(m_countAllQuery, m_testTableName);

            long ret = (long)m_countAllCmd.ExecuteScalar();
            m_countAllCmd.Dispose();

            return ret;
        }

        private void DeleteRecords() {
            m_deleteAllCmd = m_dbCon.CreateCommand();
            m_deleteAllCmd.CommandText = string.Format(m_deleteAllQuery, m_testTableName);

            m_deleteAllCmd.ExecuteNonQuery();
            m_deleteAllCmd.Dispose();
        }

        private SQLiteDataReader SelectAllRecords() {
            m_selectAllCmd = m_dbCon.CreateCommand();
            m_selectAllCmd.CommandText = string.Format(m_selectAllQuery, m_testTableName);
            return m_selectAllCmd.ExecuteReader();
        }

        [TestMethod()]
        public void AddParameterTest() {
            SQLiteBulkInsert target = new SQLiteBulkInsert(m_dbCon, m_testTableName);
            AddParameters(target);

            string pd = target.ParamDelimiter;
            string expectedStmnt = "INSERT INTO [{0}] ([somestring], [somereal], [someint], [somedt]) VALUES ({1}somestring, {2}somereal, {3}someint, {4}somedt)";
            expectedStmnt = string.Format(expectedStmnt, m_testTableName, pd, pd, pd, pd);
            Assert.AreEqual(expectedStmnt, target.CommandText);
        }

        [TestMethod()]
        public void SQLiteBulkInsertConstructorTest() {
            SQLiteBulkInsert target = new SQLiteBulkInsert(m_dbCon, m_testTableName);
            Assert.AreEqual(m_testTableName, target.TableName);

            bool wasException = false;
            try {
                string a = target.CommandText;
            }
            catch (SQLiteException ex) { wasException = true; }

            Assert.IsTrue(wasException);
        }

        [TestMethod()]
        public void CommandTextTest() {
            SQLiteBulkInsert target = new SQLiteBulkInsert(m_dbCon, m_testTableName);
            AddParameters(target);

            string pd = target.ParamDelimiter;
            string expectedStmnt = "INSERT INTO [{0}] ([somestring], [somereal], [someint], [somedt]) VALUES ({1}somestring, {2}somereal, {3}someint, {4}somedt)";
            expectedStmnt = string.Format(expectedStmnt, m_testTableName, pd, pd, pd, pd);
            Assert.AreEqual(expectedStmnt, target.CommandText);
        }

        [TestMethod()]
        public void TableNameTest() {
            SQLiteBulkInsert target = new SQLiteBulkInsert(m_dbCon, m_testTableName);
            Assert.AreEqual(m_testTableName, target.TableName);
        }

        [TestMethod()]
        public void InsertTest() {
            SQLiteBulkInsert target = new SQLiteBulkInsert(m_dbCon, m_testTableName);

            bool didThrow = false;
            try {
                target.Insert(new object[] { "hello" }); //object.length must equal the number of parameters added
            }
            catch (Exception ex) { didThrow = true; }
            Assert.IsTrue(didThrow);

            AddParameters(target);

            target.CommitMax = 4;
            DateTime dt1 = DateTime.Now; DateTime dt2 = DateTime.Now; DateTime dt3 = DateTime.Now; DateTime dt4 = DateTime.Now;
            target.Insert(new object[] { "john", 3.45f, 10, dt1 });
            target.Insert(new object[] { "paul", -0.34f, 100, dt2 });
            target.Insert(new object[] { "ringo", 1000.98f, 1000, dt3 });
            target.Insert(new object[] { "george", 5.0f, 10000, dt4 });

            long count = CountRecords();
            Assert.AreEqual(4, count);

            SQLiteDataReader reader = SelectAllRecords();

            Assert.IsTrue(reader.Read());
            Assert.AreEqual("john", reader.GetString(1)); Assert.AreEqual(3.45f, reader.GetFloat(2));
            Assert.AreEqual(10, reader.GetInt32(3)); Assert.AreEqual(dt1, reader.GetDateTime(4));

            Assert.IsTrue(reader.Read());
            Assert.AreEqual("paul", reader.GetString(1)); Assert.AreEqual(-0.34f, reader.GetFloat(2));
            Assert.AreEqual(100, reader.GetInt32(3)); Assert.AreEqual(dt2, reader.GetDateTime(4));

            Assert.IsTrue(reader.Read());
            Assert.AreEqual("ringo", reader.GetString(1)); Assert.AreEqual(1000.98f, reader.GetFloat(2));
            Assert.AreEqual(1000, reader.GetInt32(3)); Assert.AreEqual(dt3, reader.GetDateTime(4));

            Assert.IsTrue(reader.Read());
            Assert.AreEqual("george", reader.GetString(1)); Assert.AreEqual(5.0f, reader.GetFloat(2));
            Assert.AreEqual(10000, reader.GetInt32(3)); Assert.AreEqual(dt4, reader.GetDateTime(4));

            Assert.IsFalse(reader.Read());

            DeleteRecords();

            count = CountRecords();
            Assert.AreEqual(0, count);
        }

        [TestMethod()]
        public void FlushTest() {
            string[] names = new string[] { "metalica", "beatles", "coldplay", "tiesto", "t-pain", "blink 182", "plain white ts", "staind", "pink floyd" };
            Random rand = new Random(Environment.TickCount);

            SQLiteBulkInsert target = new SQLiteBulkInsert(m_dbCon, m_testTableName);
            AddParameters(target);

            target.CommitMax = 1000;

            //Insert less records than commitmax
            for (int x = 0; x &lt; 50; x++)
                target.Insert(new object[] { names[rand.Next(names.Length)], (float)rand.NextDouble(), rand.Next(50), DateTime.Now });

            //Close connect to verify records were not inserted
            m_dbCon.Close();

            m_dbCon = new SQLiteConnection(m_connectionString);
            m_dbCon.Open();

            long count = CountRecords();
            Assert.AreEqual(0, count);

            //Now actually verify flush worked
            target = new SQLiteBulkInsert(m_dbCon, m_testTableName);
            AddParameters(target);

            target.CommitMax = 1000;

            //Insert less records than commitmax
            for (int x = 0; x &lt; 50; x++)
                target.Insert(new object[] { names[rand.Next(names.Length)], (float)rand.NextDouble(), rand.Next(50), DateTime.Now });

            target.Flush();

            count = CountRecords();
            Assert.AreEqual(50, count);

            //Close connect to verify flush worked
            m_dbCon.Close();

            m_dbCon = new SQLiteConnection(m_connectionString);
            m_dbCon.Open();

            count = CountRecords();
            Assert.AreEqual(50, count);

            DeleteRecords();
            count = CountRecords();
            Assert.AreEqual(0, count);
        }

        [TestMethod()]
        public void CommitMaxTest() {
            SQLiteBulkInsert target = new SQLiteBulkInsert(m_dbCon, m_testTableName);

            target.CommitMax = 4;
            Assert.AreEqual(4, target.CommitMax);

            target.CommitMax = 1000;
            Assert.AreEqual(1000, target.CommitMax);
        }

        //SPEED TEST
        [TestMethod()]
        public void AllowBulkInsertTest() {
            string[] names = new string[] { "metalica", "beatles", "coldplay", "tiesto", "t-pain", "blink 182", "plain white ts", "staind", "pink floyd"};
            Random rand = new Random(Environment.TickCount);

            SQLiteBulkInsert target = new SQLiteBulkInsert(m_dbCon, m_testTableName);
            AddParameters(target);

            const int COUNT = 100;

            target.CommitMax = COUNT;

            DateTime start1 = DateTime.Now;
            for (int x = 0; x &lt; COUNT; x++)
                target.Insert(new object[] { names[rand.Next(names.Length)], (float)rand.NextDouble(), rand.Next(COUNT), DateTime.Now });

            DateTime end1 = DateTime.Now;
            TimeSpan delta1 = end1 - start1;

            DeleteRecords();

            target.AllowBulkInsert = false;
            DateTime start2 = DateTime.Now;
            for (int x = 0; x &lt; COUNT; x++)
                target.Insert(new object[] { names[rand.Next(names.Length)], (float)rand.NextDouble(), rand.Next(COUNT), DateTime.Now });

            DateTime end2 = DateTime.Now;
            TimeSpan delta2 = end2 - start2;

            //THIS MAY FAIL DEPENDING UPON THE MACHINE THE TEST IS RUNNING ON.
            Assert.IsTrue(delta1.TotalSeconds &lt; 0.1); //approx true for 100 recs             Assert.IsTrue(delta2.TotalSeconds &gt; 1.0); //approx true for 100 recs;

            //UNCOMMENT THIS TO MAKE IT FAIL AND SEE ACTUAL NUMBERS IN FAILED REPORT
            //Assert.AreEqual(delta1.TotalSeconds, delta2.TotalSeconds);

            DeleteRecords();
        }
    }
}

 출처 : http://procbits.com/2009/09/08/sqlite-bulk-insert

'DB' 카테고리의 다른 글

[Oracle] 오라클 문자열 잘라내기  (0) 2020.05.04
(SQLite) SQLite 공부하기 좋은 사이트  (0) 2018.03.07
SQLite Bulk Insert  (0) 2018.03.07
(SQLite) 속도향상 - 링크  (0) 2018.03.01
(Oracle) UNPIVOT  (1) 2018.02.20
(SQLite) insert 쿼리 수행 속도 높이기  (3) 2018.01.20

https://medium.com/@JasonWyatt/squeezing-performance-from-sqlite-insertions-971aff98eef2

 

http://blog.quibb.org/2010/08/fast-bulk-inserts-into-sqlite/

 

https://blogs.msdn.microsoft.com/andy_wigley/2013/11/21/how-to-massively-improve-sqlite-performance-using-sqlwinrt/

 

https://www.sqlite.org/fasterthanfs.html

 

* litedb

http://www.litedb.org/

 

* flat file database system

http://www.c-sharpcorner.com/UploadFile/e731e4/flatfile-database-system/

'DB' 카테고리의 다른 글

(SQLite) SQLite 공부하기 좋은 사이트  (0) 2018.03.07
SQLite Bulk Insert  (0) 2018.03.07
(SQLite) 속도향상 - 링크  (0) 2018.03.01
(Oracle) UNPIVOT  (1) 2018.02.20
(SQLite) insert 쿼리 수행 속도 높이기  (3) 2018.01.20
SQLite 참고 링크 모음  (1) 2017.11.23

Oracle 11g 부터 제공되는 UNPIVOT 명령어가 있는데

일일히 UNPIVOT 쿼리를 만들어 내지 않고 쉽게 UNPIVOT 을 할수 있다.

그런데 속도가 느리다 ㅜㅠ

하여 찾아보니 아래처럼 하면 속도가 10배나 빨라진다고 하는데..

한번 시도해 봐야겠다.

 

WITH base as (select my_data, ...) select * from base UNPIVOT (...)

'DB' 카테고리의 다른 글

SQLite Bulk Insert  (0) 2018.03.07
(SQLite) 속도향상 - 링크  (0) 2018.03.01
(Oracle) UNPIVOT  (1) 2018.02.20
(SQLite) insert 쿼리 수행 속도 높이기  (3) 2018.01.20
SQLite 참고 링크 모음  (1) 2017.11.23
SQLite 공유 캐시 모드  (0) 2017.08.24
  1. Favicon of https://kjun.kr keisoft kjun.kr 2018.03.05 12:55 신고

    속도가 10배이상 빨라졌다 위 방법을 추천한다

+ Recent posts