Willkommen auf myCSharp.de! Anmelden | kostenlos registrieren
 | Suche | FAQ

Hauptmenü
myCSharp.de
» Startseite
» Forum
» Suche
» Regeln
» Wie poste ich richtig?

Mitglieder
» Liste / Suche
» Wer ist online?

Ressourcen
» FAQ
» Artikel
» C#-Snippets
» Jobbörse
» Microsoft Docs

Team
» Kontakt
» Cookies
» Spenden
» Datenschutz
» Impressum

  • »
  • Community
  • |
  • Diskussionsforum
SqlDataReader: Inperformante Read()-Methode bei großen Datenmengen
bbb
myCSharp.de - Member



Dabei seit:
Beiträge: 72

Themenstarter:

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

beantworten | zitieren | melden

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;
}
private Nachricht | Beiträge des Benutzers
Gelöschter Benutzer

beantworten | zitieren | melden

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



Dabei seit:
Beiträge: 72

Themenstarter:

beantworten | zitieren | melden

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.
private Nachricht | Beiträge des Benutzers
Gelöschter Benutzer

beantworten | zitieren | melden

Mach doch mal ein Test mit einem Performance tool.
FZelle
myCSharp.de - Experte



Dabei seit:
Beiträge: 9.999

beantworten | zitieren | melden

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
private Nachricht | Beiträge des Benutzers
bbb
myCSharp.de - Member



Dabei seit:
Beiträge: 72

Themenstarter:

beantworten | zitieren | melden

Zitat von FZelle
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.
Zitat
3. 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!
private Nachricht | Beiträge des Benutzers
Gelöschter Benutzer

beantworten | zitieren | melden

Zitat
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.
dN!3L
myCSharp.de - Experte

Avatar #avatar-2985.png


Dabei seit:
Beiträge: 2.891

beantworten | zitieren | melden

Und noch eine kleine Stilsache:
Zitat von bbb
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.
private Nachricht | Beiträge des Benutzers
bbb
myCSharp.de - Member



Dabei seit:
Beiträge: 72

Themenstarter:

beantworten | zitieren | melden

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?
Dieser Beitrag wurde 1 mal editiert, zum letzten Mal von bbb am .
private Nachricht | Beiträge des Benutzers
Khalid
myCSharp.de - Experte

Avatar #avatar-2534.gif


Dabei seit:
Beiträge: 3.511
Herkunft: Hannover

beantworten | zitieren | melden

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)
private Nachricht | Beiträge des Benutzers
bbb
myCSharp.de - Member



Dabei seit:
Beiträge: 72

Themenstarter:

beantworten | zitieren | melden

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?
private Nachricht | Beiträge des Benutzers
Xynratron
myCSharp.de - Member



Dabei seit:
Beiträge: 1.177

beantworten | zitieren | melden

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!
Zitat von herbivore
Die Erfahrung zeigt immer wieder, dass viele Probleme sich in Luft auslösen, wenn man sich den nötigen Abstand bzw. Schlaf gönnt.
private Nachricht | Beiträge des Benutzers
MrSparkle
myCSharp.de - Team

Avatar #avatar-2159.gif


Dabei seit:
Beiträge: 5.655
Herkunft: Leipzig

beantworten | zitieren | melden

Zitat von bbb
- 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
private Nachricht | Beiträge des Benutzers
Xynratron
myCSharp.de - Member



Dabei seit:
Beiträge: 1.177

beantworten | zitieren | melden

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!
Zitat von herbivore
Die Erfahrung zeigt immer wieder, dass viele Probleme sich in Luft auslösen, wenn man sich den nötigen Abstand bzw. Schlaf gönnt.
private Nachricht | Beiträge des Benutzers
Xynratron
myCSharp.de - Member



Dabei seit:
Beiträge: 1.177

beantworten | zitieren | melden

huhu,
Zitat von MrSparkle
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!
Zitat von herbivore
Die Erfahrung zeigt immer wieder, dass viele Probleme sich in Luft auslösen, wenn man sich den nötigen Abstand bzw. Schlaf gönnt.
private Nachricht | Beiträge des Benutzers
bbb
myCSharp.de - Member



Dabei seit:
Beiträge: 72

Themenstarter:

beantworten | zitieren | melden

Zitat von Xynratron
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.
Zitat
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.
Zitat
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.
private Nachricht | Beiträge des Benutzers
xxxprod
myCSharp.de - Experte

Avatar #avatar-2329.gif


Dabei seit:
Beiträge: 1.378
Herkunft: Österreich\Wien

beantworten | zitieren | melden

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
private Nachricht | Beiträge des Benutzers
bbb
myCSharp.de - Member



Dabei seit:
Beiträge: 72

Themenstarter:

beantworten | zitieren | melden

@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.
private Nachricht | Beiträge des Benutzers
Xynratron
myCSharp.de - Member



Dabei seit:
Beiträge: 1.177

beantworten | zitieren | melden

Zitat von bbb
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!
Zitat von herbivore
Die Erfahrung zeigt immer wieder, dass viele Probleme sich in Luft auslösen, wenn man sich den nötigen Abstand bzw. Schlaf gönnt.
private Nachricht | Beiträge des Benutzers
bbb
myCSharp.de - Member



Dabei seit:
Beiträge: 72

Themenstarter:

beantworten | zitieren | melden

@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.
Dieser Beitrag wurde 1 mal editiert, zum letzten Mal von bbb am .
private Nachricht | Beiträge des Benutzers
Gelöschter Benutzer

beantworten | zitieren | melden

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

Zeig mal die Tabellendefinition.
bbb
myCSharp.de - Member



Dabei seit:
Beiträge: 72

Themenstarter:

beantworten | zitieren | melden

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.
Attachments
private Nachricht | Beiträge des Benutzers
xxxprod
myCSharp.de - Experte

Avatar #avatar-2329.gif


Dabei seit:
Beiträge: 1.378
Herkunft: Österreich\Wien

beantworten | zitieren | melden

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
private Nachricht | Beiträge des Benutzers
bbb
myCSharp.de - Member



Dabei seit:
Beiträge: 72

Themenstarter:

beantworten | zitieren | melden

Ja, wie beschrieben ein merklicher Unterschied:

Ein SELECT ID = 2-3 Sek
Ein SELECT ID, FileAttributes = 5 Minuten
private Nachricht | Beiträge des Benutzers
Xynratron
myCSharp.de - Member



Dabei seit:
Beiträge: 1.177

beantworten | zitieren | melden

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
Dieser Beitrag wurde 1 mal editiert, zum letzten Mal von Xynratron am .
Herr, schmeiss Hirn vom Himmel - Autsch!
Zitat von herbivore
Die Erfahrung zeigt immer wieder, dass viele Probleme sich in Luft auslösen, wenn man sich den nötigen Abstand bzw. Schlaf gönnt.
private Nachricht | Beiträge des Benutzers
bbb
myCSharp.de - Member



Dabei seit:
Beiträge: 72

Themenstarter:

beantworten | zitieren | melden

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!
private Nachricht | Beiträge des Benutzers