Datenbank als XML auf Rechner mit Produktionsdatenbank aktuell halten

verwendetes Datenbanksystem: <sqlexpress2012>

Hallo, bin gerade dabei eine große Datenbank, die ich mit meiner Applikation pflege und aktualisiere auch auf anderen Rechnern arbeiten zu lassen, die nicht im Firmennetz sind.

Das funktioniert eigentlich auch, nur ist die Datenbank mittlerweile so groß, das bei der Abfrage der Daten man mächtig lange warten muss, bis die Daten vom Server gesaugt sind.

Also, habe ich mir gedacht, ich speichere ein XML-Abbild der DB mit ihren Tabellen auf dem Rechner. Wenn man dann extern arbeitet, muss die Applikation die XML vom Rechner in ein DataSet einlesen. Dann muss die Kopftabelle, mit all ihren Artikeln mit der vom Produktionsserver abgeglichen werden. Das heißt: Wenn man ein paar Tage nicht am Netz war, sind beim Produktionsserver neue Datensätze hinzugekommen. Diese muss ich in der XML-Struktur auf den oder dem externen Rechner aktualisieren.
Wenn ich einen Abgleich der Kopftabelle aus der XML mit der Kopftabelle vom Produktionsserver mache, kann ich alle anderen XML-Tabellen mit den nötigen Informationen aktualisieren und das XML aktualisiert zurückschreiben.

Aber nun zu meinem Problem. Die XML habe ich auf dem Rechner erstellt, ich bin gerade bei dem Abgleich der XML-Kopftabelle mit der Produktions-Kopftabelle.

Ich hatte mir gedacht, ich durchlaufe in einer Schleife die Produktionskopftabelle un dvergleiche jeden mit der XML-Kopftabelle (über den Dateinamen, das ist der Schlüssel).
Leider dauert das ewig, da ca. 80000 Datensätze verglichen werden müssen.

Ich hätte aber gerne sofort nur die Datensätze, die einfach nur fehlen. Habe das schon mit LinQ probiert (GroupJoin), aber ohne Erfolg. Und nur mit Schleife das dauert zu lange.

Hat jemand eine Idee wie man die beiden Tabellen sehr schnell abgleichen kann (also das man ald Ergebnis nur die Datensätze hat bei denen es keine Zuordnung gibt)?

Erstelle eine Spalte mit einem Zeitstempel und lade alle Zeilen mit einer Zeitstempel neuer als der letzte Abgleich.

Deine Lösung hört sich aber so an als ob man sich über das Design der Anwendung Gedanken machen müsste.

Wieso installierst Du lokal keinen SLQ Server Express ?

Wie ist die Anbindung dass das so langsam ist ? Die Größe der Datenbank sollte bei richtiger Architektur keine Rolle spielen bei der Geschwindkeit. Bei mir dauert die Anzeige großer Datenmengen nur wenige Sekunden auch bei langsamer Anbindung.

Grüße Bernd

Wenn du schon dermaßen Probleme hast die Datenmenge abzufragen, dann ist XML kein sinnvoller Ansatz.
Je nach Datenmenge hast du dann einige hundert MB oder gar DB an XML.
Davon hast du dann durch die ganzen Tags etc. einen riesen Overhead.
Das ganze dann zu parsen kostet dann enorm viel Speicher + CPU Leistung.

Nimm für lokale Datenbanken lieber Sqlite.
Hier kannst du einen Service umsetzen, der dem Client dann immer die aktuelle DB als Sqlite Datei senden bzw. kann der Client über einen Service die Daten selbst abholen und in eine Sqlite Datei schreiben.

Was mich aber stört, ist die Tatsache das deine DB scheinbar nicht optimal läuft.
Wenn du nicht gerade alle Daten aus deiner DB an den Client senden muss, dann sollten deine Clients doch nur Teildaten bekommen oder?
Dann wäre die Frage warum die Abfrage der Daten auf deinem Server so lange braucht.
Wenn du hier saubere Indizies hast, dann sollte auch bei einer größeren DB die Abfrage der Daten zügig gehen.

Ebenfalls solltest du nicht auf DataTable/DataSet setzen.
Wenn du die Daten effizient verarbeiten willst, bau die die Tabellen als Klassen nach und nutze dann die .Net Klassen und Methoden zum Objekt vergleichen.
Je nachdem was du für Daten verarbeitest/abgleichst, ist es meistens effizienter dies über eigene Klassen in Kombination mit entsprechenden Compare Implementierungen oder anderen Mitteln zu machen als über DataTable Methoden.

Ebenfalls arbeiten DataTable/DataSet sehr speicher intensiv, weshalb ab einem bestimmten Punkt das Ganze sehr speucherhungrig wird.
Hier ist eine List<T> mit Custom Klassen um Faktor 5 bis 10 kleiner.
Je nachdem wie du die Daten sucht, kannst du auch ein Dictionary verwenden, was dann mit einem Key einen sehr schnellen Zugriff auf einzelne Einträge erlaubt.

Das geht um längen schneller als z.B. ein Select im DataTable auf eine bestimmte Row über den Primär Schlüssel.
Würde also empfehlen den Aufbau deiner Anwendung zu überdenken und auch die Lieferung der finalen Datenbank für den Client dann sogar direkt im Server umzusetzen.
So muss dein Client dann nur die Sqlite Datei laden und kann direkt lokal arbeiten oder durch saubere Logik sogar einfach nur abgleichen.

Nachtrag:
@BerndFfm
Sql Server Express bei Clients halte ich für einen falschen Ansatz.
Da hier auch die größe der DB schon Probleme macht, müsste man die DB Größe bei Express von 10 GB beachten und müsste dann ggf. die Datenbank splitten.
Dann kann man auch direkt eine Sqlite DB nehmen und damit entfällt auch ein lokaler Sql Server.

Da 80.000 Datensätze eigentlich nicht viel sind, wäre auch mal interessant was für eine Anbindung da anliegt.
Ohne die DB zu kennen, würde ich mal sagen läuft da irgendwas falsch bzw. zu langsam.
Den so aus dem Stehgreif klingt das nicht normal, da ich selbst mit DBs arbeite die über 2 MBit/s schon einige Datensätze über X Tabellen liefern können.
Hier synce ich teilweise Daten aus Live Datenbanken in unsere Test DB, was im Schnitt bei 5 MBit/s liegt un ca. 20-30 Tabellen und dort jeweils zwischen 20 bis teilweise 100.000+ Datenstäze in wenigen Sekunden kopiert.

Anbei könnte es auch helfen den Abgleich, falls möglich mit parallelen Abfragen zu machen.
Da hier die lokalen Dateinamen gegen die DB abgefragt werden, kann man dies auch vom Client aus mehr mehreren parallelen Abfragen machen.
Dürfte dann auch die Bandbreite besser ausnutzen, wenn diese nicht der Flaschenhals ist.

T-Virus

Hallo, danke für euren Beiträge. MMMhh, es ist so, das die DB 28 Tabellen hat. Tabelle 0 ist die Kopftabelle, mit den Kopfangaben zu Werkzeugen. Alle weiteren Tabellen sind Operationen, mit denen das Werkzeug hergestellt wird. Also es gibt somit 27 verschiedene Operationen, mit denen ein Werkzeug hergestellt werden kann. Diese Kopftabelle hat als Schlüssel den Dateinamen zu den anderen Tabellen. Wenn ich also wissen will, mit welchen Operationen Werkzeug X aus der Kopftabelle erstellt wurde, frage ich die Kopftabelle nach dem Dateinamen ab und frage alle weiteren 27 Tabellen ab.
Die Kopftabelle hat so an die 25 Felder, alle weiteren Tabellen jeweils 97 Felder. Ich denke alles in allem sind es bestimmt schon mehr als 1 Mio. Datensätze, über alle Tabellen (in XML 1,4 GByte).
Naja, und die Verbindung bei uns im ländlichen Raum ... DSL gibt es, aber auch nicht überall so schnell (kleine Dörfer ...).
Das nächste ist, da in der Firma einige Programmierer mit dem System arbeiten, wird beim Starten die komplette DB in ein DataSet geholt, damit der Traffic im Firmennetz nicht so groß ist. Das funktioniert auch gut, die Anwendung läuft sehr schnell. Zu Beginn hatte ich die Abfragen immer direkt über den SQL-Server laufen, das war aber wesentlich langsamer. Jetzt habe ich das Problem, das diese Anwendung auch bei einem User extern laufen soll (von zuhause). Wenn er die komplette DB nachhause schaufeln soll, dann geht das mal locker 30 Minuten ( ich weiss nicht genau, aber vielleicht eine 2Mbit-Verbindung).

Dann mal zu dem Zeitstempel. Den habe ich natürlich. Ich habe auch kein Problem die neuen Datensätze zu erfassen, die hinzugekommen sind. Das geht rasend schnell.
Das Problem ist, gelöschte Datensätze aus dem Produktionssystem zu erkennen. Da habe ich das Problem, das möglichst schnell durchlaufen zu lassen.

Wenn ich 80000 Datensätze aus der XML-Sicherung gegen die aktuell abgerufenen Daten aus der Kopftabelle der Produktionsdatenbank 1:1 mit dem Dateinamen überprüfen muss, ob dieser Datensatz noch existieren darf, dann dauert das ewig.

Hatte probiert das mit LinQ über GroupJoin, funzt aber nicht, das ich sofort nur die offenen Datensätze hätte.

Das einfachste wäre natürlich, ich könnte die Daten direkt vom Produktionsserver in das DataSet des externen Rechners laden. Aber T-Virus, wie geht das parallel? Gleichzeitig mehrere SqlDataAdapter oder Reader verwenden und Anfrage absetzen, und dann die verschiedenen Tabellen in das DataSet importieren?

Was denkt ihr?

Da gibt es ja noch das BulkCopy. Schön wäre das, wenn man damit direkt vom Sqlserver in ADO.Net-Tabellen laden könnte. Aber das geht wohl so nicht, oder in irgend einer anderen Form auf den entfernten Rechner kopieren ohne auf dem den SQL-Server installieren zu müssen. Oder gibt es dazu noch eine weitere Option?

Das Problem ist, gelöschte Datensätze aus dem Produktionssystem zu erkennen. Da habe ich das Problem, das möglichst schnell durchlaufen zu lassen. Dann lösche sie doch nicht sondern setze nur ein Flag dass sie gelöscht sind. Oder kopiere sie in eine andere Tabelle.

Sinnvoll wäre es die Datensätze als gelöscht zu markieren.

Wenn das nicht möglich ist, dann holt man sich vom Server nur die Liste mit den ID-Werten und vergleicht diese mit den lokalen und löscht die, die nicht mehr auf dem Server existieren.

Wenn deine XML jetzt schon 1,4 GB ist, dann dürfte dein Programm schon mehr als 2 GB RAM brauchen.
Entsprechend wäre eine Umstellung aus Sqlite doch eine Überlegung wert.

Mein vorgehen wäre dann folgendes.
1.Sqlite Vorlagen DB anlegen, die vom Aufbau her deiner aktuellen DB entspricht
2.Die Sqlite Datei auf dem Server befüllen und zippen
3.Der Client zieht sich dann die aktuelle DB gezippt und entpackt diese dann, wenn er noch keine hat
4.Client arbeitet dann auf der aktuellen Sqlite DB und gleicht diese dann zukünftig nur noch ab.

Hier macht es Sinn nicht direkt vom Client auf die DB zuzugeifen sondern wenn möglich über einen Webservice zu arbeiten.
Hier kannst du dann, abhängig wie oft sich deine Server DB ändert, auch die Sqlite Datei zwischen Speichern und immer direkt ausliefern.
Falls dies nicht machbar ist, musst du eben pro Client Request dann die DB lokal kopieren, befüllen und dem Client über den Webservice senden.

Die Logik zum abgleichen kannst du dann im Client umsetzen.
Sollte dein Problem mit den XML Dateien im GB Bereich dann auch lösen und deine Client Anwendung auch im Speicherverbrauch optimieren.

Wie gesagt, dann noch auf List<T> oder Dictionary<T, K> setzen um die Daten abzufragen, dann solltest du auch ordentlich performant arbeiten können.

T-Virus

Hallo T-Virus. SQLite-DB-Files habe ich schon mal eingelesen (von einem Maschinenhersteller, der verwaltet seine Programme in SQLite).
Ich habe jetzt aber keine ahnung, ob SQLite sehr schnell ist, aber das kannst du mir bestimmt aus deiner Erfahrung sagen.

Zum Arbeistspeicher: Das wäre denke ich nicht das Problem, die Jungs bei uns haben in der Regel alle 16 GB RAM, was aber nicht heißen soll das ich darauf keine Rücksicht nehme (muss ja nicht verschwenderisch sein).

Ich denke das Problem ist die gezippte DB-Datei von externen Rechnern zu kopieren, das wird auch einiges an Zeit brauchen. Oder denkst du das sich das Datenvolumen durch die gezippte SQLite-Datei drastisch reduziert ?? Was sagt deine Erfahrung dazu?

Was mich dann noch stört, ist das erstellen der Zip-Datei. Das soll alles möglichst automatisiert ablaufen, oder gibt es da etwas am SQL-Server, das er das zykklisch selber macht?

Wäre es möglich mit SqlBulkCopy vom SqlServer direkt in die SQLite zu kopieren ?? Evtl. wäre das auch ein brauchbarer Ansatz.
Die SQLite-Struktur der Tabellen könnte ich bestimmt automatisiert im Code erstellen lassen, falls sich die Struktur der DB-Tabellen auf dem Server ändert (was vorkommen kann).

Dann noch Stichwort Webservice. Noch nie gemacht, nur davon gehört. Ist das schwer, läuft das über ASP (ich mach vorwiegend WPF).

@Sir Rufo:
Die Idee mit den ID's klingt gut, da man die relativ schnell über die Datenverbindung (die langsam ist) abfragen kann.
Aber ich denke das der interne Abgleich auf dem externen Rechner deswegen trotzdem nicht schneller geht (muss ich noch ausprobieren).

@oehrle
Wenn deine DB sauber strukturiert ist und auch mit den entsprechenden Indizies enthalten ist, dann ist Sqlite auch schnell.

Wenn man Speicher sparen kann, sollte man das machen.

Die gezippte Sqlite Datei dürfte bei deiner Datenmenge auch um einiges kleiner sein als deine XML Datei 😃
Hier kannst du doch bei dir lokal einen Test machen, die Daten mal in einer Test Datei speichern und dann zippen.
Dann hast du ja einen Vergleich.

Die Zip Datei musst du selbst erstellen, aber ist mit .Net Bord Mitteln machbar!
SqlBulkCopy ist vom Sql Server, aber Sqlite kann das ebenfalls.
Hier musst du dann eben nur die entsprechenden Sqlite Klasse nutzen.
Diese musst du per nuget dann in dein Projekt einbinden.

Für einen Webservice bräuchtest du natürlich ein entsprechendes Web.
Ich würde hier dann auf ASP .Net aufbauen.
Direkten DB Zugang sollten die Clients eigentlich nie haben, sonst kann quasi jeder die Daten bei euch aus der DB auslesen, wenn er den ConnectionString hat.
Damit reisst ihr euch ein enormes Sicherheitsloch rein.
Ein Webservice liefert hier dann die Daten aus der DB was dem Client dann keinen direkten aber einen indirekten Zugriff auf die Daten erlaubt.

Mit einer entsprechenden extra Authentifizierung, sichert ihr euch auch direkt vor Fremdzugriffen ab!
Hier lohnt es sich, mal in das Thema einzuarbeiten!

T-Virus

Hi T-Virus, danke dir.

Habe gerade mal die DB vom Produktionsserver auf meinen SqlExpress auf mein Rechner übertragen lassen. Das ging sehr schnell, und ich denke MS nutzt da auch das BulkCopy. Von meinem rechner aus habe ich dann eine Sicherung erstellen lassen, das sind 335 MB. Dann gezippt, da waren es nur noch 37 MB.

Ich würde das gerne mit SQLite machen, und die Daten vom Sqlserver mit BulkCopy holen. Würde das mal zuerst bei mir zuhause ausprobieren, wie schnell das geht und ob das praktikabel ist.
Hast du noch Info zu SQLite (Website mit Beispielcode oder mit den Methoden und Hinweisen)?

Wahnsinn: Habe gerade mal den Abgleich nur über ID und Dateiname gemacht. Das geht sehr schnell, noch parallelisieren, dann ist es n och schneller.
Und: Habe auch mal die XML-Datei mit 1,5 GB gezippt, die hat dann gerade mal noch 42 MB. ist doch Wahnsinn, oder. da müssen wohl große Luftblasen drin sein 😉

BulkCopy lädt die Daten IN den Sql Server aber nicht raus 😃
Raus ziehst du die mit SELECT und einem DataReader/DataAdapter.

Link:
https://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki

Über Nuget kannst du dir die entsprechenden Pakete laden um direkt mit Sqlite zu arbeiten.
Die Datenbanken müsstest du dann halt selbst übertragen.
Falls möglich, kannst du dir auch mal Entity Framework Core anschauen.
Dies kommt mit den entsprechenden Providern dann auch mit dem Sql Server und Sqlite zu recht.
Dort kannst du dann alles über das Entity Framework abarbeiten lassen auch das anlegen der DB über deine Model Klassen, was dir dann einiges an Arbeit sparen dürfte.

Dann kannst du dir über eine Bridge die Daten vom Sql Server in die Sqlite Tabellen packen lassen.
Im Best fall dann sogar mit den gleichen Model Klassen aber eigenen DB Context einmal für Sql Server und einmal für Sqlite.
Wenn alles passt, kannst du deinen Code dann sauber umsetzen und kannst direkt mit Sql Server und Sqlite parallel arbeiten.

Hab letztens mal etwas mit dem Sqlite Provider getestet und die Performance ist Ideal, auch bei größeren Datenbeständen.
Da dort auch Linq zum Einsatz kommt, dürfte dir das auch nach einiger Einarbeitungszeit recht hilfreich sein.

Nachtrag:
Heir der Link zur Entity Framework Core Doku:
https://docs.efproject.net/en/latest/

Nachtrag 2:
Bulk Insert mit Sqlite:
https://www.jokecamp.com/blog/make-your-sqlite-bulk-inserts-very-fast-in-c/

Also einfach X Insert in einer Transaction scheint selbst mit 1 Mio Zeilen nur 4 Sekunden zu brauchen.
Also sollte es so ordentlich durch rennen 😃
Wenn du Entity Framework Core nimmst, wird das alles im Code umgesetzt eine Transaction drum und dann ein commit und dann hast du eigentlich den gleichen Effekt.

Nachtrag 3:
Hier mal eine Methode, die dir das kopieren dann enorm vereinfachen dürfte.

DataTable dt = new DataTable("Data");
dt.Columns.Add("Key", typeof(string));
dt.Columns.Add("Value", typeof(string));

for (int i = 0; i < 2000000; i++)
{
    DataRow row = dt.NewRow();
    row["Key"] = i.ToString();
    row["Value"] = i.ToString();
    dt.Rows.Add(row);
}

BulkInsertToSqlite(dt);

private static void BulkInsertToSqlite(DataTable dt, string tableName)
      {
         StringBuilder builder = new StringBuilder();
         builder.AppendFormat("INSERT INTO {0} (", tableName);

         foreach (DataColumn column in dt.Columns)
            builder.AppendFormat("{0},", column.ColumnName);

         // Letztes , entfernen!
         builder.Remove(builder.Length - 1, 1);

         builder.Append(") VALUES(");

         foreach (DataColumn column in dt.Columns)
            builder.AppendFormat("@{0},", column.ColumnName);

         // Letztes , entfernen!
         builder.Remove(builder.Length - 1, 1);

         builder.Append(");");
         
         using (SQLiteConnection conn = new SQLiteConnection("Data Source=test.db;Version=3;"))
         {
            conn.Open();

            using (SQLiteTransaction transaction = conn.BeginTransaction())
            {
               using (SQLiteCommand cmd = new SQLiteCommand(builder.ToString(), conn))
               {
                  foreach (DataRow row in dt.Rows)
                  {
                     foreach (DataColumn col in dt.Columns)
                     {
                        string paramName = String.Format("@{0}", col.ColumnName);
                        SQLiteParameter parameter = new SQLiteParameter(paramName, row[col]);
                        cmd.Parameters.Add(parameter);
                     }

                     cmd.ExecuteNonQuery();
                  }
               }

               transaction.Commit();
            }
         }
      }

Geht bestimmt sauberer, aber 2 Mio Einträge werden dort in wenigen Sekunden erstellt und durch die Transaktion auch flott weg geschrieben.
Die Sqltie Datei hat eben nur eine Data Tabelle mit Key/Value Spalte und ist mit 2 Mio Einträgen 84 MB groß.
Gezippt per Windows dann nur 20 MB.

Hier kannst du mal einen Testlauf mit deiner Live DB machen und schauen was dabei rum kommt.
Aber ich denke mal, wenn wir dann bei 200 bis 300 MB liegen, sind wir schon um einiges weiter als mit den 1,4 GB in XML Form 😃

Nachtrag 4:
Das Tool hilft dir bei Sqlite enorm:
http://sqlitestudio.pl/

Ist quasi ein Managment Studio Light für Sqlite.
Hilft dir beim erstellen der DB sowie dem auslesen und bearbeiten der Daten.
Gerade wenn du die Daten ohne extra Tools prüfen willst, kannst du dies damit eben per SQL Abfragen machen bzw dir auch direkt die Daten anzeigen lassen.
Dürfte dir nach dem kopieren zur Datenprüfung helfen.

T-Virus

Hast du auch schon mal probiert Tabellen zu partitionieren, und Indexes angelegt? Damit kannst du auch schon einiges an Performance raus holen?

Siehe auch hier http://get-the-solution.net/2015/04/05/datenbankoptimierung/

@mfe
Hatte ich weiter oben eigentlich schon erwähnt 😃
Das hilft zwar beim auslesen suchen der Daten aber ob es die Übertragung dann merklich beschleunigung, kann ich natürlich nicht sagen.
Wäre natürlich auch gut dies zu prüfen und zubeheben.

Aber die restlichen ansätze für ene saubere Client-Server Architektur sollte man auch mal prüfen und soweit möglich/sinnvoll umsetzen.
Wie gesagt, sollte der Client nicht direkt mit der DB kommunizieren sondern nur über einen Webservice.

T-Virus