System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 607f4c4fde16fda94c09a4d2976419a62b27b92f
Title: SQLiteConnection is too slow on 32 bit
Status: Closed Type: Incident
Severity: Important Priority: Medium
Subsystem: None Resolution: Need_More_Info
Last Modified: 2015-12-05 00:59:43
Version Found In: 1.0.98.0
User Comments:
anonymous added on 2015-11-05 07:19:20:
-- On 64 bit it works properly

Install-Package System.Data.SQLite.x86

**************************
Run click exe: (ctrl+f5)
**************************

Before Open: 38
After Open: 7076
Before Table Create: 7076
After Table Create: 7086
After First Insert: 7091
Second First Insert: 7095

Orange : Turuncu
Blue : Mavi

Select Completed: 7103
Before Close: 7103
After Close: 7104

******************************
Run from Visual Studio: (f5)
******************************

Before Open: 56
After Open: 116
Before Table Create: 117
After Table Create: 144
After First Insert: 149
Second First Insert: 154

Orange : Turuncu Blue : Mavi

Select Completed: 172
Before Close: 172
After Close: 174

class Program
{
    private const string connStr = "Data Source=C:\\Test.db3;";

    private const string createTableStr = @"CREATE TABLE IF NOT EXISTS PRM_COLOR (
                                              [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                                              [Key] NVARCHAR(50)  NOT NULL,
                                              [Value] VARCHAR(255)  NOT NULL
                                            )";

    static void Main(string[] args)
    {
        Stopwatch stopwatch = Stopwatch.StartNew();

        CreateDbIfNotExists("C:\\Test.db3");
        using (SQLiteConnection conn = new SQLiteConnection(connStr))
        {
            Console.WriteLine(string.Format("Before Open: {0}", stopwatch.ElapsedMilliseconds));

            conn.Open();

            Console.WriteLine(string.Format("After Open: {0}", stopwatch.ElapsedMilliseconds));

            using(SQLiteCommand cmd = new SQLiteCommand(conn))
            {
                Console.WriteLine(string.Format("Before Table Create: {0}", stopwatch.ElapsedMilliseconds));

                cmd.CommandText = createTableStr;
                cmd.ExecuteNonQuery();

                Console.WriteLine(string.Format("After Table Create: {0}", stopwatch.ElapsedMilliseconds));

                cmd.CommandText = "INSERT INTO PRM_COLOR(Key,Value) VALUES('Orange','Turuncu')";
                cmd.ExecuteNonQuery();

                Console.WriteLine(string.Format("After First Insert: {0}", stopwatch.ElapsedMilliseconds));


                cmd.CommandText = "INSERT INTO PRM_COLOR(Key,Value) VALUES('Blue','Mavi')";
                cmd.ExecuteNonQuery();

                Console.WriteLine(string.Format("Second First Insert: {0}", stopwatch.ElapsedMilliseconds));


                cmd.CommandText = "Select * FROM PRM_COLOR";      

                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine(reader["Key"] + " : " + reader["Value"]);     
                    }
                }

                Console.WriteLine(string.Format("Select Completed: {0}", stopwatch.ElapsedMilliseconds));

            }

            Console.WriteLine(string.Format("Before Close: {0}", stopwatch.ElapsedMilliseconds));

            conn.Close();
        }

        Console.WriteLine(string.Format("After Close: {0}", stopwatch.ElapsedMilliseconds));


        Console.Read();
    }

    static void CreateDbIfNotExists(string dbPath)
    {
        string directory = Path.GetDirectoryName(dbPath);
        if (!File.Exists(dbPath))
        {
            // Creates directory if it doesn't already exist:
            Directory.CreateDirectory(directory);

            // Creates file if it doesn't already exist:
            SQLiteConnection.CreateFile(dbPath);
        }
    }
}

mistachkin added on 2015-11-05 21:51:56:
Which operation is too slow?  Are you using the same x86 binaries on both 32-bit
and 64-bit operating systems?