SQL Server : Perfomanceprobleme - sinnvolle DB Verbindung gesucht

[Piro]

Moin zusammen,

mich würde mal interessieren, wie Ihr eure Anwendung in Bezug auf die SQL Verbingung programmiert bzw. einstellt.

Zitat:

Mein SQL Server: 2 CPU's 2.66GHz mit 2GB RAM

Ich habe seit ein paar Tagen Performanceprobleme mit meinem SQL 2005 Express Server.

Morgens, wenn die Anwender sich am Computer anmelden, wird mein Programm ausgeführt, welches Computerinformationen über WMI ermittelt und in die SQL DB schreibt. Das Programm braucht normalerweise nicht länger als 30 Sekunden, um die Daten zu ermitteln und in die DB zu schreiben.

Es kann vorkommen, dass 500 Anwender gleichzeit Daten in die DB schreiben. In dieser Zeit ist der SQL Server an seinem Limit und hat längere bis ganz lange Antwortzeiten. Wenn die Stoßzeit des Anmeldens vorbei ist, ist er wieder performat.

SQL Einstellungen im Programm:

Code:

KeppConnecton: True
Datenübermittlung via TADODataSet

Sollte man eventuell auf TADOCommand umsteigen und mit Parameter arbeiten und ggf. prepare verwenden? Massendaten sind es aber eigentlich nicht. Wäre schön zu wissen, wie ihr das händeln würdet bzw. was ich noch optimieren könnte.

Des weiteren habe ich Programme, die die Informationen anzeigen für eine handvoll Anwender. Würdet ihr die SQL Verbindung immer wieder trennen nach einer DB Abfrage oder einmal öffnen und KeppConnection True lassen und erst mit dem Programmende die Verbindung schließen?

Ich habe das Gefühl, dass er die Resourcen nicht frei gibt, wenn ich die Verbindung mit SQLConnection.Close beendet im Programm. Erst wenn das ganz Programm geschlossen wird.

Wäre schön zu wissen, wie Ihr solche Anforderungen löst?

Vielen Dank im voraus und hoffe auf Eure Erfahrungen.

Sven

[taveuni]

Zitat:

Zitat von Piro (Beitrag 1084010)

Es kann vorkommen, dass 500 Anwender gleichzeit Daten in die DB schreiben. In dieser Zeit ist der SQL Server an seinem Limit und hat längere bis ganz lange Antwortzeiten. Wenn die Stoßzeit des Anmeldens vorbei ist, ist er wieder performat.

Ist dann wohl wirklich ein SQL Server Performance Problem.
2005 Express ist auf eine CPU und 1GB Ram beschränkt.
Kann es sein dass dein Applikationsdesign suboptimal ist?
Bei einer Client-Server Anwendung könnte man die Daten z.b. cachen und dann gesammelt inserten.

Wie werden denn die Daten geschrieben (einzelne Inserts via Schleife, Transaktionen ..)?

Zitat:

Zitat von Piro (Beitrag 1084010)

Das Programm braucht normalerweise nicht länger als 30 Sekunden, um die Daten zu ermitteln und in die DB zu schreiben.

Wo wird "normalerweise" die Zeit gebraucht? Um die Daten zu ermitteln oder um diese in die Datenbank zu schreiben?

[jobo]

Ich hab schon lange nicht mehr mit SQL Server gearbeitet. Aus der ganzen ADO.Net Technik, Verbindungstrennung, lokale Datenhaltung, etc. pp. habe ich immer abgeleitet, dass es "best practice" ist, sprich MS das extra so designed hat, um seine Server zu schonen.

2 Gedanken unabhängig vom Server:
- WMI auszulesen könnte teilweise auch was hakelig sein, daher erst auslesen und komplett sammeln, im nächsten Schritt an den Server senden (falls es nicht schon so ist)
- Wie schreibst Du in die SQL DB? Mittels Dataset.insert, fieldbyname('field').value := xy … ?
Das wäre wahrscheinlich die ungünstigste Lösung. Geeigneter wäre einzelne, parametrierte Inserts via TADOQuery oder vielleicht auch komplette Batch Scripte, wenn es viele inserts sind.
Da weiß ich leider nicht, ob MS SQL via ADOQuery soetwas schluckt. (Vielleicht reicht es, einzelne Statements per ; zu trennen)

[EarlyBird]

wenn es um schnellen Datentransfer auf den SQL-Server geht kann ich dir die TMSLoader Komponente von Devart empfehlen.
(ist in den "SDAC Professional Edition components" enthalten)
Damit habe ich sehr gute Erfahrungen gemacht.
Für mich hat sich die Investition gelohnt.

[Bummi]

bei SQL-Express, Vorschlag
wenn der Umfang es erlaubt eine Prozedur schreiben die alle Parameter "schluckt", nachdem diese gesammelt wurden und die Connection nur so lange wie nötig offen halten

[Bernhard Geyer]

Wo genau hängt das Programm? Wie schreibst du die Daten in die DB? ich hoffe doch direkt mit parametrisierten "INSERT..."-Anweisungen bzw. SP's. Oder öffnenst du eine TADOTable und lässts erst mal die gesammte DB-Tabelle zum Client übertragen?

[Piro]

Also ich mach das so:

Ablauf:
1. WMI Daten in Records bzw. Arrays speichern (findet im Program lokal statt)
2. Records bzw. Arrays in die DB (jeweilige Tabelle) speichern
3. Programm beenden

Folgende Daten werden übertragen, die in der dazugehörigen SQL Tabelle sind:
1. Allgemeine Informationen
2. Arbeitsspeicher
3. Festplatten
4. Netzwerklaufwerke
5. Netzwerkinfos
6. Software
7. Hotfixes
8. Printer
9. Monitors

Folgende Komponenten verwende ich:
- TADOConnection
- TADODataSet
- TADOCommand (nur für das Löschen)

Folgende Prozeduren verwende ich:
z.B.: Allgemeine Infos

Delphi-Quellcode:

   DSData.Close;
   DSData.CommandText := 'Select id from computer where (computername='+QuotedStr(sysname)+') and (serial='+QuotedStr(serial)+')';
   DSData.Open;
   id := DSData.FieldByName('id').AsString;
   DSData.Close;

   if id <> '' then
   begin
     DSData.Close;
     DSData.CommandText := 'Select * from computer where id='+id;
     DSData.Open;
     DSData.Edit;
   end
   else
   begin
     DSData.Close;
     DSData.CommandText := 'Select * from computer';
     DSData.Open;
     DSData.Append;
   end;

   try
     DSData.FieldByName('computername').AsString     := sysname;
     DSData.FieldByName('domain').AsString           := BasicInfo.domain;
     DSData.FieldByName('manufacturer').AsString     := BasicInfo.manufacturer;
     DSData.FieldByName('model').AsString            := BasicInfo.model;
     DSData.FieldByName('serial').AsString           := BasicInfo.serial;
     DSData.FieldByName('assetclass').AsInteger      := BasicInfo.assetclass;
     ...
     DSData.POST;

Für die weiteren Bereich ist es immer die gleiche Procedure, nur die Daten und die Tabelle ändert sich.

Delphi-Quellcode:

for i := 0 to max_AMemory do
begin
  if AMemory[i].name = '' then
    break;

  DSData.Close;
  DSData.CommandText := 'Select * from computer_memory where sid = ' + id + ' and name = '+QuotedStr(AMemory[i].name);
  DSData.Open;

  if DSData.RecordCount > 0 then
    DSData.Edit
  else
    DSData.Append;

  DSData.FieldByName('name').AsString       := AMemory[i].name;
  DSData.FieldByName('capacity').AsString   := AMemory[i].capacity;
  DSData.FieldByName('speed').AsString      := AMemory[i].speed;
  DSData.FieldByName('lastupdate').AsString := FormatDateTime('dd.mm.yyyy hh:nn:ss',lastupdate);
  DSData.FieldByName('sid').AsString        := id;
  DSData.Post;
end;

// Alte Einträge löschen
SQLCommand.CommandText := 'delete from computer_memory where sid = ' + QuotedStr(id) + ' and lastupdate <> ' + QuotedStr(FormatDateTime('dd.mm.yyyy hh:nn:ss',lastupdate));
SQLCommand.Execute;

Zitat:

parametrisierten "INSERT..."-Anweisungen bzw. SP's

Habe ich damit wirklich einen Performancegewinn? Ich verwende das nur bei Massendaten. Da klappt es super aber ist, dass das gleiche, wenn ich immer wieder eine andere Tabelle anwählen muss. Dann brauche ich 8 x TADOCommand, oder?
Was sind SP's?

Leider kann ich nicht nur "EINEN" parametrisierten INSERT benutzen, da ich mehre Tabellen verwende. Das wäre echt gut. Aber naja. Es muss ja auch anders gehen.

Gruß, Sven

[Sir Rufo]

Bei der Anzahl von Clients würde ich die nicht direkt auf den SQL-Server loslassen (schon weil der ja ganz klein ist).

Einfache Lösung (mit Hausmitteln zu erledigen):

FTP-Server einschalten und die Clients übertragen ihre Daten an den FTP-Server.
Ein kleiner Dienst überwacht den Ordner und pustet diese Daten dann in den SQL-Server rein.

Vorteile:
- Über den Dienst steuerst du die Auslastung des Servers (evtl. nur x Einträge pro Sekunde reinpusten)
- Nur eine Stelle (der Dienst) überträgt die Daten an den SQL-Server, Änderungen am DB-Layout sind somit ohne weiteres möglich
- Dienst abschalten und den SQL-Server warten/sichern - Die Clients werden trotzdem nicht beim Abladen gestört

Ach ja, so wie du das im Moment machst (Append, Post) ist zudem auch die langsamste aller Methoden.

[Piro]

Coole Idee mit dem FTP Server & dem Dienst. Da denke ich mal mehr drauf rum.

Was wäre denn die beste Lösung ohne Append, Post? Parametrisierte INSERTs für die 8 Bereiche. Würde es gerne vorher einmal wissen bevor ich mir die Mühe mache, mein Programm anzupassen.

[Sir Rufo]

Zitat:

Zitat von Piro (Beitrag 1084137)

Parametrisierte INSERTs für die 8 Bereiche. Würde es gerne vorher einmal wissen bevor ich mir die Mühe mache, mein Programm anzupassen.

Ja ;)

Das geht aber auch in einem Rutsch:

Code:

INSERT INTO Tabelle1 ( fooa, foob ) VALUES ( :fooa, :foob );
INSERT INTO Tabelle2 ( fooc, food ) VALUES ( :fooc, :food );
...

Somit benötigst du nur 1 Query, wo du bislang mehrere hast

Ach ja ... wenn du Parameter öfter verwenden möchtest, dann so

Code:

SET @fooa = :fooa;
SET @foob = :foob;
SET @fooc = :fooc;
INSERT INTO Tabelle1 ( fooa, foob ) VALUES ( @fooa, @foob );
INSERT INTO Tabelle2 ( fooa, fooc ) VALUES ( @fooa, @fooc );
...

Und wenn man sich ganz oft wiederholt (z.B. die Liste der installierten Software) und die Tabelle so aussieht:

Code:

Domain
Computer
IP-Adresse
Software

dann macht es durchaus Sinn, nur die Spalte Software in eine temp. Tabelle auf den SQL-Server zu schieben und die dann an die Ziel-Tabelle anzuhängen.

Code:

INSERT INTO Software (Domain, Computer, IP, Software )
SELECT :Domain, :Computer, :IP, Software
FROM tempSoftware

Das spart enorm Bandbreite, weil die Informationen zu Domain, Computer, IP-Adresse nur einmal übertragen werden und nicht x-mal

[Piro]

Ich probiere gerade herum. Was ist denn besser geeignet TADOCommand oder TADOQuery? Wo ist denn da der Unterschied?

Würde das dann so machen für eine Tabelle am Beispiel der Hotfixes.

Delphi-Quellcode:

SQLCommand.Parameters.Clear;
SQLCommand.CommandText := 'INSERT Into hotfixes (kbnumber,type,installedon,installedby, description, lastupdate,sid) ' +
                          'VALUES (:kbnumber,:type,:installedon,:installedby,:description,:lastupdate,:sid);';
SQLCommand.Prepared;

// Verarbeitung: Hotfixes
for i := 0 to max_AHotfixes do
begin
  if AHotfixes[i].kbnumber = '' then
    break;

  SQLCommand.Parameters.ParamByName('kbnumber').Value    := AHotfixes[i].kbnumber;
  SQLCommand.Parameters.ParamByName('type').Value        := AHotfixes[i].typ;
  SQLCommand.Parameters.ParamByName('installedon').Value := AHotfixes[i].installedon;
  SQLCommand.Parameters.ParamByName('installedby').Value := AHotfixes[i].installedby;
  SQLCommand.Parameters.ParamByName('description').Value := AHotfixes[i].description;
  SQLCommand.Parameters.ParamByName('lastupdate').Value  := FormatDateTime('dd.mm.yyyy hh:nn:ss',lastupdate);
  SQLCommand.Parameters.ParamByName('sid').Value         := id;
  SQLCommand.Execute;
end;

// Verarbeitung: Software
SQLCommand.Parameters.Clear;
SQLCommand.CommandText := 'INSERT Into software (name,version,installedon,installedby,lastupdate,sid) ' +
                          'VALUES (:name,:version,:installedon,:lastupdate,:sid);';
SQLCommand.Prepared;

for i := 0 to max_ASoftware do
begin
  if ASoftware[i].name = '' then
    break;

  SQLCommand.Parameters.ParamByName('name').Value        := ASoftware[i].name;
  SQLCommand.Parameters.ParamByName('version').Value     := ASoftware[i].version;
  SQLCommand.Parameters.ParamByName('installedon').Value := ASoftware[i].installedon;
  SQLCommand.Parameters.ParamByName('lastupdate').Value  := FormatDateTime('dd.mm.yyyy hh:nn:ss',lastupdate);
  SQLCommand.Parameters.ParamByName('sid').Value         := id;
  SQLCommand.Execute;
end;

// und so weiter

Würde das so Sinn machen oder sollte man 8 TADOCommands am Anfang vorbereiten und dann darauf zugreifen? Ein weiterer Punkt wäre, dass ich prüfen muss, ob es den Eintrag schon gibt. Abhängig davon mache ich ein INSERT oder ein UPDATE. Muss ich dann nicht doch mit TADODataSet arbeiten.

Delphi-Quellcode:

// Check, ob der Datensatz schon vorhanden ist
DSData.Close;
DSData.CommandText := 'Select * from computer_hotfixes where sid = ' + id + ' and kbnumber = '+QuotedStr(AHotfixes[i].kbnumber);
DSData.Open;

if DSData.RecordCount = 0 then
  // INSERT verwenden
else
  // UPDATE verwenden

DSData.Close;

Oder gibt es noch bessere Variante?

Ich könnte ja auch nur INSERTs machen und dann die alten Daten löschen. Habe nur bedenken, dass das eventuell nicht so gut für die incrementelle ID ist.

Delphi-Quellcode:

// Alte Einträge löschen
SQLCommand.Parameters.Clear;
SQLCommand.CommandText := 'delete from computer_hotfixes where sid = ' + QuotedStr(id) + ' and lastupdate <> ' + QuotedStr(FormatDateTime('dd.mm.yyyy hh:nn:ss',lastupdate));
SQLCommand.Execute;

Danke schon mal für Eure großartige Hilfe, mir dieses Thema etwas näher zu bringen.

[Sir Rufo]

Dafür erstellt man sich eine SP (Stored Procedure) der man die Werte übergibt und die SP veranlasst dann entweder ein INSERT oder UPDATE

[Piro]

Sorry für die ganze Fragerein aber ich will das gerne genau verstehen.

Wie würde denn so eine Store Procedure aussehen? Ich schreibe sie doch im Delphi Programm oder?

[Sir Rufo]

Zitat:

Zitat von Piro (Beitrag 1084261)

Sorry für die ganze Fragerein aber ich will das gerne genau verstehen.

Wie würde denn so eine Store Procedure aussehen? Ich schreibe sie doch im Delphi Programm oder?

Nein, der Begriff Stored Procedure kommt aus der SQL-Welt und erklärt sich eigentlich von selbst :mrgreen:
Eine gespeicherte Abfolge von SQL-Befehlen - und gespeichert auf dem SQL-Server ;)

Aber bevor ich mir hier die Finger wund tippe lassen wir doch MS selber zu Wort kommen
WebCast: MSSQL - Stored Procedures (deutsch)
(Kanne Kaffee nicht vergessen, ist nicht so peppig wie der Daniel)

[Piro]

Vielen Danke Sir Rufo für deine Geduld. Dann werde ich mir das mal anschauen.

Schönen Abend noch. Und danke an alle. Ihr habt mir sehr geholfen.