Laden...

SQLite DB mega langsam

Erstellt von rollerfreak2 vor 15 Jahren Letzter Beitrag vor 15 Jahren 12.915 Views
rollerfreak2 Themenstarter:in
916 Beiträge seit 2008
vor 15 Jahren
SQLite DB mega langsam

verwendetes Datenbanksystem: <SQLite da free und embedded>

Ich habe mal eine Frage bezüglich Performance der SQLite Datenbank und Anbindung an c#. Ich brauche die DB nur um Daten abzulegen und dann schnelelr drauf zugreifen zu können. Also hab ich mich für SQLite entscheiden da free und embedded. Nun hab ich hier die 3 dll's eingefügt. 2 konnte ich nicht referencieren nur die sqlite.dll, die anderen beiden hab ich mit dazu kopiert. Die sind angeblich keine COM komponenten, aber die weren von der sqlite.dll zu laufzeit mit eingebunden, zumindest vermute ich das.

Nun hab ich mal schnell folgenden Code geschrieben um mir die Performance anzusehen.


        private SQLiteConnection sqlite_conn;
        private SQLiteCommand sqlite_cmd;
        private SQLiteDataReader sqlite_datareader;

        
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                sqlite_conn = new SQLiteConnection("Data Source=database.db;Version=3;New=True;Compress=True;");

                sqlite_conn.Open();

                sqlite_cmd = sqlite_conn.CreateCommand();

                sqlite_cmd.CommandText = "CREATE TABLE test (id integer primary key, text varchar(100));";
                sqlite_cmd.ExecuteNonQuery();

                Stopwatch watch = new Stopwatch();
                watch.Start();
                for (int i = 0; i < 100; i++)
                {
                    sqlite_cmd.CommandText = "INSERT INTO test (id, text) VALUES (" + i.ToString() + ", 'Test');";
                    sqlite_cmd.ExecuteNonQuery();
                }
                watch.Stop();
                this.label1.Text = "time:" + watch.ElapsedMilliseconds.ToString() + " ms";

                sqlite_conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);  
            }
        }

Es sollte also einfach mal 100 einträge in eine leere Datenbank eintragen und die Zeit messen. Das dauert bei mir 13 Sekunden. Das ist doch der Hammer.

Nutze ich eventuell völlig veraltete Verison von SQLite oder einfach eine zu alte Anbindung in c#? Das muss doch schneller gehen, ich dachte so an ca. 1000-2000 Einträge pro Sekunde.

Bitte um Hilfe

Again what learned...

J
3.331 Beiträge seit 2006
vor 15 Jahren
Programmcode mega langsam

Hallo,

wenn Du jeden Befehl einzeln mit Execute zur DB schickst, dauert es natürlich viel länger. Wenn Du mehrere Befehle hintereinander ausführen lassen willst, solltest Du **Transaction **benutzen. Dazu findest Du auch eine Reihe von Hinweisen in der Forumssuche.

Nebenbei: Eine DbConnection sollte unbedingt in einen using-Block eingebunden werden. Dein CommandText wird hoffentlich nur bei diesem Test per String-Verknüpfung erzeugt; bei realen Befehlen benutze bitte Parameters.

Jürgen

rollerfreak2 Themenstarter:in
916 Beiträge seit 2008
vor 15 Jahren

Das mit dem Transaction ist mir neu. Aber ich werde es mal ausprobieren. Was du allerdings mit den Parametern meinst weis ich nicht. Kannst du das bisschen genauer erklären.

Again what learned...

J
3.331 Beiträge seit 2006
vor 15 Jahren

Was du allerdings mit den Parametern meinst weis ich nicht. Kannst du das bisschen genauer erklären.

Wozu gibt es bloß die Forumssuche X( Mit parameters in "Datentechnologien" bekommst Du Hunderte von Treffern; fast alle Themen, die Probleme mit Formatierungen (bei DateTime oder Zahlen) oder beim Speichern haben, liefern Hinweise.

Jürgen

rollerfreak2 Themenstarter:in
916 Beiträge seit 2008
vor 15 Jahren

Also das mit der Transaction hab ich jetzt geschnallt. Warum ich allerdings ein using bei der connection brauch nicht. Ich denke das using macht nix weiter als die resource in dem fall die Connection bereitstellen, und am ende wieder freigeben.

Das heißt wenn ich im catch block die connection wieder schließe (sollte eine schon bestehen, kann man ja abchecken) dann ist das doch dass selbe, oder nicht?

Das mit den Parameter ist sicher nur performance technisch wichitg, da hast du schon recht! Weil ja jedes mal ein String zusammen gecattet wird, und das sehr umständlich ist, oder hat das auch noch einen anderen Grund warum ich das nehmen soll?

THX for your help 😃

Again what learned...

F
323 Beiträge seit 2007
vor 15 Jahren

Das mit den Parameter ist sicher nur performance technisch wichitg, da hast du schon recht! Weil ja jedes mal ein String zusammen gecattet wird, und das sehr umständlich ist, oder hat das auch noch einen anderen Grund warum ich das nehmen soll?

Stichwort: SQL_Injection

3.971 Beiträge seit 2006
vor 15 Jahren

Das heißt wenn ich im catch block die connection wieder schließe

falsch, wenn dann wird diese im Finally geschlossen. Der Finally-Bereich wird automatisch immer durchlaufen, egal ob erfolgreich oder bei einer Exception

Ein Using ist nichts weiter als ein Try/Finally Konstrukt, erhöht aber die Lesbarkeit.

Es gibt 3 Arten von Menschen, die die bis 3 zählen können und die, die es nicht können...

rollerfreak2 Themenstarter:in
916 Beiträge seit 2008
vor 15 Jahren

Jo das meine ich auch, im catch wäre schwachsinn, weil man es dann im try block auch closen müsste.

Ich hab das jetzt mal so modifiziert das es echt schnell läuft, und denk ich performance technisch auch ordentlich ist.


         private void button1_Click(object sender, EventArgs e)
        {
            using (SQLiteConnection sqlite_conn = new SQLiteConnection("Data Source=database.db;Version=3;New=True;Compress=True;"))
            {
                using (SQLiteCommand sqlite_cmd = sqlite_conn.CreateCommand())
                {
                    //open connection
                    sqlite_conn.Open();

                    sqlite_cmd.CommandText = "CREATE TABLE test (id integer primary key, text varchar(100));";
                    sqlite_cmd.ExecuteNonQuery();

                    Stopwatch watch = new Stopwatch();

                    sqlite_cmd.Transaction = sqlite_conn.BeginTransaction();
                    watch.Start();

                    SQLiteParameter parameter_list;
                    
                    for (int i = 0; i < 100000; i++)
                    {
                        sqlite_cmd.Parameters.Clear();
                        
                        parameter_list = sqlite_cmd.CreateParameter();
                        parameter_list.ParameterName = "@id";
                        parameter_list.DbType = DbType.Int32;
                        parameter_list.Value = i;
                        sqlite_cmd.Parameters.Add(parameter_list);
                        
                        parameter_list = sqlite_cmd.CreateParameter();
                        parameter_list.ParameterName = "@text";
                        parameter_list.DbType = DbType.String;
                        parameter_list.Value = "test";
                        sqlite_cmd.Parameters.Add(parameter_list);

                        sqlite_cmd.CommandText = "INSERT INTO test (id, text) VALUES (@id, @text);";
                        sqlite_cmd.ExecuteNonQuery();
                    }
                    watch.Stop();
                    this.label1.Text = "time:" + watch.ElapsedMilliseconds.ToString() + " ms";
                    sqlite_cmd.Transaction.Commit();
                    
                    // close connection
                    sqlite_conn.Close();
                }
            }
        }

Oder gibt es jetzt noch was auszusetzen dran 😃

Again what learned...

J
3.331 Beiträge seit 2006
vor 15 Jahren

Oder gibt es jetzt noch was auszusetzen dran

Ja, aber nichts Gravierendes mehr, sondern nur etwas Praktisches, das Deine Arbeit aber noch etwas beschleunigt.

Die Parameterliste muss nur einmal erstellt werden (genauso wie der DbCommand selbst); innerhalb der Schleife genügt eine erneute Zuweisung der aktuellen Werte.

sqlite_cmd.CommandText = "INSERT INTO test (id, text) VALUES (@id, @text);";
sqlite_cmd.Parameters.AddWithValue("@id", 0);
sqlite_cmd.Parameters.AddWithValue("@text", "x");

for (int i = 0; i < 100000; i++)
{
    sqlite_cmd.Parameters["@id"].Value = i;
    sqlite_cmd.Parameters["@test"].Value = "test";
    sqlite_cmd.ExecuteNonQuery();
}

Wahrscheinlich solltest Du wegen der Zeitprüfung noch das Commit vor watch.Stop setzen.

Jürgen

/Edit
Fehler mit Value beseitigt (siehe den nächsten Beitrag).

rollerfreak2 Themenstarter:in
916 Beiträge seit 2008
vor 15 Jahren

Ganz so gehts net, hast beim zuweisen .value vergesen und die AddWithValue hab ich zu Add(name, DbType) gemacht. Gleichzeitg hab ich noch mal wegen den Umlaute Problem geprüft was mal hie rim Forum erwähnt wurde, und das funzt auch. Mann muss einfach oben im ConnectionString UTF8 oder UTF16 auf true stellen.

Hier mal wie's im release Mode 100000 Elemente in 1,7 Sekunden in die DB schiebt. Das find ich echt schnell 😃 Dank deiner Hilfe


        private void button1_Click(object sender, EventArgs e)
        {
            using (SQLiteConnection sqlite_conn = new SQLiteConnection("Data Source=database.db;Version=3;New=True;Compress=True;UTF8Encoding=True;"))
            {
                using (SQLiteCommand sqlite_cmd = sqlite_conn.CreateCommand())
                {
                    //open connection
                    sqlite_conn.Open();

                    sqlite_cmd.CommandText = "CREATE TABLE test (id integer primary key, text varchar(100));";
                    sqlite_cmd.ExecuteNonQuery();

                    Stopwatch watch = new Stopwatch();

                    sqlite_cmd.Transaction = sqlite_conn.BeginTransaction();
                    watch.Start();

                    SQLiteParameter parameter_list = sqlite_cmd.CreateParameter(); ;

                    sqlite_cmd.CommandText = "INSERT INTO test (id, text) VALUES (@id, @text);";
                    sqlite_cmd.Parameters.Add("@id", DbType.Int32);
                    sqlite_cmd.Parameters.Add("@text", DbType.String);

                    for (int i = 0; i < 100000; i++)
                    {
                        if (i > 5000 && i < 5050)
                            sqlite_cmd.Parameters[1].Value = "Übung";
                        else
                            sqlite_cmd.Parameters[1].Value = "test";

                        sqlite_cmd.Parameters[0].Value = i;
                        sqlite_cmd.ExecuteNonQuery();
                    }
                    sqlite_cmd.Transaction.Commit();

                    watch.Stop();
                    this.label1.Text = "time:" + watch.ElapsedMilliseconds.ToString() + " ms";

                    sqlite_cmd.CommandText = "SELECT * FROM test where text = 'Übung'";
                    SQLiteDataReader sqlite_datareader = sqlite_cmd.ExecuteReader();

                    StringBuilder tmp = new StringBuilder();
                    while (sqlite_datareader.Read())
                    {
                        tmp.Append(sqlite_datareader[0].ToString() + "," + sqlite_datareader[1].ToString() + Environment.NewLine);
                    }

                    this.textBox1.Text = tmp.ToString();
                    // close connection
                    sqlite_conn.Close();
                }
            }
        }

Many Thanks

Again what learned...

3.511 Beiträge seit 2005
vor 15 Jahren

Nur noch so nebenbei:
Das "sqlite_conn.Close()" am Ende, brauchst du nicht schreiben. Das erledigt das Dispose welches durch das using aufgerufen wird.

"Jedes Ding hat drei Seiten, eine positive, eine negative und eine komische." (Karl Valentin)

F
10.010 Beiträge seit 2004
vor 15 Jahren

Und nochetwas, der Finiar SQLite Provider ist veraltet.

Der ist noch für FW 1.1 und hat auch sonst einige unzulänglichkeiten.

Benutze lieber den hier, der hat ausserdem noch alles in einer einzigen dll.
http://sourceforge.net/projects/sqlite-dotnet2/

Der hat auch z.b. nen EF Provider

rollerfreak2 Themenstarter:in
916 Beiträge seit 2008
vor 15 Jahren

Der Provider ist eine dll das stimmt. Aber die sqlite3.dll muss ja auch mit rein, das ist ja das Datenbankmanagment an sich.

Dazu eine Frage. Wenn ich verusche diese dll ins studio einzubinden via reference, dann geht das nicht. Weil der mir sagt das "is not a valid assembly or COM commponent". Kann man das irgendwie einbinden.

Sprich wenn ich die dll mit via ilmerge mit in die application.exe rein haue, gibt es da probleme. Oder geht das gar nicht weil es keine .net assembly bzw com commponente ist?

Ansonsten funzt die neue Provider dll sehr gut.

Again what learned...

F
10.010 Beiträge seit 2004
vor 15 Jahren

Du hast es irgendwie nicht verstanden was ich gesagt habe.

Der Provider den Du benutzt ist für FW 1.1 entwickelt und hat dementsprechend
wenig Funktionen.
Auch ist das letzte Update schon äonen her.

Der andere hingegen ist auf dem neuesten Stand und beinhaltet
eben diese weiteren nativen DLL's bereits, die du nicht mit ILMerge
einbinden kannst, da das nur mit .NET DLL's geht.

Und wenn Du keine Express Version benutzt , ist sogar designerunterstützung und EF Support mit dabei.

rollerfreak2 Themenstarter:in
916 Beiträge seit 2008
vor 15 Jahren

Falsch!!!! Er hat die nativen Dll's ist im übrigen nur eine "sqlite3.dll" nicht enthalten. Aber guck einfach selber, da ist nur die Provider Dll drin.

http://sourceforge.net/projects/sqlite-dotnet2/

Hier ist die sqlite3.dll nicht drin, die muss man sich, wie es auch in der dazugehörigen readme steht sepperat runter laden auf http://www.sqlite.org! Und das die nicht mit dem ilmerge einzubinden geht war mir ziemlich klar da kein .net assembly. Das heißt die muss immer sepperat liegen, und es gibt auch keine andere Möglichkeit die in die exe rein zu bekommen.

War auch nur eine Frage am Rande, ist nicht weiter wichtig...!

Trotzdem Danke.

Again what learned...

F
21 Beiträge seit 2008
vor 15 Jahren

Für den Provider von

http://sourceforge.net/projects/sqlite-dotnet2/

brauchst du die sqlite3.dll nicht, siehe Readme:

Distributing The SQLite Engine and ADO.NET Assembly

On the desktop, only the System.Data.SQLite.DLL file needs to be distributed with your application(s). This DLL contains both the managed wrapper and the native SQLite3 codebase.

Gruß, Frank

F
10.010 Beiträge seit 2004
vor 15 Jahren

@rollerfreak2:
Bevor du soetwas behauptest, erst lesen.

rollerfreak2 Themenstarter:in
916 Beiträge seit 2008
vor 15 Jahren

Also entweder bin ich zu blöd oder ich kann nicht lesen. Ich glaub euch das ja gern das man da nur die System.Data.SQLite.dll einbinden muss, und die beinhaltet dann auch die DB Engine. Nur wenn ich mir das downloade dann steht in der readme dazu

This managed version of the ADO.NET provider for SQLite requires the native
sqlite3.dll or Linux shared library from
>

Requires version 3.6.3 or higher.

Das heißt ich brauche die doch noch, oder lade ich einfach die falsche dll?

Weil wenn ich nur die dll einbinde, und dann oben stehenden code laufen lasse, dann kommt bei der connection.open eine exception das die sqlite3.dll nicht gefunden wurde...

Again what learned...

F
10.010 Beiträge seit 2004
vor 15 Jahren

Also entweder bin ich zu blöd oder ich kann nicht lesen

Musst du entscheiden.

Der Link den wir gemeint haben beinhaltet für den Windows Desktop sowohl die
sourcen zur eigentlichen Engine ( in eine einzelne C/C++ Datei zusammengefasst )
als auch den Provider.

Und die DLL aus dem Binary Package hat deshalb den Provider auch
eingebettet.

Bist Du sicher, das du die richtige DLL eingebunden hast, und nicht immer noch
die alte?
Die haben nämlich noch nichteinmal vom Namespace etwas gemein.

rollerfreak2 Themenstarter:in
916 Beiträge seit 2008
vor 15 Jahren

Also ich hab die System.Data.SQLite.dll eingebunden, die in den managedbinaries mit drin war.

Dann den folgenden code, fehlt nur die Form an sich, da designer.


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SQLite;
using System.Data.Common;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Diagnostics;

namespace db_conn
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            using (SQLiteConnection sqlite_conn = new SQLiteConnection("Data Source=database.db;Version=3;New=True;Compress=True;UTF8Encoding=True;"))
            {
                using (SQLiteCommand sqlite_cmd = sqlite_conn.CreateCommand())
                {
                    //open connection
                    sqlite_conn.Open();

                    sqlite_cmd.CommandText = "CREATE TABLE test (id integer primary key, text varchar(100));";
                    sqlite_cmd.ExecuteNonQuery();

                    Stopwatch watch = new Stopwatch();

                    sqlite_cmd.Transaction = sqlite_conn.BeginTransaction();
                    watch.Start();

                    SQLiteParameter parameter_list = sqlite_cmd.CreateParameter(); ;

                    sqlite_cmd.CommandText = "INSERT INTO test (id, text) VALUES (@id, @text);";
                    sqlite_cmd.Parameters.Add("@id", DbType.Int32);
                    sqlite_cmd.Parameters.Add("@text", DbType.String);

                    for (int i = 0; i < 100000; i++)
                    {
                        if (i > 5000 && i < 5050)
                            sqlite_cmd.Parameters[1].Value = "Übung";
                        else
                            sqlite_cmd.Parameters[1].Value = "test";

                        sqlite_cmd.Parameters[0].Value = i;
                        sqlite_cmd.ExecuteNonQuery();
                    }
                    sqlite_cmd.Transaction.Commit();

                    watch.Stop();
                    this.label1.Text = "time:" + watch.ElapsedMilliseconds.ToString() + " ms";

                    sqlite_cmd.CommandText = "SELECT * FROM test where text = 'Übung'";
                    SQLiteDataReader sqlite_datareader = sqlite_cmd.ExecuteReader();

                    StringBuilder tmp = new StringBuilder();
                    while (sqlite_datareader.Read())
                    {
                        tmp.Append(sqlite_datareader[0].ToString() + "," + sqlite_datareader[1].ToString() + Environment.NewLine);
                    }

                    this.textBox1.Text = tmp.ToString();
                    // close connection
                    sqlite_conn.Close();
                }
            }
        }
    }
}


Und bei


 sqlite_conn.Open();

kommt folgende Exeption

An unhandled exception of type 'System.BadImageFormatException' occurred in System.Data.SQLite.dll

Additional information: Es wurde versucht, eine Datei mit einem falschen Format zu laden. (Ausnahme von HRESULT: 0x8007000B)

Ich check das net wieso das bei mir net mit einer dll geht! Hat jemand einen rat für mich? Welche dll bindet ihr denn ein das es bei euch funktioniert?

Again what learned...

F
21 Beiträge seit 2008
vor 15 Jahren

Hab's eben ausprobiert: die BadImageFormatException bekommt man auf Vista 64 mit der falschen Assembly. Für Vista 64 muss man die Assembly aus dem x64 Verzeichnis einbinden. Die aus dem Hauptverzeichnis geht wohl nur auf 32Bit Systemen. Ebenso die Assembly aus dem ManagedOnly Verzeichnis. Für die würdest du dann aber tatsächlich noch die sqlite3.dll benötigen.

Gruß, Frank

P.S. FYI: Dein Test benötigt bei mir 900ms (Core 2 Duo E6600@2.4GHz, 4GB, Vista 64)

rollerfreak2 Themenstarter:in
916 Beiträge seit 2008
vor 15 Jahren

Ich habe aber ein 32 Bit System, mit Dual Core Intel. Und welche soll ich jetzt einbinden, du sagtest du aus dem managed Verzeichnis geht nicht. Kannst du mal den genauen Namen sagen?

Again what learned...

rollerfreak2 Themenstarter:in
916 Beiträge seit 2008
vor 15 Jahren

mhh jetzt hab ichs. Die aus dem Hauptverzeichnis funktioniert. Jetzt stellt sich mir die Frage wenn ich das dann alle via ilmerge in ein assembly knalle, dann an jemand anders weiter gebe, der eventuell ein 64 bit system hat, geht das dann bei dem noch?

Danke erstmal für deine Hilfe. Wegen den 1,7 Sekunden, das war auf einen single Core, ich teste das gleich mal bei mir und poste mal die Zeit 😃

Again what learned...

rollerfreak2 Themenstarter:in
916 Beiträge seit 2008
vor 15 Jahren

Also ich habs jetzt mal auf meinem Intel DualCore 2,1 GHZ, 2GB RAM, VISTA 32Bit 1,6 Sekunden, und dabei ist eine TV Karte gelaufen.

Again what learned...

M
221 Beiträge seit 2008
vor 15 Jahren

2140er CPU @ 1,6 Ghz mit 1 GB Ram unter Windows XP, 80er Festplatte, kein Raid

1,1 Sekunden.

Wundert mich jetzt selber ein bisschen!

F
10.010 Beiträge seit 2004
vor 15 Jahren

@rollerfreak2:
Das geht garnicht, da ILMerge schon meckern wird, weil es mit der DLL nicht zurecht kommt.

Und was glaubst Du, warum derzeit noch viele SW als 32 und als 64 Bit version getrennt geliefert werden?

rollerfreak2 Themenstarter:in
916 Beiträge seit 2008
vor 15 Jahren

Jo das mit ILMerge klingt logisch. Also leg ich einfach beide dll's dazu, und muss dann auf dem zielsystem die Bittiefe prüfen und dann die richtige dll laden.
Mal sehen wie ich das mache.

Again what learned...

F
10.010 Beiträge seit 2004
vor 15 Jahren

Jedenfalls nicht so.

Entweder du stellst explizit dein Projekt auf 32Bit, oder du musst
2 Versionen erstellen, aber das nachladen einer OS-Spezifischen version
ist nicht "mal eben so" zu machen.

rollerfreak2 Themenstarter:in
916 Beiträge seit 2008
vor 15 Jahren

Hab das Thema grad mal gegoogelt, zu laufzeit eine Dll umladen ist echt nicht leicht. Aber man kann ja einen installer schreiben, der dann automatisch die richtige dll dazu legt, und dann wird auch immer die richtige dll geladen. Das ist zumindest eine einfache Lösung.

Again what learned...