C# sehr langsame Verbindung zwischen SQL Server Datenbank - TIPPS
Hallo zusammen,
ich hätte mal da wieder eine Frage.
Ich erstelle eine Verbindung mit einer Datenbank, welche auf einem SQL-Server ist.
Die Verbindung funktioniert ohne Probleme. 😃
Jetzt möchte ich das zusätzlich noch mit Excel verknüpfen. Mein Ziel ist es, dass das Programm die Zellen einer Spalte in Excel durchgeht und dann die in der Datenbank sucht und einen anderen Wert des Datensatzes in die nächste Spalte schreibt.
Soweit funktioniert es auch. Ich habe anstatt der Excel Liste, den Wert manuell "1111111" geschrieben, das Programm findet und gibt das richtige aus. Allerdings ist mein Problem, dass es extrem lange dauert. ca. 30 sek. pro Wert.
// ---------- Datenbankverbindung herstellen ----------
SqlConnection con = new SqlConnection();
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;
con.ConnectionString = "Data Source=*;Initial Catalog=*;User ID=*;Password=*";
cmd.Connection = con;
try
{
con.Open();
}
catch (Exception ex)
{
Console.WriteLine(Convert.ToString(ex));
}
i = 2;
objWorksheetXLSX.Cells[1, intLetzteSpalte] = "ns1:Debitorennummer";
objCellXLSX = objWorksheetXLSX.Cells[i, 1];
while (objCellXLSX.Value != null)
{
cmd.CommandText = "SELECT * FROM * WHERE Mitgliedsnummer = 1111111";
reader = cmd.ExecuteReader();
reader.Read();
Console.WriteLine(i + ". Durchlauf" + reader["KontoNr"].ToString() + " " + reader["Mitgliedsnummer"].ToString());
i++;
objCellXLSX = objWorksheetXLSX.Cells[i, 1];
reader.Close();
}
Kann mir vielleicht bitte jemand sagen, wie ich das ganze optimieren kann?
Vielen Dank im Voraus
Was auf alle Fälle bremst ist, dass Du DB-Code mit UI-Code vermischt.
Console.Writeline ist sehr langsam.
Wieso lädst Du nicht erst die Informationen, und gibst sie dann aus?
[Artikel] Drei-Schichten-Architektur
Ansonsten einen Profiler verwenden, um zu ermitteln, was langsam ist.
Auch wir haben keine Glaskugel 😉
- performance is a feature -
Microsoft MVP - @Website - @AzureStuttgart - github.com/BenjaminAbt - Sustainable Code
Hallo Abt,
das Console.WriteLine ist nur zum Testen, was zurück gegeben wird.
Wenn ich alles hintereinander ausgebe und die Abfrage nach der Mitgliedsnummer lösche, funktioniert es eigentlich sehr schnell. Innerhalb paar Sekunden, bekomme ich alles mit Console.WriteLine geliefert.
Allerdings wenn ich zuvor aber den Wert aus der Excel Tabelle nehme und cmd.CommandText Where=Mitgliedsnummer mache, dauert es ewig, da er zuerst entsprechend sucht und dann schreibt.
Hast du vielleicht ein Tipp, wie ich es ansonsten machen könnte, damit ich nicht jedesmal nach dem Mitgliedsnummer suchen muss?
Hallo,
wie schnell ist denn die Abfrage, wenn du Sie direkt auf dem SQL-Server ausführst?
glandorf
Was ist denn langsam?
Das Abfragen des Wertes vom Server oder das Schreiben des Wertes in Excel? 🤔
Versuch stattdessen mal das Query-Ergebnis direkt in eine DataTable zu schreiben und dann durch die einzelnen Rows zu loopen.
Ist deswegen schon zu empfehlen, da du in deinem Beispiel die Datenbankverbindung so lange geöffnet lässt wie du mit den Ergebnissen irgendwann anstellst. Und eigentlich möchte man solche Verbindungen so kurz wie möglich geöffnet haben.
z.B.:
private DataTable ExecuteDataReader(SqlCommand ReaderCommand)
{
var myTable = new DataTable();
if (ReaderCommand.Connection.State != ConnectionState.Open)
{
ReaderCommand.Connection.Open();
}
myTable.Load(ReaderCommand.ExecuteReader());
ReaderCommand.Connection.Close();
return myTable;
}
Durch das DataTable-Objekt kannst du dann mit mit foreach loopen:
foreach(DataRow row in myTable.Rows)
{
Console.WriteLine(row["KontoNr"].ToString());
}
Falls es das auch nicht bringt würde ich auf irgend nen Konfigurationsproblem mit der Datenbankverbindung tippen.
Beste Grüße
emuuu
2+2=5( (für extrem große Werte von 2)
Hallo emuuu,
vielen Dank für deine Antwort. Das hörst sich natürlich logisch korrekt an. 😃
Vielen Dank für den Tipp.
Nur was ich nicht genau verstanden habe, ist wie kann ich es in meinem Beispiel machen.
Ich lese zuerst meine Datenbank in ein DataTable und dann suche ich mit einer foreach-Schleife den Wert aus der Tabelle?
Immer ruhig,
wir sind hier kein commercial-Support sonder ein freiwilliges Forum und innerhalb von 15min nen Push-Post findet ich ein wenig übertrieben.
Habe gerade gesehen, dass du Excel via Interop ansprichst. Das ist der Flaschenhals. Gerade mit Schleifen ist Excel via Interop eine Performance-Katastrophe.
Lad dir EPPlus via Nuget runter und erstelle/bearbeite deine Excel-Tabellen damit. (Das unterstützt auch das füllen einer Excel-Tabelle direkt aus einem DataTable).
Meine oben geposteter Code zu DataTables ist glaube ich sehr selbsterklärend und sollte dich Zusammen mit dem Lesen der Dokumentation ziemlich schnell zum Ziel führen.
2+2=5( (für extrem große Werte von 2)
Hallo emuuu,
vielen Dank für deine Antwort und tut mir leid, wenn ich falsch verstanden wurde.
Mein aktueller Code sieht so aus und ich habe eine Laufzeit von ca. 3 sekunden.
Zuerst öffnet das Programm eine .xml-Datei und speichert Sie als .xlsx ab ->
Danach öffne ich die Excel-Arbeitsmappe und ermittle die letzte Spalte mit Inhalt. -> Anschließend schreibe ich eine WENN-Formel in jede Zelle der Spalte -> Speichere und Schließe die Arbeitsmappe.
Jetzt möchte ich allerdings noch die Abfrage mit der Datenbank abfragen. Ich müsste auch jede Zelle einer Spalte durchgehen, schauen welche Mitgliedsnummer drinnsteht und anschließend in die intLetzteSpalte ein anderen Wert aus der DB schreiben.
Ich habe mich bezüglich EPLUS bisschen schlau gemacht. Ich habe es in meinem Projekt auch eingebunden und ein zwei kleinere Tests durchgeführt.
// ---------- .xml-Datei in Excel öffnen und als .xlsx speichern & schließen ----------
Excel.Application objExcelXML = new Excel.Application();
objExcelXML.Visible = true;
objExcelXML.DisplayAlerts = false;
Excel.Workbook objWorkbookXML = objExcelXML.Workbooks.OpenXML(@"Pfad", Type.Missing, Excel.XlXmlLoadOption.xlXmlLoadImportToList);
objWorkbookXML.SaveAs(@"Pfad", CreateBackup: false);
objExcelXML.DisplayAlerts = true;
objWorkbookXML.Close();
objExcelXML.Quit();
// ---------- Excel-Arbeitsmappe öffnen & letzte Spalte mit Inhalt ermitteln ----------
Excel.Application objExcelXLSX = new Excel.Application();
objExcelXLSX.Visible = true;
objExcelXLSX.DisplayAlerts = false;
Excel.Workbook objWorkbookXLSX = objExcelXLSX.Workbooks.Open(@"Pfad");
Excel.Worksheet objWorksheetXLSX = objWorkbookXLSX.Worksheets["Tabelle1"];
Excel.Range objCellXLSX;
int intLetzteSpalte = objWorksheetXLSX.UsedRange.Cells.Columns.Count;
intLetzteSpalte++;
// ---------- Berechnung - Werte in Zellen schreiben ----------
int i = 2;
objWorksheetXLSX.Cells[1, intLetzteSpalte] = "ns1:Bruttobetrag";
objCellXLSX = objWorksheetXLSX.Cells[i, 1];
while (objCellXLSX.Value != null)
{
objCellXLSX = objWorksheetXLSX.Cells[i, intLetzteSpalte];
objCellXLSX.Formula = "=IF(F" + i + "<0,E" + i + ",ROUND(E" + i + "+E" + i + "*F" + i + "/100,2))";
i++;
objCellXLSX = objWorksheetXLSX.Cells[i, 1];
}
intLetzteSpalte++;
// ---------- Excel-Arbeitsmappe speichern & schließen ----------
objWorkbookXLSX.Save();
objExcelXLSX.DisplayAlerts = true;
objWorkbookXLSX.Close();
objExcelXLSX.Quit();
// ---------- Ausgabe ----------
Console.WriteLine("Vorgang erfolgreich durchgeführt. Letzte Spalte: " + intLetzteSpalte);
Console.ReadLine();
Jetzt wäre meine Frage. Könnte ich ab diesem Quellcode mit EPLUS nuget weiterarbeiten?
Wenn ja, wie könnte ich das machen?
Kannst du mir auch vielleicht deine eigene Meinung für den aktuellen Code sagen, ob du eventuell etwas anderst machen würdest? Ich wäre für jede Hilfe & Tipp dankbar. 😃
Jetzt wäre meine Frage. Könnte ich ab diesem Quellcode mit EPLUS nuget weiterarbeiten?
Wenn ja, wie könnte ich das machen?
Das Ganze heißt nur EPPlus. Nuget (in Visual Studio nativ eingebaut) ist die Plattform über welche du es beziehen kannst.
Und um die Frage zu beantworten: Ja das geht. EPPlus bietet sämtliche Funktionalität die du via Excel-Interop aufrufen kannst, nur eben sehr viel performanter und mit einigen Zusatzfunktionen (z.B. DataTable to Excel). Ich würde aber Interop komplett aus meinem Projekt verbannen, weil es einfach unbeschreiblich langsam ist. Ich habe jetzt keine Messwerte dafür, aber ich würde schätzen, dass EPPlus beim durchlaufen von Zeilen ca. um den Faktor 50 schneller ist als Interop.
Zu deiner Frage bezüglich der Datenbankanbindung, bleibt meine Antwort wie oben:
Wenn die Zahl aller Datenbankeinträge unter deinen Mitgliedsnummern klein ist, würde ich einfach immer alle in ein DataTable laden und dann damit weiterarbeiten, andernfalls solltest du einen Vorfilter aller "möglichen" Mitgliedsnummern implementieren und diese dann in ein DataTable laden.
Die Variante "neue Zeile in Excel, dann neue Datenbankabfrage" würde ich auf jeden Fall versuchen zu vermeiden, da du deine Datenbank sonst mit tausenden Anfragen zubombst. Einerseits besteht dann die Gefahr, dass du andere Anwendungen die die gleiche Verbindung nutzen wollen blockierst, andererseits ist es einfach VIEL schneller, wenn du alle Daten ins RAM lädst und dann von dort aus abrufst, als immer wieder die Datenbankverbindung zu öffnen.
2+2=5( (für extrem große Werte von 2)
// ---------- Datenbankverbindung, Datenbankabfrage - Kontobezeichnung, Erloeskonto ----------
OleDbConnection con = new OleDbConnection();
OleDbCommand cmd = new OleDbCommand();
OleDbDataReader reader;
con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=Pfad";
cmd.Connection = con;
cmd.CommandText = "SELECT * FROM Tabelle";
try
{
con.Open();
var varDataTable = new DataTable();
varDataTable.Load(cmd.ExecuteReader());
cmd.Connection.Close();
i = 2;
objWorksheetXLSX.Cells[1, intLetzteSpalte] = "ns1:KontoNr";
objCellXLSX = objWorksheetXLSX.Cells[i, 1];
while (objCellXLSX.Value != null)
{
objCellXLSX = objWorksheetXLSX.Cells[i, 16];
foreach (DataRow row in varDataTable.Rows)
{
if (objCellXLSX.Value == row["Bezeichnung"].ToString())
{
objWorksheetXLSX.Cells[i, intLetzteSpalte] = row["Kontonr"];
//Console.WriteLine(i + ". " + row["Kontonr"]);
}
}
i++;
objCellXLSX = objWorksheetXLSX.Cells[i, 1];
}
}
catch (Exception ex)
{
Console.WriteLine(Convert.ToString(ex));
}
Hallo emuuu,
vielen Dank für deine hilfreichen Tipps.
Ich lade nun alle Datensätze in eine DataTable und anschließend frage ich die Abfragen von der Tabelle ab.
Das Programm läuft durch. Insgesamt benötigt das Programm jedoch ca. eine Minute, bis er vollständig durchgelaufen ist.
Meine erste Frage wäre, wie findest du den Code, kann ich hierbei etwas verbessern und die zweite Frage kann ich die Laufzeit bzw. die Geschwindigkeit noch reduzieren?
Ich bin noch ganz am Anfang. Vielen Dank für die Antworten im Voraus
select * ... und ohne where-Klausel ist eine ganz schlechte Idee wenn du eine große Datentabelle hast. Grenze deine Abfrage ein nach dem Motto soviel nötig so wenig wie möglich.
Ich denke die Bremse bei dir ist objWorksheetXLSX und nicht varDatatable. Ich vermute mal dass du damit eine ExcelTabelle direkt öffnest und bearbeitest(?), dann ist ja alles klar.
Ich bin verantwortlich für das, was ich sage, nicht für das, was du verstehst.
**:::
Hallo vbprogger,
vielen Dank für die Antwort.
Es ist keine große Datenmenge ca. 2000 Datensätze, aber ich habe jetzt zusätzlich noch eine WHERE-Klausel hinzugefügt. Danke 😃
Hast du vielleicht noch etwas anderes, was du vielleicht anderst machen würdest ?
Tipps zur Geschwindigkeit ?
In der Excel-Tabelle habe ich ca. 500 Zeilen.
Wie könnte ich das ganze in EPPlus realisieren ohne den kompletten Code zu ändern müssen, sondern lediglich nur den Abschnitt mit der Datenbank?
kann ich hierbei etwas verbessern und die zweite Frage kann ich die Laufzeit bzw. die Geschwindigkeit noch reduzieren?
Benutze EPPlus....! Nochmal: Wenn der Code mit Interop jetzt 1min braucht, würde er mit EPPlus nur wenige Sekunden dauern. Interop = böse.
Als Sofortmaßnahme kannst du in der if-Klammer noch ein break einfügen, da es wohl nicht nötig ist das DataTable weiter zu durchsuchen, wenn bereits der passende Datensatz gefunden wurde:
if (objCellXLSX.Value == row["Bezeichnung"].ToString())
{
objWorksheetXLSX.Cells[i, intLetzteSpalte] = row["Kontonr"];
//Console.WriteLine(i + ". " + row["Kontonr"]);
break;
}
Des Weiteren würde ich die Daten anstatt in ein DataTable in deinem Fall lieber in eine Objekt-Liste mappen und dann mit Linq weiterarbeiten. Sehr gut lässt sich hierfür Dapper verwenden.
2+2=5( (für extrem große Werte von 2)
Hallo emuuu,
vielen Dank für deine Antwort.
Leider habe ich EPPlus nichts vieles gefunden. Kannst du mir vielleicht sagen, wie ich diesen Abschnitt mit EPPlus machen könnte?
Vielleicht ein kurzer Beispielcode wäre ich sehr dankbar.
Leider habe ich EPPlus nichts vieles gefunden. Kannst du mir vielleicht sagen, wie ich diesen Abschnitt mit EPPlus machen könnte?
Ein kleines bisschen Eigeninitiative wäre hier wirklich hilfreich:
Google -> EPPlus documentation -> Erstes Ergebnis: Getting Started with EPPlus
2+2=5( (für extrem große Werte von 2)
Hallo emuuu,
sorry, ich habe mich falsch ausgedrückt. Ich habe den folgenden Code aktuell, bis dahin läuft das Programm innerhalb ca. 5 sekunden durch.
// ---------- .xml-Datei in Excel öffnen und als .xlsx speichern & schließen ----------
Excel.Application objExcelXML = new Excel.Application();
objExcelXML.Visible = true;
objExcelXML.DisplayAlerts = false;
Excel.Workbook objWorkbookXML = objExcelXML.Workbooks.OpenXML(@"Pfad", Type.Missing, Excel.XlXmlLoadOption.xlXmlLoadImportToList);
objWorkbookXML.SaveAs(@"Pfad", CreateBackup: false);
objExcelXML.DisplayAlerts = true;
objWorkbookXML.Close();
objExcelXML.Quit();
// ---------- Excel-Arbeitsmappe öffnen & letzte Spalte mit Inhalt ermitteln ----------
Excel.Application objExcelXLSX = new Excel.Application();
objExcelXLSX.Visible = true;
objExcelXLSX.DisplayAlerts = false;
Excel.Workbook objWorkbookXLSX = objExcelXLSX.Workbooks.Open(@"Pfad");
Excel.Worksheet objWorksheetXLSX = objWorkbookXLSX.Worksheets["Tabelle1"];
Excel.Range objCellXLSX;
int intLetzteSpalte = objWorksheetXLSX.UsedRange.Cells.Columns.Count;
intLetzteSpalte++;
// ---------- Berechnung - Werte in Zellen schreiben ----------
int i = 2;
objWorksheetXLSX.Cells[1, intLetzteSpalte] = "ns1:Bruttobetrag";
objCellXLSX = objWorksheetXLSX.Cells[i, 1];
while (objCellXLSX.Value != null)
{
objCellXLSX = objWorksheetXLSX.Cells[i, intLetzteSpalte];
objCellXLSX.Formula = "=IF(F" + i + "<0,E" + i + ",ROUND(E" + i + "+E" + i + "*F" + i + "/100,2))";
i++;
objCellXLSX = objWorksheetXLSX.Cells[i, 1];
}
intLetzteSpalte++;
// ---------- Excel-Arbeitsmappe speichern & schließen ----------
objWorkbookXLSX.Save();
objExcelXLSX.DisplayAlerts = true;
objWorkbookXLSX.Close();
objExcelXLSX.Quit();
// ---------- Ausgabe ----------
Console.WriteLine("Vorgang erfolgreich durchgeführt. Letzte Spalte: " + intLetzteSpalte);
Console.ReadLine();
Jetzt möchte ich vor dem speichern & schließen der Excel-Arbeitsmappe meine Datenbankverbindung und das Schreiben in die Zellen machen.
Wie mache ich das, war meine Frage?
Also kannst du mir bitte nur einen Ansatz geben?