verwendetes Datenbanksystem: <MSSQL 2000>
Hallo zusammen,
ich habe das Problem, dass ich bei einer Abfrage, die ca. 140.000 Datensätze betrifft, echte Performance Probleme beim Auslesen innerhalb der Read() Methode habe. Die Daten werden über eine Stored Procedure geladen und im Command korrekt mit Parametern zusammengebaut. In der Read() Methode werden die Datensätze in einen eigenen Typen geladen und als List<> zurückgegeben. Die Read Methode braucht für den Durchlauf der 140.000 Datensätze in etwa 5 Minuten. Das kommt mir etwas lang vor. Was kann ich tun um dies zu tweaken? Hier der entsprechende Code:
public List<LoggedFile> GetFileListFromDb(string projectName)
{
List<LoggedFile> files = null;
SqlCommand com = new SqlCommand();
SqlDataReader reader = null;
try
{
this.SetConnection();
com.CommandType = CommandType.StoredProcedure;
com.Connection = _cnn;
com.CommandText = "dbo.spHnSelectFilesByProjectName";
com.Parameters.Add(new SqlParameter(
"@Hostname",
SqlDbType.NVarChar,
50,
ParameterDirection.Input,
false,
0,
0,
null,
DataRowVersion.Current,
this._hostName));
com.Parameters.Add(new SqlParameter(
"@ProjectName",
SqlDbType.NVarChar,
255,
ParameterDirection.Input,
true,
0,
0,
"ProjectName",
DataRowVersion.Current,
projectName));
reader = com.ExecuteReader(CommandBehavior.SingleResult);
// Daten mithilfe der Reader Methode abgreifen
files = this.ReadData(ref reader);
reader.Close();
}
finally
{
reader.Dispose();
}
return files;
}
private List<LoggedFile> ReadData(ref SqlDataReader reader)
{
List<LoggedFile> files = new List<LoggedFile>();
while (reader.Read())
{
// Init-Daten holen
Guid id = reader.GetGuid(reader.GetOrdinal("ID"));
DateTime recCreated = reader.GetDateTime(reader.GetOrdinal("RecordCreatedOn"));
DateTime recModified = reader.GetDateTime(reader.GetOrdinal("RecordModifiedOn"));
bool deleted = reader.GetBoolean(reader.GetOrdinal("_Deleted"));
bool locked = reader.GetBoolean(reader.GetOrdinal("_Locked"));
// Neues LoggedFile anlegen
LoggedFile file = new LoggedFile(id, recCreated, recModified, deleted, locked);
// Daten übernehmen
file.CreationTimeUtc = reader.GetDateTime(reader.GetOrdinal("CreationTimeUtc"));
file.Description = reader.GetString(reader.GetOrdinal("Description"));
file.DirectoryName = reader.GetString(reader.GetOrdinal("DirectoryName"));
file.Extension = reader.GetString(reader.GetOrdinal("Extension"));
file.FileAttributes = (FileAttributes)reader.GetInt32(reader.GetOrdinal("FileAttributes"));
file.FullName = reader.GetString(reader.GetOrdinal("FullName"));
file.IsReadOnly = reader.GetBoolean(reader.GetOrdinal("IsReadOnly"));
file.LastAccessTimeUtc = reader.GetDateTime(reader.GetOrdinal("LastAccessTimeUtc"));
file.LastWriteTimeUtc = reader.GetDateTime(reader.GetOrdinal("LastWriteTimeUtc"));
file.Length = reader.GetInt64(reader.GetOrdinal("Length"));
file.Name = reader.GetString(reader.GetOrdinal("Name"));
files.Add(file);
}
return files;
}
Danke für den Tipp. Werde ich umsetzen. Dennoch glaube ich nicht, dass das der Flaschenhals ist. Ich habe zu Testzwecken mal in der Read-Methode einfach einen int hochgezählt. Das hat ähnlich lange gedauert.
Wenn ich mir den Code so anschaue sind da einige fehler drin.
Schau mal genau nach was Ref macht, und wieso man das mit Objecten selten braucht.
Premature Optimization ist zwar selten sinnvoll, hier aber schon. Hol dir die Spaltenpositionen ausserhalb der Schleife.
Wenn du eine Read() leerschleife machst und das dann langsam ist, hat deine DB ein Problem.
this.SetConnection(); Nur Vorsichtshalber [Artikel] Ressourcen schonen - Datenbanken richtig öffnen und schließen
Wenn ich mir den Code so anschaue sind da einige fehler drin.
2. Premature Optimization ist zwar selten sinnvoll, hier aber schon. Hol dir die Spaltenpositionen ausserhalb der Schleife.
Wird umgesetzt.
- Wenn du eine Read() leerschleife machst und das dann langsam ist, hat deine DB ein Problem.
Dieses Problem tritt in der Tat auf. Ich hab mir eben mal die Tabellendefinition angeschaut. In der Tabelle liegen ca. 1.3 Mio Datensätze und die Stored Procedure greift die Daten in der Where Klausel über den Projectnamen (string) ab. Diese Spalte besitzt keinen Index. Wird es eventuell das Problem schon lösen hier einen Index drauf zu setzen? Was genau hat es mit den Vollindexen auf sich? Bin leider kein MSSQL Spezi. Was würdest du empfehlen?
Danke bis hier schonmal für die hilfreichen Denkanstöße!
Und noch eine kleine Stilsache:
finally { reader.Dispose(); }
Guck dir mal die using-Anweisung (C#-Referenz) an. Das macht das freigeben von IDisposable-Objekten schreibtechnisch um einiges leichter. Btw: SqlCommand und SqlDataReader sind auch IDisposable.
Hallo zusammen,
nach längerem rumprobieren habe ich das Problem leider immernoch nicht lösen können.
Folgende Rahmenbedingungen habe ich:
SELECT [ID],
[Name],
[DirectoryName],
[FullName],
[Extension],
[Length],
[FileAttributes],
[IsReadOnly],
[CreationTimeUtc],
[LastAccessTimeUtc],
[LastWriteTimeUtc],
[RecordCreatedOn],
[RecordModifiedOn],
[PrjIdIntranet],
[ProjectName],
[SequenceNumber],
[SceneNumber],
[FileNumber],
[Description],
[_Deleted],
[_Locked]
FROM [ProjectSynchronizerDB].[dbo].[Files_VELOCITY-02]
WHERE [ProjectName] = @ProjectName
AND [_Deleted] = 0
Alle Anpassungen haben bisher keinerlei Verbesserung gezeigt. Die 5 Minuten für die Read() Methode kommen mir sehr lang vor. Hat noch jemand Ideen wo ich ansetzen könnte um das Ganze zu tweaken?
Hast du für den MSSQL 2000 alle aktuellen Service Packs installiert? Dauert die Abfrage auch so lange, wenn du diese im Management Studio ausführst?
"Jedes Ding hat drei Seiten, eine positive, eine negative und eine komische." (Karl Valentin)
Ja, mit dem SP4 ist der SQL 2000 auf dem neusten Stand.
Erstaunlich ist tatsächlich, dass eine Abfrage im Management Studio recht flott geht. Ich bin mir zwar nicht ganz sicher, wie das gehandhabt wird, denn es macht den Anschein als würde immer nur der sichtbare Auschnitt der Daten im Management Studio geladen werden und wenn man scrollt fühlt es sich so an, als würde nachgeladen werden. Kann mich aber auch täuschen. Auf jedenfall sind die Spalten nach wenigen Sekunden sichtbar, bei genau der selben Abfrage. Auch ein COUNT auf die betroffenen Spalten ist nach 5-10 Sek. da. Liegt das Problem dann vielleicht eher am DataReader?
huhu,
hast du nen Clustered Index drauf?
Was sagt der Server, wenn du die Abfrage ausführst? (hoher Datendurchsatz auf der Platte, Server sowieso schon immer mit 130% Last etc.)
😃
xynratron
Herr, schmeiss Hirn vom Himmel - Autsch!
Die Erfahrung zeigt immer wieder, dass viele Probleme sich in Luft auslösen, wenn man sich den nötigen Abstand bzw. Schlaf gönnt.
- Ich hab auch einen FullTextIndex auf dem Projektnamen erzeugt
Da du nur einen einfach Vergleich mit "=" durchführst, brauchst du keinen Volltextindex, ein ganz normaler Index würde reichen.
Im Prinzip könntest du die Projektbeschreibung aber auch in eine andere Tabelle auslagern, dann hast du in der großen Tabelle nur einen Fremdschlüssel auf das jeweilige Projekt. Damit ersparst du dir den Stringvergleich bei jedem Datensatz, und stattdessen wird nur ein Integer verglichen. Das sollte schon eine Menge Performance mit sich bringen.
Weeks of programming can save you hours of planning
huhu,
jetzt war ich zu spät^^
Ein count(*) ist immer schneller, weil er keine Daten "lesen" muss. wobei hier 5-10 sek. schon langsam sind.
die Daten im Management-Studio werden auch einfach mit einem DataReader geholt, hier interessiert eigentlich die Zeit, wenn alle Daten gelesen wurden, also die Query als "Fertiggestellt" gilt. (unten rechts in der Statusleiste). Eventuell auch mal einfach den SQLProfiler mitlaufen lassen und gucken was der sagt.
Der DataReader kann ja schon gelesen werden, bevor alle Daten da sind. Du bekommst einfach, sobald die Query die ersten Treffer hat, auch schon die ersten Daten zurück. Deswegen hängt dein Programm auch nicht bei "ExecuteReader()" sondern erst in der Read()-Schleife. Das bedeutet, dass die Daten sehr langsam in dein Programm fliessen.
😃
Xynratron
Herr, schmeiss Hirn vom Himmel - Autsch!
Die Erfahrung zeigt immer wieder, dass viele Probleme sich in Luft auslösen, wenn man sich den nötigen Abstand bzw. Schlaf gönnt.
huhu,
Im Prinzip könntest du die Projektbeschreibung aber auch in eine andere Tabelle auslagern, dann hast du in der großen Tabelle nur einen Fremdschlüssel auf das jeweilige Projekt. Damit ersparst du dir den Stringvergleich bei jedem Datensatz, und stattdessen wird nur ein Integer verglichen. Das sollte schon eine Menge Performance mit sich bringen.
Das wäre die richtige Richtung zur Normalisierung, aber bei der Performace dürfte sich hier nicht viel tun. Ein string als Index wir nicht als Liste von Strings abgelegt, sondern als Baum.
😃
Xynratron
Herr, schmeiss Hirn vom Himmel - Autsch!
Die Erfahrung zeigt immer wieder, dass viele Probleme sich in Luft auslösen, wenn man sich den nötigen Abstand bzw. Schlaf gönnt.
huhu,
die Daten im Management-Studio werden auch einfach mit einem DataReader geholt, hier interessiert eigentlich die Zeit, wenn alle Daten gelesen wurden, also die Query als "Fertiggestellt" gilt. (unten rechts in der Statusleiste). Eventuell auch mal einfach den SQLProfiler mitlaufen lassen und gucken was der sagt.
Sowas in der Richtung hatte ich vermutet. Ich habe daher mal den SQL Query Analyzer aus dem Management Studio bemüht. Dieser zeigt keine Daten an, solange die Query nicht fertig ist und siehe da: Auch hier wieder 5 Minuten für die Abfrage.
Was sagt der Server, wenn du die Abfrage ausführst? (hoher Datendurchsatz auf der Platte, Server sowieso schon immer mit 130% Last etc.)
2-5% CPU Auslastung zeigt der Taskmanager während der Ausführung der Query an.
hast du nen Clustered Index drauf?
Nein, nicht das ich wüßte. Was genau hat es denn damit auf sich und wo lege ich diesen fest?
Der Einwand von MrSparkle ist berechtigt, würde aber eine starke Änderung mit sich ziehen. Mal sehen, ob dafür mal Zeit ist. Und auch danke für den Hinweis mit dem FullIndex, kann den ja nun wieder entfernen.
Ist die App und die DB auf dem selben Rechner? Bzw. wie schaut die Verbindung zwischen den App und DB aus? Evt. liegts an einer überlasteten Netzwerkverbindung?
Lg XXX
@xxxprod
Wie ich in dem Post davor schon schrieb, tritt das inperformante Verhalten auch bei einer Abfrage direkt auf dem Server auf. Es kann also nur noch an der Tabelle / DB liegen. Was aber merkwürdig ist, da die Tabelle mit einem Index ausgestattet ist, welcher von der WHERE Klausel betroffen ist (siehe Query weiter oben). Nicht mal Joins werden benutzt.
2-5% CPU Auslastung zeigt der Taskmanager während der Ausführung der Query an.
Der Einwand von MrSparkle ist berechtigt
Wenn du nen Index auf der varchar-Spalte hast, dann macht das keinen Unterschied. Da du nur sehr wenig CPU-Last hast, bedeutet das, dass wenig zu rechnen ist (=keine Stringvergleiche) denn ohne Index würde hier deine CPU-Last schnell auf 100% steigen.
Ich tippe jetzt wirklich drauf, dass dein Flaschenhals der Datendurchsatz zur Festplatte ist. Vermutlich hat der Server deutlich zu wenig Ram, und muss sich den Index und dann die Daten immer häppchenweise von der Platte laden.
Im günstigsten Fall ist der Durchsatz von/zur Festplatte sehr gering und die DB (oder wenigstens die Indizes) liegt praktisch vollständig im Arbeitsspeicher. Dann merkt man auch, dass die CPU-Last hochgeht und der SQL-Server das arbeiten anfängt.
Nachdem es im SQL Query Analyzer genauso langsam ist, liegt es auch definitiv nicht an deiner App.
Zum Clustered Index: Man kann (und sollte unbedingt) einen Index pro Tablle als Clustered definieren. Im Normalfall ist das dann automatisch der Primary Key. Der Clustered Index spiegelt die physikalische sortierung der Tabelle wieder. Manchmal kann es hier sinnvoll sein, das zu ändern, aber meistens nicht.
😃
xynratron
Herr, schmeiss Hirn vom Himmel - Autsch!
Die Erfahrung zeigt immer wieder, dass viele Probleme sich in Luft auslösen, wenn man sich den nötigen Abstand bzw. Schlaf gönnt.
@Xynratron
Zu der Überzeugung komme ich nun langsam auch. Nun habe ich aber beim rumprobieren grad noch ne Erkenntnis gewonnen:
Starte ich eine Query die sowohl im SELECT wie auch in der WHERE Statement eine Spalte hat die ein Index besitzt, dann ist die Abfrage plötzlich sehr schnell ~3 Sek. Die Query sieht dann in etwa so aus:
SELECT ID
FROM [Files_VELOCITY-02]
WHERE (ProjectName='10_104_eiskalt')
ID als Primary Key hat automatisch einen Index und ProjectName habe ich hinzugefügt. Sobald ich aber nun im SELECT eine Spalte hinzufüge die keinen Index hat, wird kriechend langsam:
SELECT ID, FullName
FROM [Files_VELOCITY-02]
WHERE (ProjectName='10_104_eiskalt')
Heißt das ich muss allen Spalten die für mich relevant sind einen Index verpassen? Kommt mir auch komisch vor, da ich meine mal gelesen zu haben, dass man dies nur bei Spalten des WHERE Statements machen sollte, nicht bei Spalten aus dem SELECT.
Hier die Tabellendefinition und zusätzlich die gesetzten Indexe. Wie man sieht ist FullName nicht vom Typ Text. Selbes Verhalten tritt auch bei anderen Spalten auf wie z.B. FileAttributes.
Aber Description ist vom typ ntext. Probier mal die Abfrage ohne die Textspalte auszuführen? Oder zusätzlich einmal die Abfrage nur mit der ID-Spalte im Select um zu sehen obs an der Dauer was ändert.
Lg XXX
Ja, wie beschrieben ein merklicher Unterschied:
Ein SELECT ID = 2-3 Sek
Ein SELECT ID, FileAttributes = 5 Minuten
huhu,
Das "Gruppiert" bei den Indizes ist ein "Clustered Index" - nur zur Erklärung.
Wie ist die Maschine ausgestattet und kannst du das mal auf eine aktuelle Workstation portieren? (SQL-Server installieren, Backup einspielen, Abfrage 5 mal ausführen)
FieldAttributes ist ja "nur" ein Int. Kannst du mal eine "Select top 10" gegen einen select top "1000" testen - und lass mal den Profiler nebenbei laufen; zusätzlich im Query Analyser die Kosten und Dauer mitloggen lassen. (Profiler gabs auch schon bei 2000; vielleicht gabs hier auch schon die Duration, ansonsten Start und End protokollieren und den Unterschied nehmen)
🙂
xynratron
Herr, schmeiss Hirn vom Himmel - Autsch!
Die Erfahrung zeigt immer wieder, dass viele Probleme sich in Luft auslösen, wenn man sich den nötigen Abstand bzw. Schlaf gönnt.
So, noch mal ein kleines Feedback. Ich hab Zwischenzeitlich die DB mal auf einen anderen Server portiert, auf diesem lief MSSQL 2008 Express. Auch hier traten die beschriebenen Phänomene auch auf. Mir ist dann allerdings im Kontextmenü der Indexe des 2008er Servers aufgefallen, dass es dort einen Punkt gibt "Alle neu organisieren". Diesen Defragmentierungsvorgang hab ich mal durchgeführt. Der Index der ID hatte vorher den Faktor 90 und nach dem Defragmentieren ca 0,5. Das hatte zur Folge, dass die besagte Query nun statt mit 5 Minuten ca ~30 Sek. benötigt.
Auf dem MSSQL 2000 habe ich den Punkt "Alle neu organisieren" zwar nicht gefunden, aber ich glaube die Checkbox "Index auffüllen" beim bearbeiten der Indexe hatte einen ähnlichen Effekt.
Ich konnte somit die Abfrage ca. um den Faktor 10 verbessern. 30 Sek. ist zwar meiner Ansicht nach immer noch viel zu langsam, bei einer so simplen Query, aber für mich erstmal annehmbar.
Ich danke an dieser Stelle allen, die ein Feedback gegeben haben und mir so geholfen haben!