Laden...

SqlDataReader: Inperformante Read()-Methode bei großen Datenmengen

Erstellt von bbb vor 13 Jahren Letzter Beitrag vor 13 Jahren 11.157 Views
B
bbb Themenstarter:in
72 Beiträge seit 2009
vor 13 Jahren
SqlDataReader: Inperformante Read()-Methode bei großen Datenmengen

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;
}

Gelöschter Account
vor 13 Jahren

Initialisiere die Liste mit mindestens der benötigten Größe (also new List<..>(150000))

B
bbb Themenstarter:in
72 Beiträge seit 2009
vor 13 Jahren

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.

Gelöschter Account
vor 13 Jahren

Mach doch mal ein Test mit einem Performance tool.

F
10.010 Beiträge seit 2004
vor 13 Jahren

Wenn ich mir den Code so anschaue sind da einige fehler drin.

  1. Schau mal genau nach was Ref macht, und wieso man das mit Objecten selten braucht.

  2. Premature Optimization ist zwar selten sinnvoll, hier aber schon. Hol dir die Spaltenpositionen ausserhalb der Schleife.

  3. Wenn du eine Read() leerschleife machst und das dann langsam ist, hat deine DB ein Problem.

  4. this.SetConnection(); Nur Vorsichtshalber [Artikel] Ressourcen schonen - Datenbanken richtig öffnen und schließen

B
bbb Themenstarter:in
72 Beiträge seit 2009
vor 13 Jahren

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.

  1. 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!

Gelöschter Account
vor 13 Jahren

Wird es eventuell das Problem schon lösen hier einen Index drauf zu setzen?

Ja. Unbedingt bei so einer großen Tabelle einen Index setzen... wenn nicht sogar mehrere.

2.891 Beiträge seit 2004
vor 13 Jahren

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.

B
bbb Themenstarter:in
72 Beiträge seit 2009
vor 13 Jahren

Hallo zusammen,

nach längerem rumprobieren habe ich das Problem leider immernoch nicht lösen können.

Folgende Rahmenbedingungen habe ich:

  • Tabelle mit ca. 1.4 Mio Datensätzen
  • Abfrage die ca 140.000 Datensätze betrifft
  • Wie von FZelle vorgeschlagen habe ich eine leere Read() Methode eingebaut, die beim Durchlauf 5 Minuten braucht.
  • Die Stored Procedure ist überschaubar und enthält keine Joins:

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

  • Index ist auf die Felder ProjectName und _Deleted gesetzt, sowie der Primary Key der ID
  • Ich hab auch einen FullTextIndex auf dem Projektnamen erzeugt

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?

3.511 Beiträge seit 2005
vor 13 Jahren

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)

B
bbb Themenstarter:in
72 Beiträge seit 2009
vor 13 Jahren

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?

X
1.177 Beiträge seit 2006
vor 13 Jahren

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.

5.657 Beiträge seit 2006
vor 13 Jahren
  • 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

X
1.177 Beiträge seit 2006
vor 13 Jahren

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.

X
1.177 Beiträge seit 2006
vor 13 Jahren

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.

B
bbb Themenstarter:in
72 Beiträge seit 2009
vor 13 Jahren

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.

1.378 Beiträge seit 2006
vor 13 Jahren

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

B
bbb Themenstarter:in
72 Beiträge seit 2009
vor 13 Jahren

@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.

X
1.177 Beiträge seit 2006
vor 13 Jahren

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.

B
bbb Themenstarter:in
72 Beiträge seit 2009
vor 13 Jahren

@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.

Gelöschter Account
vor 13 Jahren

FullName ist nicht zufälligerweise vom Typ "Text" ?

Zeig mal die Tabellendefinition.

B
bbb Themenstarter:in
72 Beiträge seit 2009
vor 13 Jahren

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.

1.378 Beiträge seit 2006
vor 13 Jahren

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

B
bbb Themenstarter:in
72 Beiträge seit 2009
vor 13 Jahren

Ja, wie beschrieben ein merklicher Unterschied:

Ein SELECT ID = 2-3 Sek
Ein SELECT ID, FileAttributes = 5 Minuten

X
1.177 Beiträge seit 2006
vor 13 Jahren

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.

B
bbb Themenstarter:in
72 Beiträge seit 2009
vor 13 Jahren

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!